使用outline固定sql执行计划

来源：互联网发布：js中select标签编辑：程序博客网时间：2024/05/16 17:33

转http://blog.csdn.net/aaaaaaaa2000/article/details/9086111

SQL> set linesize 1000
SQL> col index_name for a40
SQL> col COLUMN_NAME for a20
SQL> select index_name,COLUMN_NAME from dba_ind_columns where table_name='TEST_PART' and table_owner='TEST_DBA';

INDEX_NAME COLUMN_NAME
---------------------------------------- --------------------
IND_PART_DT DT

SQL>
此表由索引，可以测试走做引以及走全表扫描
SQL> set autotrace on explain
SQL> set pagesize 300
SQL> set linesize 300
SQL> var name1 varchar2(10);
SQL> var name2 varchar2(100);
SQL> var name1 varchar2(100);
SQL> exec :name1 :='2012-09-21 8:05:55';

PL/SQL procedure successfully completed.

SQL> exec :name2 :='2012-09-21 8:06:05';

PL/SQL procedure successfully completed.
SQL> select * from TEST_DBA.TEST_PART where dt>=to_date(:name1,'yyyy-mm-dd hh24:mi:ss') and
2 dt<to_date(:name2,'yyyy-mm-dd hh24:mi:ss') ;

        ID STR        DT
---------- ---------- ---------
     29155 aaaaaaaaa 21-SEP-12
     29156 aaaaaaaaa 21-SEP-12
     29157 aaaaaaaaa 21-SEP-12
     29158 aaaaaaaaa 21-SEP-12
     29159 aaaaaaaaa 21-SEP-12
     29160 aaaaaaaaa 21-SEP-12
     29161 aaaaaaaaa 21-SEP-12
     29162 aaaaaaaaa 21-SEP-12
     29163 aaaaaaaaa 21-SEP-12
     29164 aaaaaaaaa 21-SEP-12

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2229598636

-------------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name        | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |   250 | 5750 |     5   (0)| 00:00:01 |       |       |
|* 1 | FILTER                             |             |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR          |             |   250 | 5750 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PART   |   250 | 5750 |     5   (0)| 00:00:01 |   KEY |   KEY |
|* 4 |     INDEX RANGE SCAN                | IND_PART_DT |   450 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss')<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))
   4 - access("DT">=TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss') AND "DT"<TO_DATE(:NAME2,'yyyy-mm-dd
              hh24:mi:ss'))
以上生成了索引的执行sql

SQL> select /*+FULL(TEST_PART)*/ * from TEST_DBA.TEST_PART where dt>=to_date(:name1,'yyyy-mm-dd hh24:mi:ss') and
2 dt<to_date(:name2,'yyyy-mm-dd hh24:mi:ss') ;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1956636844

-------------------------------------------------------------------------------------------------------
| Id | Operation                 | Name      | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |   250 | 5750 |   161   (3)| 00:00:02 |       |       |
|* 1 | FILTER                   |           |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|           |   250 | 5750 |   161   (3)| 00:00:02 |   KEY |   KEY |
|* 3 |    TABLE ACCESS FULL      | TEST_PART |   250 | 5750 |   161   (3)| 00:00:02 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

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

   1 - filter(TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss')<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))
   3 - filter("DT">=TO_DATE(:NAME1,'yyyy-mm-dd hh24:mi:ss') AND
              "DT"<TO_DATE(:NAME2,'yyyy-mm-dd hh24:mi:ss'))
以上是走全表扫描的执行计划
现在要将该语句全部变为走全表扫描，也就是用全表扫描的执行计划来绑定该语句
SQL> select sql_id from v$sql_plan where plan_hash_value=2229598636;

SQL_ID
-------------
day4zwzuhxjnd
day4zwzuhxjnd
day4zwzuhxjnd
day4zwzuhxjnd
day4zwzuhxjnd

SQL> select sql_id from v$sql_plan where plan_hash_value=1956636844;

SQL_ID
-------------
fzs1wktabj9ny
fzs1wktabj9ny
fzs1wktabj9ny
fzs1wktabj9ny
进行绑定
SQL> declare
2 m_clob clob;
3 begin
4 select sql_fulltext
5 into m_clob
6 from v$sql
7 where sql_id = 'day4zwzuhxjnd'
8 and child_number = 0;
9 dbms_output.put_line(m_clob);
10 dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(
11 sql_id          => 'fzs1wktabj9ny',
12 plan_hash_value => 1956636844,
13 sql_text        => m_clob,
14 fixed           => 'YES',
15 enabled         => 'YES'));
16 end;
17 /
PL/SQL procedure successfully completed.
验证
SQL> select * from TEST_DBA.TEST_PART where dt>=to_date(:name1,'yyyy-mm-dd hh24:mi:ss') and
2 dt<to_date(:name2,'yyyy-mm-dd hh24:mi:ss') ;

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1956636844

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

Note
-----
- SQL plan baseline "SQL_PLAN_c28fvkn3sbtbg70e51298" used for this statement

SQL>

查看生成的sql baseline信息
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurge
2 from dba_sql_plan_baselines where sql_text like '%select * from TEST_DBA.TEST_PART%';

SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT
---------- ------------------------------ ------------------------------ -------------- --- --- --- ---
1.3917E+19 SQL_c121db950785e56f SQL_PLAN_c28fvkn3sbtbg70e51298 MANUAL-LOAD YES YES YES YES

SQL>
如果不想要这个绑定计划了，则可以删除它
declare
l_pls number;
begin
l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c121db950785e56f',
plan_name => 'SQL_PLAN_c28fvkn3sbtbg70e51298'
);
end;

0 0