RE: Spotting the real cause of a Production slowdown (10g)
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 20 Apr 2006 14:49:20 +0100
And to help find those same-but-for-literal-values SQLs (which typically have
the same optimizer plan), you can use this query, which I have found very
useful:
select plan_hash_value, count(*) from v$sql
where plan_hash_value > 0
group by plan_hash_value having count(*) > 4 --or whatever number you like
order by count(*);
Paul Baumgartel
paul.baumgartel@xxxxxxxxxxxxxxxxx
212.538.1143
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Dennis Williams
Sent: Thursday, April 20, 2006 9:44 AM
To: sac@xxxxxxxxxxxxx
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
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Other related posts:
