Re: Cache hits again

Someone wrote in message <34BE48DE.4A2B_at_nowhere.ca>...
>Been looking at my performance, trying out some tuning here and there
>but
>really just guessing at it. My Library cache hit % is 100, my
>Dictionary cache hit % is 100. great, my Cache hit Ratio % (data) is
>iffy, usually around 50%. I've doubled the db_block_buffers (3600 to
>7200) but hasn't changed much. I'm running a relatively small DB of 1
>GIG on an HP machine with 128 megs of memory. Any suggestions? Buy
>more memory and keep increasing the db-block-buffers is my thought but
>it's so damn expensive is the prob.

My 2'c worth. :-)

Just throwing more memory at the db block buffers may just fix the symptoms and not the problem and in a few months time you're back at square one again.

There must be a reason why the cache hit ratio is so low. What type of database are you running - DSS, OLTP, OLAP? What type of SQL statements are running? What joins do they do? Have the standard SQL statements from the client app(s) been put thru explain plan and analysed?

A simple way to get a feel of what's going on in the database is to look at the v$ tables relating to sessions, processes and their stats. Join v$session with v$process (using addr and paddr) to get a list of session and corresponding process info. Join v$sqlarea with v$session to get a list of active SQL statements. Use v$open_cursor to get a list per session of open cursors. And look at v$session_event to get an idea of what each session is doing in Oracle.

You can also get a cache hit ratio per session from v$sesstat:

--
SELECT
  'Hit Ratio',
  TO_CHAR(ROUND(100 *
((a.value+b.value)-c.value)/(a.value+b.value)),'900.00')||'%'
FROM v$sesstat a, v$sesstat b, v$sesstat c
WHERE  a.statistic# = 37
AND    b.statistic# = 38
AND    c.statistic# = 39
AND    a.sid = <sessionid>
AND    b.sid = <sessionid>
AND    c.sid = <sessionid>
--

Using this SQL may be a good place to start to see which sessions have very
low cache hit ratios and then investigate each session in turn.

Have fun. I always enjoy digging around with the internal Oracle tables. :-)

regards,
Billy