Re: DBA Job Functions

From: Mladen Gogala <>
Date: Sun, 4 Mar 2018 18:59:03 -0500
Message-ID: <>

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 <
> <>> 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
> < <>> 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

Received on Mon Mar 05 2018 - 00:59:03 CET

Original text of this message