oracle sqlprofile 固定执行计划，并迁移执行计划
发表于：2025-01-22 作者：千家信息网编辑
千家信息网最后更新 2025年01月22日，sqlprofile固定执行计划模拟10g 执行计划迁移至11g oracle数据库中，11g库用10g的执行计划，这里是把hint 全盘扫描的执行计划迁移--1.准备阶段sqlplus / as s
千家信息网最后更新 2025年01月22日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安全错误
数据库的锁怎样保障安全
sql数据库医院例题
网络安全防护的著名案例
软件开发项目组 考核
qq解密数据库
网络安全技术与控制专业课程
方舟生存进化还要自己搞服务器吗
网络技术与多媒体通信
mysql数据库查询输出
用友数据库显示可疑
自贡市网络安全工作
方舟在服务器里买镰刀龙多少钱
英雄联盟服务器崩溃修复了吗
数据库主文件名在哪里
域控服务器 管理外网连接
互联网数据库同步训练
汇金软件开发有限公司怎么样
mic并行服务器
军事服务器
中国科学院网络技术中心官网
互联网科技信息
论文摘要软件开发
网络安全审计员岗位职责
数据库中sid指什么
三年级孩子网络安全手抄报
怎样删除数据库表
方舟在服务器里买镰刀龙多少钱
软件开发流程细化分工
北极星网络安全知识
信息网络安全制度试题及答案
网络安全起源是什么
