我们在数据迁移的过程中，经常会发现数据库升级或迁移后，执行计划改变的情况，我们可以通过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：这个也是原厂售后面试必考的哦。。哈哈哈。
942
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
