oracle 基线与优化（二）

最新推荐文章于 2024-02-01 11:25:02 发布

1ihj2015

最新推荐文章于 2024-02-01 11:25:02 发布

阅读量646

点赞数

分类专栏： Study & Testing

本文链接：https://blog.csdn.net/itdba/article/details/42146221

版权

Study & Testing 专栏收录该内容

66 篇文章 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> 
<span style="color:#ff0000;">此表由索引，可以测试走做引以及走全表扫描</span>
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: <span style="color:#ff0000;">2229598636</span>

-------------------------------------------------------------------------------------------------------------------
| 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'))
<span style="color:#ff6666;">以上生成了索引的执行sql</span>

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: <span style="color:#ff0000;">1956636844</span>

-------------------------------------------------------------------------------------------------------
| 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=<span style="color:#ff6666;">2229598636</span>; 

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

SQL> select sql_id from v$sql_plan where plan_hash_value=<span style="color:#ff6666;">1956636844</span>; 

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 = '<span style="background-color: rgb(255, 102, 102);">day4zwzuhxjnd</span>'
  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 =><span style="color:#3366ff;"> <span style="background-color: rgb(255, 102, 102);">195663684</span></span><span style="color:#ff0000;background-color: rgb(255, 102, 102);">4</span>,
 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
-----
  <span style="color:#ff6666;"> - SQL plan baseline "SQL_PLAN_c28fvkn3sbtbg70e51298" used for this statement</span>

SQL> 

查看生成的sql baseline信息
SQL><span style="color:#ff0000;"> 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%</span>';

 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> 
<strong><span style="color:#ff6666;">如果不想要这个绑定计划了，则可以删除它</span></strong>
declare
l_pls number;
begin
l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c121db950785e56f',
plan_name  => 'SQL_PLAN_c28fvkn3sbtbg70e51298'
);
end;

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;