Fixed execution plan using SQL Profile and SQL Tuning Advisor

SQL Profile provides other information except system statistics and object (table and index) statistics for an SQL statement, such as the running environment and more accurate statistics, to help the optimizer select a more suitable execution plan for SQL statements.
SQL Profiles can be said to be the evolution of Outlines. The functions that Outlines can implement can also be fully implemented by SQL Profiles, while SQL Profiles have optimizations that Outlines do not have. The most important are the following:
SQL Profiles are easier to generate, change, and control.
SQL Profiles provides better support for SQL statements, that is, a wider range of applications.
Use SQL Profiles for the following purposes:
Lock or stable execution plan.
Make the SQL statement run according to the specified execution plan when the SQL statement in the application cannot be modified.
Refer to the old bear blog, link: http://www.laoxiong.net/sql-profiles-partii.html http://www.laoxiong.net/sql-profiles-part.html http://blog.sina.com.cn/s/blog_5037eacb01011mgu.htm
1. create table bys. t1 as select * from dba_objects;
Create index bys. t1_idx on t1 (object_id );
Exec dbms_stats.gather_table_stats ('bys ', 't1', cascade => true, degree => 4 );
Set autotrace trace;
Select a. *, B. owner from t1 a, t1 B where a. object_name like '% T1 %' and a. object_id = B. object_id;
Select/* + use_nl (a B) index (t2) */. *, B. owner from t1 a, t1 B where. object_name like '% T1 %' and. object_id = B. object_id;
Set autotrace off;
Explain plan for select/* + use_nl (a B) index (t2) */. *, B. owner from t1 a, t1 B where. object_name like '% T1 %' and. object_id = B. object_id;
Col SQL _text for a100
SELECT SQL _ID, SQL _TEXT from v $ SQL where SQL _TEXT LIKE '% a. *, B. owner from t1 a, t1 B % ';
#### 2t5xqt4d1dsaw
Starting from 10 Gb, v $ SQL _plan includes the SQL statement OUTLINE data, that is, Hints of the stable execution plan.As follows:
Set pagesize 1000
SQL> select * from table (dbms_xplan.display_cursor ('2t5xqt4d1dsaw ', null, 'outline '));
PLAN_TABLE_OUTPUT
Bytes ---------------------------------------------------------------------------------------------------------------
SQL _ID 2t5xqt4d1dsaw, child number 0
-------------------------------------
Select/* + use_nl (a B) index (t2) */a. *, B. owner from t1 a, t1 B where
A. object_name like '% T1 %' and a. object_id = B. object_id
Plan hash value: 190596302
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 979 (100) |
| 1 | table access by index rowid | T1 | 1 | 9 | 2 (0) | 00:00:01 |
| 2 | nested loops | 471 | 43803 | 979 (1) | 00:00:12 |
| * 3 | table access full | T1 | 471 | 39564 | 36 (0) | 00:00:01 |
| * 4 | index range scan | T1_IDX | 1 | 1 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

Outline Data ---------- this part:/* + */Is the required data.
-------------
/* +
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE ('10. 2.0.1 ')
ALL_ROWS
OUTLINE_LEAF (@ "SEL $1 ")
FULL (@ "SEL $1" "A" @ "SEL $1 ")
INDEX (@ "SEL $1" "B" @ "SEL $1" ("T1". "OBJECT_ID "))
LEADING (@ "SEL $1" "A" @ "SEL $1" "B" @ "SEL $1 ")
USE_NL (@ "SEL $1" "B" @ "SEL $1 ")
END_OUTLINE_DATA
*/

