Skip to Main Content
  • Questions

  • Why is the optimizer so wildly off ?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Falco.

Asked: January 08, 2014 - 4:34 pm UTC

Last updated: January 10, 2014 - 6:44 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi! I recently had a Problem with 2 SQL-Statements performing an equal query, but with wildly different runtimes. I think I narrowed the problem down to a simple case, where the optimizer is wildly off. The statistics are up to date and fit the actual data.

When I run the query with a subquery (returning a single constant value) in the WHERE-clause, the query is excruciating slow (about 350 seconds). If I evaluate the subquery by hand (takes some miliseconds) and pass the return-value as a constant to the query, the query will run in some miliseconds time (with a completely different XPLAN)
Since the subquery is a simple MAX(...) query - why can't the optimizer use the same plan, or at least predict that a single value will be returned and optimize the same way as with a binding-variable ?

Both tables have several M rows of data. The Type-Field of doc_context has only 5 different cases. The ctx_doc_id in doc_context is a Foreign Key to the Primary Key of documents (doc_id).

SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER1 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = 'TYPE1'
   AND ctx_doc_id > 
       (SELECT MAX(doc_id) - 1500000
        FROM documents);
 
 ... takes about 350 seconds returns about 1M rows...

SELECT * FROM TABLE(dbms_xplan.display_cursor('19m325gnn5n7u', 0));

SQL_ID  f4kydzugzp1nw, child number 0
-------------------------------------
SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER1 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = :"SYS_B_0"
   AND ctx_doc_id > (SELECT MAX(doc_id) - :"SYS_B_1" FROM documents)
 
Plan hash value: 1956809540
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |       |       |       | 86320 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID     | DOC_CONTEXT  |  1020K|    92M|       | 86319  (11)| 00:01:19 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |              |       |       |       |            |          |
|   3 |    BITMAP AND                    |              |       |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|              |       |       |       |            |          |
|   5 |      SORT ORDER BY               |              |       |       |    48M|            |          |
|*  6 |       INDEX RANGE SCAN           | CTX_DOC_ID   |    20M|       |       |   596   (6)| 00:00:01 |
|   7 |        SORT AGGREGATE            |              |     1 |     6 |       |            |          |
|   8 |         INDEX FULL SCAN (MIN/MAX)| DOC_PK       |     1 |     6 |       |     1   (0)| 00:00:01 |
|   9 |     BITMAP CONVERSION FROM ROWIDS|              |       |       |       |            |          |
|* 10 |      INDEX RANGE SCAN            | CTX_TYPE     |    20M|       |       | 29398   (4)| 00:00:27 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("CTX_DOC_ID">)
       filter("CTX_DOC_ID">)
  10 - access("CTX"."CTX_TYPE"=:SYS_B_0)
  
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

SELECT MAX(doc_id) - 1500000
       /*+ gather_plan_statistics */ /* MARKER2 */
  FROM documents;
  
>> returns: 97123324
  
 ... takes about 17ms ...

 
 SQL_ID  9qqrs33jf7nb8, child number 0
-------------------------------------
SELECT MAX(doc_id) - :"SYS_B_0" /*+ gather_plan_statistics */ /* MARKER2 */
  FROM documents
 
Plan hash value: 3095536997
 
-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE            |        |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| DOC_PK |     1 |     6 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
 
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
 
SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER3 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = 'TYPE1'
   AND ctx_doc_id > 
       (97123324);

 ... takes about 2 seconds ...
 
 
 
 SQL_ID  dqyr4uh1v51yu, child number 0
-------------------------------------
SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER3 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = :"SYS_B_0"
   AND ctx_doc_id > 
       (:"SYS_B_1")
 
Plan hash value: 1220738720
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       | 40251 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DOC_CONTEXT  |   436K|    39M| 40251   (2)| 00:00:37 |
|*  2 |   INDEX RANGE SCAN          | CTX_DOC_ID   |  1347K|       |  1414   (5)| 00:00:02 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("CTX"."CTX_TYPE"=:SYS_B_0)
   2 - access("CTX_DOC_ID">:SYS_B_1)

and Tom said...

