Upendra
In your position I would run AWR for minimal time duration(60mins) and compare
AWR to find which is causing bottleneck.
AWR provide more details such as system load from DW or OLTP. Does DW and OLTP
running on same DB account? Do they share single Temp table space?
In addition with AWR if you have license generate ADDM. That gives for all
instance of RAC that gives clear picture.
HTH
Karth
Sent from my IPhone
On Nov 19, 2015, at 11:14 PM, Upendra nerilla <nupendra@xxxxxxxxxxx> wrote:
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
