He also gave the presentation at the IOUG-A in Orlando recently, so it
should be available from their CD's/archives or whatever. It's a neat
presentation. Way too many people and monitoring products want you do keep
increasing db_block_buffers until your BCHR is about 99.9% or so. It's
pure nonsen
Particularly, this can be caused by a huge amount of logical IO
(proportionately dwarfing the amount of disk io and resulting in a
falsely 'positive' hit ratio). Take a look at your sqlarea again and
look at buffer_gets/executions - that may kick up some queries doing
more logical io than you
Did you say 99% hit ratio?
Contrary to a once popular opinion, that is
not really a good thing.
It often means there's a cartesian product
in a join, caused by not fully specifying
the join key. At least that's the reason
I've usually seen for it.
Keeps disk access down though. :)
Jared
On
Raj,
One statement caught my eye:
The buffer cache hit ratio is 99%
My bet is you've got some very inefficent SQL. As is
typically the case, application tuning has the biggest
effect on performance.
I'd suggest checking out Cary Millsap's excellent
paper "Why 99% Database Buffer Cache Hit Ra
Thanks Chris.
The problem I am facing is 100% CPU usage and memory paging out at times. I
tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL
Vision can not happen here immediately. The RAM size is 512MB and SGA is
210MB. The buffer cache hit ratio is 99%.
I was just wonder
Oracle has no guarentee how long statistics and plans will be available for
statements, depending on the activity of the database they may be there for
2 seconds they may be there for 2 weeks.
Products like Precise SQL and SQL Vision Lab help in that manor where they
capture transactions and acti
DBAs
The statistics in v$sqlarea is getting flushed very often.
In the morning I found the a query which has more than 10,000 disk reads as
the top one. But now the top most query with disk reads has not more than
100 disk reads. I have not bouncd the DB or flushed the shared pool. The
overall
