| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: pga_aggregate_target
I used from 9i release 1, it works fine, you can tune it using
PGA_AGGREGATE_TARGET must be set
And STATISTICS_LEVEL can be in TYPICAL or ALL, not BASIC.
SELECT 'PGA SIZE '||(PGA_TARGET_FOR_ESTIMATE/1024/1024)||' MB CAUSES A CACHE
HIT '
|ESTD_PGA_CACHE_HIT_PERCENTAGE
FROM V$PGA_TARGET_ADVICE;
PGA size 12,5 MB causes a cache hit 92
PGA size 25 MB causes a cache hit 99
PGA size 50 MB causes a cache hit 99
PGA size 75 MB causes a cache hit 100
PGA size 100 MB causes a cache hit 100
PGA size 120 MB causes a cache hit 100
PGA size 140 MB causes a cache hit 100
PGA size 160 MB causes a cache hit 100
PGA size 180 MB causes a cache hit 100
PGA size 200 MB causes a cache hit 100
PGA size 300 MB causes a cache hit 100
PGA size 400 MB causes a cache hit 100
PGA size 600 MB causes a cache hit 100
PGA size 800 MB causes a cache hit 100
The current size (50M) seems to be OK.
Juan Carlos Reyes Pacheco
OCP
-------Original Message-------
From: oracle-l_at_freelists.org
Date: 09/02/04 16:12:31
To: Oracle-l
Subject: pga_aggregate_target
Has anyone used this parameter in 9i (9.2.0.4)?
I have a vended application with multiply nested unions and select distincts. The vendor has cleaned up some of the worst queries but it is still very sloppy and ineffcient.
The server is dedicated to this application's database. It has lots of RAM (12G) and CPUs (4). It hammers the CPUs pretty hard, as much as 100% utilization during prime user time. With everything taken into account, it barely uses 2G of memory. The best conclusion I can draw from mulitple analysis of statspack output is tune the application which is not possible.
Any thoughts or experience with this parameter?
 |
 |