cursor_sharing = force/similar :( :( :(

that means you have a problem big time here, in the developed code. They have not only introduced a big performance problem, scalability problem - but a huge huge security hole in your application. You are subject to sql injection. Not "might be", but you are subject to sql injection. If your coders didn't know to use binds, they do not know how to protect (it is really quite hard) from sql injection issues.


In the first case - the optimizer has to "guess" when optimizing:


SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER1 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = :"SYS_B_0"
   AND ctx_doc_id > (SELECT MAX(doc_id) - :"SYS_B_1" FROM documents)



it doesn't know what might be returned from the subquery - it sees "select f(x) from t" - some expression is being returned. It knows it will be a single row returned, but it has no idea what the value might be.

Then it compares that to ctx_doc_id and assumes 20,000,000 rows will come back (in real life, it looks like ~1,347,000 rows will be returned... I base that on the second query).

In short, the first query is optimizing "wherre ctx_doc_id = ?", it doesn't know what ? is.


the second query:



SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER3 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = :"SYS_B_0"
   AND ctx_doc_id > 
       (:"SYS_B_1")


will be optimized using bind peeking, the subquery value is actually plugged in and the query:

SELECT ctx.* /*+ gather_plan_statistics */ /* MARKER3 */
  FROM doc_context ctx
 WHERE ctx.ctx_type = 'some value'
   AND ctx_doc_id > some_number


will be optimized - bind peeking will supply 'some value' and some_number. As compared to:

<code>
WHERE ctx.ctx_type = 'some value'
AND ctx_doc_id > (select f(x) from t)
<code>


see some of the articles:

https://www.google.com/search?q=site%3Atkyte.blogspot.com+bind+peeking

to read about bind peeking.


this is a case where a sql profile might be called for. The sql profile will look at the execution history and possibly run parts of the where clause to validate the estimated cardinalities. If they are far off from reality - the profile will adjust them. A sql profile is like having "statistics on a query", it is not a workaround, it is not a hack, it is simply a way to give the optimizer better - more - information to arrive at correct cardinality estimates



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you very much - would the optimizer actually be right with his guess?

Falco, January 09, 2014 - 9:40 am UTC

Hi Tom! Thank you very much for the fast response.

So the optimizer guesses the number of returned rows wrong, since he can't use bind peeking...

So the XPLAN of the first query (bitmap conversion...) would actually perform better, if the number of rows would be 20M (or whatever the optimizer guesses) ?

PS: I can't really discern the first part of your answer about SQL injection... ?
Tom Kyte
January 09, 2014 - 7:23 pm UTC

first about sql injection.


I can see you are using cursor-sharing = force/similar, not exact.

that means your developers didn't use bind variables. They are using string concatenation. They take inputs passed to them and just put them into SQL and then *compile* that sql (including the inputs just sent to them) and run that compiled program.

that means your developed code is subject to sql injection, it has a security hole the size of the moon in it. sql injection is perhaps the *#1* cause of security breaches with respect to internet applications. If I find a sql injection flaw in your application - just one out of the 10's of thousands of sql statements in your application - I have at the very least read access to everything in your applications control. I might have total control over your application schema - and the ability to do anything in your database your application schema can do. So, the potential for damage from sql injection goes from very very bad to catastrophically, horrendously bad.

And I know your application suffers from it, because they are using string concatenation - and there will be at least one - if not hundreds or thousands - of points in your application where I can change the sql in such a way to do some very nasty things.

read more here:
https://www.google.com/search?q=site%3Atkyte.blogspot.com+sql+injection



And yes, if the estimated cardinalities are correct, the optimizer will almost surely (almost ;) ) choose the best plan.

I say in my talks that if you have a bad plan - the root cause is going to be an incorrectly estimated cardinality. The cause of bad plans is almost always incorrect card estimates. If you get the right card in there - we get the right plan. statistics, extended statistics, dynamic sampling (dynamic statistics in 12c and above), sql profiles, first_rows(n) hint, cardinality hint, adaptive query plans, cardinality feedback, etc are all ways of getting the right card estimates to the optimizer.

Using Optimizer Hints or Baseline to speed up the query

Falco, January 09, 2014 - 5:46 pm UTC

Hi! Thanks again for your kind reply.

I tried to optimize the query, by using optimizer hints to get the fast XPLAN when running the query - after a long time of searching around, I thought the CARDINALITY hint was the right one for my problem, but the optimizer will always merge the queries together and choose the approach converting to bitmap indexes...

I could finally convince the optimizer to use the faster Plan, by using the trick from an older question on asktom: providing the subquery with an WHERE ROWNUM > 0 to force the creation of a subview...
But I couldn't produce the same with optimizer hints (NO_MERGE, NO_UNNEST, didn't have any impact)

My current statement:

SQL_ID  2mvc4689szu9z, child number 0
-------------------------------------
SELECT /*+ CARDINALITY(ctx2 1500000) gather_plan_statistics */
       COUNT(*) /* MARKER_A2 */
  FROM (SELECT /*+  CARDINALITY(ctx 1500000) */ *
          FROM doc_context ctx
         WHERE ctx.ctx_doc_id > (SELECT MAX(doc_id) - 1500000 FROM documents)
           AND rownum > 0
       ) ctx2
 WHERE ctx2.ctx_type = 'TYPE1'
 
Plan hash value: 2492668154
 
----------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | E-Rows |A-Rows|A-Time|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |      2 |        |    2 |01:16 |
|   1 |  SORT AGGREGATE                  |             |      2 |      1 |    2 |01:16 |
|*  2 |   VIEW                           |             |      2 |   1500K| 1997K|01:19 |
|   3 |    COUNT                         |             |      2 |        | 3168K|00:42 |
|*  4 |     FILTER                       |             |      2 |        | 3168K|00:40 |
|   5 |      TABLE ACCESS BY INDEX ROWID | DOC_CONTEXT |      2 |   1500K| 3168K|00:37 |
|*  6 |       INDEX RANGE SCAN           | CTX_DOC_ID  |      2 |    566K| 3168K|00:24 |
|   7 |        SORT AGGREGATE            |             |      2 |      1 |    2 |00:00 |
|   8 |         INDEX FULL SCAN (MIN/MAX)| DOC_PK      |      2 |      1 |    2 |00:00 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("CTX2"."CTX_TYPE"='TYPE1')
   4 - filter(ROWNUM>0)
   6 - access("CTX"."CTX_DOC_ID">)

Tom Kyte
January 09, 2014 - 7:35 pm UTC

can you give me a full test case - create tables, create indexes, dbms_stats.SET_ (set, not gather) calls

and I'll play with it.

(have you tried first_rows(1) at the top level of the query? )

Sample Tables to reproduce behaviour

Falco, January 10, 2014 - 1:46 pm UTC

Hi Tom!

Thanks again for your dedication!!! So I took the time to construct a complete test-case like you always do for demonstrations... I hope you can play with this and maybe share some insight!

Here we go:

CREATE TABLE t AS SELECT * FROM all_objects;

ALTER TABLE t ADD CONSTRAINT t_id_pk PRIMARY KEY (object_id);
CREATE INDEX t_idx_id ON t(object_id);
CREATE INDEX t_idx_type ON t(object_type);

exec DBMS_STATS.GATHER_TABLE_STATS('me','t');

-- Checking Numbers:
SELECT count(*) FROM t; -- 92,591 ROWS
SELECT count(*) FROM t WHERE object_type = 'TABLE'; -- 8,553 ROWS
SELECT MAX(object_id) - 500000 FROM t; -- 19,975
SELECT count(*) FROM t WHERE object_id > (SELECT MAX(object_id) - 500000 FROM t); -- 78,561 ROWS

set autotrace traceonly;



-- Now the SELECT statement how I would write it naturally:

SQL> SELECT /*+ gather_plan_statistics */
  2         *
  3    FROM t t1
  4   WHERE t1.object_type = 'TABLE'
  5     AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);

