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 /*+ full(test_raugher) */ * 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"