Oracle Scratchpad

April 13, 2010

Rule Rules

Filed under: Execution plans,Hints,Troubleshooting — Jonathan Lewis @ 6:39 pm GMT Apr 13,2010

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

13 Comments »

  1. SQL> select view_name, view_definition from v$fixed_view_definition where lower(view_definition) like '%/*+%rule%*/%';
    
    no rows selected
    

    10.2.0.4P35 (Win) & 11.2.0.1 (OEL).

    Comment by Timur Akhmadeev — April 13, 2010 @ 7:36 pm GMT Apr 13,2010 | Reply

    • 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 GMT Apr 14,2010 | Reply

      • 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 GMT Apr 14,2010 | Reply

  2. Had this issue, it’s bug 5251842. Patches are available for most platforms.

    Comment by Michael — April 13, 2010 @ 8:08 pm GMT Apr 13,2010 | Reply

  3. 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 GMT Apr 14,2010 | Reply

    • 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 GMT Apr 14,2010 | Reply

      • 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 GMT Apr 14,2010 | Reply

      • 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 GMT Apr 15,2010 | Reply

  4. 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 GMT Apr 15,2010 | Reply

  5. […] 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 | Reply

  6. Did you got any resolution on this issue from Oracle?

    Comment by Israel — May 14, 2013 @ 8:18 pm GMT May 14,2013 | Reply

    • 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 GMT Jun 9,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers