PGA Tuning - Part 1 & PGA Tuning - Part 2
PGA Tuning - Part 1
Program Global Area or PGA as it is called globally contains
data and control information about the server process.
Two components of 2 PGA are.
1.
Stack Space and
2.
User Global Area.
Stack Space: It holds the bind variables, plsql array,etc.
User Global Area: UGA
contains the following.
a.
Session information such as logon details and
other information by the database session.
b.
SQL work areas : used for sorting, hash
operations, etc.
c.
Private SQL area : Contains open/closed cursors
and cursor state information for open cursors. Eg. The number of rows retrieved
so far in a full table scan.
For complex queries, work areas in UGA are required to
perform memory intensive operations. Few
examples are below.
Ø
A sort operator uses the sort area to sort a set
of rows.
Ø
A hash join operator uses a hash area to build
the hash table from its left output., whereas a bitmap merge uses the bitmap
merge area to merge data received from scans of multiple bitmap indexes.
Memory intensive operations which makes use of PGA are .
a.
Sort based operations (order by, group by,
rollup, distincts, etc)
b.
Hash join
c.
Bitmap related operations
d.
Write buffers used by bulk load operations
(direct path load).
How PGA size
affects the performance of the database.
In PGA, we have work area component, if the amount of
data to be processed fits in the work area, considering having enough work area
size, all the operations are done in memory.
If memory required by the amount of data to be processed
is more than the size of the work area, then the input is divided into many
pieces. Then some pieces of data are processed in memory while the remaining
operations are performed in temporary tablespace.
When one extra pass is performed on all or some of the input data, the corresponding size
of the available work area is called ONE-PASS SIZE i.e., if the data having
size equal to one-pass size is sorted with currently available PGA workarea, an
extra pass on the data will have to be made.
When the available work area size is even less than one
pass threshold, multiple passes over the input data are needed causing dramatic
increase in response time. This is
referred as MULTIPASS SIZE of the workarea.
In OLTP systems, size of the input data is small and
hence they mostly run in optimal mode.
In DSS systems, the input data will be very large, it is
very important to size the workarea for good performance as in-memory
operations are faster than the temporary disk operations. If you have processes of higher memory consumption,
better to have high workarea size.
Manual Tuning
of PGA
In earlier stages, DBA has to manually specify the
maximum workarea size for each type of SQL cursor.
PGA has 2 components, tunable and untenable.
Untunable components.
·
Context information of each session
·
Each open cursor
·
PL/SQL, OLAP or Java memory.
Tunable components. –
·
Consists of memory available for SQL work areas
(used by sort operations).
·
Mostly 10% of PGA in OLTP and
·
90% of PGA in DSS.
This
component is tunable means that memory available and hence consumed may be less
than what is needed and the operation will still complete but may spill to
disk. So, increasing available memory
may still improve performance.
In manual memory management, DBA manually specifies the
maximum work area size for type of SQL operator (sort, hash, etc) i.e., WORKAREA_SIZE_POLICY=MANUAL.
The parameters which decide on the memory available for
each of these operations per server process are.
I.
SORT_AREA_SIZE
II.
HASH_AREA_SIZE
III.
CREATE_BITMAP_AREA_SIZE
IV.
BITMAP_MERGE_AREA_SIZE
Let us take an example of SORT_AREA_SIZE which is used
for sorting operations like group by, order by, etc.
Consider SORT_AREA_SIZE=1M and if the number of sessions
performing sorting in the database is “n”, then the total PGA memory size will
increase considerably by n*1M .
Conclusion/Implication
ü
Each user session will do 1M of PGA for sorting
irrespective of the size of data.
ü
If size of data >1M, his sorts will spill to
the disk even though we might be having PGA memory available to the instance
i.e., a session won’t be available to use the available memory because the
memory allocated to his session has been fixed by SORT_AREA_SIZE.
ü
If size of data < 1M, the sorting will take
place in memory without any spilling to disks but still it would consume 1M of
memory than what is needed. Extra memory
can’t be transferred to another session which needs it.
ü
If SORT_AREA_SIZE is small, sorts spill to disk.
ü
If SORT_AREA_SIZE is large and no.of session
performing sorts are large, then the total memory available will be (n * 1M)
more than the available memory (Total memory for the oracle instance – SGA),
paging and swapping will takes place to satisfy that memory requirement
resulting in heavily performance degradation.
Hence, DBA should continuously
monitor the workload and decide on the appropriate value of SORT_AREA_SIZE such
that neither the sorts spilled to disk not swapping/paging takes place.
AUTOMATIC PGA MEMORY MANAGEMENT
We have seen how manual PGA
memory management can be done effectively. Now, we will see
How automatic PGA memory management can be managed which
is used from 10g and later.
Automatic PGA memory management resolved the above issues
on memory spilling to disks or going for excess swapping by allowing the DBA to
allocate an aggregate PGA to all server processes for all the SQL operations
which could be distributed as per the requirement.
In this case, oracle dynamically adapts the SQL memory
allocation based on.
a. PGA
memory available.
b. SQL
operator needs
c. System
workload.
With automatic PGA memory management, sizing of SQL work
areas for all dedicated server processes is automatic and all *_AREA_SIZE
initialization parameters are ignored for these sessions.
At any given time, the total amount of PGA memory
available for active work area on the instance is automatically derived from
the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of
PGA_AGGREGATE_TARGET – PGA memory allocated for other purposes (eg. Session
memory). The resulting PGA memory is
then allocated to the individual active work areas based on their specific
memory requirements.
Automatic PGA memory management is implemented using 2
parameters.
1.
WORKARE_SIZE_POLICY= AUTO
2.
PGA_AGGREGATE_TARGET = Target Size of the PGA
for the total instance.
By specifying the target PGA, oracle itself tunes the
size of the individual PGA’s depending upon the workload.
Conclusion/
Implications
As workload changes, memory available to each session
changes dynamically while keeping the sum of all PGA allocations under the
threshold PGA_AGGREGATE_TARGET.
Eg. If PGA_AGGREGATE_TARGET = 10G, let tunable PGA = 5G.
If one session performing sort needing 5G workarea, he
will get workarea 5G.
If 2 sessions performing the same sort, they will get 5/2
=2.5 G each. So Sort area is not fixed
and changes dynamically.
PGA_AGGREGATE_TARGET is a goal of an upper limit. It is
not the value that is allocated when instance is started i.e., memory will be
allocated only when there is a sorting operation which requires it. Else, it
will be return to operation system or transferred to SGA if AMM is enabled.
Practical
Implementation on How PGA is allocated using Automatic PGA Memory Management
1.
Disable AMM
Memory_target=0.
SQL> show parameter
memory_target;
NAME TYPE VALUE
------------------------------------
-------------------------------- ------------------------------
memory_target big integer 0
2.
Grant permissions to execute dbms_stats
SQL> grant
select_catalog_role to bctest,testbuf,test1;
Grant succeeded.
SQL> grant execute on
dbms_stats to bctest,testbuf,test1;
Grant succeeded.
3.
Create 3 test tables with different sizes. (9M,18M,34M)
SQL> conn bctest/bctest
Connected.
SQL> create table small as
select * from dba_objects;
Table created.
SQL> execute
dbms_stats.gather_table_stats('BCTEST','SMALL');
PL/SQL procedure successfully
completed.
SQL> select segment_name,bytes/1024/1024 from
user_segments where segment_name='SMALL';
SEGMENT_NAME BYTES/1024/1024
--------------- ---------------
SMALL 9
SQL> conn testbuf/testbuf
Connected.
SQL> create table medium as
select * from dba_objects;
Table created.
SQL> insert into medium
select * from medium;
75358 rows created.
SQL> commit;
Commit complete.
SQL> execute
dbms_stats.gather_table_stats('TESTBUF','MEDIUM');
PL/SQL procedure successfully
completed.
SQL> select
segment_name,bytes/1024/1024 from user_segments where segment_name='MEDIUM';
SEGMENT_NAME BYTES/1024/1024
--------------- ---------------
MEDIUM 18
SQL> conn test1/test1
Connected.
SQL> create table large as
select * from dba_objects;
Table created.
SQL> insert into large select
* from large;
75359 rows created.
SQL> insert into large select
* from large;
150718 rows created.
SQL> commit;
Commit complete.
SQL> execute dbms_stats.gather_table_stats('TEST1','LARGE');
PL/SQL procedure successfully
completed.
SQL> select
segment_name,bytes/1024/1024 from user_segments where segment_name='LARGE';
SEGMENT_NAME BYTES/1024/1024
--------------- ---------------
LARGE 34
4.
Check for the parameters for Automatic PGA
memory management.
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------
-------------------------------- ------------------------------
pga_aggregate_target big integer 496M
SQL> show parameter workarea;
NAME TYPE VALUE
------------------------------------
-------------------------------- ------------------------------
workarea_size_policy string AUTO
5.
Check how much PGA is allocated now from
V$PGASTAT
SQL> select
name,value/1024/1024 "MB" from v$pgastat where name in('aggregate PGA
target parameter','total PGA allocated');
NAME
MB
----------------------------------------------------------------
----------
aggregate PGA target
parameter 496
total PGA allocated
153.600586
PGA allocated < PGA_AGGREGATE TARGET
6.
Let us also check the current allocation
over-allocation count and PGA used from V$PGASTAT view
SQL> select name,value/1024/1024 "MB" from
v$pgastat;
NAME
MB
----------------------------------------------------------------
----------
aggregate PGA target parameter 496
aggregate PGA auto target
333.052734
global memory bound
99.1992188
total PGA inuse 126.047852
total PGA allocated
150.78418
maximum PGA allocated
203.791992
total freeable PGA memory 12
process count
.000031471
max processes count
.000041962
PGA memory freed back to OS
371.5625
total PGA used for auto workareas 0
maximum PGA used for auto workareas 3.046875
total PGA used for manual workareas 0
maximum PGA used for manual workareas 1.02539063
over allocation count
.002171516 (2276)
bytes processed
167.362305
extra bytes read/written 0
cache hit percentage
.000095367
recompute count (total)
.002198219
19 rows selected.
Ø PGA
allocated (150M) > Total PGA in use (126 M). – Means extra memory is allocated.
Ø Overallocation
count = 2276 because currently
Fixed memory requirement < PGA_AGGREGATE_TARGET (496M)
Now, we need “TO
CHECK WHETHER PGA MEMORY ALLOCATED TO THE INSTANCE CAN EXCEED EVEN WHEN
PGA_AGGREGATE_TARGET IS FIXED, AND IF
PGA REQUIREMENT IS MORE.”,
7.
We will create a PL/SQL array which would
require a large fixed PGA
SQL> create or replace
package demo_pkg as
2 type array is table of
char(2000) index by binary_integer;
3 g_data array;
4 end;
5 /
Package created.
8.
Fill up the chararray ( a CHAR datatype is
blank-padded so that each of these array elements is exactly 2000 characters in
length).
begin
for i in 1..200000
loop
demo_pkg.g_data(i):='X';
end loop;
end;
/
9.
When executing the above commands , check for
the PGA allocation using V$PGASTAT.
SQL> select
name,value/1024/1024 "MB" from v$pgastat;
NAME
MB
----------------------------------------------------------------
----------
aggregate
PGA target parameter 496
aggregate PGA auto target
31
global memory bound
99.1992188
total
PGA inuse 569.753906
total
PGA allocated
600.246094
maximum PGA allocated
603
total freeable PGA memory 16.25
process count
.000032425
max processes count
.000041962
PGA memory freed back to OS
421.8125
total PGA used for auto
workareas 0
maximum PGA used for auto
workareas
14.4619141
total PGA used for manual
workareas
0
maximum PGA used for manual
workareas
1.02539063
over allocation count 0.002
(2320)
bytes processed
265.616211
extra bytes read/written 0
cache hit percentage
.000095367
recompute count (total)
.00243187
19 rows selected.
ü
As you can see that the PGA allocated
(600M) > PGA_AGGREGATE_TARGET(496M).
ü
PGA allocated (600M) > PGA in use ( 569.7 M)
ü
Over allocation increased from 2276 to 2320
Next, we will see whether the ‘over allocation count ‘
increases if fixed memory requirement of SQL work area is not satisfied by the
extra PGA memory allocated to the instance (PGA allocated > PGA in use)
10.
Execute queries on all 3 tables.
SQL> conn bctest/bctest
Connected.
SQL> set autotrace traceonly
SQL> select * from small
order by 1,2,3,4,5,6;
75357 rows selected.
SQL> select name,value/1024/1024 "value_mb"
from v$pgastat where name in('aggregate PGA target parameter','aggregate PGA
auto target','total PGA inuse','total PGA allocated','maximum PGA allocated')
union select name,value from v$pgastat where name='over allocation count';
NAME
value_mb
----------------------------------------------------------------
----------
aggregate PGA auto target
326.390625
aggregate PGA target parameter 496
maximum PGA allocated
610.875
over allocation
count
2463
total PGA allocated
172.141602
total PGA inuse
133.503906
6 rows selected.
SQL> conn testbuf/testbuf
Connected.
SQL> set autotrace traceonly
SQL> select * from medium order by 1,2,3,4,5,6;
150716 rows selected.
SQL> select name,value/1024/1024 "value_mb"
from v$pgastat where name in('aggregate PGA target parameter','aggregate PGA
auto target','total PGA inuse','total PGA allocated','maximum PGA allocated')
union select name,value from v$pgastat where name='over allocation count';
NAME
value_mb
----------------------------------------------------------------
----------
aggregate PGA auto
target 329.818359
aggregate PGA target parameter 496
maximum PGA allocated
610.875
over allocation count 2463
total PGA allocated
188.395508
total PGA inuse
148.44043
6 rows selected.
SQL> conn test1/test1
Connected.
SQL> set autotrace traceonly
SQL> select * from large order by 1,2,3,4,5,6,7;
301436 rows selected.
SQL> select name,value/1024/1024 "value_mb"
from v$pgastat where name in('aggregate PGA target parameter','aggregate PGA
auto target','total PGA inuse','total PGA allocated','maximum PGA allocated')
union select name,value from v$pgastat where name='over allocation count';
NAME
value_mb
----------------------------------------------------------------
----------
aggregate PGA auto
target
330.389648
aggregate PGA target parameter 496
maximum PGA allocated
610.875
over allocation
count
2463
total PGA allocated
209.083008
total PGA inuse
169.484375
6 rows selected.
Implications
The over allocation counts increases when fixed memory
requirements of SQL work area is not satisfied by the extra PGA memory
allocated to the instance (PGA allocated > PGA in use). In our case, it got increased from 2320 to 2463.
For Automatic PGA memory management follow the below link
http://sandeepnandhadba.blogspot.in/2014/06/pga-tuning-part-2.htmlPGA Tuning - Part 2
Part 2 – How to decide on the value of PGA_AGGREGATE_TARGET for new instance
When configuring a new database, we need to set the PGA_AGGREGATE_TARGET value and we can estimate the size in 3 stages.
1. Initially,
we will set up as Oracle recommends 20% of SGA size for DSS systems.
But this seems to be very less for large DSS systems.
2. Run
the workload on the instance and monitor the performance using PGA
statistics by oracle to see whether the PGA is under-configured or
over-configured.
3. Tune PGA_AGGREGATE_TARGET using PGA advice statistics.
How to set the PGA value initially
The initial setting value of PGA_AGGREGATE_TARGET is based on the total
memory available for the oracle database instance. This value then can
be modified dynamically at the instance level.
Let us see in an example.
Physical memory of the system = 4GB
Memory available for OS and other non-oracle applications available on the system = 20% of RAM, i.e (0.8GB.)
Memory available for oracle instance=80% of RAM (3.2GB)
Now, we must divide the SGA and PGA memory from the above memory of 3.2 GB.
OLTP systems, 20% of 3.2 = 0.2*3.2=0.64GB and 80% for SGA
DSS systems with high memory intensive queries, we can use up to 70% of 3.2 =0.7*3.2=2.2 GB.
Good Initial values for PGA_AGGREGATE_TARGET are
For OLTP: PGA_AGGREGATE_TARGET=(Total memory *80)*20%
For DSS: PGA_AGGREGATE_TARGET=(Total_memory *80)*70%
In this example, total memory is 4G, so, for OLTP, the value of PGA_AGGREGATE_TARGET=655M and for DSS, it is 1600M.
How to monitor the statistics
Before starting, we will see how to monitor and interpret the statistics
of PGA collected by oracle database to find the performance of
automatic PGA memory management.
V$PGASTAT - this view will gives the instance-level statistics on the PGA memory usage and the automatic PGA memory manager.
Columns which are useful for the statistics.
Over Allocation count :
Ø This statistic is cumulative from instance startup.
Ø Over
allocation of PGA memory can happen when the size of PGA is too small
to accommodate the untunable component of PGA plus the minimum memory
required to execute the workarea workload. When this happens, oracle
database cannot honor the initialization parameter PGA_AGGREGATE_TARGET
and extra PGA memory has to be allocated. If we see occurrence of over
allocation, then we need to increase the value of PGA_AGGREGATE_TARGET
using the information provided in V$PGA_TARGET_ADVICE.
Extra bytes read/written:
Ø When a work area cannot run optimally, one or more extra passes is performed over the input data.
Ø Extra bytes read/written represent the number of bytes processed during these extra passes since instance startup.
Ø This number is also used to compute the cache hit percentage; ideally, this should be small compared to total bytes processed.
Cache hit percentage
Ø This column reflects the metric on performance of PGA memory component. It is a cumulative from instance startup.
Ø Value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.
Ø When
a work area cannot run optimally, one or more extra passes is performed
over the input data. This reduces the cache hit percentage in
proportion to the size of the input data and number of extra passes
performed.
V$PROCESS
· This view has on row for each oracle process connected to an instance.
· The
columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM and PGA_MAX_MEM
can be used to monitor memory usage of these processes.
V$SQL_WORKAREA_HISTOGRAM
· This
view shows the number of work areas executed with optimal memory size;
one-pass memory size and multi pass memory size since instance startup.
· Statistics
in this view are divided into buckets that are defined by optimal
memory requirement of the work area. Each bucket is identified by a
range of optimal memory requirements specified by the values of the
column LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE.
· The
work areas are split into 33 groups based on their optimal memory
requirements with the requirements increasing in powers of 2. i.e., work
areas optimal requirement varies from 0 KB to 1KB, 1KB to 2KB, 2KB to
4KB and 2TB to 4TB.
· For
each work area group, the v$sql_workarea_histogram view shows how many
work areas in that group were able to run in optimal mode, how many were
able run in one-pass mode and finally how many ran in multi-pass mode.
SELECT
low_optimal_size/1024 "Low (K)",
(high_optimal_size + 1)/1024 "High (K)",
optimal_executions "Optimal",
onepass_executions "1-Pass",
multipasses_executions ">1 Pass"
FROM v$sql_workarea_histogram
WHERE total_executions <> 0;
We can check the proportion of work areas since you started the Oracle
instance, using optimal, 1-pass, and multi-pass PGA memory sizes.
SELECT name PROFILE, cnt COUNT,
DECODE(total, 0, 0, ROUND(cnt*100/total)) PERCENTAGE
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT
WHERE name like 'workarea exec%');
V$SQL_WORKAREA_ACTIVE
· This view is used for finding the work areas that are active (or executing) in the instance.
· Small active sorts of 64KB are excluded from this view.
· Use
this view to precisely monitor the size of all workarea and to
determine if these active areas are spilled to temporary segment.
V$SQL_WORKAREA
· Oracle
database maintains cumulative work area statistics for each loaded
cursor whose execution plan uses one or more work area.
· Every time a work area is de-allocated, the v$sql_workarea table is updated with execution statistics for the work area.
Tuning the PGA
To help in tune the initialization parameter PGA_AGGREGATE_TARGET , we can use V$PGA_TARGET_ADVICE view.
V$PGA_TARGET_ADVICE
· This view predicts how the statistics cache hit percentage and over allocation count in v$pgastat
will be impacted if we change the value of initialization parameter pga_aggregate_target.
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;
How to tune PGA_AGGREGATE_TARGET
1. If over allocation count
> 0, it indicates that PGA_AGGREGATE_TARGET is too small to even meet
the minimum PGA memory needs. If PGA_AGGREGATE_TARGET is set within
the over allocation zone, the memory manager will over-allocate memory
and actual PGA memory consumed will be more than the limit we set. Set
PGA_AGGREGATE_TARGET value so that there is no memory over allocation;
avoid setting it in the over-allocation zone.
2. After eliminating the
over-allocations, aim at maximizing the PGA cache hit percentage, based
on the response-time requirements and memory constraints.
a. If available memory >=
optimal memory for 100% PGA cache hit %, then set PGA_AGGREGATE_TARGET=
optimal memory as in PGA_TARGET_ADVICE .
If AMM is enabled,
Remaining memory will be allocated to SGA.
Else,
Rest of the available memory will be returned to operating system.
b. If available memory <
optimal memory for 100% PGA cache hit %, then set
PGA_AGGREGATE_TARET=available memory and settle lower PGA cache hit %.
V$PGA_TARGET_ADVICE_HISTOGRAM
· This
view predicts how the statistics displayed by the performance view will
be impacted if you change the value of the initialization parameter
PGA_AGGREGATE_TARGET.
· We
can use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM to view the
detailed information on the predicted number of optimal, one-pass and
multi-pass work area executions for the set of PGA_AGGREGATE_TARGET
values you use for the predication.
V$SYSSTAT and V$SESSTAT
· Statistics
in v$sysstat and v$sesstat views shows the total number of work areas
executed with optimal memory size, one-pass memory size and multi-pass
memory size.
· These statistics are cumulative since the instance or session was started.
· The
following query gives the total number and the percentage of times work
areas were executed in these three modes since the instance was started
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
FROM (SELECT name, value cnt, (sum(value) over ()) total
FROM V$SYSSTAT
WHERE name like ‘workarea exec%’);
References :
