Slow rman Backups? Check This Out
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.
- 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
-
Setting rman to use the
RULEhint instead of CBO: add the following to your rman script:sql "alter session set optimizer_mode=RULE";
- In spite of what Oracle says, there really is no end of the road for the
RULEhint just yet.
The underlying bug# is 5247609 if you’re interested.
January 16th, 2007 at 5:11 pm
very useful stuff to know.
thanks, Raj!
January 16th, 2007 at 9:00 pm
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.
January 16th, 2007 at 9:29 pm
Offhand, do you think this would have any effect on restore/duplication times? My duplications take ~ 8 hours as well.
January 17th, 2007 at 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?
Jaffar
January 18th, 2007 at 12:52 am
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.
January 24th, 2007 at 2:09 pm
Raj,
this is scary , I really thought that RULE was totally out in 10g…..i hope there has been no issues with query performances
January 24th, 2007 at 5:15 pm
Thanks to Dmitry Bogomolov, our Russian readers can enjoy a translated version.
May 3rd, 2007 at 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?
May 4th, 2007 at 7:47 am
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.