RE: Spotting the real cause of a Production slowdown (10g)
- From: "Schultz, Charles" <sac@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 08:52:33 -0500
Thanks. Yes, I agree with what you are saying. One of the fallouts from
our case with Oracle Support is that we are bumping up against a bug
whereby the 10g SGA auto-sizer will inflate the shared pool
indiscriminately, even if you are flooding it with literal statements
(counter-intuitive, as you pointed out below).
The problem with finding many copies of sql statements that look similar
is how granular do you go? The common method is to substring sql_text
from v$sql. How many characters do you use? To be empirical, one would
have to loop through all reasonable numbers (ie, between 7 and 150).
Unless you know something I don't, which is entirely possible. *grin*
Good point about which processes are consuming CPU - I did not even
think about that. How does one use the Historical view of EM to
determine that information? All I can find is "CPU used" under
performance, and if I try to break it down, I get user processes. But I
will keep looking.
Just saw the note from Paul Baumgartel - thanks. I will experiment with
the plan_hash_value.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dennis Williams
Sent: Thursday, April 20, 2006 8:44 AM
To: Schultz, Charles
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Spotting the real cause of a Production slowdown (10g)
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
Other related posts:
