Processing math: 0%

[20151201]备份迁移sql profile.txt

简介: [20151201]备份迁移sql profile.txt --在生产系统使用 sql profile 来稳定计划,需要将这些内容移植到测试环境。自己做一个测试: 1.

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

目录
相关文章
|
3月前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
151 0
|
5月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
556 3
|
5月前
|
SQL 存储 数据库
备份SQL语句
【8月更文挑战第20天】备份SQL语句
50 3
|
5月前
|
SQL 数据库
SQL Server 如何进行备份?
【8月更文挑战第20天】SQL Server 如何进行备份?
128 3
|
5月前
|
SQL 存储 数据库
SQL Server 中的备份类型详解
【8月更文挑战第31天】
132 0
|
5月前
|
数据库 Java 监控
Struts 2 日志管理化身神秘魔法师,洞察应用运行乾坤,演绎奇幻篇章!
【8月更文挑战第31天】在软件开发中,了解应用运行状况至关重要。日志管理作为 Struts 2 应用的关键组件,记录着每个动作和决策,如同监控摄像头,帮助我们迅速定位问题、分析性能和使用情况,为优化提供依据。Struts 2 支持多种日志框架(如 Log4j、Logback),便于配置日志级别、格式和输出位置。通过在 Action 类中添加日志记录,我们能在开发过程中获取详细信息,及时发现并解决问题。合理配置日志不仅有助于调试,还能分析用户行为,提升应用性能和稳定性。
71 0
|
5月前
|
SQL 安全 测试技术
【数据守护者必备】SQL数据备份与恢复策略全解析:从全量到日志备份,手把手教你确保企业信息万无一失的实战技巧!
【8月更文挑战第31天】数据库是企业核心业务数据的基石,为防止硬件故障、软件错误或人为失误导致的数据丢失,制定可靠的备份与恢复策略至关重要。本文通过一个在线购物平台的案例,详细介绍了使用 SQL Server 进行全量备份、差异备份及事务日志备份的方法,并演示了如何利用 SQL Server Agent 实现自动化备份任务。此外,还提供了数据恢复的具体步骤和测试建议,确保数据安全与业务连续性。
272 0
|
6月前
|
SQL 存储 数据库
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
MySQL设计规约问题之性能分析工具如Sql explain、show profile和mysqlsla在数据库性能优化中有什么作用
|
5月前
|
SQL 数据库
SQL Server 还原 备份集中的数据库备份与现有的 'XXX' 数据库不同
SQL Server 还原 备份集中的数据库备份与现有的 'XXX' 数据库不同
396 0
|
7月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。