[20151201]備份遷移sql profile.txt

[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;

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 - SEL$1 / TX@SEL$1

Predicate Information (identified by operation id):

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

   1 - filter("ID"=:X)

2.建立sql profile：

SCOTT@book> @ &r/sp1 4rgrzpar16sv2

SCOTT@book> execute dbms_sqltune.accept_sql_profile(task_name => 'tuning 4rgrzpar16sv2', replace => TRUE, name=>'tuning 4rgrzpar16sv2', FORCE_MATCH=>True);

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 |

   2 - SEL$1 / TX@SEL$1

Peeked Binds (identified by position):

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

   1 - (NUMBER): 42

   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

                                                  3 REPLACE              PL/SQL BOOLEAN  IN        PL/SQL BOOLEAN  N

17 rows selected.

SCOTT@book> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(TABLE_NAME=>'t_sql_profile',SCHEMA_NAME=>user);

--這樣建立了t_sql_profile表。

SCOTT@book> select count(*) from t_sql_profile ;

  COUNT(*)

----------

SCOTT@book> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF(STAGING_TABLE_NAME=>'t_sql_profile',STAGING_SCHEMA_OWNER=>user);

         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')

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'

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);

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.sqlobj$data 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);