 |
|
Oracle Tips by Burleson |
Process Global Area
The next section of the AWRRPTreport deals with the Process Global Area(PGA) statistics. The
report example is in the following listing:
PGA Aggr
Summary DB/Inst: SSD/ssd2 Snaps: 3-4
-> PGA cache hit
% - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit
% W/A MB Processed Extra W/A MB Read/Written
---------------
------------------ --------------------------
100.0 2,887 0
-------------------------------------------------------------
PGA Aggr Target
Stats DB/Inst: SSD/ssd2 Snaps: 3-4
-> B: Begin
snap E: End snap (rows dentified with B or E contain data
which is
absolute i.e. not diffed over the interval)
-> Auto PGA
Target - actual workarea memory target
-> W/A PGA
Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem
- percentage of PGA memory allocated to workareas
-> %Auto W/A Mem
- percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem
- percentage of workarea memory under manual control
%PGA %Auto %Man
PGA Aggr
Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M)
Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- ----------
---------- ---------- ---------- ------ ------ ------ ----------
B
340 255 148.7 0.0 .0 .0 .0 17,408
E
340 274 89.8 0.0 .0 .0 .0 17,408
-------------------------------------------------------------
PGA Aggr Target
Histogram DB/Inst: SSD/ssd2 Snaps: 3-4
-> Optimal
Executions are purely in-memory operations
Low High
Optimal
Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- -------
-------------- -------------- ------------ ------------
2K
4K 761 761 0 0
512K
1024K 3,399 3,399 0 0
-------------------------------------------------------------
PGA Memory
Advisory DB/Inst: SSD/ssd2 Snap: 4
-> When using
Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd
PGA Overalloc Count is 0
Estd Extra Estd PGA Estd
PGA
PGA Target
Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB)
Factr Processed Written to Disk Hit % Count
----------
------- ---------------- ---------------- -------- ----------
43
0.1 7,485.9 2,657.9 74.0 80
85
0.3 7,485.9 1,883.5 80.0 50
170
0.5 7,485.9 298.9 96.0 0
255
0.8 7,485.9 298.9 96.0 0
340
1.0 7,485.9 298.9 96.0 0
408
1.2 7,485.9 265.8 97.0 0
476
1.4 7,485.9 265.8 97.0 0
544
1.6 7,485.9 265.8 97.0 0
612
1.8 7,485.9 265.8 97.0 0
680
2.0 7,485.9 234.5 97.0 0
1,020
3.0 7,485.9 186.0 98.0 0
1,360
4.0 7,485.9 186.0 98.0 0
2,040
6.0 7,485.9 186.0 98.0 0
2,720
8.0 7,485.9 186.0 98.0 0
-------------------------------------------------------------
This report listing clearly shows that there is
enough pga_aggregate_target
set as there are no single or
multi-pass sort executions. From the data file I/O section of the
report, one can also see that there was no I/O to the temporary
tablespace, again indicating all sort and hash type activity was
accomplished in memory.
If there was a maximum value for
pga_aggregate_target
or the DBA wanted to reduce the
setting to allow more users to utilize the system, the temporary
tablespace could be moved to SSD and deliberately force sorts and hash
operations to the temporary tablespace.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
