sql profile的使用
本篇完全是根據老熊文章進行的實驗再現，具體可以參照 或者《DBA手記3》
首先建立兩個測試表
SQL> create table t1 as select object_id,object_name from dba_objects;
Table created
SQL> create table t2 as select * from dba_objects;
Table created
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1');
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2');
PL/SQL procedure successfully completed
SQL> create index t2_idx on t2(object_id);
Index created
檢視執行計劃，兩個表執行的均是全表掃描
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1536 | 56832 | 153 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 1536 | 56832 | 153 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| T1 | 1538 | 36912 | 37 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 30863 | 391K| 116 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Statistics
----------------------------------------------------------
157 recursive calls
0 db block gets
573 consistent gets
0 physical reads
0 redo size
2319 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
44 rows processed
新增hint，邏輯讀由573下降到215
SQL> select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1536 | 56832 | 3114 (1)| 00:00:38 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1536 | 56832 | 3114 (1)| 00:00:38 |
|* 3 | TABLE ACCESS FULL | T1 | 1538 | 36912 | 37 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
215 consistent gets
27 physical reads
0 redo size
2319 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
有兩種發放為其建立sqlprofile：1、使用sql tuning advisor自動建立 2、使用dmbs_sqltune.import_tuning_task手工建立
1、
首先看一下自動建立
SQL> var tuning_task varchar2(100);
SQL> declare
2 l_sql_id v$session.prev_sql_id%TYPE;
3 l_tuning_task varchar2(30);
4 begin
5 l_sql_id := 'dcwxqvu7kpzk0';
6 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
7 :tuning_task := l_tuning_task;
8 dbms_sqltune.execute_tuning_task(l_tuning_task);
9 dbms_output.put_line(l_tuning_task);
10 end;
11 /
SQL> set long 100000
SQL> select dbms_sqltune.report_tuning_task(:tuning_task) from dual;
DBMS_SQLTUNE.REPORT_TUNING_TAS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_16412
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 11/10/2011 14:49:55
Completed at : 11/10/2011 14:49:55
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : dcwxqvu7kpzk0
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like
:"SYS_B_0" and t1.object_id = t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 18.46%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_16412',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 1628 | 153 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 44 | 1628 | 153 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| T1 | 44 | 1056 | 37 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 30863 | 391K| 116 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE :SYS_B_0)
2- Using SQL Profile
--------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 1628 | 125 (1)| 00:0
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13 | 2 (0)| 00:0
| 2 | NESTED LOOPS | | 44 | 1628 | 125 (1)| 00:0
|* 3 | TABLE ACCESS FULL | T1 | 44 | 1056 | 37 (3)| 00:0
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE :SYS_B_0)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
tuning_task
---------
TASK_16412
根據提示執行
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_16413',replace => TRUE);
PL/SQL procedure successfully completed.
此時sql已經可以選擇正確的執行計劃，且使用到SQL profile "SYS_SQLPROF_014ce311c9b80001"
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44 | 1628 | 125 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 44 | 1628 | 125 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 44 | 1056 | 37 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_014ce311c9b80001" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
215 consistent gets
0 physical reads
0 redo size
2319 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
可以呼叫將之刪除
SQL> exec dbms_sqltune.drop_sql_profile(name => 'SYS_SQLPROF_014ce310146dc000');
PL/SQL procedure successfully completed
2、手工建立profile
需要呼叫dbms_sqltune.import_sql_profile
SQL> declare
2 v_hint sys.sqlprof_attr;
3 begin
4 v_hint := sys.sqlprof_attr('use_nl(t1 t2)','index(t2)');
5 dbms_sqltune.import_sql_profile(' select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id = t2.object_id;',v_hint,'SQLPROFILE_NAME1',force_match => true);
6 end;
7 /
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b where a.signature = b.signature and a.name ='SQLPROFILE_NAME1';
ATTR_VAL
--------------------------------------------------------------------------------
use_nl(t1 t2)
index(t2)
此時雖然使用到sql profile，但是執行計劃並沒有改變
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1536 | 56832 | 153 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 1536 | 56832 | 153 (2)| 00:00:02 |
|* 2 | TABLE ACCESS FULL| T1 | 1538 | 36912 | 37 (3)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 30863 | 391K| 116 (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
573 consistent gets
0 physical reads
0 redo size
2319 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
44 rows processed
重新生成
SQL> declare
2 v_hint sys.sqlprof_attr;
3 begin
4 v_hint := sys.sqlprof_attr('use_nl( );
5 dbms_sqltune.import_sql_profile(' select t1.*,t2.owner from t1,t2 where t1.object_name like ''%T1%'' and t1.object_id = t2.object_id',v_hint,'SQLPROFILE_NAME1',force_match => true,replace => true);
6 end;
7 /
PL/SQL procedure successfully completed
SQL> select attr_val from dba_sql_profiles a, sys.sqlprof$attr b where a.signature = b.signature and a.name ='SQLPROFILE_NAME1';
ATTR_VAL
--------------------------------------------------------------------------------
use_nl( )
index()
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
44 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3787413387
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1536 | 56832 | 3114 (1)| 00:00:38 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1536 | 56832 | 3114 (1)| 00:00:38 |
|* 3 | TABLE ACCESS FULL | T1 | 1538 | 36912 | 37 (3)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SQLPROFILE_NAME1" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
215 consistent gets
0 physical reads
0 redo size
2319 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
44 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-710769/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql profile使用SQL
- ORACLE SQL PROFILE使用OracleSQL
- sql_profile的使用(一)SQL
- SQL Server profile使用技巧SQLServer
- 檢視SQL PROFILE使用的HINTSQL
- 使用SQL Profile進行SQL優化案例SQL優化
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profileSQL
- MySQL Profile檢視SQL的資源使用MySql
- 使用SQL Profile進行SQL最佳化案例SQL
- 使用sql profile實現outline的功能SQL
- 使用sql profile固定執行計劃SQL
- sql最佳化：使用sql profile最佳化sql語句SQL
- 使用SQL PROFILE 給出合理的執行計劃SQL
- SQL PROFILE 測試SQL
- Oracle profile的使用Oracle
- sql tuning task和sql profileSQL
- SQL Profile 實驗03SQL
- SQL Profile 實驗02SQL
- SQL Profile 實驗01SQL
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- 使用coe_xfr_sql_profile固定執行計劃SQL
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- SQL Profile（第四篇）SQL
- SQL Profile（第三篇）SQL
- SQL Profile（第二篇）SQL
- 1223 result cache,sql profile,sql patchSQL
- SQL Profile（第一篇）SQL
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- 在不同的資料庫內移植SQL PROFILE優化的SQL資訊資料庫SQL優化
- spring @profile註解的使用Spring
- 西瓜狼的profile使用入門
- oracle 通過sql profile為sql語句加hintOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- SQL PROFILE修改固定執行計劃SQL
- 關於sql_profile中的繫結變數SQL變數
- sql profile和baseline的協作關係SQL
- Oracle Profile 使用詳解Oracle