PGA cache hit ratio mis-leading.
- From: The Human Fly <sjaffarhussain@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 10 Sep 2005 11:11:53 +0300
Hello list,
Our DB has 984M as PGA_AGGREGATE_TARGET and following is the output
from v$pgastat:
NAME VALUE UNIT
------------------------------ -------------- ------------
aggregate PGA target parameter 984.0 mbytes
aggregate PGA auto target 794.1 mbytes
global memory bound 49.2 mbytes
total PGA inuse 101.8 mbytes
total PGA allocated 190.8 mbytes
maximum PGA allocated 296.8 mbytes
total freeable PGA memory 8.8 mbytes
PGA memory freed back to OS 2,287.8 mbytes
total PGA used for auto .0 mbytes
workareas
maximum PGA used for auto 85.8 mbytes
workareas
total PGA used for manual .0 mbytes
workareas
maximum PGA used for manual .0 mbytes
workareas
over allocation count .0
bytes processed 32,006.6 mbytes
extra bytes read/written 85,046.7 mbytes
cache hit percentage 27.3 percent
My concern is, when my DB uses maximum PGA around 300M and total PGA
currently in use is about 100M, i.e. I still have 700+M as tunable to
my PGA. But, how come the cache hit percentage show only 27.3%?
but, following output from v$pga_target_advice, hit ratio shows 99%
PGA_TARGET_FOR_ESTIMATE PGA_TAR BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_
----------------------- ------- --------------- ------------------- -----
123 12.5% 32190 784 98%
246 25.0% 32190 467 99%
492 50.0% 32190 418 99%
738 75.0% 32190 387 99%
984 100.0% 32190 300 99%
1180 120.0% 32190 269 99%
1377 140.0% 32190 269 99%
1574 160.0% 32190 269 99%
1771 180.0% 32190 190 99%
1968 200.0% 32190 100 100%
2952 300.0% 32190 100 100%
3936 400.0% 32190 100 100%
5904 600.0% 32190 100 100%
7872 800.0% 32190 100 100%
Can someone shed the lime light on this issue?
--
Best Regards,
Syed Jaffar Hussain
OCP 8i & 9i DBA,
Banque Saudi Fransi,
Saudi Arabia
----------------------------------------------------------------------------------------
"It is your atittude, not your aptitude that determins your altitude."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » PGA cache hit ratio mis-leading.