6115 rows selected.

Execution Plan
-----------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |   413 | 39235 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | T          |   413 | 39235 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |            |       |       |
|   3 |    BITMAP AND                    |            |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |
|   5 |      SORT ORDER BY               |            |       |       |
|*  6 |       INDEX RANGE SCAN           | T_IDX_ID   |  8252 |       |
|   7 |        SORT AGGREGATE            |            |     1 |     5 |
|   8 |         INDEX FULL SCAN (MIN/MAX)| T_IDX_ID   |     1 |     5 |
|   9 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |
|* 10 |      INDEX RANGE SCAN            | T_IDX_TYPE |  8252 |       |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM
              "T" "T2"))
       filter("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM
              "T" "T2"))
  10 - access("T1"."OBJECT_TYPE"='TABLE')


-- Now the SELECT statement with a literal instead of the subquery:

SQL> SELECT MAX(t2.object_id) - 500000 FROM t t2;

MAX(T2.OBJECT_ID)-500000
------------------------
                   19975


SQL> SELECT /*+ gather_plan_statistics */
  2         *
  3    FROM t t1
  4   WHERE t1.object_type = 'TABLE'
  5     AND t1.object_id > 19975;

6115 rows selected.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |  7198 |   667K|   102   (2)|
|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |  7198 |   667K|   102   (2)|
|*  2 |   INDEX RANGE SCAN          | T_IDX_TYPE |  8553 |       |     6   (0)|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."OBJECT_ID">19975)
   2 - access("T1"."OBJECT_TYPE"='TABLE')



