+1 Lothar
Which is a good job because turning tracing on for a production session and
then getting hold of trace files takes too long and takes too much paperwork
for many organisations, even if you are a DBA which I am not so it’s even more
difficult.
Sent from my iPhone
On 5 Mar 2018, at 15:21, Lothar Flatz
<l.flatz@xxxxxxxxxx<mailto:l.flatz@xxxxxxxxxx>> wrote:
Nonsense. You are looking in ASH where the time is spent. If the issue is an
useful index not used, you don't need a trace to figure that.
On 05.03.2018 00:59, Mladen Gogala wrote:
Replies in-line
On 03/02/2018 04:03 PM, Sayan Malakshinov wrote:
Ram,
where the time is going
Using ASH you can find suboptimal plan executions that caused so huge IO load.
Analyzing those plans you can find that CBO can't use invalid indexes to build
optimal plan.
During the tuning process, you are not looking for suboptimal plans, you are
looking where in the application the time is spent. The next step is the
question how to decrease that time. Only 10046 trace can tell you that.
On Fri, Mar 2, 2018 at 11:45 PM, Ram Raman
<veeeraman@xxxxxxxxx<mailto:veeeraman@xxxxxxxxx>> wrote:
From my experience, those views and utilities tell me where the time is going,
but how do I figure why it is doing so many extra IOs suddenly, so many delays
suddenly, etc. In this case the same disk subsytem was unable to handle a
sudden burst in IO requests.
One thing that came to my mind was seeing the past performance when things were
bad for the SQLs in question. But past history was not available.
On Fri, Mar 2, 2018 at 3:22 AM, Sayan Malakshinov
<xt.and.r@xxxxxxxxx<mailto:xt.and.r@xxxxxxxxx>> wrote:
Mladen,
You could have used the 10046 trace. That would tell you what is you
application waiting for.
On recent oracle versions I wouldn't start from 10046, because in more than 80%
cases it's overkill and it requires more time(you need to wait end of
execution, otherwise you will not get row statistics) than other more real-time
tools, for example, Session Snapper, ASH, RTSM, v$session_event, etc...
It is also the only thing that guarantees 100% correct answer. I've seen the
cases of AWR and ASH reports sending people to the wild goose chases. You see,
it's not always the plan. Sometimes the problem is in a row-by-row fetch, a
thing I see increasingly frequently with the tools that generate applications,
like the project Symfony or Django.
At this point, I have to ask: have you read Cary Millsap's book?
"Optimizing Oracle Performance" by Cary and Jeff Holt? Yes, of course, long
time ago :)
That is the most ground breaking book in the history of Oracle books. It
reveals, in gory details, the tuning methodology and its gory details.
Everything else is just a guesswork.
Moreover, I subscribed to his blog (as to many others from this thread) and
some of my scripts are based on Cary's scripts.
So what's your point? Don't you think that it's much better to send more
detailed questions? Mail-list is not a telegram or twitter :D
I am not on Twitter or Facebook. Hashtag #RealMladenGogala.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
--