Re: Finding long running queries..
Date: Thu, 19 Nov 2015 11:12:29 -0800
Message-ID: <564E1F1D.6000404_at_oracle.com>
Before you look at query details..how about backing up a little bit and seeing the BIG picture ?
- *Why *type of database are the queries running on ? OLTP, DWH, Hybrid
- *When *are you running the queries (batch or otherwise)
- *What *other activity is occurring on the DB at time of query (DDL / DML)
- *How *much data is being scanned by the query(s) & and is it static or dynamic
If you can answer some / all of the above..you maybe able to address from a macro-level, ie adjusting a DB parameter ?
I've learned folks trying to look into tuning SQL when the DB architecture itself is setup incorrectly, and no amount of tuning will give a good to decent ROI on the time / effort spent.
Good luck.
On 11/19/2015 10:55 AM, 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:12:29 CET