-- The optimizer finds the better Plan if he knows the literal '19975'
-- Can he better guess the number of rows from this? Because I couldn't convince him
-- To use this Plan with any hints in the original query...
-- But this query is 5 times as fast!!!

-- One way to get the optimizer to use a similar Plan (INDEX_SCAN over TYPE followed
-- by filtered access via OBJECT_ID) is the trick with a subquery including rownum
-- And this is the only way I found!


SQL> SELECT /*+ gather_plan_statistics */
  2         *
  3    FROM (SELECT *
  4            FROM t t2
  5           WHERE t2.object_id > (SELECT MAX(t3.object_id) - 500000 FROM t t3)
  6             AND rownum > 0
  7         ) t1
  8   WHERE t1.object_type = 'TABLE';

6115 rows selected.


Execution Plan
--------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |  4629 |   714K|
|*  1 |  VIEW                           |          |  4629 |   714K|
|   2 |   COUNT                         |          |       |       |
|*  3 |    FILTER                       |          |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID | T        |  4629 |   429K|
|*  5 |      INDEX RANGE SCAN           | T_IDX_ID |   833 |       |
|   6 |       SORT AGGREGATE            |          |     1 |     5 |
|   7 |        INDEX FULL SCAN (MIN/MAX)| T_IDX_ID |     1 |     5 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."OBJECT_TYPE"='TABLE')
   3 - filter(ROWNUM>0)
   5 - access("T2"."OBJECT_ID"> (SELECT MAX("T3"."OBJECT_ID")-500000
              FROM "T" "T3"))


Although I have some overhead with the subview and in this case with these numbers I scan over the worse attribute first (since fitering by Type first and accessing by Object_id afterwards would be better) I am still twice as fast as the optimizers original plan using two bitmap-converted merges...

I hope this is everything you need to test it yourself...

The question is - how can I tell the optimizer to use the best plan (the one he self chooses when I put in a literal instead of the subquery) - because if I could do it with hints, I could save a baseline for future executions, right?
Tom Kyte
January 10, 2014 - 6:42 pm UTC

ok, I tested "first_rows(1)" and as I suspected - it "worked", it got the desired plan. It could have been that we disabled _b_tree_bitmap_plans or some other parameter setting to get the desired plan - that isn't relevant - what is relevant is that I found a hinted plan that did what I desired.

Having that - I can put that plan in place for the "bad" query.

Consider:

ops$tkyte%ORA11GR2> CREATE TABLE t AS SELECT * FROM all_objects;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> ALTER TABLE t ADD CONSTRAINT t_id_pk PRIMARY KEY (object_id);
ops$tkyte%ORA11GR2> CREATE INDEX t_idx_type ON t(object_type);
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T');
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> SELECT *
  2    FROM t t1
  3   WHERE t1.object_type = 'TABLE'
  4     AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);

Execution Plan
----------------------------------------------------------
Plan hash value: 2949394672

