How to use the manual type sql_profile 04/20 Update SLTechnology News&Howtos

How to use the manual type sql_profile

2025-04-20 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to use manual type sql_profile". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Automatic type of sql_profile (using dbms_sqltune)

The experiments are as follows:

SQL > create table T1 (n number)

Table created.

SQL > declare

Begin

For i in 1..10000 loop

Insert into T1 values (I)

Commit

End loop

End

PL/SQL procedure successfully completed.

SQL > select count (*) from T1

COUNT (*)

10000

SQL > create index idx_t1 on T1 (n)

Index created.

SQL > analyze table T1 compute statistics

Table analyzed.

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1

one

SQL > set lines 200

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID 1kg76709mx29d, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1

Plan hash value: 3617692013

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

| | * 1 | TABLE ACCESS FULL | T1 | 1 | 3 | 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]

42 rows selected.

SQL >

-- use sql tunning optimization (dbms_sqltune)

Declare

My_task_name VARCHAR2 (30)

My_sqltext CLOB

BEGIN

My_sqltext: = 'select / * + no_index (T1 idx_t1) * / * from T1 where naugh1'

My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (

Sql_text = > my_sqltext

User_name = > 'SYS'

Scope = > 'COMPREHENSIVE'

Time_limit = > 60

Task_name = > 'my_sql_tuning_task_2'

Description = > 'Task to tune a query on T1')

END

PL/SQL procedure successfully completed.

-- perform automatic tune tasks:

Exec dbms_sqltune.execute_tuning_task ('my_sql_tuning_task_2')

-- View the adjustment results of the automatic adjustment task

Set long 9000

Set longchunksize 1000

Set linesize 100

Select dbms_sqltune.report_tuning_task ('my_sql_tuning_task_2') from dual

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

GENERAL INFORMATION SECTION

Tuning Task Name: my_sql_tuning_task_2

Tuning Task Owner: SYS

Workload Type: Single SQL Statement

Scope: COMPREHENSIVE

Time Limit (seconds): 60

Completion Status: COMPLETED

Started at: 09/03/2017 12:22:50

Completed at: 09/03/2017 12:22:51

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

Schema Name: SYS

SQL ID: 4bh7sn1zvpgq7

SQL Text: select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1

FINDINGS SECTION (1 finding)

1-SQL Profile Finding (see explain plans section below)

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

A potentially better execution plan was found for this statement.

Recommendation (estimated benefit: 90.91%)

-Consider accepting the recommended SQL profile.

Execute dbms_sqltune.accept_sql_profile (task_name = >

'my_sql_tuning_task_2', task_owner = >' SYS', replace = > TRUE)

Validation results

The SQL profile was tested by executing both its plan and the original plan

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

And measuring their respective execution statistics. A plan may have been

Only partially executed if the other could be run to completion in less time.

Original Plan With SQL Profile Improved

Completion Status: COMPLETE COMPLETE

Elapsed Time (s):. 000136. 000017

CPU Time (s):. 0001

User O Time (s): 0 0

Buffer Gets: 22 290.9%

Physical Read Requests: 0 0

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

Physical Write Requests: 0 0

Physical Read Bytes: 0 0

Physical Write Bytes: 0 0

Rows Processed: 1 1

Fetches: 1 1

Executions: 1 1

Notes

1. Statistics for the original plan were averaged over 10 executions.

2. Statistics for the SQL profile plan were averaged over 10 executions.

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

EXPLAIN PLANS SECTION

1-Original With Adjusted Cost

Plan hash value: 3617692013

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

| | 0 | SELECT STATEMENT | | 1 | 3 | 7 (0) | 00:00:01 |

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

Predicate Information (identified by operation id):

1-filter ("N" = 1)

2-Using SQL Profile

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

Plan hash value: 1369807930

| | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0) | 00:00:01 |

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

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK ('MY_SQL_TUNING_TASK_2')

1-access ("N" = 1)

-- accept sql_profile:

Execute dbms_sqltune.accept_sql_profile (task_name = > 'my_sql_tuning_task_2', task_owner = >' SYS', replace = > TRUE)

PL/SQL procedure successfully completed.

-- Delete tuning_task, which is not executed here

Exec dbms_sqltune.drop_tuning_task ('my_sql_tuning_task_2')

-- Verification (execute sql and view execution plan)

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1

one

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID 1kg76709mx29d, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1

Plan hash value: 1369807930

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

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 3 | 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 SYS_SQLPROF_015e45fbfb7e0001 used for this statement

46 rows selected.

-now try to change the nasty 1 adjusted by the original sql's where to nasty 2.

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

two

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID c4j6hxkqudj1s, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

Plan hash value: 3617692013

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

| | * 1 | TABLE ACCESS FULL | T1 | 1 | 3 | 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" = 2)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

