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)
------------------------------------------------------------------------------