You dont list your version of Oracle, but here are a couple of things:
1. Make sure your programmers are using bind variables instead of dynamic
sql. Using dynamic sql keeps your queries from using your cache
effectively.
2. I understand there is a bug whereby, if timed_statistics are turned on,
you get a bunch of duplicate entries in your cache, so try turning off timed
statistics.
3. If none of the above work, try increasing your db_block_buffers.
-----Original Message-----
From: mike balint [mailto:[Email Address Removed] Tuesday, July 31, 2001 08:47
To: LazyDBA.com Discussion
Subject: RE: my buffer cache hit ratio is 63.63? any recommendation?
Majid,
i didn't know you were working at my place!! we have
exactly the same performance stats on one of our
production systems, these are our causes and what
we're thinking of doing:
hi parse/execute ratio = literal sql = rewrite app
hi ch/ftch ratio = db block size too small, bad db
design = reinstall oracle, redesign db
lo hit ratio = big scans wiping out cache = get better
control over what sql is running
i am very interested in what list members suggest to
help our mutual situation.
thanks, mike
--- "Khan, Majid" <majid.[Email Address Removed] wrote:
> if you go to Toad software and you know where it
> says tunning|server
> stats|analysis
> it tell me that
> buffer cache hit ratio = 63.5861 = may need to in
> increase db_block_buffer
> chained fetch ratio = 0.0667 = picture too low for a
> table
> parse/execute ratio = 81.8339 = high parse to
> execute ratio
> I know I can increase the db_block_buffer but what
> about those...
> now any suggestion
> please
>
> -----Original Message-----
> From: Schoen Volker [mailto:v.[Email Address Removed] Sent: Tuesday, July 31, 2001 9:02 AM
> To: 'Khan, Majid'; LazyDBA.com Discussion
> Subject: AW: my buffer cache hit ratio is 63.63? any
> recommendation?
>
>
> increase db_block_buffers in init.ora and check
> SQL-Statements with most
> physical reads.
>
> Regards
>
> Volker Schön
> E-Mail: mailto:v.[Email Address Removed] http://www.inplan.de
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: Khan, Majid [mailto:majid.[Email Address Removed] Gesendet: Dienstag, 31. Juli 2001 14:42
> An: LazyDBA.com Discussion
> Betreff: my buffer cache hit ratio is 63.63? any
> recommendation?
>
>
> hi dba's
> my buffer hit ratio is 63.63 but my user hit ratio
> is about 99%
> I need to tune my database is there any
> recommendation from you guys
>
>
>
> SELECT Sum(DECODE(name, 'consistent gets', value,
> 0)) AS "consistent_gets",
> Sum(DECODE(name, 'db block gets', value, 0)) AS
> "db_block_gets",
> Sum(DECODE(name, 'physical reads', value, 0)) AS
> "physical_reads",
> TO_CHAR(
> (
> Sum(DECODE(name, 'consistent gets', value, 0))
> + Sum(DECODE(name, 'db block gets', value, 0))
> - Sum(DECODE(name, 'physical reads', value, 0))
> )
> /
> (
> Sum(DECODE(name, 'consistent gets', value, 0))
> + Sum(DECODE(name, 'db block gets', value, 0))
> )
> * 100, '99999999.99' ) AS "hit_ratio, %"
> FROM v$sysstat;
>
>
--------------------------------------------------------------------------
>
> consistent_gets db_block_gets physical_reads
> hit_ratio, %
> --------------- ------------- --------------
> ------------
> 15744863 783286 6011121
> 63.63
>
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to
> oracledba-[Email Address Removed] To subscribe: send a blank email to
> oracledba-[Email Address Removed] Visit the list archive:
> http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to
> oracledba-[Email Address Removed] To subscribe: send a blank email to
> oracledba-[Email Address Removed] Visit the list archive:
> http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
>
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page