SQL Profile 总结（一）

转载： 原文地址：http://blog.csdn.net/oradh/article/details/29830615

一、前提概述

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

1. SQL语句文本
2. 存储在共享池中的SQL语句，指定SQL_ID即可
3. 存储在AWR资料库中的SQL语句，指定SQL_ID即可
4. 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');

下一篇开始正式介绍SQL profile