Shulou(Shulou.com)06/01 Report--

Purpose: Manual type stable execution plan, which is more stable than AUTOMATIC type

Keywords: dbms_stats.gather_table_stats, DBMS_SQLTUNE.DROP_SQL_PROFILE, coe_xfr_sql_profile.sql

First, create tables and collect table statistics

SQL > create table T1 (n number)

The table has been created.

SQL > declare

2 begin

3 for i in 1.. 10000

4 loop

5 insert into T1 values (I)

6 commit

7 end loop

8 end

9 /

The PL/SQL process completed successfully.

SQL > select count (*) from T1

COUNT (*)

-

10000

SQL > create index idx_t1 on T1 (n)

The index has been created.

SQL > exec dbms_stats.gather_table_stats (ownname = > 'TEST', tabname = >' T1', method_opt = > 'for all columns size 1', CASCADE = > TRUE)

The PL/SQL process completed successfully.

Second, find the SQL_PROFILE and delete the corresponding SQL_PROFILE

SQL > SET LONG 9000

SQL > SET LONGCHUNKSIZE 1000

SQL > SET LINESIZE 2000

SQL > SELECT NAME, SQL_TEXT, TYPE,STATUS,FORCE_MATCHING FROM DBA_SQL_PROFILES WHERE SQL_TEXT LIKE 'SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE%'

NAME SQL_TEXT

TYPE STATUS FORCE_

- - - - - - - - - --

SYS_SQLPROF_0162663bdb700000 SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1

MANUAL ENABLED NO

SYS_SQLPROF_01626643a6130001 SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1

MANUAL ENABLED YES

SQL > EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_01626643a6130001')

The PL/SQL process completed successfully.

SQL > EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_0162663bdb700000')

The PL/SQL process completed successfully.

The third is to call coe_xfr_sql_profile.sql to generate a SQL PROFILE script of type Manual

SQL > SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1

N

-

one

SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID gn8zuq00kd86g, child number 0

-

SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1

Plan hash value: 3617692013

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 7 (100) | | |

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

PLAN_TABLE_OUTPUT

Query Block Name / Object Alias (identified by operation id):

1-SEL$1 / T1@SEL$1

Outline Data

-

/ * +

PLAN_TABLE_OUTPUT

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

