Slow rman Backups? Check This Out

Posted in: Technical Track

If you have found rman backups slow in Oracle, 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.



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Raj has been managing and guiding highly effective teams for over 20 years. Raj combines strong leadership skills with a deeply technical background in IT consulting to bring tailored and efficient technology solutions to Pythian. Additionally, his focus on data security has helped Pythian meet and often exceed client and regulatory requirements. Prior to joining Pythian in 2003, Raj held various leadership positions with a proven record of success. He has a degree in Industrial Engineering and has worked in multiple IT and engineering fields.

10 Comments. Leave new

very useful stuff to know.
thanks, Raj!


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.


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

Jsffar Hussain
January 17, 2007 1:43 am

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?



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.


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

Alex Gorbachev
January 24, 2007 5:15 pm

Thanks to Dmitry Bogomolov, our Russian readers can enjoy a translated version.

chris dittaro
May 3, 2007 4:42 pm

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
May 4, 2007 7:47 am

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.


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

Your email address will not be published. Required fields are marked *