------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes | Cost (
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |    94 |  9118 |    32
|   1 |  TABLE ACCESS BY INDEX ROWID     | T          |    94 |  9118 |    30
|   2 |   BITMAP CONVERSION TO ROWIDS    |            |       |       |
|   3 |    BITMAP AND                    |            |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |
|   5 |      SORT ORDER BY               |            |       |       |
|*  6 |       INDEX RANGE SCAN           | T_ID_PK    |  1871 |       |     3
|   7 |        SORT AGGREGATE            |            |     1 |     5 |
|   8 |         INDEX FULL SCAN (MIN/MAX)| T_ID_PK    |     1 |     5 |     2
|   9 |     BITMAP CONVERSION FROM ROWIDS|            |       |       |
|* 10 |      INDEX RANGE SCAN            | T_IDX_TYPE |  1871 |       |     6
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM "T"
       filter("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM "T"
  10 - access("T1"."OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        535  consistent gets
          0  physical reads
          0  redo size
     150108  bytes sent via SQL*Net to client
       2631  bytes received via SQL*Net from client
        203  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3027  rows processed


<b>Now, there is your "bad" plan.  Once I have that - we can baseline it (seems like a bad idea, but it isn't!)
</b>



ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2      dbms_output.put_line(
  3      dbms_spm.load_plans_from_cursor_cache
  4         ( sql_id => 'crvk9z6mx9n4d' )
  5      );
  6  end;
  7  /
1
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select sql_handle,
  2         substr(sql_text,1,10)||'...'||
  3               substr(sql_text,length(sql_text)-10) stext,
  4         plan_name, enabled
  5    from dba_sql_plan_baselines
  6   where sql_text like
  7     'SELECT%FROM t t1%(SELECT MAX(t2.object_id) - 500000 FROM t t2)';

SQL_HANDLE           STEXT                    PLAN_NAME                      ENA
-------------------- ------------------------ ------------------------------ ---
SQL_e738c19a5191e8fd SELECT *                 SQL_PLAN_fff61m98t3u7xda64b1bb YES
                      ... FROM t t2)


<b>so, that is our baseline - making so this plan WILL be used. But that isn't what we want - we'll disable it:</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2      dbms_output.put_line(
  3      dbms_spm.alter_sql_plan_baseline
  4      ( sql_handle => 'SQL_e738c19a5191e8fd',
  5        attribute_name => 'enabled',
  6        attribute_value => 'NO' )
  7      );
  8  end;
  9  /
1

<b>Now, the database will *still* use that plan as it is the only game in town right now.  So, let's get something loaded up that we like better:</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> SELECT /*+ first_rows(1) */ *
  2    FROM t t1
  3   WHERE t1.object_type = 'TABLE'
  4     AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1289158178

------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    97 |     4   (0)
|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |     1 |    97 |     2   (0)
|*  2 |   INDEX RANGE SCAN          | T_IDX_TYPE |  1871 |       |     1   (0)
|   3 |   SORT AGGREGATE            |            |     1 |     5 |
|   4 |    INDEX FULL SCAN (MIN/MAX)| T_ID_PK    |     1 |     5 |     2   (0)
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."OBJECT_ID"> (SELECT MAX("T2"."OBJECT_ID")-500000 FROM "T"
              "T2"))
   2 - access("T1"."OBJECT_TYPE"='TABLE')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        582  consistent gets
          0  physical reads
          0  redo size
     150108  bytes sent via SQL*Net to client
       2631  bytes received via SQL*Net from client
        203  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3027  rows processed

<b>that is the plan we desired - all we need to do is load that plan in place of the bad one we have:</b>


ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2      dbms_output.put_line(
  3      dbms_spm.load_plans_from_cursor_cache
  4        ( sql_id => '5mn39tz7fpjnu',
  5          plan_hash_value => 1289158178,
  6          sql_handle => 'SQL_e738c19a5191e8fd' )
  7      );
  8  end;
  9  /
1
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select sql_handle,
  2         substr(sql_text,1,10)||'...'||
  3               substr(sql_text,length(sql_text)-10) stext,
  4         plan_name, enabled
  5    from dba_sql_plan_baselines
  6   where sql_text like
  7     'SELECT%FROM t t1%(SELECT MAX(t2.object_id) - 500000 FROM t t2)';

SQL_HANDLE           STEXT                    PLAN_NAME                      ENA
-------------------- ------------------------ ------------------------------ ---
SQL_e738c19a5191e8fd SELECT *                 SQL_PLAN_fff61m98t3u7x971f1a3f YES
                      ... FROM t t2)

SQL_e738c19a5191e8fd SELECT *                 SQL_PLAN_fff61m98t3u7xda64b1bb NO
                      ... FROM t t2)


