Everybody knows you shouldn’t be using the Rule-based optimizer (RBO) any more – everyone, that is, except some of the folks at Oracle Corp.
I had a conversation a few weeks ago with someone who was having a problem with their standby database on 10.2 because a query against v$archive_gap was taking a very long time to complete. Now that’s an easy to address (in principle) – collect stats on the underlying X$ objects using the call dbms_stats.gather_fixed_objects_stats() and the magic of cost-based optimisation takes over and solves everything.
So they did - but it didn’t. This is the email he then sent me:
When I used dbms_xplan.display_cursor to find the actual execution plan it indicated that RBO had been used and there were “fixed table full” operations on all the x$ tables used.
Execution Plan ---------------------------------------------------------- Plan hash value: 986750025 ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | |* 1 | FILTER | | | 2 | MERGE JOIN | | | 3 | VIEW | | | 4 | SORT GROUP BY | | | 5 | CONCATENATION | | | 6 | NESTED LOOPS | | | 7 | MERGE JOIN | | | 8 | MERGE JOIN | | | 9 | SORT JOIN | | | 10 | MERGE JOIN | | | 11 | SORT JOIN | | | 12 | FIXED TABLE FULL | X$KCVFH | |* 13 | SORT JOIN | | |* 14 | FIXED TABLE FULL | X$KCCFN | |* 15 | SORT JOIN | | |* 16 | FIXED TABLE FULL | X$KCCFE | |* 17 | SORT JOIN | | |* 18 | FIXED TABLE FULL | X$KCCFN | |* 19 | FIXED TABLE FULL | X$KCCLH | | 20 | NESTED LOOPS | | | 21 | MERGE JOIN | | | 22 | SORT JOIN | | | 23 | MERGE JOIN | | | 24 | SORT JOIN | | | 25 | MERGE JOIN | | | 26 | SORT JOIN | | | 27 | FIXED TABLE FULL| X$KCVFH | |* 28 | SORT JOIN | | |* 29 | FIXED TABLE FULL| X$KCCFN | |* 30 | SORT JOIN | | |* 31 | FIXED TABLE FULL | X$KCCFE | |* 32 | FILTER | | |* 33 | SORT JOIN | | | 34 | FIXED TABLE FULL | X$KCCFN | |* 35 | FIXED TABLE FULL | X$KCCLH | |* 36 | FILTER | | |* 37 | SORT JOIN | | | 38 | VIEW | | |* 39 | FILTER | | | 40 | SORT GROUP BY | | | 41 | MERGE JOIN | | | 42 | VIEW | | | 43 | SORT GROUP BY | | | 44 | FIXED TABLE FULL | X$KCCLH | |* 45 | FILTER | | |* 46 | SORT JOIN | | |* 47 | FIXED TABLE FULL | X$KCCAL | ------------------------------------------------
After looking at v$fixed_view_definition, I found that v$archive_gap actually uses v$log_history, v$datafile, and v$archived_log. All of the X$ tables behind those views had fixed stats on them so I was puzzled why CBO was not used when my optimizer_mode was set to ALL_ROWS.
Then I noticed that the SQL for v$datafile has a hint /*+ rule */ so I guess that forces all the other views used in v$archive_gap to use RBO too?
By hinting my query on v$archived_gap I was able to invoke the CBO, presumably overriding the rule hint in v$datafile.
So “select /*+ all_rows */ * from v$archive_gap;” uses CBO and a much better plan that uses FIXED TABLE FIXED INDEX. It’s makes the difference between seconds and minutes of elapsed time.
Execution Plan ---------------------------------------------------------- Plan hash value: 3885132979 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 52 | 4 (100)| 00:00:01 | |* 1 | FILTER | | | | | | |* 2 | HASH JOIN | | 1 | 52 | 4 (100)| 00:00:01 | | 3 | VIEW | | 1 | 26 | 3 (100)| 00:00:01 | |* 4 | FILTER | | | | | | | 5 | HASH GROUP BY | | 1 | 26 | 3 (100)| 00:00:01 | |* 6 | HASH JOIN | | 2 | 52 | 2 (100)| 00:00:01 | | 7 | VIEW | | 1 | 16 | 1 (100)| 00:00:01 | | 8 | HASH GROUP BY | | 1 | 7 | 1 (100)| 00:00:01 | | 9 | FIXED TABLE FULL | X$KCCLH | 292 | 2044 | 0 (0)| 00:00:01 | |* 10 | FIXED TABLE FULL | X$KCCAL | 40 | 400 | 0 (0)| 00:00:01 | | 11 | VIEW | | 1 | 26 | 1 (100)| 00:00:01 | | 12 | HASH GROUP BY | | 1 | 177 | 1 (100)| 00:00:01 | | 13 | NESTED LOOPS | | 1 | 177 | 0 (0)| 00:00:01 | | 14 | NESTED LOOPS | | 1 | 152 | 0 (0)| 00:00:01 | | 15 | NESTED LOOPS | | 1 | 94 | 0 (0)| 00:00:01 | | 16 | NESTED LOOPS | | 1 | 91 | 0 (0)| 00:00:01 | |* 17 | FIXED TABLE FULL | X$KCCFE | 1 | 33 | 0 (0)| 00:00:01 | |* 18 | FIXED TABLE FIXED INDEX| X$KCCFN (ind:1) | 1 | 58 | 0 (0)| 00:00:01 | |* 19 | FIXED TABLE FIXED INDEX | X$KCVFH (ind:1) | 1 | 3 | 0 (0)| 00:00:01 | |* 20 | FIXED TABLE FULL | X$KCCFN | 1 | 58 | 0 (0)| 00:00:01 | |* 21 | FIXED TABLE FULL | X$KCCLH | 1 | 25 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Interesting, eh?
Definitely worth knowing - and I have an idea I’ve seen a couple of other dynamic view definitions which also include a rule hint – so keep an eye open for them.
Addendum: I’ve just trawled through v$fixed_view_definition, and it’s only gv$datafile that has the /*+ rule */ hint – and even that one has gone by 11.2.0.1
10.2.0.4P35 (Win) & 11.2.0.1 (OEL).
Comment by Timur Akhmadeev — April 13, 2010 @ 7:36 pm UTC Apr 13,2010 |
Timur,
On a contrary, I see one view in my 10.2.0.4 (32-bit WinXP) database.
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
SQL>
SQL>
SQL> select view_name from v$fixed_view_definition where lower(view_definition) like ‘%/*+%rule%*/%’;
VIEW_NAME
——————————
GV$DATAFILE
SQL>
Comment by Asif Momen — April 14, 2010 @ 12:09 pm UTC Apr 14,2010 |
That’s why bundled patches are recommended:
>From 10.2.0.4 onwards each Windows bundled patch goes through full regression testing because it includes a large number of dependent fixes, CPU, DST and PSU. Oracle recommends all customers apply the latest bundle as soon as it is convenient irrespective of encountering a specific problem resolved by the bundle.
This particular bug was fixed in P5.
Comment by Timur Akhmadeev — April 14, 2010 @ 1:43 pm UTC Apr 14,2010 |
Had this issue, it’s bug 5251842. Patches are available for most platforms.
Comment by Michael — April 13, 2010 @ 8:08 pm UTC Apr 13,2010 |
Michael,
Thanks for the reference.
Comment by Jonathan Lewis — April 13, 2010 @ 8:37 pm UTC Apr 13,2010 |
The optmizer doesn’t seem to trust itself though… ;-)
select parsing_schema_name,sql_text from v$SQL where SQL_TEXT like ‘%/*+%rule%*/%’;
Comment by Marcus Mönnig — April 14, 2010 @ 10:29 am UTC Apr 14,2010 |
Marcus,
That’s the problem of recursion: to optimize a statement you need to read data about the objects from the data dictionary – which means you have to query the data dictionary – which means you have to optimize the statements that query the data dictionary … but you can’t optimize the data dictionary queries until you’ve read the data dictionary information that defines the data dictionary.
Comment by Jonathan Lewis — April 14, 2010 @ 10:53 am UTC Apr 14,2010 |
Jonathan,
that makes very much sense. And it puts the “deprecated” status of the RBO into perspective.
Comment by Marcus Moennig — April 14, 2010 @ 8:27 pm UTC Apr 14,2010 |
Oracle can include base step in recursion which will stop it. I mean, incorporate RBO to CBO for data dictionary queries. Since it hasn’t been done for years, I suspect it isn’t an easy task.
Comment by Timur Akhmadeev — April 15, 2010 @ 8:59 am UTC Apr 15,2010 |
As the person who Jonathan spoke to a few weeks ago I’d just like to point out a few things.
1) Although v$datafile itself doesn’t have the rule hint in it, it is itself a view on gv$datafile so you still get a rule based plan.
SQL> select view_definition from v$fixed_view_definition where view_name like ‘V$DATAFILE’;
VIEW_DEFINITION
——————————————————————————–
select … from GV$DATAFILE where inst_id = USERENV(‘Instance’)
(columns removed for brevity)
2) The patch for bug 5251842 isn’t in the PSUs for the non-Windows platforms. On our platform Linux x86-64 it’s not even included in 10.2.0.4.3. It is available as a one-off patch though, so many thanks to Michael for that.
3) The all_rows hint makes the difference between 3 seconds and 3 hours on our largest database which has 11,000 datafiles !
3) I am sure this bug is to blame for our standby database taking hours to decide which logs to obtain when performing gap resolution. It must internally use v$archive_gap. When I apply the patch for bug 5251842 I’ll let you know.
4) My E-mail was blogged with my full permission to Jonathan in the interests of making the Oracle community aware of this issue.
Comment by Owen Ireland — April 15, 2010 @ 4:40 pm UTC Apr 15,2010 |
[...] also shows how Oracle uses the /*+ rule */ hint in some internal views – and everyone knows you should never do [...]
Pingback by Log Buffer #185, a Carnival of the Vanities for DBAs — February 13, 2013 @ 4:39 pm UTC Feb 13,2013 |
Did you got any resolution on this issue from Oracle?
Comment by Israel — May 14, 2013 @ 8:18 pm UTC May 14,2013 |