Re: DBA Job Functions

+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_at_bluewin.ch<mailto:l.flatz_at_bluewin.ch>> 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_at_gmail.com<mailto:veeeraman_at_gmail.com>> 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_at_gmail.com<mailto:xt.and.r_at_gmail.com>> 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

--

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 06 2018 - 08:33:36 CET