RE: Finding long running queries..
Date: Thu, 19 Nov 2015 23:14:33 -0500
Message-ID: <BLU181-W78D57A96619F2DA920DBBCD81A0_at_phx.gbl>
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_at_gmail.com
To: nupendra_at_hotmail.com
CC: oracle-l_at_freelists.org
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_at_hotmail.com> 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-l
Received on Fri Nov 20 2015 - 05:14:33 CET