使用sqlprofile固定与迁移执行计划

我们在数据迁移的过程中,经常会发现数据库升级或迁移后,执行计划改变的情况,我们可以通过profile的方法固定执行计划。
以下为一个10G-11G的模拟实验:

1.创建实验环境

[oracle@new10g ~]$ sqlplus mouse/mouse

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 25 14:57:40 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t1 on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats('MOUSE','T1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> explain plan for select object_name from t1 where object_id=2;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 50753647

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    30 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    30 |     2   (0)| 00:0
0:01 |

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)

14 rows selected.

2.在此我们可以看见默认是走索引的,先使用HINT强制走全表扫描,并得出sqlid

SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where object_id=2;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED    LAST_DDL_T TIMESTAMP           STATUS  T G S
---------- ---------- ------------------- ------- - - -
SYS
C_OBJ#
                                        2              2 CLUSTER
20-4? -10  20-4? -10  2010-04-20:08:24:28 VALID   N N N


SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%from t1 where object_id=2%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------

0unwd7079k9sr
select /*+ no_index(t1 idx_t1) */ * from t1 where object_id=2

3.通过sqlid得出outline,这个是profile的基础。

SQL> set linesize 132
SQL> select * from table(dbms_xplan.display_cursor('0unwd7079k9sr',null,'outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0unwd7079k9sr, child number 0
-------------------------------------
select /*+ no_index(t1 idx_t1) */ * from t1 where object_id=2

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   159 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   159   (2)| 00:00:02 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=2)


31 rows selected.

3.生成sqlprofile

SQL> declare
  2   v_hints sys.sqlprof_attr;
  3   begin
  4   v_hints:=sys.sqlprof_attr(
  5        'BEGIN_OUTLINE_DATA',
  6        'IGNORE_OPTIM_EMBEDDED_HINTS',
  7        'OPTIMIZER_FEATURES_ENABLE(''10.2.0.1'')',
  8        'ALL_ROWS',
  9        'OUTLINE_LEAF(@"SEL$1")',
 10        'FULL(@"SEL$1" "T1"@"SEL$1")',
 11        'END_OUTLINE_DATA');
 12  dbms_sqltune.import_sql_profile(
 13  'select * from t1 where object_id=2',
 14  v_hints,'SQLPROFILE_T1',               
 15  force_match=>true,replace=>true);
 16  end;
 17  /

PL/SQL procedure successfully completed.

4.现在sqlprofile起作用了,默认走全表。
SQL> explain plan for select * from t1 where object_id=2;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   159   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   159   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - SQL profile "SQLPROFILE_T1" used for this statement

17 rows selected.

5.将该profile打包,为迁移做准备。
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'TEST_SQLPRO1',schema_name=>'MOUSE');

PL/SQL procedure successfully completed.

 

SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'TEST_SQLPRO1',profile_name=>'SQLPROFILE_T1');

PL/SQL procedure successfully completed.


6.导出表与打包的执行计划
[oracle@new10g ~]$ expdp system/oracle dumpfile=mouse.dmp directory=expdir schemas=mouse

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 25 December, 2014 15:42:20

Copyright (c) 2003, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=mouse.dmp directory=expdir schemas=mouse
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6.312 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MOUSE"."T1"                                4.670 MB   50062 rows
. . exported "MOUSE"."TEST_SQLPRO1"                      8.937 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /oracle/backup/mouse.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:42:45

7. 在11G环境中导入表与打包的执行计划
oracle@11g backup]$ impdp system/oracle dumpfile=mouse.dmp directory=mouse schemas=mouse

Import: Release 11.2.0.3.0 - Production on Thu Dec 25 15:52:14 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** dumpfile=mouse.dmp directory=mouse schemas=mouse
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MOUSE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MOUSE"."T1"                                4.670 MB   50062 rows
. . imported "MOUSE"."TEST_SQLPRO1"                      8.937 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 15:53:07

8.默认在新库中,是走索引的。
SQL> conn mouse/mouse
Connected.
SQL> explain plan for select * from t1 where object_id=2
  2  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 50753647

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:0
0:01 |

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=2)

14 rows selected.

9.解包sqlprofile,执行计划变更为与10G库一样的执行计划。

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST_SQLPRO1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t1 where object_id=2
  2  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   195   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   195   (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=2)

Note
-----
   - SQL profile "SQLPROFILE_T1" used for this statement

17 rows selected.

 

在很多时候,在我们在新库收集统计信息或使用HINT无法满足我们的目的的时候,sqlprofile可以作为首选方案。

PS:这个也是原厂售后面试必考的哦。。哈哈哈。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值