PLAN_TABLE_OUTPUT
Bytes -----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("A". "OBJECT_NAME" LIKE '% T1 % ')
4-access ("A". "OBJECT_ID" = "B". "OBJECT_ID ")
######################################## #######################################
2. Create and apply an SQL Profile using an SQL statement without HINT, and specify SQL _PROFILE name: SQLPROFILE_T1
Note:Two quotation marks (for example, '10. 2.0.1 ') must be written as ('10. 2.0.1 '') --' % T1 % 'to be written as' % T1 %''
At the end of the SQL statement. Do not enter it. When the variable value is '1', enter '1''
Declare
V_hints sys. sqlprof_attr;
Begin
V_hints: = sys. sqlprof_attr (
'Begin_outline_data ',
'Ignore _ OPTIM_EMBEDDED_HINTS ',
'Optimizer _ FEATURES_ENABLE (''10. 2.0.1 '')',
'All _ ROWS ',
'Outline _ LEAF (@ "SEL $1 ")',
'Full (@ "SEL $1" "A" @ "SEL $1 ")',
'Index (@ "SEL $1" "B" @ "SEL $1" ("T1". "OBJECT_ID "))',
'Leading (@ "SEL $1" "A" @ "SEL $1" "B" @ "SEL $1 ")',
'Use _ NL (@ "SEL $1" "B" @ "SEL $1 ")',
'End _ OUTLINE_DATA ');
Dbms_sqltune.import_ SQL _profile (
'Select a. *, B. owner from t1 a, t1 B where a. object_name like ''% T1 %'' and a. object_id = B. object_id ',
V_hints, 'sqlprofile _ t1 ',
Force_match => true, replace => true );
End;
/
Delete statement:
Exec dbms_sqltune.drop_ SQL _profile (name => 'sqlprofile _ t1 ');
######
3. verify whether the SQL statement uses the SQL _PROFILE: SQL> set autotrace trace created in the previous step;
SQL> select a. *, B. owner from t1 a, t1 B where a. object_name like '% T1 %' and a. object_id = B. object_id;
23 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 190596302
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 471 | 43803 | 979 (1) | 00:00:12 |
| 1 | table access by index rowid | T1 | 1 | 9 | 2 (0) | 00:00:01 |
| 2 | nested loops | 471 | 43803 | 979 (1) | 00:00:12 |
| * 3 | table access full | T1 | 471 | 39564 | 36 (0) | 00:00:01 |
| * 4 | index range scan | T1_IDX | 1 | 1 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("A". "OBJECT_NAME" LIKE '% T1 % ')
4-access ("A". "OBJECT_ID" = "B". "OBJECT_ID ")
Note
-----
-SQL profile "SQLPROFILE_T1" used for this statement

Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
163 consistent gets
0 physical reads
0 redo size
2609 bytes sent via SQL * Net to client
392 bytes encoded ed via SQL * Net from client
3 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed 0 sorts (disk)
######################################## #############################
Use Oracle SQL Tuning Advisor to set the execution plan-SQL _ID

---- SQL _id => 'a2h6pzvqncfvg 'through SQL _id; -- SQL _id to be optimized

Show serveroutput
Set serveroutput on;

DECLARE
A_tuning_task VARCHAR2 (30 );
BEGIN
A_tuning_task: = dbms_sqltune.create_tuning_task (SQL _id => 'a2h6pzvqncfvg ', task_name => 'tuning _ test ');
Dbms_sqltune.execute_tuning_task (a_tuning_task );
END;
/
Sets long 1000
SELECT dbms_sqltune.report_tuning_task ('tuning _ test') FROM dual;
 
The SQL _id in the output is found from v $ SQL, corresponding to the SQL without hint.
If SQL Tuning Advisor finds the ideal execution plan, the next step is: Accept SQL Profile: Accept this SQL Profile.

# Execute dbms_sqltune.accept_ SQL _profile (task_name => 'tuning _ test', replace => TRUE, force_match => true );
Verification statement after completion:
Set autotrace trace;
Select a. *, B. owner from t1 a, t1 B where a. object_name like '% T1 %' and a. object_id = B. object_id;
Set autotrace off;
Delete An SQL statement:
Exec dbms_sqltune.drop_tuning_task ('tuning _ test ');
If an ideal execution plan is not found, refer to the next method.
######################################## ##########################
Use Oracle's SQL Tuning Advisor to set the execution plan-use SQL _TEXT, refer to the following: DECLARE
My_task_name VARCHAR2 (30 );
My_sqltext CLOB;
BEGIN
My_sqltext: = 'select * from test where OBJECT_ID = 15'; -- the SQL statement to be optimized
My_task_name: = dbms_sqltune.create_tuning_task (SQL _text => my_sqltext,
-- Bind_list => 'undo $ ',
-- User_name => 'song ',
-- Scope => 'comphension ',
-- Time_limit => 60,
Task_name => 'SQL _ tuning_test'
-- Description => 'tuning task'
);
END;
/
 
Exec dbms_sqltune.execute_tuning_task ('SQL _ tuning_test'); -- execute the optimization task
Select status from USER_ADVISOR_TASKS WHERE task_name = 'SQL _ tuning_test'; -- view the STATUS of the optimization job
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('SQL _ tuning_test') FROM DUAL; -- View optimization suggestions
Exec dbms_sqltune.drop_tuning_task ('SQL _ tuning_test'); -- delete a task