11G执行计划管理SPM(SQL PLAN MANAGEMENT)的研究(四)

这一篇介绍的是执行计划的进化(evolution),所谓(evolution)就是将那些not accepted的计划改变为eccepted的计划。这样在下一次SQL语句运行的时候,这个被accecpted的计划就能有可能被选择。我们一般使用dbms_spm.evolve_sql_plan_baseline来进行基线进化的操作。我们可以有三种选择。
Options:

  • 运行evolve,做性能测试,如果性能更好,则accept这个计划。
  • 运行evolve,做性能测试,仅仅输出报告,不accept这个计划。
  • 运行evolve,不做性能测试,accept这个计划。

那么接下来我们来做个演示:

我创建了2个表,然后收集了它们的统计信息,在ALL_ROWS优化器模式下面,我执行了一条SQL,并将这条SQL的执行计划从cursor中加载到了基线,成为Accecpt的计划.那么在下一次我将优化器模式改成first_rows之后,first_rows模式下会产生一个新的计划。因为有执行计划基线,所以我们会去使用基线中的计划,而新产生的计划也会被列入到Baseline中来,成为一个NO Accecpted的计划

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> create index t2_idx on t2(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'T1',TABNAME=>'T1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'T1',TABNAME=>'T2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace traceonly
SQL> select t1.object_id,t2.object_id from t1,t2 where t1.object_id=t2.object_id;

74569 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2302613501

----------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        | 74569 |   728K|       |   198   (2)| 00:00:03 |
|*  1 |  HASH JOIN            |        | 74569 |   728K|  1240K|   198   (2)| 00:00:03 |
|   2 |   INDEX FAST FULL SCAN| T2_IDX | 74570 |   364K|       |    38   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| T1_IDX | 74569 |   364K|       |    38   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Statistics
----------------------------------------------------------
         63  recursive calls
          0  db block gets
       5394  consistent gets
          0  physical reads
          0  redo size
    1724209  bytes sent via SQL*Net to client
      55204  bytes received via SQL*Net from client
       4973  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
      74569  rows processed

SQL> set autotrace off
SQL> select sql_id,PLAN_HASH_VALUE,id,OPERATION from v$sql_plan where PLAN_HASH_VALUE='2302613501'; 

SQL_ID        PLAN_HASH_VALUE         ID OPERATION
------------- --------------- ---------- ------------------------------------------------------------
9x7un7kvgxvbd      2302613501          0 SELECT STATEMENT
9x7un7kvgxvbd      2302613501          1 HASH JOIN
9x7un7kvgxvbd      2302613501          2 INDEX
9x7un7kvgxvbd      2302613501          3 INDEX

SQL> declare
  2  y int;
  3  begin
  4  y:=dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'9x7un7kvgxvbd',PLAN_HASH_VALUE=>'2302613501');
  5  end;
  6  / 

PL/SQL procedure successfully completed.

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wd24de464 YES YES NO             198

SQL> set autotrace traceonly
SQL> alter session set optimizer_mode =first_rows;

Session altered.

SQL> select t1.object_id,t2.object_id from t1,t2 where t1.object_id=t2.object_id;

74569 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2302613501

----------------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        | 74569 |   728K|       |   198   (2)| 00:00:03 |
|*  1 |  HASH JOIN            |        | 74569 |   728K|  1240K|   198   (2)| 00:00:03 |
|   2 |   INDEX FAST FULL SCAN| T2_IDX | 74570 |   364K|       |    38   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| T1_IDX | 74569 |   364K|       |    38   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

Statistics
----------------------------------------------------------
         91  recursive calls
         53  db block gets
       5349  consistent gets
          2  physical reads
      15240  redo size
    1724209  bytes sent via SQL*Net to client
      55204  bytes received via SQL*Net from client
       4973  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      74569  rows processed

SQL> set autotrace off
SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wd24de464 YES YES NO             198
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wfd958112 YES NO  NO           74753

此时我们就需要对这个计划做进化,对其进行验证,使其成为Accecpted的计划,这样下一次在使用first rows参数的时候,我们就可以采用这个计划了。这里要讲一下,因为使用first rows的时候,我们的cost值比all rows的时候大很多,所以如果verify的话,是不会让你Accecpted成功的。我们可以看一下对比输出的性能测试报告。此时,我们为了方便演示,第二次我就不做性能测试,直接accept了这个计划。

SQL> set long 1000
SQL> set long 10000
SQL> variable res1 clob;
SQL> begin
  2  :res1:=DBMS_SPM.evolve_sql_plan_baseline(sql_handle=>'SQL_b42ead9cd786a8dc',verify=>'YES',commit=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print res1

RES1
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_b42ead9cd786a8dc
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_b8bpdmmbsda6wfd958112
------------------------------------
  Plan was verified: Time used .74 seconds.
  Plan failed performance criterion: 1.53 times worse than baseline plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                   74569          74569
  Elapsed Time(ms):                27.049         41.795               .65
  CPU Time(ms):                     26.44         40.327               .66
  Buffer Gets:                        344           1328               .26
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 0

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wd24de464 YES YES NO             198
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wfd958112 YES NO  NO           74753

SQL> begin
  2  :res1:=DBMS_SPM.evolve_sql_plan_baseline(sql_handle=>'SQL_b42ead9cd786a8dc',PLAN_NAME=>'SQL_PLAN_b8bpdmmbsda6wfd958112',verify=>'NO',commit=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,OPTIMIZER_COST from dba_sql_plan_baselines; 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --- --------------
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wd24de464 YES YES NO             198
SQL_b42ead9cd786a8dc           SQL_PLAN_b8bpdmmbsda6wfd958112 YES YES NO           74753

SQL> print res1

RES1
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_b42ead9cd786a8dc
  PLAN_NAME  = SQL_PLAN_b8bpdmmbsda6wfd958112
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = NO
  COMMIT     = YES

Plan: SQL_PLAN_b8bpdmmbsda6wfd958112
------------------------------------
  Plan was changed to an accepted plan.

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 1

尝试再一次使用first_rows模式运行该SQL,则直接选择了我们刚刚Accecpted的计划。

SQL> set autotrace traceonly
SQL> select t1.object_id,t2.object_id from t1,t2 where t1.object_id=t2.object_id;

74569 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2428627570

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 74569 |   728K| 74753   (1)| 00:14:58 |
|   1 |  NESTED LOOPS     |        | 74569 |   728K| 74753   (1)| 00:14:58 |
|   2 |   INDEX FULL SCAN | T1_IDX | 74569 |   364K|   166   (0)| 00:00:02 |
|*  3 |   INDEX RANGE SCAN| T2_IDX |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

Statistics
----------------------------------------------------------
         13  recursive calls
         14  db block gets
      16381  consistent gets
          0  physical reads
       3032  redo size
    1724209  bytes sent via SQL*Net to client
      55204  bytes received via SQL*Net from client
       4973  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      74569  rows processed

其实大家可以试试数据量发生变化之后导致执行计划改变的例子。再下一篇会介绍SPM执行计划的选择。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *