记一次sql执行慢的优化过程

        sql有10多张表join，查看执行计划耗费很低，但是执行时间很长
        下面是大致优化过程
  1， 查看执行计划资源消耗
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3252  consistent gets
          0  physical reads
          0  redo size
    1524932  bytes sent via SQL*Net to client
       1250  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        996  rows processed
如此低的资源消耗竟然用了47S，开始做10046事件追踪

2，做10046追踪
    准备对当前session使用oradebug命令
    SQL>oradebug setmypid
    激活10046事件
    SQL>oradebug event 10046 trace name context forever,level 12
    执行目标sql
    SQL>select .......
    获取trace文件名称和路径
    SQL>oradebug tracefile_name
    关闭10046事件
    SQL> oradebug event 10046 trace name context off
    格式化trace文件
    tkprof xxxx.trc  xxx.trc
查看追踪文件（源文件）
PARSE #1:c=46272965,e=46346147,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=455866081,tim=1493343560178137
EXEC #1:c=0,e=211,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=455866081,tim=1493343560178742
tkprof格式化之后的：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     46.27      46.34          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.09       0.34         44       7556          0         996
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70     46.36      46.69         44       7558          0         996
可以看到实际执行时间很短，大部分时间花在了解析上面，接着做10053事件追踪cbo优化器工作过程

3，10053事件
设置追踪文件名称：
SQL > alter session set tracefile_identifier = ' 10053事件 ' ;
启用10053追踪事件

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
执行事件

SQL>Explain plan for select ........

关闭10053事件追踪

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

查看追踪文件：
[oracle@oracle-1 trace]$ more ora11g_ora_20680_10053??????.trc | grep -i "permutations tried"
Number of join permutations tried: 1
Number of join permutations tried: 1124
Number of join permutations tried: 1
Number of join permutations tried: 1093
Number of join permutations tried: 1
Number of join permutations tried: 5
Number of join permutations tried: 1
Number of join permutations tried: 1
Number of join permutations tried: 1
Number of join permutations tried: 1093
Number of join permutations tried: 1
Number of join permutations tried: 1

发现CBO考虑了如此多的join路径，时间花在了这里~~~~
然后针对解析做具体的优化（不同的场景可能优化方式不一样），大概有下面一些优化方式：
1，修改隐含参数_OPTIMIZER_MAX_PERMUTATIONS的值来控制CBO考虑的路径数量，默认是2000，适当的改小，这样可能会使得CBO漏掉最优的路径
2，加hint，人为控制表连接的顺序
3，创建执行计划基线，创建sql profile等来稳定执行计划

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29098758/viewspace-2138171/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29098758/viewspace-2138171/