Re: Cache hits again

In article <69useo$art$1_at_hermes.is.co.za>, Billy Verreynne <vslabs_at_onwe.co.za> wrote:
>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?

I'll bet there's a lot of full table scans that push stuff out of the buffers before something else can utilise it. May be nothing to be done, but may have some real wins with statement tuning.

>
>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
>
>
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!