基线的导出导入
(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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------
| 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
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-752989/
137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
