SQL Profile 总结（一）

浏览数：35 / 时间：2015年06月12日

一、前提概述

在介绍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)

-------------------------------------------------------------------------------

三、查看报告建议后，删除该报告

exec dbms_sqltune.drop_tuning_task(‘TASK_18580‘);