RE: SQL Performance Problem between 2 Databases WITH FIX included for this case

  • From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • To: 'Wolfgang Breitling' <breitliw@xxxxxxxxxxxxx>
  • Date: Tue, 17 Jan 2012 11:06:45 -0600

Some of that is subjective isn't it? (I'm asking)

For me enabling a 10046 trace is not clumsy at all and if the sql is executing 
I can enable it and get at least a partial 10046, and since SQL is capturable I 
can grab it and execute in a test session.
For me, digging through V$ tables is clumsy precisely because I'm not as 
familiar with them - I will concede I should probably make that part of my 
toolbox where I am comfortable banging away at V$ information for a particular 
query that is in process.

Typically I imagine this scenario:

1. Bad performing SQL -> 2. Examine V$ views -> 3.Determine some cause of 
performance issue -> 4.Implement Some fix -> 5. See if query responds on next 
run (since current run won't be affected) 6. -> *IF* query not affected as 
expected, then you're probably going to go to the 10046 trace level?

It seems like there is a good chance (and perhaps I overestimate the chance) 
that we are both going to end up looking at a 10046 trace for any problem that 
is significant.  (Oracle EM can usually take care of the non-significant ones 
through SQL Tuning advisors)

Most often in a transaction or job (a connected session) there are only 1 or 2 
offending SQLs.  A 10046 where you're looking at gobs of SQL statements is not 
very helpful in my opinion.  Target the offending SQL(s) only.

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 
Sent: Tuesday, January 17, 2012 9:52 AM
To: Taylor, Chris David
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: Re: SQL Performance Problem between 2 Databases WITH FIX included for 
this case

But that requires that you start a trace ( with level 12 ) prior to executing 
the sql. The row counts and timing you can get from v$sql_plan and 
v$sql_plan_statistics and the wait events from v$session_event. I am not 
disputing the usefulness of a 10046 trace. I was merely replying to you comment 
about the 10053 trace. You need to use the appropriate tools for the task at 
hand and there are different ways to attack a problem. To analyze the 
performance of a single sql a 10046 trace would not be my first choice. Far too 
clumsy. The mentioned v$ views ( plus maybe a few more ) are sufficient for 
that. To analyze the performance of an entire transaction or job, of course I'd 
request a 10046 trace because nothing else gives you the full picture.  

Wolfgang Breitling
Centrex Consulting Corporation

On 2012-01-16, at 3:33 PM, Taylor, Chris David wrote:

> I'm still partial to the 10046 due to all the information it gives you.  Does 
> Tom's bstat/estat script give execution plans with row counts and wait events 
> and recursive sqls?  If not, I can get all that at once :)
> (But I still have to remember to actually look at all of it and not get too 
> single minded about a particular piece of it)


Other related posts: