使用SQL PROFILE
究竟什么是SQL PROFILE? SQL PROFILE是指一个SQL执行计划的相关信息,里面包含了数据分布,连接顺序.直方图等等.可能会有很人问这些信息不是在正常的优化器产生执行计划的时候就有这些信息了.其实不尽然,我们知道ORACLE的优化器有两种模式,一种是正常的模式,二种是调整模式.在正常的SQL优化期限间,由于优化器需要尽快的返回执行计划,所以优化器没有时间来收集更多详细的信息.而在调整模式下,这时候优化器有足够的时候来得到相关的信息,因此得到的执行计划也是最好的.我们来看一个例子:
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create table t3 as select sid from v$session;
Table created.
SQL> create index t3_sid on t3(sid);
Index created.
SQL> create table t6 as select serial# from v$session;
SQL> exec dbms_stats.gather_table_stats(tabname=>'T1',ownname=>'SYS',cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(tabname=>'T2',ownname=>'SYS',cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(tabname=>'T3',ownname=>'SYS',cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(tabname=>'T6',ownname=>'SYS',cascade=>true);
PL/SQL procedure successfully completed.
我们来写一个简单的查询,并且使用HINTS来固定让我们的查询使用NEST LOOPS
SQL> select /*+ USE_NL(t2 t3 t6 t1) */ count(*)
2 from t1,t2,t3,t6
3 where t1.object_id=t3.sid
4 and t6.col#=t3.sid
5 and t2.object_id=t6.col#
6 and t6.col# in (4,5,6);
COUNT(*)
----------
18117
Execution Plan
----------------------------------------------------------
Plan hash value: 1775837690
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1469 (1)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | NESTED LOOPS | | 5 | 80 | 1469 (1)| 00:00:18 |
| 3 | NESTED LOOPS | | 9 | 117 | 1120 (1)| 00:00:14 |
| 4 | NESTED LOOPS | | 3 | 24 | 281 (1)| 00:00:04 |
|* 5 | TABLE ACCESS FULL| T1 | 3 | 15 | 281 (1)| 00:00:04 |
|* 6 | INDEX RANGE SCAN | T3_SID | 1 | 3 | 0 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T2 | 3 | 15 | 280 (1)| 00:00:04 |
|* 8 | TABLE ACCESS FULL | T6 | 1 | 3 | 39 (3)| 00:00:01 |
可以看到全部使用NL连接,成本为1469
使用SQL TUNING ADVISOR后
declare
i varchar2(1000);
j varchar2(100);
begin
i:='select /*+ USE_NL(t2 t3 t6 t1) */ count(*)
from t1,t2,t3,t6
where t1.object_id=t3.sid
and t6.col#=t3.sid
and t2.object_id=t6.col#
and t6.col# in (4,5,6)';
j:=dbms_sqltune.create_tuning_task(task_name=>'test8',sql_text=>i);
dbms_sqltune.execute_tuning_task('test8');
end;
select dbms_sqltune.report_tuning_task('test8') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test8
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 01/17/2013 09:10:26
Completed at : 01/17/2013 09:10:29
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : gtargkgvfft8x
SQL Text : select /*+ USE_NL(t2 t3 t6 t1) */ count(*)
from t1,t2,t3,t6
where t1.object_id=t3.sid
and t6.col#=t3.sid
and t2.object_id=t6.col#
and t6.col# in (4,5,6)
-------------------------------------------------------------------------------
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: 56.01%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'test8', task_owner
=> 'SYS', replace => TRUE);
Validation results
------------------
The SQL profile was tested by executing both its plan and the original plan
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(us): 149381 46381 68.95 %
CPU Time(us): 146811 40593 72.35 %
User I/O Time(us): 0 0
Buffer Gets: 4514 1988 55.95 %
Physical Read Requests: 0 0
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. The original plan was first executed to warm the buffer cache.
2. Statistics for original plan were averaged over next 5 executions.
3. The SQL profile plan was first executed to warm the buffer cache.
4. Statistics for the SQL profile plan were averaged over next 9 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1775837690
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1469 (1)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | NESTED LOOPS | | 18117 | 283K| 1469 (1)| 00:00:18 |
| 3 | NESTED LOOPS | | 9 | 117 | 1120 (1)| 00:00:14 |
| 4 | NESTED LOOPS | | 3 | 24 | 281 (1)| 00:00:04 |
|* 5 | TABLE ACCESS FULL| T1 | 3 | 15 | 281 (1)| 00:00:04 |
|* 6 | INDEX RANGE SCAN | T3_SID | 1 | 3 | 0 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T2 | 3 | 15 | 280 (1)| 00:00:04 |
|* 8 | TABLE ACCESS FULL | T6 | 2013 | 6039 | 39 (3)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("T1"."OBJECT_ID"=4 OR "T1"."OBJECT_ID"=5 OR
"T1"."OBJECT_ID"=6)
6 - access("T1"."OBJECT_ID"="T3"."SID")
filter("T3"."SID"=4 OR "T3"."SID"=5 OR "T3"."SID"=6)
7 - filter("T2"."OBJECT_ID"=4 OR "T2"."OBJECT_ID"=5 OR
"T2"."OBJECT_ID"=6)
8 - filter(("T6"."COL#"=4 OR "T6"."COL#"=5 OR "T6"."COL#"=6) AND
"T6"."COL#"="T3"."SID" AND "T2"."OBJECT_ID"="T6"."COL#")
2- Using SQL Profile
--------------------
Plan hash value: 796174040
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 603 (1)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | HASH JOIN | | 18117 | 283K| 603 (1)| 00:00:08 |
|* 3 | TABLE ACCESS FULL | T2 | 3 | 15 | 281 (1)| 00:00:04 |
|* 4 | HASH JOIN | | 18117 | 194K| 321 (1)| 00:00:04 |
| 5 | NESTED LOOPS | | 3 | 24 | 281 (1)| 00:00:04 |
|* 6 | TABLE ACCESS FULL| T1 | 3 | 15 | 281 (1)| 00:00:04 |
|* 7 | INDEX RANGE SCAN | T3_SID | 1 | 3 | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T6 | 18023 | 54069 | 40 (3)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."OBJECT_ID"="T6"."COL#")
3 - filter("T2"."OBJECT_ID"=4 OR "T2"."OBJECT_ID"=5 OR
"T2"."OBJECT_ID"=6)
4 - access("T6"."COL#"="T3"."SID")
6 - filter("T1"."OBJECT_ID"=4 OR "T1"."OBJECT_ID"=5 OR
"T1"."OBJECT_ID"=6)
7 - access("T1"."OBJECT_ID"="T3"."SID")
filter("T3"."SID"=4 OR "T3"."SID"=5 OR "T3"."SID"=6)
8 - filter("T6"."COL#"=4 OR "T6"."COL#"=5 OR "T6"."COL#"=6)
为我们找到了一个更好的执行计划.而我们需要做的仅仅是接受SQL PROFILE.
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test8', task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
我们再次执行之前的查询
SQL> select /*+ USE_NL(t2 t3 t6 t1) */ count(*)
2 from t1,t2,t3,t6
3 where t1.object_id=t3.sid
4 and t6.col#=t3.sid
5 and t2.object_id=t6.col#
6 and t6.col# in (4,5,6)
7 ;
COUNT(*)
----------
18117
Execution Plan
----------------------------------------------------------
Plan hash value: 796174040
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 603 (1)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | HASH JOIN | | 18117 | 283K| 603 (1)| 00:00:08 |
|* 3 | TABLE ACCESS FULL | T2 | 3 | 15 | 281 (1)| 00:00:04 |
|* 4 | HASH JOIN | | 18117 | 194K| 321 (1)| 00:00:04 |
| 5 | NESTED LOOPS | | 3 | 24 | 281 (1)| 00:00:04 |
|* 6 | TABLE ACCESS FULL| T1 | 3 | 15 | 281 (1)| 00:00:04 |
|* 7 | INDEX RANGE SCAN | T3_SID | 1 | 3 | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T6 | 18023 | 54069 | 40 (3)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."OBJECT_ID"="T6"."COL#")
3 - filter("T2"."OBJECT_ID"=4 OR "T2"."OBJECT_ID"=5 OR
"T2"."OBJECT_ID"=6)
4 - access("T6"."COL#"="T3"."SID")
6 - filter("T1"."OBJECT_ID"=4 OR "T1"."OBJECT_ID"=5 OR
"T1"."OBJECT_ID"=6)
7 - access("T1"."OBJECT_ID"="T3"."SID")
filter("T3"."SID"=4 OR "T3"."SID"=5 OR "T3"."SID"=6)
8 - filter("T6"."COL#"=4 OR "T6"."COL#"=5 OR "T6"."COL#"=6)
Note
-----
- SQL profile "SYS_SQLPROF_013c4613343d0000" used for this statement
在NOTE可以看出SQL PROFILE中可以看这个SQL PROFLE已经生效了,即使我指定的了HINTS也没有用了.关于SQL PROFILE中的相关信息和签名的信息都存放在DBA_
SQL_PROFILES中.这里面有一列SIGNATURE栏位.与V$SQL中的EXACT_MATCHING_SIGNATURE和FORCE_MATCHING_SIGNATURE栏位相对应.在解析SQL时,把所有的文本都转换成大写,去掉变量部分和常量部分就是FORCE_MATCHING_SIGNATURE,我在之前的执行中指定的FORCE_MATCHING_SIGNTURE为TRUE.所以只要对应就会使用这个SQL PROFILE.而在之前的版本中使用OUTLINES时要语句文本全部对应.
SQL PROFILE中究竟是存放的什么信息,信息来源存放在SQLOBJ$DATA的数据字典中的COMPDATA栏位中,这个字段(CLOB)是存放一个XML文件;.如
OPT_ESTIMATE(@"SEL$1", JOIN, ("T6"@"SEL$1", "T3"@"SEL$1", "T2"@"SEL$1", "T1"@"SEL$1"), SCALE_ROWS=3618.837965)
]]>
一个查询块,在T1,T6,T3连接时,评估行的基数,这个基数应该是最准确的.通过这个基数乘以一定的值就可以得出返回的行.
在11G中,数据库在每天的维护窗口期间,会运行一个SQL TUNING ADVISOR.对系统中负载高的语句,会自动产生SQL PROFIEL.作为DBA日常工作的部分,可以在每日巡检中可以通过select dbms_sqltune.report_auto_tuning_task() from dual
来查询这个自动化任务为我们产生了哪些建议.对有异常的语句再进行下一步处理.我们也可以定义参数.dbms_sqltune.set_auto_tuning_task_parameter来让数据库自动接受SQL PROFILE,不过建议最好还是别这么做.还有由人工干预来决定比较好.
结论:对于复杂的语句我们可先使用STA产生SQL PROFILE.看看ORACLE是怎么对这个SQL进行优化的,同时也使SQL优化变得更加简单了.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22749436/viewspace-752777/ ，如需转载，请注明出处，否则将追究法律责任。
