固定执行计划

最新推荐文章于 2023-08-08 09:42:31 发布

aaaaaaaa2000

最新推荐文章于 2023-08-08 09:42:31 发布

阅读量4.7k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/aaaaaaaa2000/article/details/9086111

版权

oracle 专栏收录该内容

53 篇文章 0 订阅

订阅专栏

在遭遇执行计划不稳定或者执行计划错误的情况下，通过baseline来固定SQL执行计划以确保执行计划稳定性、提高性能。baseline是oracle 11G提供的稳固sql执行计划的功能，是spm功能的一部分。

步骤如下:

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

        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: 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') ;

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

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;

对于dbms_spm.load_plans_from_cursor_cache的说明

既然是from cursor的话那么要保证在执行过程中sql还是在内存里的不能被刷出去

declare
m_clob clob;
begin
select sql_fulltext
into m_clob
from v$sql
where sql_id = 'day4zwzuhxjnd'————–原始sql的 sql_id
and child_number = 0;—————–为了让sql只返回一行，也可以rownum=1代替
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(
sql_id => 'fzs1wktabj9ny',————HINT SQL_ID
plan_hash_value => 1956636844,——————-HINT PLAN_HASH_VALUE
sql_text => m_clob,————————-原始SQL文本
fixed => 'YES', ———————禁止演化baseline
enabled => 'YES'));------生效
end;