sql plan baseline（二）

基線的匯出匯入

(1) 建立表用於儲存基線

Begin

Dbms_spm.create_stgtab_baseline（table_name=>’stage’）;

End;

/

SQL> desc stage;

Name Null? Type

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

VERSION NUMBER

SIGNATURE NUMBER

SQL_HANDLE VARCHAR2(30)

OBJ_NAME VARCHAR2(30)

OBJ_TYPE VARCHAR2(30)

PLAN_ID NUMBER

SQL_TEXT CLOB

CREATOR VARCHAR2(30)

ORIGIN VARCHAR2(30)

DESCRIPTION VARCHAR2(500)

DB_VERSION VARCHAR2(64)

CREATED TIMESTAMP(6)

LAST_MODIFIED TIMESTAMP(6)

LAST_EXECUTED TIMESTAMP(6)

LAST_VERIFIED TIMESTAMP(6)

STATUS NUMBER

OPTIMIZER_COST NUMBER

MODULE VARCHAR2(48)

ACTION VARCHAR2(32)

EXECUTIONS NUMBER

ELAPSED_TIME NUMBER

CPU_TIME NUMBER

BUFFER_GETS NUMBER

DISK_READS NUMBER

DIRECT_WRITES NUMBER

ROWS_PROCESSED NUMBER

FETCHES NUMBER

END_OF_FETCH_COUNT NUMBER

CATEGORY VARCHAR2(30)

SQLFLAGS NUMBER

TASK_ID NUMBER

TASK_EXEC_NAME VARCHAR2(30)

TASK_OBJ_ID NUMBER

TASK_FND_ID NUMBER

TASK_REC_ID NUMBER

INUSE_FEATURES NUMBER

PARSE_CPU_TIME NUMBER

PRIORITY NUMBER

OPTIMIZER_ENV RAW(2000)

BIND_DATA RAW(2000)

PARSING_SCHEMA_NAME VARCHAR2(30)

COMP_DATA CLOB

(2) 使用pack_stgtab_baseline函式將sql management base中的內容匯入stage表

SQL> DECLARE

my_plans number;

BEGIN

my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(

table_name => 'stage', --將所有的基線都匯出了，也可以通過sql_handle等條件將特定內容匯出

enabled => 'yes',

creator => 'SCOTT');

END;

/

2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

(3) 使用exp命令將stage表匯出

[oracle@localhost ~]$ expdp scott/scott directory='AWRRPT_DIR' dumpfile=stage.dmp

tables=stage

Export: Release 11.2.0.1.0 - Production on Sun Dec 23 08:39:18 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=AWRRPT_DIR dumpfile=stage.dmp tables=stage

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."STAGE" 25.64 KB 5 rows

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/u01/stage.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 08:40:07

(4) 將匯出檔案放到目標系統中，（可以通過ftp等方式）

(5) 將stage表匯入目標系統中

在本機演示，是將基線和stage表刪除後再進行stage表的匯入

SQL> !impdp scott/scott directory=awrrpt_dir dumpfile=stage.dmp tables=scott.stage remap_schema=scott:scott

Import: Release 11.2.0.1.0 - Production on Sun Dec 23 08:57:00 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=awrrpt_dir dumpfile=stage.dmp tables=scott.stage remap_schema=scott:scott

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."STAGE" 25.64 KB 5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 08:57:08

(6) 匯出基線

SQL> DECLARE

my_plans number;

BEGIN

my_plans := DBMS_SPM.unpack_stgtab_baseline(table_name => 'stage',sql_handle => 'SYS_SQL_88feb7b8a8d72e29');

END;

/ 2 3 4 5 6

PL/SQL procedure successfully completed.

基線的evolve

如果表結構修改等原因使執行同一個語句產生了比當前baseline更好的執行計劃，可以將更好的計劃加入到baseline：

如：建立了以下基線

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(

plan_name=>'SQL_PLAN_8jzprr2ndfbj994ecae5c',format=>'basic')); 2

PLAN_TABLE_OUTPUT

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

SQL handle: SYS_SQL_88feb7b8a8d72e29

SQL text: select * from t where mgr=7788

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

Plan name: SQL_PLAN_8jzprr2ndfbj994ecae5c Plan id: 2498539100

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| T |

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

19 rows selected.

看一下當前有無效能更好的執行計劃：

SQL> SET SERVEROUTPUT ON

SET LONG 10000

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => 'SYS_SQL_88feb7b8a8d72e29');

DBMS_OUTPUT.PUT_LINE(report);

END;

/SQL> SQL> 2 3 4 5 6 7 8

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

Evolve SQL Plan Baseline

Report

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

------

Inputs:

-------

SQL_HANDLE = SYS_SQL_88feb7b8a8d72e29

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT =

YES

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

Report

Summary

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

There were no SQL plan baselines that required processing.

PL/SQL procedure successfully completed.

我們知道，全表掃描不是最好的路徑，因此mgr列上建立了索引t_idx，然後進行如下操作。

SQL> select * from t where mgr=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7788 02-APR-81 2975

20

7698 BLAKE MANAGER 7788 01-MAY-81 2850

20

7782 CLARK MANAGER 7788 09-JUN-81 2450

10

7876 ADAMS CLERK 7788 23-MAY-87 1100

20

Execution Plan

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T | 1 | 38 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("MGR"=7788)

Note

-----

- SQL plan baseline "SQL_PLAN_8jzprr2ndfbj994ecae5c" used for this statement

SQL> select /*+index(t t_idx)*/* from t where mgr=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7788 02-APR-81 2975

20

7698 BLAKE MANAGER 7788 01-MAY-81 2850

20

7782 CLARK MANAGER 7788 09-JUN-81 2450

10

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7876 ADAMS CLERK 7788 23-MAY-87 1100

20

Execution Plan

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

Plan hash value: 470836197

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

| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 38 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("MGR"=7788)

SQL> SET SERVEROUTPUT ON

SET LONG 10000

DECLARE

report clob;

BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

sql_handle => 'SYS_SQL_88feb7b8a8d72e29');

DBMS_OUTPUT.PUT_LINE(report);

END;

/SQL> SQL> 2 3 4 5 6 7 8

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

Evolve SQL Plan Baseline Report

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

Inputs:

-------

SQL_HANDLE = SYS_SQL_88feb7b8a8d72e29

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT = YES

Plan: SQL_PLAN_8jzprr2ndfbj9ae82cf72

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

Plan was verified: Time used .04 seconds.

Plan passed performance criterion: 1.51 times better than baseline plan.

Plan was changed to an accepted plan.

Baseline Plan Test Plan Stats Ratio

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

Execution Status: COMPLETE COMPLETE

Rows Processed: 4 4

Elapsed Time(ms): .086 .06 1.43

CPU Time(ms): .111 0

Buffer Gets: 3 2 1.5

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: 1

上面的對比資訊顯示出待測試的計劃比基線中的計劃執行效率高。執行了evolve操作後，索引的執行計劃被放入基線中。再執行該語句時使用的是索引。

SQL> select * from t where mgr=7788;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

……（內容略）

……

Execution Plan

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

Plan hash value: 470836197

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

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

| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 38 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("MGR"=7788)

Note

-----

SQL plan baseline "SQL_PLAN_8jzprr2ndfbj9ae82cf72" used for this statement（走索引的基線名）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-752989/，如需轉載，請註明出處，否則將追究法律責任。

sql plan baseline（二）

相關文章