42 rows selected.

-to make the original sql profile still effective, you need to add force_match= > true, and then re-implement dbms_sqltune.accept_sql_profile

Execute dbms_sqltune.accept_sql_profile (task_name = > 'my_sql_tuning_task_2', task_owner = >' SYS', replace = > TRUE,force_match= > true)

Note the force_match parameter, which is equivalent to replacing the input value in the where condition of sql with the binding variable.

SQL > execute dbms_sqltune.accept_sql_profile (task_name = > 'my_sql_tuning_task_2', task_owner = >' SYS', replace = > TRUE,force_match= > true)

PL/SQL procedure successfully completed.

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

two

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID c4j6hxkqudj1s, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

Plan hash value: 1369807930

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

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 3 | 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" = 2)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

Note

PLAN_TABLE_OUTPUT

-SQL profile SYS_SQLPROF_015e462e462e0002 used for this statement

46 rows selected.

Manual type of sql_profile (using coe_xfr_sql_profile.sql)

-- if you follow the above sql, you need to delete sql_profile first.

Exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_015e45fbfb7e0001')

-- verify and re-execute the original sql

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

two

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID c4j6hxkqudj1s, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 1

Plan hash value: 3617692013

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

| | * 1 | TABLE ACCESS FULL | T1 | 1 | 3 | 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]

42 rows selected.

-generate sql_profile of the manual type of the original sql

SQL > @ coe_xfr_sql_profile.sql

Parameter 1:

SQL_ID (required)

Enter value for 1: c4j6hxkqudj1s

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: "c4j6hxkqudj1s"

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_c4j6hxkqudj1s_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 >! ls coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

Coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

-- optimize sql (add hint to index)

SQL > select / * + index (T1 idx_t1) * / * from T1 where nasty 2

two

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID 81hhdnr1waru8, child number 0

Select / * + index (T1 idx_t1) * / * from T1 where nasty 2

Plan hash value: 1369807930

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

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 3 | 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" = 2)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

42 rows selected.

-generate sql_profile of manual type of rewritten sql

SQL > @ coe_xfr_sql_profile

Parameter 1:

SQL_ID (required)

Enter value for 1: 81hhdnr1waru8

PLAN_HASH_VALUE AVG_ET_SECS

1369807930. 001

Parameter 2:

PLAN_HASH_VALUE (required)

Enter value for 2: 1369807930

Values passed to coe_xfr_sql_profile:

~ ~

SQL_ID: "81hhdnr1waru8"

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_81hhdnr1waru8_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 >

SQL >! ls coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql

Coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql

-adjust the plan first by looking at the sql_profile of the original sql

That is coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

[oracle@slient ~] $more coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

SPO coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.log

SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999

Omit; omit.

WHENEVER SQLERROR EXIT SQL.SQLCODE

REM

VAR signature NUMBER

REM

DECLARE

Sql_txt CLOB

H SYS.SQLPROF_ATTR

BEGIN

Sql_txt: = q' [

Select / * + no_index (T1 idx_t1)

* / * from T1 where nasty 2

= Note hint=

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)

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

Sql_text = > sql_txt

Profile = > h

Name = > 'coe_c4j6hxkqudj1s_3617692013'

Description = > 'coe c4j6hxkqudj1s 3617692013' | |: signature | |''

Category = > 'DEFAULT'

Validate = > TRUE

Replace = > TRUE

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

END

WHENEVER SQLERROR CONTINUE

SET ECHO OFF

PRINT signature

PRO

PRO... Manual custom SQL Profile has been created

PRO

SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF ""

SPO OFF

PRO

PRO COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed

[oracle@slient ~] $

-- check the sql_profile of the rewritten sql

That is coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql

[oracle@slient ~] $more coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql

SPO coe_xfr_sql_profile_81hhdnr1waru8_1369807930.log

SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999

Omit.

WHENEVER SQLERROR EXIT SQL.SQLCODE

REM

VAR signature NUMBER

REM

DECLARE

Sql_txt CLOB

H SYS.SQLPROF_ATTR

BEGIN

Sql_txt: = q' [

Select / * + index (T1 idx_t1)

* / * from T1 where nasty 2

= = hint=

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]')

: signature: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt)

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

Sql_text = > sql_txt

Profile = > h

Name = > 'coe_81hhdnr1waru8_1369807930'

Description = > 'coe 81hhdnr1waru8 1369807930' | |: signature | |''

Category = > 'DEFAULT'

Validate = > TRUE

Replace = > TRUE

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

END

WHENEVER SQLERROR CONTINUE

SET ECHO OFF

PRINT signature

PRO

PRO... Manual custom SQL Profile has been created

PRO

SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF ""

SPO OFF

PRO

