RE: Finding long running queries..

From: Upendra nerilla <nupendra_at_hotmail.com>
Date: Thu, 19 Nov 2015 15:17:18 -0500
Message-ID: <BLU181-W50706EB7792FEB3377FC14D81B0_at_phx.gbl>

Right. I usually run tkprof and sort based on execution time and pick the statements that matter the most. That eliminates most noise..

-Upendra

Subject: Re: Finding long running queries.. To: nupendra_at_hotmail.com; oracle-l_at_freelists.org From: sfaroult_at_roughsea.com
Date: Thu, 19 Nov 2015 13:45:33 -0600

  
    
  
  
    Looks more to me that your problem isn't long running queries but
    tons of short running ones. I have had a similar problem once with a
    report that was taking (more modestly) over one hour to generate 70
    pages. I traced the process, I couldn't open (on Solaris) the
    tracefile in vi. Too big.

    I had to resort to that good old grep to try to see what was in the
    file and I found over 600,000 queries.

    None of them was very long. They were all pretty fast, actually. But
    around 10,000 queries per page was perhaps a bit excessive.

    

    Stéphane Faroult

    

    On 19/11/15 12:55, 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 
    
    
 		 	   		  

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 19 2015 - 21:17:18 CET