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.
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.
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
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.
Raj,
this is scary , I really thought that RULE was totally out in 10g…..i hope there has been no issues with query performances
Thanks to Dmitry Bogomolov, our Russian readers can enjoy a translated version.
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?
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.
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.