oracle sqlprofile 固定执行计划，并迁移执行计划
发表于：2026-11-02 作者：千家信息网编辑
千家信息网最后更新 2026年11月02日，sqlprofile固定执行计划模拟10g 执行计划迁移至11g oracle数据库中，11g库用10g的执行计划，这里是把hint 全盘扫描的执行计划迁移--1.准备阶段sqlplus / as s
千家信息网最后更新 2026年11月02日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"=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 /*+ full(test_raugher) */ * from test_raugher where object_id=20
dqp79vx5pmw0k EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(test_raugher) */ * 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 /*+ 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 | | | | 166 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
----------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")
END_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');
模拟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"=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 /*+ full(test_raugher) */ * from test_raugher where object_id=20
dqp79vx5pmw0k EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select /*+ full(test_raugher) */ * 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 /*+ 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 | | | | 166 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_RAUGHER | 1 | 95 | 166 (2)| 00:00:02 |
----------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_RAUGHER"@"SEL$1")
END_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');
成功
数据
数据库
时候
过程
准备
信息
对象
方案
环境
用户
目的
索引
语句
阶段
中导
生成
统计
选择
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
科技互联网
金融科技还是互联网
互联网络科技有限公司
易互联网科技有限公司
碳银互联网科技
南京欣网互联网科技
互联网科技美容
互联网金融和金融科技
互联网科技实习生岗位职责
华夏互联网科技业
晓花互联网科技有限公司网址
苏州铭阳互联网科技有限公司
东莞新华互联网科技学校
互联网第三次科技革命特征
济南小医互联网科技有限公司招聘
互联网硬科技公司
科技互联网行业投资研究
陕西九四连强互联网科技有限公司
上海旺乐尼互联网科技有限公司
中国主要互联网科技公司
瑰夏咖啡
中关村互联网 科技集团
科技企业和互联网企业
网络科技有限互联网
礼当家互联网科技
滨湖区互联网智能科技产品供应
恒生科技互联网指数分析
烟台灵鸽互联网科技有限公司
亚光科技低轨卫星互联网
武汉亿视互联网科技有限公司
