星期三, 2月 03, 2016

[轉載]Oracle 11g 如何手動Capture SQL至sql baseline

Ref:

https://avdeo.com/tag/load_plan_from_cursor_cache/

 

1.

select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

from dba_sql_plan_baselines

WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

------------------------------ ------------------------------ --- --- ---

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO

 

SQL handle : 語法相同的SQL

Plan_Name : 不同執行計畫路徑就會有不同的plan name

 

2.

select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

SQL handle: SYS_SQL_1447ba3a1d83920f

SQL text: select * from t where col1 = 1

--------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------

Plan name: SYS_SQL_PLAN_1d83920fae82cf72

Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE

--------------------------------------------------------------------------------

 

Plan hash value: 470836197

 

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("COL1"=1)

 

25 rows selected.

 

SQL>

 

3.

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_1447BA3A1D83920F')

--------------------------------------------------------------------------------

 

-------------------------------------------------------------------------------

                        Evolve SQL Plan Baseline Report

-------------------------------------------------------------------------------

 

Inputs:

-------

  SQL_HANDLE = SYS_SQL_1447ba3a1d83920f

  PLAN_NAME  =

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY     = YES

  COMMIT     = YES

 

Plan: SYS_SQL_PLAN_1d83920fae82cf72

-----------------------------------

  Plan was verified: Time used .01 seconds.

  Passed performance criterion: Compound improvement ratio >= 7.33

  Plan was changed to an accepted plan.

 

                      Baseline Plan      Test Plan     Improv. Ratio

                      -------------      ---------     -------------

  Execution Status:        COMPLETE       COMPLETE

  Rows Processed:                 1              1

  Elapsed Time(ms):               0              0

  CPU Time(ms):                   0              0

  Buffer Gets:                   22              3              7.33

  Disk Reads:                     0              0

  Direct Writes:                  0              0

  Fetches:                        0              0

  Executions:                     1              1

 

-------------------------------------------------------------------------------

                                 Report Summary

-------------------------------------------------------------------------------

Number of SQL plan baselines verified: 1.

Number of SQL plan baselines evolved: 1.

 

4.

SET SERVEROUTPUT ON

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',

    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',

    attribute_name  => 'ENABLED',

    attribute_value => 'YES');

 

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

 

Once you evolve the plan, you can see that plan is ACCEPTED now.

 

SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED

  2  from dba_sql_plan_baselines

  3  WHERE sql_text like 'select * from t%';

 

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX

------------------------------ ------------------------------ --- --- ---

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO

SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO

 

 

5.Verify SQL是否已經進入到sql baseline

SQL> explain plan for

  2  select * from t where col1 = 1;

 

Explained.

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 470836197

 

-------------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("COL1"=1)

 

Note

-----

   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

 

18 rows selected.

 

SQL>

沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...