SQL Profile 总结（一）
一、前提概述
在介绍SQL Profile之前，不得不说的一个工具就是SQL Tuning Advisor；这个工具是从Oracle 10g開始引入，它的任务就是分析一个指定的SQL语句，并建议怎样使用一些方法来提高指定语句的性能，比如：收集缺失的对象统计信息、或者收集过时的对象统计信息、创建新的索引、调整SQL语句结构、採用SQL Profile等等方式。
二、为什么SQL Tuning Advisor可以找出提高语句性能的方法？
这须要从SQL Tuning Advisor的工作原理開始说明，例如以下
1、首先SQL Tuning Advisor将给定的SQL语句委派给Automotic Tuning Optimizer来完毕
2、Automotic Tuning Optimizer为Oracle查询优化器的一部分
3、Automotic Tuning Optimizer能够花较长的时间来产生一个高效的运行计划，比如：
- 能够使用耗时的技术如如果分析（what-if），并加强对动态採样技术的利用来核实它的预计值
- 能够执行实际执行计划中的多个步骤，并将得出的实际值与优化器评估的预计值相比較，来验证优化器最初的预计
注意：
1、尽管Automotic Tuning Optimizer为Oracle查询优化器的一部分，优化器无法再第一时间找到高效的运行计划，而Automotic Tuning Optimizer能够找到的原因主要是两者的工作职责不同。在正常情况下，优化器必须以最快的速度产生运行计划（秒级以内），而另外一个工具能够花较长时间来寻找高效的运行计划！
2、SQL Tuning Advisor并不一定每次到能找到高效的运行计划
3、SQL Tuning Advisor生成的建立，我们一定要细致阅读，并依据实际情况来决定是否採用这些建议
三、SQL Tuning Advisor简单概述
SQL Tuning Advisor的核心接口是通过dbms_sqltune程序包来提供的，能够接受的SQL语句类型例如以下4种
- SQL语句文本
- 存储在共享池中的SQL语句，指定SQL_ID就可以
- 存储在AWR资料库中的SQL语句，指定SQL_ID就可以
- SQL调优集的名称（能够看做是存储一系列SQL语句以及相关信息的对象集合）
四、封装的存储过程
依据实际使用，一般都是指定SQL_ID来进行优化，因此，我创建了一个存储过程，指定sql_id參数后，会自己主动进行sql优化，并打印出查看报告的方法，例如以下（以SYS用户执行）：
create or replace procedure p_create_sqltuning_task(p_sql_id varchar2) is
v_tuning_task varchar2(30);
v_sql_id v$session.sql_id%type;
begin
v_sql_id := p_sql_id;
v_tuning_task := dbms_sqltune.create_tuning_task(sql_id => v_sql_id);
dbms_sqltune.execute_tuning_task(v_tuning_task);
dbms_output.put_line('This Tuning task name is : '|| v_tuning_task);
dbms_output.put_line('-------------Please using follow command query SQL tuning report!------------');
dbms_output.put_line('set linesize 200 pagesize 9999');
dbms_output.put_line('set long 100000');
dbms_output.put_line('select dbms_sqltune.report_tuning_task('''||v_tuning_task||''') from dual;');
end;
/
理论知识都相对枯燥，我们来看一个详细的測试；
五、演示样例
一、执行SQL tuning advisor
SQL> exec p_create_sqltuning_task('g8hkhf0ma30vk');
This Tuning task name is : TASK_18580
-------------Please using follow command query SQL tuning report!------------
set linesize 200 pagesize 9999
set long 100000
select dbms_sqltune.report_tuning_task('TASK_18580') from dual;
PL/SQL procedure successfully completed.
二、查看产生的报告
SQL> set linesize 200 pagesize 9999
SQL> set long 100000
SQL> select dbms_sqltune.report_tuning_task('TASK_18580') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_18580')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_18580
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/10/2014 13:10:17
Completed at : 06/10/2014 13:10:21
-------------------------------------------------------------------------------
Schema Name: FLOW
SQL ID : g8hkhf0ma30vk
SQL Text : SELECT "IP","PR_URL","ACC_DATE","COOKIE" FROM "TB_FLOW" "F"
WHERE SUBSTR("PR_URL",INSTR("PR_URL",:"SYS_B_0"),:1)<>:"SYS_B_1"
AND TRUNC("ACC_DATE")=:2 AND "PR_URL" IS NOT NULL
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.26%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_18580',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 64 will improve its response time
98.26% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.11% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate TRUNC("F"."ACC_DATE")=:B1 used at line ID 2 of the execution
plan contains an expression on indexed column "ACC_DATE". This expression
prevents the optimizer from efficiently using indices on table
"FLOW"."TB_FLOW".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3675585382
-------------------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2381 | 253K| 312K (0)|999:59:59 |
| |
| 1 | PARTITION RANGE ALL| | 2381 | 253K| 312K (0)|999:59:59 | 1 | 82 |
|* 2 | TABLE ACCESS FULL | TB_FLOW | 2381 | 253K| 312K (0)|999:59:59 | 1 |
82 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:S
YS_B_1 AND TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)
2- Using Parallel Execution
---------------------------
Plan hash value: 1016406201
---------------------------------------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ
|IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 2381 | 253K| 5419 (0)| 40:42:45 | |
| | | |
| 1 | PX COORDINATOR | | | | | |
| | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2381 | 253K| 5419 (0)| 40:42:45 | | | Q1,00 | P->S | QC
(RAND) |
| 3 | PX BLOCK ITERATOR | | 2381 | 253K| 5419 (0)| 40:42:45 | 1 | 82 | Q1,00 | PC
WC | |
|* 4 | TABLE ACCESS FULL| TB_FLOW | 2381 | 253K| 5419 (0)| 40:42:45 | 1 | 82 | Q1,
00 | PCWP | |
-----------------------------------------------------------------------------------------------
-------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:SYS_B_1 AND
TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)
-------------------------------------------------------------------------------
This Tuning task name is : TASK_18580
-------------Please using follow command query SQL tuning report!------------
set linesize 200 pagesize 9999
set long 100000
select dbms_sqltune.report_tuning_task('TASK_18580') from dual;
PL/SQL procedure successfully completed.
二、查看产生的报告
SQL> set linesize 200 pagesize 9999
SQL> set long 100000
SQL> select dbms_sqltune.report_tuning_task('TASK_18580') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_18580')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_18580
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/10/2014 13:10:17
Completed at : 06/10/2014 13:10:21
-------------------------------------------------------------------------------
Schema Name: FLOW
SQL ID : g8hkhf0ma30vk
SQL Text : SELECT "IP","PR_URL","ACC_DATE","COOKIE" FROM "TB_FLOW" "F"
WHERE SUBSTR("PR_URL",INSTR("PR_URL",:"SYS_B_0"),:1)<>:"SYS_B_1"
AND TRUNC("ACC_DATE")=:2 AND "PR_URL" IS NOT NULL
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.26%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_18580',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 64 will improve its response time
98.26% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.11% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate TRUNC("F"."ACC_DATE")=:B1 used at line ID 2 of the execution
plan contains an expression on indexed column "ACC_DATE". This expression
prevents the optimizer from efficiently using indices on table
"FLOW"."TB_FLOW".
Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3675585382
-------------------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2381 | 253K| 312K (0)|999:59:59 |
| |
| 1 | PARTITION RANGE ALL| | 2381 | 253K| 312K (0)|999:59:59 | 1 | 82 |
|* 2 | TABLE ACCESS FULL | TB_FLOW | 2381 | 253K| 312K (0)|999:59:59 | 1 |
82 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:S
YS_B_1 AND TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)
2- Using Parallel Execution
---------------------------
Plan hash value: 1016406201
---------------------------------------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ
|IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 2381 | 253K| 5419 (0)| 40:42:45 | |
| | | |
| 1 | PX COORDINATOR | | | | | |
| | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2381 | 253K| 5419 (0)| 40:42:45 | | | Q1,00 | P->S | QC
(RAND) |
| 3 | PX BLOCK ITERATOR | | 2381 | 253K| 5419 (0)| 40:42:45 | 1 | 82 | Q1,00 | PC
WC | |
|* 4 | TABLE ACCESS FULL| TB_FLOW | 2381 | 253K| 5419 (0)| 40:42:45 | 1 | 82 | Q1,
00 | PCWP | |
-----------------------------------------------------------------------------------------------
-------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("PR_URL" IS NOT NULL AND SUBSTR("PR_URL",INSTR("PR_URL",:SYS_B_0),:1)<>:SYS_B_1 AND
TRUNC(INTERNAL_FUNCTION("ACC_DATE"))=:2)
-------------------------------------------------------------------------------
三、查看报告建议后，删除该报告
exec dbms_sqltune.drop_tuning_task('TASK_18580');
下一篇開始正式介绍SQL profile
