In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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)
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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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 Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
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 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_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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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
N
-
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
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 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.
Views: 0
*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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.