##執行sql，形成執行計劃

SQL> conn scott/tiger

Connected.

SQL> set autot trace

SQL> set serveroutput on

SQL> select * from t1 where mgr=7698;

40 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3617692013

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40 | 3480 | 4 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T1 | 40 | 3480 | 4 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("MGR"=7698)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

9 consistent gets

0 physical reads

0 redo size

2664 bytes sent via SQL*Net to client

541 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40 rows processed

SQL> create index idx_t1_mgr on t1(mgr);

Index created.

SQL> select /*+ index(t1 idx_t1_mgr) */ * from t1 where mgr=7698;

40 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2410253714

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 40 | 3480 | 10 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 40 | 3480 | 10 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MGR | 40 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("MGR"=7698)

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

11 consistent gets

0 physical reads

0 redo size

3183 bytes sent via SQL*Net to client

541 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40 rows processed

##透過coe_xfr_sql_profile指令碼生成sql指令碼

SQL> @coe_xfr_sql_profile ##生成沒有走索引的sql指令碼

Parameter 1:

SQL_ID (required)

Enter value for 1: 6dcqdf0u77n1q

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

3617692013 .002

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 3617692013

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "6dcqdf0u77n1q"

PLAN_HASH_VALUE: "3617692013"

SQL>BEGIN

2 IF :sql_text IS NULL THEN

3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

4 END IF;

5 END;

6 /

SQL>SET TERM OFF;

SQL>BEGIN

2 IF :other_xml IS NULL THEN

3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

4 END IF;

5 END;

6 /

SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_6dcqdf0u77n1q_3617692013.sql

on TARGET system in order to create a custom SQL Profile

with plan 3617692013 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>@coe_xfr_sql_profile ##生成走索引的sql指令碼

Parameter 1:

SQL_ID (required)

Enter value for 1: 6mbku0c4m6jc5

PLAN_HASH_VALUE AVG_ET_SECS

--------------- -----------

2410253714 .001

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 2410253714

Values passed to coe_xfr_sql_profile:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID : "6mbku0c4m6jc5"

PLAN_HASH_VALUE: "2410253714"

SQL>BEGIN

2 IF :sql_text IS NULL THEN

3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

4 END IF;

5 END;

6 /

SQL>SET TERM OFF;

SQL>BEGIN

2 IF :other_xml IS NULL THEN

3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

4 END IF;

5 END;

6 /

SQL>SET TERM OFF;

Execute coe_xfr_sql_profile_6mbku0c4m6jc5_2410253714.sql

on TARGET system in order to create a custom SQL Profile

with plan 2410253714 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL>exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

##替換內容，把coe_xfr_sql_profile_6mbku0c4m6jc5_2410253714的內容，替換到coe_xfr_sql_profile_6dcqdf0u77n1q_3617692013對應的位置

h := SYS.SQLPROF_ATTR(

q'[BEGIN_OUTLINE_DATA]',

q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

q'[DB_VERSION('11.2.0.4')]',

q'[ALL_ROWS]',

q'[OUTLINE_LEAF(@"SEL$1")]',

q'[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."MGR"))]',

q'[END_OUTLINE_DATA]');

##執行指令碼

SQL> conn / as sysdba

Connected.

SQL> @coe_xfr_sql_profile_6dcqdf0u77n1q_3617692013.sql

SQL> REM

SQL> REM $Header: 215187.1 coe_xfr_sql_profile_6dcqdf0u77n1q_3617692013.sql 11.4.3.5 2017/05/23 carlos.sierra $

SQL> REM

SQL> REM AUTHOR

SQL> REM carlos.sierra@oracle.com

SQL> REM

SQL> REM SCRIPT

SQL> REM coe_xfr_sql_profile_6dcqdf0u77n1q_3617692013.sql

SQL> REM

SQL> REM DESCRIPTION

SQL> REM This script is generated by coe_xfr_sql_profile.sql

SQL> REM It contains the SQL*Plus commands to create a custom

SQL> REM SQL Profile for SQL_ID 6dcqdf0u77n1q based on plan hash

SQL> REM value 3617692013.

SQL> REM The custom SQL Profile to be created by this script

SQL> REM will affect plans for SQL commands with signature

SQL> REM matching the one for SQL Text below.

SQL> REM Review SQL Text and adjust accordingly.

SQL> REM

SQL> REM PARAMETERS

SQL> REM None.

SQL> REM

SQL> REM EXAMPLE

SQL> REM SQL> START coe_xfr_sql_profile_6dcqdf0u77n1q_3617692013.sql;

SQL> REM

SQL> REM NOTES

SQL> REM 1. Should be run as SYSTEM or SYSDBA.

SQL> REM 2. User must have CREATE ANY SQL PROFILE privilege.

SQL> REM 3. SOURCE and TARGET systems can be the same or similar.

SQL> REM 4. To drop this custom SQL Profile after it has been created:

SQL> REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_6dcqdf0u77n1q_3617692013');

SQL> REM 5. Be aware that using DBMS_SQLTUNE requires a license

SQL> REM for the Oracle Tuning Pack.

SQL> REM

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;

SQL> REM

SQL> VAR signature NUMBER;

SQL> REM

SQL> DECLARE

2 sql_txt CLOB;

3 h SYS.SQLPROF_ATTR;

4 BEGIN

5 sql_txt := q'[

6 select * from t1 where mgr=7698

7 ]';

8 h := SYS.SQLPROF_ATTR(

9 q'[BEGIN_OUTLINE_DATA]',

10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',

11 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',

12 q'[DB_VERSION('11.2.0.4')]',

13 q'[ALL_ROWS]',

14 q'[OUTLINE_LEAF(@"SEL$1")]',

15 q'[INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."MGR"))]',

16 q'[END_OUTLINE_DATA]');

17 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

18 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

19 sql_text => sql_txt,

20 profile => h,

21 name => 'coe_6dcqdf0u77n1q_3617692013',

22 description => 'coe 6dcqdf0u77n1q 3617692013 '||:signature||'',

23 category => 'DEFAULT',

24 validate => TRUE,

25 replace => TRUE,

26 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );

27 END;

28 /

PL/SQL procedure successfully completed.

SQL> WHENEVER SQLERROR CONTINUE

SQL> SET ECHO OFF;

SIGNATURE

---------------------

3774831808199813374

... manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_6dcqdf0u77n1q_3617692013 completed

##驗證結果

SQL> select * from t1 where mgr=7698;

40 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2410253714

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 522 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 6 | 522 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_T1_MGR | 2 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("MGR"=7698)

Note

-----

- SQL profile "coe_6dcqdf0u77n1q_3617692013" used for this statement

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

11 consistent gets

0 physical reads

0 redo size

3183 bytes sent via SQL*Net to client

541 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

40 rows processed

##備註，sql語句中有繫結變數，可以使用變數替換或者使用匿名塊

var v number(4); ##varchar(10)

exec :n :=7698;

select * from t1 where mgr=:n;

DECLARE

V_SQL VARCHAR2(300); ##varchar

BEGIN

V_SQL := 'select * from t1 where mgr=:1';

EXECUTE IMMEDIATE V_SQL USING 7698; ##using 後面多個引數逗號分隔

END;

/

使用coe_xfr_sql_profile固定執行計劃

相關文章