PGA cache hit ratio mis-leading.

From: The Human Fly <sjaffarhussain_at_gmail.com>
Date: Sat, 10 Sep 2005 11:11:53 +0300
Message-ID: <97b7fd2f05091001114c2b4189@mail.gmail.com>

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."
--
http://www.freelists.org/webpage/oracle-l

Received on Sat Sep 10 2005 - 03:14:00 CDT