[oracle-l] Re: pga_aggregate_target -- actual values from my database

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sat, 24 Jan 2004 14:13:53 +0800
Message-Id: <5.1.1.6.0.20040124141250.00aaa058@pop.singnet.com.sg>

We have a new reporting instance [Solaris 8, 8GB RAM, 4 CPUs, 9.2.0.4] where initially SGA_MAX_SIZE was 2GB, DB_CACHE_SIZE was 32MB, SHARED_POOL_SIZE was 200MB and PGA_AGGREGATE_TARGET was 100MB. There are approx 10 to 15 active users at any time.

Without really having any "baseline" we've gone to DB_CACHE_SIZE=800MB and PGA_AGGREGATE_TARGET=1GB just about a day ago.

On the OS side, I currently see about 2.6GB free RAM yet.

On the Oracle side :

SQL>
1* select * from v$pgastat
SQL> / NAME VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter 1073741824 bytes aggregate PGA auto target 949662720 bytes global memory bound 53686272 bytes
total PGA inuse 18585600 bytes
total PGA allocated 65071104 bytes
maximum PGA allocated 237120512 bytes
total freeable PGA memory 6684672 bytes
PGA memory freed back to OS 6182862848 bytes total PGA used for auto workareas 28672 bytes maximum PGA used for auto workareas 119459840 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0
bytes processed 2.9389E+10 bytes
extra bytes read/written 3742183424 bytes cache hit percentage 88.7 percent

16 rows selected.

SQL> SQL> desc v$pga_target_advice
Name Null? Type
----------------------------------------- -------- ----------------------------
PGA_TARGET_FOR_ESTIMATE NUMBER
PGA_TARGET_FACTOR NUMBER
ADVICE_STATUS VARCHAR2(3)
BYTES_PROCESSED NUMBER

ESTD_EXTRA_BYTES_RW NUMBER
ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER
ESTD_OVERALLOC_COUNT NUMBER

SQL> set numformat 99,999,999,990.00

SQL> l
1* select * from v$pga_target_advice
SQL> / PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED
----------------------- ------------------ --- ------------------
ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT

------------------- ----------------------------- --------------------

134,217,728.00 0.13 ON 29,766,417,408.00 20,737,393,664.00 59.00 0.00

268,435,456.00 0.25 ON 29,766,417,408.00 19,436,601,344.00 60.00 0.00

536,870,912.00 0.50 ON 29,766,417,408.00 5,331,615,744.00 85.00 0.00

805,306,368.00 0.75 ON 29,766,417,408.00 4,051,345,408.00 88.00 0.00

1,073,741,824.00 1.00 ON 29,766,417,408.00 3,716,258,816.00 89.00 0.00

1,288,489,984.00 1.20 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

1,503,238,144.00 1.40 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

1,717,986,304.00 1.60 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

1,932,734,464.00 1.80 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

2,147,483,648.00 2.00 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

3,221,225,472.00 3.00 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

4,294,967,296.00 4.00 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

6,442,450,944.00 6.00 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

8,589,934,592.00 8.00 ON 29,766,417,408.00 3,690,334,208.00 89.00 0.00

14 rows selected.

SQL> SQL> l
1 SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb, 2 optimal_executions, onepass_executions, multipasses_executions 3 FROM v$sql_workarea_histogram
4* WHERE total_executions != 0
SQL> / LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS

------------------ ------------------ ------------------ ------------------

MULTIPASSES_EXECUTIONS

4.00 8.00 91,590.00 0.00
0.00

8.00 16.00 4,133.00 0.00
0.00

16.00 32.00 3,632.00 0.00
0.00

32.00 64.00 8,038.00 0.00
0.00

64.00 128.00 3,208.00 0.00
0.00

128.00 256.00 2,362.00 0.00
0.00

256.00 512.00 1,015.00 0.00
0.00

512.00 1,024.00 844.00 0.00
0.00

1,024.00 2,048.00 890.00 0.00
0.00

2,048.00 4,096.00 902.00 0.00
0.00

4,096.00 8,192.00 760.00 10.00
0.00

8,192.00 16,384.00 354.00 0.00
0.00

16,384.00 32,768.00 286.00 0.00
0.00

32,768.00 65,536.00 84.00 0.00
0.00

1,048,576.00 2,097,152.00 0.00 2.00
0.00

15 rows selected.

SQL> Referencing Note 223730.1, do I seem to have hit the "right" value for PGA_AGGREGATE_TARGET using my hit-or-miss method without any baseline figures ?

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {last updated 22-Jan-04} Received on Sat Jan 24 2004 - 00:13:53 CST