Re: Finding long running queries..
Date: Thu, 19 Nov 2015 13:45:33 -0600
Message-ID: <564E26DD.5030409_at_roughsea.com>
Looks more to me that your problem isn't long running queries but tons of short running ones. I have had a similar problem once with a report that was taking (more modestly) over one hour to generate 70 pages. I traced the process, I couldn't open (on Solaris) the tracefile in vi. Too big.
I had to resort to that good old grep to try to see what was in the file and I found over 600,000 queries.
None of them was very long. They were all pretty fast, actually. But around 10,000 queries per page was perhaps a bit excessive.
Stéphane Faroult
On 19/11/15 12:55, Upendra nerilla wrote:
> Hello everyone -
> Environment: Oracle RAC 11.2 on OEL
>
> I am trying to set 10046 trace on a batch process to see which queries
> needs tuning.. The process runs for over 12 hours. Last time I set a
> max dump file to 6G which got filled up in a couple of hours. current
> udump directory has limited storage and I don't want to repoint it to
> another location for the entire duration.
> I am trying to see if there are any work around like - writing a
> specific trace file to another file system? or any other ways to
> minimize the output?
>
> Or if there are better ways to find long running queries for a
> specific session, i am open to suggestions.
> FYI, I do have Tuning/Diag pack licenses on these databases..
>
> Thanks much
> -Upendra
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 19 2015 - 20:45:33 CET