Thanks for feedback from everyone. I think I should add more information so the
context is clear..
It is a hybrid environment, OLTP and DW mixed together. Also large and
small tables. So simply picking any SQL based on the execution time
won't always work.
The scenario is that we have identified a problem package or procedure which
needs to be tuned based on standard monitoring/AWR/user complaints etc . I am
trying to trace the session running ONLY that procedure and gathering various
metrics before pinpointing which specific SQL statement needs to be tuned.
The issue I am running into is I don't have enough space in the udump directory
to run large scale trace. I have space in a NFS file system, which I could
offload but i don't want to repoint the entire dump dest to NFS for extended
period of time.
Wanted to see if there is a way to write a specific trace file to another
location or just for a specific session?
Thanks again
-Upendra
Date: Fri, 20 Nov 2015 09:09:57 +0530
Subject: Re: Finding long running queries..
From: vijaysehgal21@xxxxxxxxx
To: nupendra@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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
