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_001 05-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_001 05-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');
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30345407/viewspace-2143301/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle固定執行計劃--sqlprofileOracleSQL
- oracle 固定執行計劃Oracle
- 【SPM】Oracle如何固定執行計劃Oracle
- Oracle手動固定SQL執行計劃OracleSQL
- Oracle緊急固定執行計劃之手段Oracle
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- sqlprofile繫結執行計劃實驗測試SQL
- ORACLE執行計劃Oracle
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- oracle使用outline固定執行計劃事例Oracle
- 執行計劃-1：獲取執行計劃
- baseline固定SQL執行計劃SQL
- 用sql profile來固定執行計劃SQL
- 使用OUTLINE固定執行計劃
- Oracle sql執行計劃OracleSQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 使用SPM和STA進行固定執行計劃
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 執行計劃
- SQL BASELINE修改固定執行計劃SQL
- 用outline修改固定執行計劃
- Oracle執行計劃詳解Oracle
- Oracle 索引和執行計劃Oracle索引
- Oracle閱讀執行計劃Oracle
- oracle執行計劃相關Oracle
- oracle 執行計劃變更Oracle
- 【優化】Oracle 執行計劃優化Oracle
- oracle 執行計劃設定Oracle
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【sql調優之執行計劃】獲取執行計劃SQL
- SYBASE執行計劃
- MySQL 執行計劃MySql
- MySQL執行計劃MySql
- sql 執行計劃SQL
- 編輯計劃任務並執行
- 使用coe_xfr_sql_profile固定執行計劃SQL