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.
No comments:
Post a Comment