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

Creating stored scripts in an RMAN database caused a ridiculously large amount 
of archivelog space to be generated - why?  
   
  Has anyone else had this issue?
   
  Thanks,
  Paula

"Goulet, Dick" <DGoulet@xxxxxxxx> wrote:
      Paul's statement is a diamond.  I've been having a shared pool problem 
for a week now.  Ran Paul's query & found my offending application in about a 
minute.  Darn Co-Ops.  Oh well I guess we all have to start somewhere.

    
---------------------------------
  From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Schultz, Charles
Sent: Thursday, April 20, 2006 9:53 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Spotting the real cause of a Production slowdown (10g)


  
  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
   


                
---------------------------------
New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.

Other related posts: