Re: DBA Job Functions

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sat, 3 Mar 2018 00:03:06 +0300
Message-ID: <CAOVevU5Otty9yhDkAT3F61kO6RwABwoH77+ko=Lo-knOYfRZOA_at_mail.gmail.com>



 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.

On Fri, Mar 2, 2018 at 11:45 PM, Ram Raman <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>
> 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...
>>
>> 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 :)
>> 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
>>
>> On Fri, Mar 2, 2018 at 10:04 AM, Mladen Gogala <gogala.mladen_at_gmail.com>
>> wrote:
>>
>>> At this point, I have to ask: have you read Cary Millsap's book?
>>>
>>> On 03/01/2018 08:48 PM, Sayan Malakshinov wrote:
>>>
>>> There are a lot of different types of problems, so it's more important
>>> to determine what information you need to troubleshoot current issue, and
>>> only after that you can choose a tool which can show it.
>>> In your case I think sqlplus+v$active_session_history (perhaps with
>>> v$segment_statistics) should be enough. For example, my script from not yet
>>> published post: https://github.com/xtender/xt_scripts/blob/master/ash/
>>> top_predicates.sql
>>> It shows top predicates from ASH for table access analysis
>>>
>>>
>>> --
>>> Mladen Gogala
>>> Database Consultant
>>> Tel: (347) 321-1217
>>>
>>>
>>
>>
>> --
>> Best regards,
>> Sayan Malakshinov
>> Oracle performance tuning engineer
>> Oracle ACE Associate
>> http://orasql.org
>>
>
>
>
> --
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 02 2018 - 22:03:06 CET

Original text of this message