oracle sqlprofile 固定执行计划，并迁移执行计划

sqlprofile固定执行计划


模拟10g 执行计划迁移至11g oracle数据库中，11g库用10g的执行计划，这里是把hint 全盘扫描的执行计划迁移




 --1.准备阶段 
 
 sqlplus  / as sysdba


SQL> select * from v$version; 
 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
PL/SQL Release 10.2.0.1.0 - Production 
CORE    10.2.0.1.0      Production 
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 
NLSRTL Version 10.2.0.1.0 - Production 






create user mouse identified by oracle;
grant create session,resource to mouse;
grant dba to mouse;


conn  mouse/oracle


SQL> create table test_raugher as select * from dba_objects; 
 
表已创建。 
 
SQL> create index ind_objectid on test_raugher(object_id); 
 
索引已创建。 
 
SQL> select object_id from test_raugher where rownum<2; 
 
 OBJECT_ID 
---------- 
        20 
 
SQL> exec dbms_stats.gather_table_stats(user,'TEST_RAUGHER',cascade=>true); 
 
PL/SQL 过程已成功完成。 
 
--原sql执行计划 
SQL> set autot trace explain 
SQL> select * from test_raugher where object_id=20; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 800879874 
 
-------------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT            |              |     1 |    95 |     2   (0)| 00:00:01 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_RAUGHER |     1 |    95 |     2   (0)| 00:00:01 | 
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID |     1 |       |     1   (0)| 00:00:01 | 
-------------------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   2 - access("OBJECT_ID"=20) 
 
SQL> 
--新sql执行计划 
SQL> select * from test_raugher where object_id=20; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=20) 
 
--2.获取新sql的sql_id 
SQL> col sql_id for a20 
SQL> col sql_text for a100 
SQL> select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%'; 
 
SQL_ID               SQL_TEXT 
-------------------- ---------------------------------------------------------------------------------------------------- 
5nkhk378705z3        select sql_id,sql_text from v$sql where sql_text like '%full(test_raugher)%' 
g23hbdmcsdahc        select * from test_raugher where object_id=20 
dqp79vx5pmw0k        EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * from test_raug 
                     her where object_id=20 
                      
--3.获取新sql的outline 
SQL> set pagesize 1000 
SQL> select * from table(dbms_xplan.display_cursor('g23hbdmcsdahc',null,'outline')); 
 
PLAN_TABLE_OUTPUT 
----------------------------------------------------------------------------------------------- 
----------------------------------------------------------------------------------------------- 
SQL_ID  g23hbdmcsdahc, child number 0 
------------------------------------- 
select * from test_raugher where object_id=20 
 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |       |       |   166 (100)|          | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Outline Data 
------------- 
 
   
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=20) 
 
 
已选择31行。 
 
--4.创建sql profile(SQLPROFILE_001)                     
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" "TEST_RAUGHER"@"SEL$1")', 
 11     'END_OUTLINE_DATA'); 
 12   dbms_sqltune.import_sql_profile( 
 13     'select * from test_raugher where object_id=20', 
 14     v_hints,'SQLPROFILE_001', 
 15     force_match=>true,replace=>false); 
 16  end; 
 17  / 
 
PL/SQL 过程已成功完成。 
 
--5.查看是否使用sql profile 
SQL> set autot trace explain 
SQL> select * from test_raugher where object_id=20; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=20) 
 
Note 
----- 
   - SQL profile "SQLPROFILE_001" used for this statement 
 
SQL> select * from test_raugher where object_id=200; 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 3725671026 
 
---------------------------------------------------------------------------------- 
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |              |     1 |    95 |   166   (2)| 00:00:02 | 
|*  1 |  TABLE ACCESS FULL| TEST_RAUGHER |     1 |    95 |   166   (2)| 00:00:02 | 
---------------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_ID"=200) 
 
Note 
----- 
   - SQL profile "SQLPROFILE_001" used for this statement 
 
 
将该profile打包，为迁移做准备。 
生成一张TEST_SQLPRO1表
exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'TEST_SQLPRO1',schema_name=>'MOUSE');


SQL> select * from tab;
T1       TABLE
TEST_SQLPRO1 TABLE


将执行计划插入到TEST_SQLPRO1表中
 exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'TEST_SQLPRO1',profile_name=>'SQLPROFILE_001');


导出表与打包的执行计划
导出用户下面所有对象或者只导出放执行计划的表
expdp system/oracle dumpfile=mouse.dmp directory=expdir schemas=mouse
expdp system/oracle dumpfile=mouse.dmp directory=expdir tables=mouse.TEST_SQLPRO1






[oracle@nod ~]$ expdp system/123456 dumpfile=mouse.dmp directory=expdp tables=mouse.TEST_SQLPRO1,mouse.TEST_RAUGHER


Export: Release 10.2.0.1.0 - 64bit Production on Saturday, 17 June, 2017 23:58:38


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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=mouse.dmp directory=expdp tables=mouse.TEST_SQLPRO1,mouse.TEST_RAUGHER 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "MOUSE"."TEST_SQLPRO1"                      8.937 KB       1 rows
  exported        mouse.TEST_RAUGHER                      2.2MB        4000rows 
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/expdp/mouse.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:58:45




 scp mouse.dmp 192.168.0.13:/home/oracle/expdp/


11g 数据库




[oracle@oracle expdp]$ sqlplus -v


SQL*Plus: Release 11.2.0.4.0 Production






sqlplus  / as sysdba




create user mouse identified by oracle;
grant create session,resource to mouse;
grant dba to mouse;


conn mouse/oracle






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


Import: Release 11.2.0.4.0 - Production on Fri May 5 15:38:27 2017


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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=mouse.dmp directory=expdp 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MOUSE"."TEST_SQLPRO1"                      8.937 KB       1 rows
  imported        mouse.TEST_RAUGHER                      2.2MB        4000rows 
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri May 5 15:38:51 2017 elapsed 0 00:00:20










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


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


SQL> conn mouse/oracle
Connected.
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST_SQLPRO1');


PL/SQL procedure successfully completed.


SQL> set autot trace explain 
SQL> set lines 200
SQL> select * from test_raugher where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 3725671026


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


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


   1 - filter("OBJECT_ID"=20)


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




col CREATED for a10
col SQL_TEXT for a30
SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC;




NAME       CREATED  CATEGORY SQL_TEXT
------------------------------ ---------- ------------------------------ ------------------------------
SQLPROFILE_00105-MAY-17     DEFAULT select * from test_raugher where object_id=20
    


这里我们看到这条语句使用了SQLPROFILE_001个sql profile




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






exec  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SQLPROFILE_001');




SQL> set autot trace explain 
SQL> set lines 200
SQL> select * from test_raugher where object_id=20;




Execution Plan
----------------------------------------------------------
Plan hash value: 800879874


--------------------------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |   | 1 |93 | 2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_RAUGHER | 1 |93 | 2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN    | IND_OBJECTID | 1 |   | 1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


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


   2 - access("OBJECT_ID"=20)








SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC; 


no rows selected








如果删除sqlprofile 在想使用sqlprofile ，直接执行EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST_SQLPRO1');