Re: RMAN catalog operations taking 128 seconds per file - where is the bottleneck (and no guessing)

From: <Laimutis.Nedzinskas_at_seb.lt>
Date: Tue, 19 Oct 2010 16:09:35 +0300
Message-ID: <OFE96ED866.E6B07EE2-ONC22577C1.0047C32E-C22577C1.004849B6_at_seb.lt>



>Neither event discourages me from the activity in question

After that I found ASH, AWR, all kinds of snappers of v$(session, events, waits, sql, sqlarea, sqlplan_statistics_all ) less intrusive (i.e. more credible) and even more usable actually, specially in today's pooled sessions environment. Oracle improved quite a lot by adding service, user, module/action, plsql object columns into AWR and v$-views (even those not under additional license)


Please consider the environment before printing this e-mail

                                                                           
             Tim Gorman                                                    
             <tim_at_evdbt.com>                                               
                                                                        To 
             2010.10.14 17:06          Laimutis.Nedzinskas_at_seb.lt          
                                                                        cc 
                                       ORACLE-L <oracle-l_at_freelists.org>   
             Please respond to                                     Subject 
               tim_at_evdbt.com           Re: RMAN catalog operations taking  
                                       128 seconds per file - where is the 
                                       bottleneck (and no guessing)        
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           




  That happened to me once, too. I was also knocked out cold in bed once. Neither event discourages me from the activity in question, just makes me a little more aware...

On 10/13/2010 11:31 PM, Laimutis.Nedzinskas_at_seb.lt wrote:
>> Nothing beats SQL tracing and subsequent aggregated profiling
> yes, but in one case it failed me. SQL trace can be quite intrusive.
> In my case I've got 10ms (!!!) per indexed update of one row (no disk
> reads, normal buffer gets)
> It turned out that tracing with events and binds was the issue. Though
the
> real issue was update of dozens of columns but that's how application was
> coded(or designed)
>
>
>
>


>
> Please consider the environment before printing this e-mail
>
>
>
> Tim Gorman
> <tim_at_evdbt.com>
> Sent by:

To
> oracle-l-bounce_at_f Michael Dinh<mdinh_at_XIFIN.Com>
> reelists.org

cc
> "sacrophyte_at_gmail.com"
> <sacrophyte_at_gmail.com>, Jared
Still
> 2010.10.14 01:33<jkstill_at_gmail.com>, ORACLE-L
> <oracle-l_at_freelists.org>
>

Subject
> Please respond to Re: RMAN catalog operations
taking
> tim_at_evdbt.com 128 seconds per file - where is
the
> bottleneck (and no guessing)
>
>
>
>
>
>
>
>
>
>
> That's a good idea too, but debugging isn't really performance tuning.
> Nothing beats SQL tracing and subsequent aggregated profiling (using
> tkprof, trcanlyzr, or hotsos/method-r profiler) for identifying
performance
> issues.
>
>
> On 10/13/2010 4:07 PM, Michael Dinh wrote:
> What about running RMAN in debug?
>
> Michael Dinh : XIFIN : 858.436.2929
>
> NOTICE OF CONFIDENTIALITY - This material is intended for the use
of
> the individual or entity to which it is addressed, and may contain
> information that is privileged, confidential and exempt from
> disclosure under applicable laws. BE FURTHER ADVISED THAT THIS
EMAIL
> MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS
> MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU
> AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
> EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO
> PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE.
If
> the reader of this email (and attachments) is not the intended
> recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly
prohibited.
> Please notify the sender of the error and delete the e-mail you
> received. Thank you.
> From: oracle-l-bounce_at_freelists.org [
> mailto:oracle-l-bounce_at_freelists.org.] On Behalf Of Tim Gorman
> Sent: Wednesday, October 13, 2010 2:48 PM.
> To: sacrophyte_at_gmail.com
> Cc: Jared Still; ORACLE-L
> Subject: Re: RMAN catalog operations taking 128 seconds per file -
> where is the bottleneck (and no guessing)
>
> Charles,
>
> RMAN on a recovery catalog is just another database application,
and
> figuring out performance issue in an RMAN recovery catalog
database
> is the same as for any other database supporting a "3rd-party
> application" in which you have limited (or no) ability to modify
the
> SQL. It could be as simple as coming up with a sensible
> stats-gathering plan, to adding appropriate indexes (after
checking
> for Oracle patches that do the same), etc, but there is no sense
> guessing at it without gathering actual evidence.
>
> Employ SQL tracing in the recovery catalog database initiated with
an
> AFTER LOGON database trigger such as the one here, interpret the
> output using TKPROF or Hotsos/Method-R profiler or TRCANLYZR, and
> rinse and repeat.
>
> Hope this helps...
>
> Tim Gorman
> consultant -> Evergreen Database Technologies, Inc.
> postal => P.O. Box 630791, Highlands Ranch CO 80163-0791
> website => http://www.EvDBT.com/
> email => Tim_at_EvDBT.com
> mobile => +1-303-885-4526,
> fax => +1-303-484-3608,
> Lost Data? => http://www.ora600.be/. for info about DUDE...
>
> On 10/13/2010 1:59 PM, Charles Schultz wrote:
> Thanks, Jared, looks like it could quite possibly be unpublished
bug
> 7444008. I hate bugs. Still, I am curious, what exactly is the
root
> problem?
> On Wed, Oct 13, 2010 at 14:45, Jared Still<jkstill_at_gmail.com>
wrote:
> On Wed, Oct 13, 2010 at 12:05 PM, Charles Schultz<
> sacrophyte_at_gmail.com> wrote:
> Good day, list,
>
> What would cause an RMAN catalog operation to last a relatively
> eternal 128 seconds?
>
>
> There are a number of notes on MOS regarding RMAN performance, you
> may want to check there.
>
> IIRC there are some indexing issues, though I can't recall any
> version #'s.
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com>.
> Home Page: http://jaredstill.com.
>
>
>
>
> --
> Charles Schultz
> -- http://www.freelists.org/webpage/oracle-l
>
>
> -- http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 19 2010 - 08:09:35 CDT

Original text of this message