[20151201]备份迁移sql profile.txt
--在生产系统使用 sql profile 来稳定计划,需要将这些内容移植到测试环境。自己做一个测试:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table tx as select rownum id ,'test' name from dual connect by level<=1e5;
Table created.
SCOTT@book> create unique index pk_tx on tx(id);
Index created.
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'tx',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.
SCOTT@book> variable x number;
SCOTT@book> exec :x := 42;
PL/SQL procedure successfully completed.
SCOTT@book> select /*+ full(tx) */ * from tx where id = :x ;
ID NAME
---------- --------------------
42 test
SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4rgrzpar16sv2, child number 0
-------------------------------------
select /*+ full(tx) */ * from tx where id = :x
Plan hash value: 40191160
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 61 (100)| |
|* 1 | TABLE ACCESS FULL| TX | 179 | 3401 | 61 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL1 / TX@SEL1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
2.建立sql profile:
--参考链接: [20141119]使用脚本完成sql_profile工作 http://blog.itpub.net/267265/viewspace-1340660/
SCOTT@book> @ &r/sp1 4rgrzpar16sv2
PL/SQL procedure successfully completed.
SCOTT@book> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 4rgrzpar16sv2', replace => TRUE, name=>'tuning 4rgrzpar16sv2', FORCE_MATCH=>True);
PL/SQL procedure successfully completed.
SCOTT@book> select /*+ full(tx) */ * from tx where id = :x ;
ID NAME
---------- --------------------
42 test
SCOTT@book> @ &r/dpcz ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4rgrzpar16sv2, child number 0
-------------------------------------
select /*+ full(tx) */ * from tx where id = :x
Plan hash value: 1336555843
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL1 / TX@SEL1
2 - SEL1 / TX@SEL1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL profile tuning 4rgrzpar16sv2 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
37 rows selected.
--可以发现现在执行计划已经发生了改变,使用索引。
3.如何保存:
--检查发现可以使用包dbms_sqltune完成。
SCOTT@book> @&r/desc_proc sys dbms_sqltune %_stgtab_sqlprof%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE D
----- ------------- ---------------------- -------- -------------------- --------------- --------- --------------- -
SYS DBMS_SQLTUNE CREATE_STGTAB_SQLPROF 1 TABLE_NAME VARCHAR2 IN VARCHAR2 N
2 SCHEMA_NAME VARCHAR2 IN VARCHAR2 Y
3 TABLESPACE_NAME VARCHAR2 IN VARCHAR2 Y
PACK_STGTAB_SQLPROF 1 PROFILE_NAME VARCHAR2 IN VARCHAR2 Y
2 PROFILE_CATEGORY VARCHAR2 IN VARCHAR2 Y
3 STAGING_TABLE_NAME VARCHAR2 IN VARCHAR2 N
4 STAGING_SCHEMA_OWNER VARCHAR2 IN VARCHAR2 Y
REMAP_STGTAB_SQLPROF 1 OLD_PROFILE_NAME VARCHAR2 IN VARCHAR2 N
2 NEW_PROFILE_NAME VARCHAR2 IN VARCHAR2 Y
3 NEW_PROFILE_CATEGORY VARCHAR2 IN VARCHAR2 Y
4 STAGING_TABLE_NAME VARCHAR2 IN VARCHAR2 N
5 STAGING_SCHEMA_OWNER VARCHAR2 IN VARCHAR2 Y
UNPACK_STGTAB_SQLPROF 1 PROFILE_NAME VARCHAR2 IN VARCHAR2 Y
2 PROFILE_CATEGORY VARCHAR2 IN VARCHAR2 Y
3 REPLACE PL/SQL BOOLEAN IN PL/SQL BOOLEAN N
4 STAGING_TABLE_NAME VARCHAR2 IN VARCHAR2 N
5 STAGING_SCHEMA_OWNER VARCHAR2 IN VARCHAR2 Y
17 rows selected.
SCOTT@book> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(TABLE_NAME=>'t_sql_profile',SCHEMA_NAME=>user);
PL/SQL procedure successfully completed.
--这样建立了t_sql_profile表。
SCOTT@book> select count(*) from t_sql_profile ;
COUNT(*)
----------
0
SCOTT@book> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from t_sql_profile ;
COUNT(*)
----------
1
SCOTT@book> @ &r/pt2 'select * from t_sql_profile'
ROW_NUM COL_NUM COL_NAME COL_VALUE
------- ---------- --------------- ----------------------------------------------------------------------------------------------------
1 1 VERSION 3
2 SIGNATURE 12082737585315772852
3 SQL_HANDLE SQL_a7ae81d11701f9b4
4 OBJ_NAME tuning 4rgrzpar16sv2
5 OBJ_TYPE SQL_PROFILE
6 PLAN_ID 0
7 SQL_TEXT select /*+ full(tx) */ * from tx where id = :x
8 CREATOR SCOTT
9 ORIGIN MANUAL
10 DB_VERSION 11.2.0.4.0
11 CREATED 01-DEC-15 08.12.52.000000 AM
12 LAST_MODIFIED 01-DEC-15 08.12.52.000000 AM
13 STATUS 1
14 CATEGORY DEFAULT
15 SQLFLAGS 1
16 TASK_ID 360
17 TASK_EXEC_NAME EXEC_363
18 TASK_OBJ_ID 1
19 TASK_FND_ID 1
20 TASK_REC_ID 1
21 INUSE_FEATURES 1
22 COMP_DATA <outline_data><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint><hint><![CDATA[IGNORE_OPTIM
22 rows selected.
4.测试导入:
--先删除sql profile,单机环境先删除在导入。
SCOTT@book> execute dbms_sqltune.drop_sql_profile(name => 'tuning 4rgrzpar16sv2')
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/spext 4rgrzpar16sv2
no rows selected
--导入:
SCOTT@book> exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UNPACK_STGTAB_SQLPROF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
--参数REPLACE必须赋值。
SCOTT@book> exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE=>false,STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/spext 4rgrzpar16sv2
HINT NAME
----------------------------------------- ------------------------------
OPTIMIZER_FEATURES_ENABLE(default) tuning 4rgrzpar16sv2
IGNORE_OPTIM_EMBEDDED_HINTS tuning 4rgrzpar16sv2
--附上抽取hint脚本:
cat spext.sql /* Formatted on 2015/4/10 17:03:49 (QP5 v5.252.13127.32867) */ column hint format a200 column name format a30 SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name FROM SYS.sqlobjdata od
,SYS.sqlobj$ so
,TABLE
(
XMLSEQUENCE
(
EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')
)
) h
WHERE so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1')
AND so.signature = od.signature
AND so.CATEGORY = od.CATEGORY
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
总结:
1.建立表exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(TABLE_NAME=>'t_sql_profile',SCHEMA_NAME=>user);
2.导入表 exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);
2a.使用exp/expdp导出表。
3.导出exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE=>false,STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);