DB_VERSION ('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

FULL (@ "SEL$1"T1" @ "SEL$1")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1-filter ("N" = 1)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

Selected 42 rows.

SQL > SELECT / * + INDEX (T1 IDX_T1) * / * FROM T1 WHERE Numb3

Nse

-

three

SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID 866w0nx37z5kg, child number 0

-

SELECT / * + INDEX (T1 IDX_T1) * / * FROM T1 WHERE Niss3

Plan hash value: 1369807930

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 1 (100) | | |

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 4 | 1 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

Query Block Name / Object Alias (identified by operation id):

1-SEL$1 / T1@SEL$1

Outline Data

-

/ * +

PLAN_TABLE_OUTPUT

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

DB_VERSION ('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX (@ "SEL$1"T1" @ "SEL$1" ("T1". "N"))

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1-access ("N" = 3)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

Selected 42 rows.

SQL > SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE'% Niss3%'

SQL_TEXT

SQL_ID VERSION_COUNT

SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE'% Niss3%'

9bvng6dz8ct9z 1

SELECT / * + INDEX (T1 IDX_T1) * / * FROM T1 WHERE Niss3

866w0nx37z5kg 1

SQL > SELECT PLAN_HASH_VALUE FROM V$SQL WHERE SQL_ID='866w0nx37z5kg'

PLAN_HASH_VALUE

-

1369807930

SQL > @ F:\ oracle\ script\ coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

Enter a value of 1: 866w0nx37z5kg-SQL_ID with the condition Niss3

PLAN_HASH_VALUE AVG_ET_SECS

--

1369807930. 001

Parameter 2:

PLAN_HASH_VALUE (required)

Enter a value of 2: 1369807930

Values passed to coe_xfr_sql_profile:

~ ~

SQL_ID: "866w0nx37z5kg"

PLAN_HASH_VALUE: "1369807930"

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_866w0nx37z5kg_1369807930.sql

On TARGET system in order to create a custom SQL Profile

With plan 1369807930 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL > @ F:\ oracle\ script\ coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

Enter a value of 1: gn8zuq00kd86g-SQL_ID with the condition Numb1

PLAN_HASH_VALUE AVG_ET_SECS

--

3617692013. 002

Parameter 2:

PLAN_HASH_VALUE (required)

Enter a value of 2: 3617692013

Values passed to coe_xfr_sql_profile:

~ ~

SQL_ID: "gn8zuq00kd86g"

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_gn8zuq00kd86g_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.

4. Combine HINT in coe_xfr_sql_profile_866w0nx37z5kg_1369807930.sql

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 (@ "SEL$1"T1" @ "SEL$1" ("T1". "N"))]'

Q'[END_OUTLINE_DATA]')

Replace coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql

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'[FULL (@ "SEL$1"T1" @ "SEL$1")]'

Q'[END_OUTLINE_DATA]')

: signature: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt)

And replace the value of parameter FORCE_MATCH in coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql with FALSE instead of TRUE

Execute the script and adjust the execution plan

SQL > @ C:\ Users\ YX\ coe_xfr_sql_profile_gn8zuq00kd86g_3617692013 (modified) .sql

SQL > REM

SQL > REM $Header: 215187.1 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql 11.4.3.5 2018-03-28 carlos.sierra $

SQL > REM

SQL > REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.

SQL > REM

SQL > REM AUTHOR

SQL > REM carlos.sierra@oracle.com

SQL > REM

SQL > REM SCRIPT

SQL > REM coe_xfr_sql_profile_gn8zuq00kd86g_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 gn8zuq00kd86g 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_gn8zuq00kd86g_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_gn8zuq00kd86g_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 / * + NO_INDEX (T1 IDX_T1)

7 * / * FROM T1 WHERE Numb1

8]'

9 h: = SYS.SQLPROF_ATTR (

10 Q'[BEGIN_OUTLINE_DATA]'

11 Q'[IGNORE_OPTIM_EMBEDDED_HINTS]'

12 Q'[OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')]'

13 Q'[DB_VERSION ('11.2.0.4')]'

14 Q'[ALL_ROWS]'

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

16 q' [INDEX (@ "SEL$1"T1" @ "SEL$1" ("T1". "N"))]'

17 Q'[END_OUTLINE_DATA]')

18 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

19 sql_text = > sql_txt

20 profile = > h

21 name = > 'coe_gn8zuq00kd86g_3617692013'

22 description = > 'coe gn8zuq00kd86g 3617692013' | |: signature | |''

23 category = > 'DEFAULT'

24 validate = > TRUE

25 replace = > TRUE

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

27 END

28 /

The PL/SQL process completed successfully.

SQL > WHENEVER SQLERROR CONTIN UE

SQL > SET ECHO OFF

SIGNATURE

-

... Manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_gn8zuq00kd86g_3617692013 completed

Check the implementation plan

SQL > SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1

N

-

one

SQL > SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR (NULL,NULL,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID gn8zuq00kd86g, child number 0

-

SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1

Plan hash value: 1369807930

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | 1 (100) | | |

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 4 | 1 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

Query Block Name / Object Alias (identified by operation id):

1-SEL$1 / T1@SEL$1

Outline Data

-

/ * +

PLAN_TABLE_OUTPUT

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

DB_VERSION ('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX (@ "SEL$1"T1" @ "SEL$1" ("T1". "N"))

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1-access ("N" = 1)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

Note

-

PLAN_TABLE_OUTPUT

-SQL profile coe_gn8zuq00kd86g_3617692013 used for this statement

46 rows have been selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.