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
