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 12:45:10 -0600
Excellent point about not having access to the server. I had not considered
that aspect. That would definitely force me into an uncomfortable situation.
Good discussion. Thanks!
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 12:12 PM
To: Taylor, Chris David
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: Re: SQL Performance Problem between 2 Databases WITH FIX included for
this case
Sure, all of that is subjective. You use the tools you're most familiar with (
not that I am not familiar with 10046 trace ). With the 10046 trace being
clumsy I mean that you have to switch between environments. With the v$ views I
can do all within sqlplus. For the trace I have to go to the server, locate the
trace directory, locate the trace and run tkprof to get to the same point.
Some of that may sound trivial for many of you, but I am an outside consultant,
a travelling mercenary. Sometimes I don't have access to the server. If I have
you'd be surprised at how many different places people stash the trace
directory. I generally first have to do a "show parameter user" to get the
location of the trace. Then I may not have permission to that directory, or if
I have maybe not have permission to read the trace ( traces are only readable
by oracle and dba/oinstall unless _trace_files_public is set which can be a
security risk ).
Maybe now you understand why I say using 10046 trace for a single sql is
clumsy. And, yes, that view/experience is very subjective.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com
On 2012-01-17, at 10:06 AM, Taylor, Chris David wrote:
> 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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
