Upendra,
Already directions have been provided few suggestions.
1. If you can go through the code to understand how data is been processed.
If it's row by row processing or processing of queries in loops.
2. If permanent tables have been used as staging instead of GTT.
3. From AWR report if you already have you csn find which queries were most
time consuming, reading most data, executed most.
Based on the findings you can find execution plan of query and take it
forward, also you can selectively trace queries if required.
Hope that helps.
Regards,
Vijay Sehgal
On 20 Nov 2015 00:25, "Upendra nerilla" <nupendra@xxxxxxxxxxx> 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
