对已知sql的优化方法

如果知道了哪个SQL有性能有问题，可以通过以下3种方法来分析：

1. setautotrace trace exp stat

  可以看到sql语句的执行计划和统计数据；

   注意是否采用动态采样---dynamic sampling used for this statement；

   关闭：setautotrace off;

   显示内容：

 ExecutionPlan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |   364 | 10556 | 19691   (4)| 00:03:57 |
|*  1 |  TABLE ACCESS FULL| T   |   364 | 10556 | 19691   (4)| 00:03:57 |
--------------------------------------------------------------------------

Predicate Information (identified by operationid):
---------------------------------------------------

   1 -filter("OBJECT_ID"=8654)

Statistics
----------------------------------------------------------
        122  recursivecalls
          0  dbblock gets
      86874  consistent gets
      71608  physical reads
          0  redosize
       5376  bytes sentvia SQL*Net to client
        649  bytesreceived via SQL*Net from client
         26  SQL*Netroundtrips to/from client
          4  sorts(memory)
          0  sorts(disk)
        363 rows processed

   1）执行计划，与plsqldevelop F5显示的内容一致，不过多了一列TIME,是当前操作的估算时间，并不是实际执行时间；

   2）是否采用动态采样提示；

   3）谓词信息；

   4）统计信息。

2.sql_trace

   alter session set tracefile_identifier='qq';--设置trc文件的标示，方便寻找

   alter session set sql_trace=true; --打开sql跟踪

   select count(1) from t;--输入sql语句

   set sessioin set sql_trace=false;--关闭sql跟踪

   进入跟踪文件目录：$ORACLE_BASE/admin/orcl/udump 

   用tkprof工具查看： tkprof orcl_ora_20564_ww.trc out.txtexplain=oracle/oracle sys=no aggregate=yes；

   显示内容：

   1）sql语句统计信息：包括实际执行时间，sql语句解析情况（软解析还是硬解析），优化模式，物理读和一致读情况；

select * 
from
 t where object_id=8654

call     count       cpu    elapsed      disk      query    current       rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00      0.00          0         0          0          0
Execute      1      0.00      0.00          0          0         0           0
Fetch       26      3.32     24.87      71589      86861         0         363
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total       28      3.33     24.87      71589      86861         0         363

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55  (ORACLE)

 

  2）实际执行路径和执行计划（注意这里消耗信息都是实际消耗信息，不是CBO估算的）；

Rows    Row Source Operation
------- ---------------------------------------------------
    363  TABLE ACCESS FULL T(cr=86861 pr=71589 pw=0 time=5913208 us)

--cr:一致读    pr：物理读    pw：物理写    1s=1000000us

Rows     Execution Plan
------- ---------------------------------------------------
      0  SELECT STATEMENT  MODE: ALL_ROWS
    363   TABLE ACCESS   MODE:ANALYZED (FULL) OF 'T' (TABLE)

3.10046事件

   level 1： 同sql_trace

   level 4： 增加绑定变量信息,tkprof后的文件里看不到

   level 8： 增加等待信息

   level 12： level 4+ level 8

  1）查找当前session的跟踪文件：

       SELECTa.VALUE|| b.symbol|| c.instance_name|| '_ora_' || d.spid|| '.trc' trace_file
  FROM (SELECT VALUE FROM v$parameterWHERE NAME = 'user_dump_dest') a,
  (SELECT SUBSTR (VALUE, -6, 1)symbol
  FROM v$parameter WHERE NAME ='user_dump_dest') b,
  (SELECT instance_name FROMv$instance) c,
  (SELECT spid FROM v$session s,v$process p, v$mystat m
  WHERE s.paddr= p.addr AND s.SID =m.SID AND m.statistic# = 0) d

当然也可以alter session set tracefile_identifier='qq'设置跟踪文件的标示。

 2) 开启跟踪

SQL> alter session set events'10046 trace name context forever,level 12';

3）关闭跟踪

SQL> alter session set events'10046 trace name context off';

如果不用tkprof格式化trc文件，就可以看到绑定变量和每个步骤的等待信息，用tkprof后绑定信息就看不到了，原始trc文件里面记录了很多sys执行的sql，每一个sql都有详细的执行过程，包括解析，获取，执行，等待等全部信息，不过太多了，看得脑袋大！！而且找你自己的sql很伤神！如果不看绑定信息，我建议还是用tkprof，清晰明了，各种统计信息一目了然。哈哈，可能是我太懒了，虽然原始trc里每个指标都懂其含义，但要搞清楚数据背后隐藏的性能问题，还是需要相当的功力，很惭愧，现在我还没这样的水平！！不过以后遇到实际的问题再根据trc文件好好研究一番。