8 Replies Latest reply: Apr 2, 2013 10:23 AM by VANJ RSS

    Why was RBO used?


      spfile - optimizer_mode = ALL_ROWS. optimizer_features_enable =

      Auto stats gathering jobs are all enabled. So everything should be all CBO all the way.

      Some queries were mysteriously slow on Friday. Digging into AWR stats finally revealed that a SQL statement (sql_id) had spawned a plan_hash_value that was the culprit. It was doing a full table scan on a large table and consuming a lot of CPU/IO. Dumping out everything AWR knows about this SQL using
      select * from table(dbms_xplan.display_awr('45knuy45ha5f2',NULL,NULL,'ADVANCED'))
      Pored over the output and sure enough, the "bad" plan_hash_value was using the FTS and the Note in the output said "rule based optimizer used (consider using cbo)".

      I couldn't believe my eyes. Why in the world did that one child cursor use the RBO?

      Any ideas?
        • 1. Re: Why was RBO used?
          Either your session is altered to use rule based optimizer or you have a RULE hint in your query.
          • 2. Re: Why was RBO used?
            • 3. Re: Why was RBO used?
              Martin Preiss
              one answer - but perhaps not the one for your special situation - could be: missing statistics and no dynamic sampling (OPTIMIZER_DYNAMIC_SAMPLING=0 or a historic value for OPTIMIZER_FEATURES_ENABLE):
              drop table t;
              create table t
              select rownum id
                from dual
              connect by level <= 1000;
              -- but no statistics gathering
              alter session set OPTIMIZER_FEATURES_ENABLE='9.2.0';
              -- or: alter session set OPTIMIZER_DYNAMIC_SAMPLING=0;
              explain plan for
              select *
                from t;
              select * from table(dbms_xplan.display);
              Plan hash value: 1601196873
              | Id  | Operation         | Name |
              |   0 | SELECT STATEMENT  |      |
              |   1 |  TABLE ACCESS FULL| T    |
                 - rule based optimizer used (consider using cbo)
              Edited by: Martin Preiss on Apr 2, 2013 3:15 PM
              • 4. Re: Why was RBO used?
                Karthick - No. The query is coming from a application so the query and session parameters are fixed. As AWR shows, the query was executed dozens of times and all the plan_hash_values for the same sql_id use CBO, just this one bad apple used the RBO. Why?

                Hoek - Yes, I read all of Kerry Osborne's excellent blog entries, that's how I narrowed down the problem to this particular sql_id. But it doesn't talk about WHY a particular query would use the RBO. The most common culprit, bind variable peeking, may cause the plan to flip between 2 CBO plans but it shouldn't cause the RBO to be used.

                Martin - That's what I thought too but all objects used in the query do have stats. Since the Oracle auto-stats job/task is running daily, stats are always up-to-date. Here are all the optimizer* parameters in the spfile.

                This is really frustrating.
                optimizer_mode     ALL_ROWS
                optimizer_index_cost_adj     100
                optimizer_index_caching     0
                optimizer_dynamic_sampling     2
                optimizer_secure_view_merging     TRUE
                optimizer_use_pending_statistics     FALSE
                optimizer_capture_sql_plan_baselines     FALSE
                optimizer_use_sql_plan_baselines     TRUE
                optimizer_use_invisible_indexes     FALSE
                [Oracle Support is looking into it but they usually ask for a 10046 trace and we can't provide that because AWR doesn't capture that and the issue is not reproducible at-will]
                • 5. Re: Why was RBO used?
                  Hmz, I provided the wrong link in my previous post, apologies for that.
                  A search on Metalink/MOS didn't get me any additional pointers.
                  Without knowing the exact query, this might be useful to know and/or check anyway: RULE hints are still 'hidden' in the datadictionary tables, see: http://jonathanlewis.wordpress.com/2010/04/13/rule-rules/
                  and the issue is not reproducible at-will
                  Too bad...
                  • 6. Re: Why was RBO used?
                    The only additional pertinent data point is that the database was (inadvertently) started in READ ONLY mode and, from AWR data, the bad RBO plan seems to have been chosen right around that time. It was immediately closed and restarted in read write mode but the bad plan persisted for a few more hours after finally, thankfully, aging out of the cache and things have been smooth ever since. Would readonly mode have anything to do with this?
                    • 7. Re: Why was RBO used?
                      Cherif bh
                      may be this behavior due to an alter system done on you database by an another program or Oracle internal process.

                      alter system set optimizer_mode=rule;

                      When I check v$sql , I can see optimizer_mode =RULE.

                      select distinct optimizer_mode from v$sql

                      you can check the oracle view v$sql_optimizer_env and you can add clauses on hash_value and sql_id to get the right values of parameter used during execution of this query.

                      • 8. Re: Why was RBO used?
                        alter system set optimizer_mode=rule;
                        Nope, no changes to system parameters were done.
                        When I check v$sql , I can see optimizer_mode =RULE.
                        On my system, there are a handful of RULE queries but they are all SYS/SYSTEM ones so that's fine I guess.
                        you can check the oracle view v$sql_optimizer_env and you can add clauses on hash_value and sql_id to get the right values of parameter used during execution of this query.
                        The bad plan_hash_value has aged out of the v$ views and unfortunately AWR doesn't take a snapshot of this view for historical analyis. AWR also doesn't seem to capture the v$sql_shared_cursor view so we can see why (by inspecting all the mismatch columns in the view) the child cursor was created in the first place!