Re: Spotting the real cause of a Production slowdown (10g)

I'd concur with Dennis, though it isn't really version specific. It's a great illustration of the ratio problem btw, consider you have a poor library cache hit ratio, then either the classic advice to increase the shared pool size (eg
http://www.uaex.edu/srea/tuning.htm#Shared%20Pool%20Size) is correct. Or it is exactly the wrong thing to do, because you'll never get a match and you just increased the time Oracle wasted looking for one. So to answer the interview question "What do you do if your library cache hit ratio is below 9x%?' 'Increase the size of the shared pool, or alternatively decrease it, or finally do nothing about the shared pool'. That'll get you hired every time :(

Niall

On 4/20/06, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
>
> Charles,
>
> I have seen similar problems in earlier versions of Oracle - many literal
> statements and a large shared pool. Think about it -- when a new SQL
> statement is received, Oracle must check to see if it has ever received that
> statement before. The larger the shared pool, the more area it must scan. Of
> course, it doesn't find a match, so it must parse the new statement. Then it
> must find a spot in the shared pool to place the newly parsed statement.
> Which means it must age something out. And if the application is hammering
> Oracle with these statements inside a loop, it gets challenging for Oracle
> to keep up. If this is the situation, the answer is not to increase the size
> of the shared pool, because that just aggravates the problem.
> Rather than looking for the single statement that is getting issued
> the most, you may want to look a lot of statements that are issued once but
> look very similar, like
> select col1 from employee where empno = 1;
> select col2 from employee where empno = 2;
> In our case, the solution was to get the developers to recode the worst
> offenders to bind variables.
> Also, you didn't mention which Oracle processes were consuming so
> much CPU.
>
> Dennis Williams
>
>

--
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l