<b>there are now two plans loaded up for that sql statement - one disabled (bad plan) and one enabled (good plan, even though it is for a "different query", it can work with this query)</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> SELECT *
  2    FROM t t1
  3   WHERE t1.object_type = 'TABLE'
  4     AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1289158178

------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    97 |     4   (0)
|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |     1 |    97 |     2   (0)
|*  2 |   INDEX RANGE SCAN          | T_IDX_TYPE |  1871 |       |     1   (0)
|   3 |   SORT AGGREGATE            |            |     1 |     5 |
|   4 |    INDEX FULL SCAN (MIN/MAX)| T_ID_PK    |     1 |     5 |     2   (0)
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T1"."OBJECT_ID"> (SELECT /*+ PUSH_SUBQ INDEX ("T2" "T_ID_PK") *
              MAX("T2"."OBJECT_ID")-500000 FROM "T" "T2"))
   2 - access("T1"."OBJECT_TYPE"='TABLE')

Note
-----
   - SQL plan baseline "SQL_PLAN_fff61m98t3u7x971f1a3f" used for this statemen


Statistics
----------------------------------------------------------
         30  recursive calls
         16  db block gets
        604  consistent gets
          2  physical reads
       5100  redo size
     150108  bytes sent via SQL*Net to client
       2631  bytes received via SQL*Net from client
        203  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       3027  rows processed

ops$tkyte%ORA11GR2> set autotrace off


and when we run it again, we can see it used the query plan baseline for our "good" plan for that query.

No permanent hinting.

No upgrade issues (you might have to completely change all of your hints after an upgrade as we add new query rewrites, access paths and so on - this way you won't)

and if a better plan comes along - we'll be able to "evolve" to using it if you are using query plan evolution....



Again, if first_rows(1) doesn't do it for you, you could also try:


ops$tkyte%ORA11GR2> select /*+ opt_param( '_b_tree_bitmap_plans', 'FALSE' ) */ *
  2    FROM t t1
  3   WHERE t1.object_type = 'TABLE'
  4     AND t1.object_id > (SELECT MAX(t2.object_id) - 500000 FROM t t2);

Execution Plan
----------------------------------------------------------
Plan hash value: 1289158178

------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    94 |  9118 |    85   (0)
|*  1 |  TABLE ACCESS BY INDEX ROWID| T          |    94 |  9118 |    83   (0)
|*  2 |   INDEX RANGE SCAN          | T_IDX_TYPE |  1871 |       |     6   (0)
|   3 |   SORT AGGREGATE            |            |     1 |     5 |
|   4 |    INDEX FULL SCAN (MIN/MAX)| T_ID_PK    |     1 |     5 |     2   (0)
------------------------------------------------------------------------------



PS additional information

Falco, January 10, 2014 - 1:57 pm UTC

Hi! I forgot to mention:

Yes I tried the first_rows hint to no avail - I tried it with expected numbers (the number of rows returned) and also with lower numbers and FIRST_ROWS(1) -- The executionplan changed, but still not to the optimal plan with the literal number (which is the same as with bind peeking)

I think currently the best option would be to encapsulate everything in a plsql method, where the subquery is evaluated first into a variable and that variable is used as a bind-variable in the query, allowing the optimizer to use bind peeking to choose the best plan... But I still find it intriguing to solve this one with an SQL Profile or a baseline! Maybe you can help!

* I didn't include the DBMS_STAT.SET_TABLE_STATS, since the behaviour was the same for several tables with greatly varying row-numbers (Millions vs. thousands) I could still provide such statements, if you can not reproduce the behavior like this!
Tom Kyte
January 10, 2014 - 6:44 pm UTC

can you try the _b_tree_bitmap_plans off?


Thanks a lot! That did the trick!!!

Falco, January 13, 2014 - 3:38 pm UTC

Wow!!! Thanks a lot - the optimizer Param /*+ opt_param( '_b_tree_bitmap_plans', 'FALSE' ) */ did the trick!!!

I have never seen this param before, but it works as expected and the optimizer finds the faster plan. And I can save it as a baseline and use the statement without hints - perfect :-)

Thank you for a lot of insight into the mysterious ways of the optimizer ^^
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.