SQL profile is a SQL statement that provides information other than system statistics, objects (tables and indexes, and so on), such as a running environment, additional, more accurate statistics, to help the optimizer choose a more appropriate execution plan for the SQL statement.
SQL profiles can be said to be the evolution of outlines. Outlines can implement the functions of SQL profiles can also be fully implemented, and SQL profiles has outlines does not have the optimization, the most important point is two points:
SQL profiles is easier to build, change, and control.
SQL Profiles is doing a better job of supporting SQL statements, which is a wider scope of application.
two purposes of using SQL profiles:
Locking or stabilizing the execution plan.
Causes the SQL statement to run on the specified execution plan without modifying the SQL in the app.
Refer to Old Bear's 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. The experimental environment constructs the statement: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) */a.*,b.owner from T1 a,t1 b where a.object_name like '%t1% ' and a.object_id=b.object_id ;
Set Autotrace off;
Explain plan for select/*+ use_nl (a b) index (T2) */a.*,b.owner from T1 a,t1 b where a.object_name like '%t1% ' and A.obje ct_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
at the beginning of 10g, the SQL statement outline data is included in V$sql_plan, which is the 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
---------------------------------------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------
Outline Data----------This section:
/*+ * * 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
--------------------------------------------------------------------------------------------------------------- --
predicate information (identified by Operation ID):
---------------------------------------------------
3-filter ("A". ") object_name "like '%t1% ')
4-access ("A". ") object_id "=" B "." OBJECT_ID ")
###############################################################################
2. Start creating and applying SQL profileUse an hint SQL statement and specify Sql_profile name: SQLPROFILE_T1
Precautions
:Two quotes---as (' 10.2.0.1 ') to be written (' 10.2.0.1 ')--'%t1% ' written '%t1% '
The end of the SQL statement; Do not write, the binding variable value is ' 1 ', to 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 that the SQL statement uses the sql_profile created in the previous step:sql> set Autotrace trace;
Sql> Select A.*,b.owner from t1 a,t1 b where a.object_name like '%t1% ' and a.object_id=b.object_id;
Rows selected.
Execution Plan
----------------------------------------------------------
Plan Hash value:190596302
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------
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 calls
0 db Block gets
163 consistent gets
0 physical Reads
0 Redo Size
2609 Bytes sent via sql*net to client
392 bytes received via sql*net from client
3 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
Rows processed 0 sorts (disk)
#####################################################################
fixed execution plan with SQL Tuning Advisor for Oracle--sql_id
----by sql_id sql_id "A2H6PZVQNCFVG",--want to tune the sql_id
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;
/
Set Long 1000
SELECT dbms_sqltune.report_tuning_task (' Tuning_test ') from dual;
Here the output of the sql_id is isolated from the v$sql, corresponding to the SQL without adding hint.
If SQL Tuning Advisor finds an ideal execution plan, the next step is:
Accept SQL profile, accepting this SQL profile.
# # Execute dbms_sqltune.accept_sql_profile (task_name = ' tuning_test ', replace = true,force_match=>true);
Validate 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;
To
Delete an SQL statement:
exec dbms_sqltune.drop_tuning_task (' tuning_test ');
If the ideal execution plan is not found, refer to the next method.
##################################################################
fixed execution plan with SQL Tuning Advisor for Oracle--Using 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 '; --Want to tune the SQL
My_task_name: = Dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
--bind_list = ' undo$ ',
--user_name = ' SONG ',
--scope = ' comprehensive ',
--time_limit = 60,
Task_name = ' Sql_tuning_test '
--description = ' Tuning Task '
);
END;
/
exec dbms_sqltune.execute_tuning_task (' sql_tuning_test '); --Perform the task of tuning
SELECT STATUS from user_advisor_tasks WHERE task_name = ' sql_tuning_test '; --View the status of the tuning job
SELECT Dbms_sqltune. Report_tuning_task (' Sql_tuning_test ') from dual;--View Tuning recommendations
exec dbms_sqltune.drop_tuning_task (' sql_tuning_test ');--Delete task
