本篇完全是根据老熊文章进行的实验再现,具体可以参照http://www.laoxiong.net/sql-profiles-part.html 或者《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(t1@sel$1 t2@sel$1)','index(t2@sel$1)');
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(t1@sel$1 t2@sel$1)
index(t2@sel$1)
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-710769/