Slow rman Backups? Check This Out

Jan 16, 2007 / By Raj Thukral

Tags: ,

If you have found rman backups slow in Oracle 10.2.0.2, you should check out note 375386.1 on metalink. It seems the CBO isn’t all its made out to be, and you have to force rman to use the RULE hint instead to make your rman backup run faster. (And you thought RULE was deprecated, didn’t you!)

A few interesting things come out of that Oracle note.

  1. Tracing an rman session: connect to rman and set a 10046 event:
    rman target / log rman.log trace rman.trc debug
    RMAN> sql "alter session set events ' '10046 trace name context forever, level
    12' '"

    Note the two single quotes before 10046 and two single quotes and a double quote after level 12

  2. Setting rman to use the RULE hint instead of CBO: add the following to your rman script:
    sql "alter session set optimizer_mode=RULE";
  3. In spite of what Oracle says, there really is no end of the road for the RULE hint just yet.

The underlying bug# is 5247609 if you’re interested.

10 Responses to “Slow rman Backups? Check This Out”

  • Noons says:

    very useful stuff to know.
    thanks, Raj!

  • Don Seiler says:

    OMFG. I just ran the two test queries in the “REDISCOVERY INFORMATION” section of the bug. Without the rule hint, the query ran in 1:15:24.79. With rule hint, it ran in 00:00:00.14. I even tested new ranges to make sure nothing was cached.

    My level 0 backup of my ~ 840 gb database has been taking over 9 hours. I can’t wait to see what this does.

  • Don Seiler says:

    Offhand, do you think this would have any effect on restore/duplication times? My duplications take ~ 8 hours as well.

  • But, Raj, the note says that the issues is when ‘RMAN SLOW PERFORMANCE DURING RESITER DATABASE/OPEN RESETLOGS’ Is the same with typical backups as well?

    Jaffar

  • Fairlie says:

    What amazes me is that the 10G Docs say that RBO is desupported and the workaround in the bug is to use RBO …lol :-)
    I had another issue a couple of days ago where a query gave incorrect results with CBO but the correct results with the RBO.

  • vidya says:

    Raj,
    this is scary , I really thought that RULE was totally out in 10g…..i hope there has been no issues with query performances

  • chris dittaro says:

    We are having some scary things happening with incorrect query results when running CBO-based statements in our 10G db. When we run the same query ruled-based, it returns the correct result. Tried to add the system parameter:
    set “_cost_equality_semi_join”=false as a workaround (from the Bug fix doc) but it didn’t change a thing.
    does anyone have an answer/fix for this one?

  • Raj Thukral says:

    Chris
    There’s been a few bugs with queries in oracle, but I doubt thats a function of the CBO. You probably need to take a closer look at your query – its likely the order of joins changes the results, which usually means a bad query or a bad join somewhere. Using the rule hint can change the order of joins.
    –Raj.

  • Mila says:

    Incorrect query-
    If you have materialized views , make sure the parameter query_rewrite_enable set to FALSE.
    This was my case with CBO and RBO. CBO was choosing mview instead of table.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>