PRO COE_XFR_SQL_PROFILE_81hhdnr1waru8_1369807930 completed

[oracle@slient ~] $

-replace:

Use the hints in sql_profile (coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql) of sql after rewriting

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 hints in the sql_profile (coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql) of the original 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]')

-- at the same time, change the sql_profile (coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql) of the original sql

The value of the parameter force_match in is changed from false to ture

View the sql_profile (coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql) of the original sql after replacement

[oracle@slient ~] $cat coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

SPO coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.log

SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999

REM

REM $Header: 215187.1 coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql 11.4.3.5 2017-09-03 carlos.sierra $

REM

REM AUTHOR

REM carlos.sierra@oracle.com

REM

REM SCRIPT

REM coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

REM

REM DESCRIPTION

REM This script is generated by coe_xfr_sql_profile.sql

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

REM SQL Profile for SQL_ID c4j6hxkqudj1s based on plan hash

REM value 3617692013.

REM The custom SQL Profile to be created by this script

REM will affect plans for SQL commands with signature

REM matching the one for SQL Text below.

REM Review SQL Text and adjust accordingly.

REM

REM PARAMETERS

REM None.

REM

REM EXAMPLE

REM SQL > START coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

REM

REM NOTES

REM 1. Should be run as SYSTEM or SYSDBA.

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

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

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

REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('coe_c4j6hxkqudj1s_3617692013')

REM 5. Be aware that using DBMS_SQLTUNE requires a license

REM for the Oracle Tuning Pack.

REM

WHENEVER SQLERROR EXIT SQL.SQLCODE

REM

VAR signature NUMBER

REM

DECLARE

Sql_txt CLOB

H SYS.SQLPROF_ATTR

BEGIN

Sql_txt: = q' [

Select / * + no_index (T1 idx_t1)

* / * from T1 where nasty 2

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]')

: signature: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt)

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

Sql_text = > sql_txt

Profile = > h

Name = > 'coe_c4j6hxkqudj1s_3617692013'

Description = > 'coe c4j6hxkqudj1s 3617692013' | |: signature | |''

Category = > 'DEFAULT'

Validate = > TRUE

Replace = > TRUE

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

END

WHENEVER SQLERROR CONTINUE

SET ECHO OFF

PRINT signature

PRO

PRO... Manual custom SQL Profile has been created

PRO

SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF ""

SPO OFF

PRO

PRO COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed

-- finally execute the sql_profile (coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql) of the original sql

SQL > @ coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql

SQL > REM

SQL > REM $Header: 215187.1 coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql 11.4.3.5 2017-09-03 carlos.sierra $

SQL > REM

SQL > REM AUTHOR

SQL > REM carlos.sierra@oracle.com

SQL > REM

SQL > REM SCRIPT

SQL > REM coe_xfr_sql_profile_c4j6hxkqudj1s_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 c4j6hxkqudj1s 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_c4j6hxkqudj1s_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_c4j6hxkqudj1s_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 nasty 2

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: signature: = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_txt)

19 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (

20 sql_text = > sql_txt

21 profile = > h

22 name = > 'coe_c4j6hxkqudj1s_3617692013'

23 description = > 'coe c4j6hxkqudj1s 3617692013' | |: signature | |''

24 category = > 'DEFAULT'

25 validate = > TRUE

26 replace = > TRUE

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

28 END

29 /

PL/SQL procedure successfully completed.

SQL > WHENEVER SQLERROR CONTINUE

SQL > SET ECHO OFF

SIGNATURE

3990623997227762646

... Manual custom SQL Profile has been created

COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed

-- Verification: execute the original sql, and then view the execution plan:

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

two

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID c4j6hxkqudj1s, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 2

Plan hash value: 1369807930

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

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 3 | 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" = 2)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

Note

PLAN_TABLE_OUTPUT

-SQL profile coe_c4j6hxkqudj1s_3617692013 used for this statement

46 rows selected.

-- because the force_ match value is changed to true in the sql_profile that rewrites the original sql, verify that the noun 2 in the where condition is changed to naught 4

SQL > select / * + no_index (T1 idx_t1) * / * from T1 where nasty 4

four

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID fd5p89b5jz0ct, child number 0

Select / * + no_index (T1 idx_t1) * / * from T1 where nasty 4

Plan hash value: 1369807930

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

| | * 1 | INDEX RANGE SCAN | IDX_T1 | 1 | 3 | 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" = 4)

Column Projection Information (identified by operation id):

1-"N" [NUMBER,22]

Note

PLAN_TABLE_OUTPUT

-SQL profile coe_c4j6hxkqudj1s_3617692013 used for this statement

46 rows selected.

It is still valid after verification.

This is the end of the content of "how to use the manual type sql_profile". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.