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
Oracle Scratchpad 
10.2.0.4P35 (Win) & 11.2.0.1 (OEL).
Comment by Timur Akhmadeev — April 13, 2010 @ 7:36 pm BST 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 BST 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 BST 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 BST Apr 13,2010 |
Michael,
Thanks for the reference.
Comment by Jonathan Lewis — April 13, 2010 @ 8:37 pm BST 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 BST 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 BST 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 BST 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 BST 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 BST 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 GMT Feb 13,2013 |
Did you got any resolution on this issue from Oracle?
Comment by Israel — May 14, 2013 @ 8:18 pm BST May 14,2013 |
Israel,
I didn’t raise this with Oracle – if I raised an SR for every oddity I found I’d never have any time to do anything interesting.
No doubt it has been raised by someone (possibly Owen), and will probably get addressed at some point.
Comment by Jonathan Lewis — June 9, 2013 @ 12:49 pm BST Jun 9,2013 |
[…] somewhere in the depths of the query rather than being in the top level query block – but we’ve seen that before in some old data dictionary […]
Pingback by Distributed Query | Oracle Scratchpad — August 23, 2021 @ 5:24 pm BST Aug 23,2021 |