10053 trace 优化 sql 还是有点用，10053trace
好久不写博客了，一来是个人比较懒，二来是帮别人优化sql之后不喜欢收集案例，三则是有些经典优化案例客户不方便发。最近遇到一个有点坑爹的sql优化案例，我觉得非常
有必要分享给大家。废话不多说，SQL如下：
select *
from (select z_results.*, rownum autorowno
from (select /*+first_rows(20)*/
A.dd_id,
A.ysje,
A.ssje,
A.xjbz,
to_char(A.lrsj, 'yyyy-mm-dd') as lrsj,
a.kh_id,
a.jbr_id,
to_char(A.zxkprq, 'yyyy-mm-dd') as zxkprq,
a.khlx,
A.ddlx,
A.kpzt,
A.skzt,
A.fzgs_dm,
case
when a.JSDKJZT = '1' then
case
when (select sum(h.fpje)
from (select f.fpje, g.kppj_id
from pjkgl_fpxx f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '2'
and f.zfbz = '0'
union all
select f.fpje, g.kppj_id
from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '2'
and f.zfbz = '0') h
where h.kppj_id = a.dd_id) is null then
0
else
(select sum(fpje)
from (select f.fpje, g.kppj_id
from pjkgl_fpxx f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '2'
and f.zfbz = '0'
union all
select f.fpje, g.kppj_id
from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '2'
and f.zfbz = '0') h
where h.kppj_id = a.dd_id)
end
else
case
when (select sum(fpje)
from (select f.fpje, g.kppj_id
from pjkgl_fpxx f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '1'
and f.zfbz = '0'
union all
select f.fpje, g.kppj_id
from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '1'
and f.zfbz = '0') h
where h.kppj_id = a.dd_id) is null then
0
else
(select sum(fpje)
from (select f.fpje, g.kppj_id
from pjkgl_fpxx f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '1'
and f.zfbz = '0'
union all
select f.fpje, g.kppj_id
from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
where f.fp_id = g.fp_id
and g.zxfs = '1'
and f.pjlx = '1'
and f.zfbz = '0') h
where h.kppj_id = a.dd_id)
end
end as kpje,
a.JSDKJZT,
a.xsfs
from swgl_ddjbxx a
WHERE a.zfbz = '0'
and (a.ysje > 0 or a.ysje < 0 or
(a.ysje = 0 and a.kpzt = '3'))
and (a.ZXKPRQ >=
to_date('2014-10-05 00:00:00',
'YYYY-MM-DD HH24:MI:SS') or
a.KJJSDSJ >=
to_date('2014-10-05 00:00:00',
'YYYY-MM-DD HH24:MI:SS'))
and (a.ZXKPRQ <=
to_date('2014-11-05 23:59:59',
'YYYY-MM-DD HH24:MI:SS') or
a.KJJSDSJ <=
to_date('2014-11-05 23:59:59',
'YYYY-MM-DD HH24:MI:SS'))
and (a.XJBZ = '9999')
and (((a.ddlx in ('0', '2') and a.ddzt = '12') or
(a.ddlx = '1' and a.ddzt in ('04', '99'))))
and (a.XSJGFZGS_DM = '001081')
and (exists
(select 1
from swgl_ddhjxx hj
where hj.dd_id = a.dd_id
and (hj.hjlx = '03' or hj.hjlx = '06' or
hj.hjlx = '07' or hj.hjlx = '08' or
hj.hjlx = '09')
and hj.JBR_ID = '9F923C36CF0BA0DCA76A5F30D642A75C'))
order by zxkprq desc, dd_id desc) z_results
where rownum <= 20)
where autorowno >= 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 786935727
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 | 25 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
| 2 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 40 | | |
| 17 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 18 | UNION-ALL | | | | | |
| 19 | NESTED LOOPS | | | | | |
| 20 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | | | | |
| 26 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 40 | | |
| 32 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 33 | UNION-ALL | | | | | |
| 34 | NESTED LOOPS | | | | | |
| 35 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | | | | |
| 41 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 46 | SORT AGGREGATE | | 1 | 40 | | |
| 47 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 48 | UNION-ALL | | | | | |
| 49 | NESTED LOOPS | | | | | |
| 50 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | | | | |
| 56 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
|* 61 | VIEW | | 1 | 173 | 25 (4)| 00:00:01 |
|* 62 | COUNT STOPKEY | | | | | |
| 63 | VIEW | | 1 | 160 | 25 (4)| 00:00:01 |
|* 64 | SORT ORDER BY STOPKEY | | 1 | 177 | 25 (4)| 00:00:01 |
| 65 | NESTED LOOPS SEMI | | 1 | 177 | 24 (0)| 00:00:01 |
|* 66 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 1 | 127 | 19 (0)| 00:00:01 |
|* 67 | INDEX RANGE SCAN | IDX_SWGL_DDJBXX_XJBZ | 39 | | 3 (0)| 00:00:01 |
|* 68 | TABLE ACCESS BY INDEX ROWID | SWGL_DDHJXX | 1077 | 53850 | 5 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | IDX_SWGL_DDHJXX_DDID | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("G"."ZXFS"='1')
7 - access("G"."KPPJ_ID"=:B1)
8 - access("F"."FP_ID"="G"."FP_ID")
9 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
12 - filter("G"."ZXFS"='1')
13 - access("G"."KPPJ_ID"=:B1)
14 - access("F"."FP_ID"="G"."FP_ID")
15 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
21 - filter("G"."ZXFS"='1')
22 - access("G"."KPPJ_ID"=:B1)
23 - access("F"."FP_ID"="G"."FP_ID")
24 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
27 - filter("G"."ZXFS"='1')
28 - access("G"."KPPJ_ID"=:B1)
29 - access("F"."FP_ID"="G"."FP_ID")
30 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
36 - filter("G"."ZXFS"='1')
37 - access("G"."KPPJ_ID"=:B1)
38 - access("F"."FP_ID"="G"."FP_ID")
39 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
42 - filter("G"."ZXFS"='1')
43 - access("G"."KPPJ_ID"=:B1)
44 - access("F"."FP_ID"="G"."FP_ID")
45 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
51 - filter("G"."ZXFS"='1')
52 - access("G"."KPPJ_ID"=:B1)
53 - access("F"."FP_ID"="G"."FP_ID")
54 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
57 - filter("G"."ZXFS"='1')
58 - access("G"."KPPJ_ID"=:B1)
59 - access("F"."FP_ID"="G"."FP_ID")
60 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
61 - filter("AUTOROWNO">=21)
62 - filter(ROWNUM<=40)
64 - filter(ROWNUM<=40)
66 - filter(("A"."ZXKPRQ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
"A"."KJJSDSJ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND ("A"."ZXKPRQ"<=TO_DATE(' 2014-11-05
23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "A"."KJJSDSJ"<=TO_DATE(' 2014-11-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
AND "A"."XSJGFZGS_DM"='001097' AND "A"."DDZT"='12' AND "A"."ZFBZ"='0' AND ("A"."DDLX"='0' OR "A"."DDLX"='2') AND
("A"."YSJE"<>0 OR "A"."YSJE"=0 AND "A"."KPZT"='3'))
67 - access("A"."XJBZ"='9999')
68 - filter("HJ"."JBR_ID"='649013B54CF555D3E4A7A1980F7854B3' AND ("HJ"."HJLX"='03' OR "HJ"."HJLX"='06' OR
"HJ"."HJLX"='07' OR "HJ"."HJLX"='08' OR "HJ"."HJLX"='09'))
69 - access("HJ"."DD_ID"="A"."DD_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2115589 consistent gets
38 physical reads
364 redo size
1405 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
这个sql是一个分页语句，取第一页，逻辑读 2115589 ，200w的逻辑读，肯定不能接受。这个sql是一个税务局的sql,国企的大爷们，sql跑得慢点其实无所谓，顶多老子出去抽
根烟回来再看。可是我们dba心里过意不去啊，200w逻辑读，要跑10多秒，反正看不惯它。如果并发多了，跑得更久了。这种sql就是祸害。
分页语句， 不管那一页 返回数据都很少，这里又是第一页，很明显，应该让这个sql 走嵌套循环(返回数据少不走这个走啥？) 走嵌套循环你得选定驱动表，驱动表一般就是
order by 的表，如果 order by 2个表，那去死吧，无法优化(当然了，你可以搞物化视图，再在mv上面创建 order by 的索引) 这个sql还好 order by 是一个表的条件
order by zxkprq desc, dd_id desc 那么，很明显，就在 这2个例建立组合索引，然后 走index full scan desc 扫描20条数据就停止。于是创建了一个索引
create index idx_sb on swgl_ddjbxx(zxkprq,dd_id) nologging online parallel 4 ;
然后加了一个hint index_desc(a idx_sb) ，索引是 走了 而且确实是走的 index full scan desc ，但是 他妈的， 居然里面有 sort order by。索引本来就是有序的啊， 都走了
index full scan desc 他妈的 还去 sort order by 那 跑得快个毛 ，建立索引反而跑更慢了。 这不科学啊，你大爷的 ，哥优化这么多sql 还没遇到这种怪事，我本来很不喜欢
10053 trace， 也不喜欢 10046 trace，但是 有时候 不用还真解决不了问题。 于是让兄弟搞个测试表，然后写了一个sql模拟上面情况，再做一个10053 trace ，看一下为啥 还有
sort order by 。关键的 10053 trace 代码如下：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."DD_ID" "DD_ID","from$_subquery$_001"."YSJE" "YSJE","from$_subquery$_001"."SSJE" "SSJE","from$_subquery$_001"."XJBZ" "XJBZ","from$_subquery$_001"."LRSJ" "LRSJ","from$_subquery$_001"."KH_ID" "KH_ID",
"from$_subquery$_001"."JBR_ID" "JBR_ID","from$_subquery$_001"."ZXKPRQ" "ZXKPRQ","from$_subquery$_001"."KHLX" "KHLX","from$_subquery$_001"."DDLX" "DDLX","from$_subquery$_001"."KPZT" "KPZT","from$_subquery$_001"."SKZT" "SKZT","from$_subquery$_001"."FZGS_DM" "FZGS_DM"
FROM (SELECT /*+ INDEX_DESC ("A" "IDX_TEST_1") */ "A"."DD_ID" "DD_ID","A"."YSJE" "YSJE","A"."SSJE" "SSJE","A"."XJBZ" "XJBZ",TO_CHAR("A"."LRSJ",'yyyy-mm-dd') "LRSJ","A"."KH_ID" "KH_ID","A"."JBR_ID" "JBR_ID",TO_CHAR("A"."ZXKPRQ",'yyyy-mm-dd') "ZXKPRQ","A"."KHLX" "KHLX",
"A"."DDLX" "DDLX","A"."KPZT" "KPZT","A"."SKZT" "SKZT","A"."FZGS_DM" "FZGS_DM" FROM "NBGL2"."TEST1" "A" WHERE "A"."ZXKPRQ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') ORDER BY TO_CHAR("A"."ZXKPRQ",'yyyy-mm-dd') DESC) "from$_subquery$_001" WHERE ROWNUM<=20
看最后面， sql最终被翻译为 ................. order by to_char(a.zxkprq,'yyyy-mm-dd') desc ， 但是 我原始的sql是 直接的 order by a.zxkprq 。卧槽 优化器 SB了。 这个问题我不
想管了， 要么表 里面 数据格式不对，要么是 某个参数 被改了， 但是 这个问题 我不想继续搞了，难得去搞。于是创建了下面的索引：
create index idx_bigsb on swgl_ddjbxx(to_char(zxkprq,'yyyy-mm-dd'),dd_id) nologging online parallel 4 ;
最终确实是走了索引， 而且没有sort order by 了， 但是 逻辑读更大了，之前200w ，现在妈的 1000w多了。这是为啥呢？ 哦草，原来忘记了， 这个sql返回0条记录。我草，这
个sql返回0条记录，那不就是相当于取分页语句里面最后一页？ 意思就是说 要 index full scan desc 把整个索引叶子块全扫描了，而且还得回表！ 卧槽，这是最垃圾的执行计
划。于是变更思路，这个sql不是有个 exists吗？ 我们来看 exists 返回多少记录，我跑了一下，返回1w左右。 from 之后的主表 有 上千万条数据。 好的 ，那思路来了，就让
exists 去驱动主表吧。于是sql 改写为下面：
SQL> set autot trace
SQL> select *
2 from (select z_results.*, rownum autorowno
3 from (select /*+ first_rows LEADING("HJ"@"SB") no_expand */
4 A.dd_id,
5 A.ysje,
6 A.ssje,
7 A.xjbz,
8 to_char(A.lrsj, 'yyyy-mm-dd') as lrsj,
9 a.kh_id,
10 a.jbr_id,
11 to_char(A.zxkprq, 'yyyy-mm-dd') as zxkprq,
12 a.khlx,
13 A.ddlx,
14 A.kpzt,
15 A.skzt,
16 A.fzgs_dm,
17 case
18 when a.JSDKJZT = '1' then
19 case
20 when (select sum(h.fpje)
21 from (select f.fpje, g.kppj_id
22 from pjkgl_fpxx f, pjkgl_pjglxx g
23 where f.fp_id = g.fp_id
24 and g.zxfs = '1'
25 and f.pjlx = '2'
26 and f.zfbz = '0'
27 union all
28 select f.fpje, g.kppj_id
29 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
30 where f.fp_id = g.fp_id
31 and g.zxfs = '1'
32 and f.pjlx = '2'
33 and f.zfbz = '0') h
34 where h.kppj_id = a.dd_id) is null then
35 0
36 else
37 (select sum(fpje)
38 from (select f.fpje, g.kppj_id
39 from pjkgl_fpxx f, pjkgl_pjglxx g
40 where f.fp_id = g.fp_id
41 and g.zxfs = '1'
42 and f.pjlx = '2'
43 and f.zfbz = '0'
44 union all
45 select f.fpje, g.kppj_id
46 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
47 where f.fp_id = g.fp_id
48 and g.zxfs = '1'
49 and f.pjlx = '2'
50 and f.zfbz = '0') h
51 where h.kppj_id = a.dd_id)
52 end
53 else
54 case
55 when (select sum(fpje)
56 from (select f.fpje, g.kppj_id
57 from pjkgl_fpxx f, pjkgl_pjglxx g
58 where f.fp_id = g.fp_id
59 and g.zxfs = '1'
60 and f.pjlx = '1'
61 and f.zfbz = '0'
62 union all
63 select f.fpje, g.kppj_id
64 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
65 where f.fp_id = g.fp_id
66 and g.zxfs = '1'
67 and f.pjlx = '1'
68 and f.zfbz = '0') h
69 where h.kppj_id = a.dd_id) is null then
70 0
71 else
72 (select sum(fpje)
73 from (select f.fpje, g.kppj_id
74 from pjkgl_fpxx f, pjkgl_pjglxx g
75 where f.fp_id = g.fp_id
76 and g.zxfs = '1'
77 and f.pjlx = '1'
78 and f.zfbz = '0'
79 union all
80 select f.fpje, g.kppj_id
81 from PJKGL_FPXX_DISABLED f, pjkgl_pjglxx g
82 where f.fp_id = g.fp_id
83 and g.zxfs = '1'
84 and f.pjlx = '1'
85 and f.zfbz = '0') h
86 where h.kppj_id = a.dd_id)
87 end
88 end as kpje,
89 a.JSDKJZT,
90 a.xsfs
91 from swgl_ddjbxx a
92 WHERE a.zfbz = '0'
93 and (a.ysje > 0 or a.ysje < 0 or
94 (a.ysje = 0 and a.kpzt = '3'))
95 and (a.ZXKPRQ >=
96 to_date('2014-10-05 00:00:00',
97 'YYYY-MM-DD HH24:MI:SS') or
98 a.KJJSDSJ >=
99 to_date('2014-10-05 00:00:00',
100 'YYYY-MM-DD HH24:MI:SS'))
101 and (a.ZXKPRQ <=
102 to_date('2014-11-05 23:59:59',
103 'YYYY-MM-DD HH24:MI:SS') or
104 a.KJJSDSJ <=
105 to_date('2014-11-05 23:59:59',
106 'YYYY-MM-DD HH24:MI:SS'))
107 and (a.XJBZ = '9999')
108 and (((a.ddlx in ('0', '2') and a.ddzt = '12') or
109 (a.ddlx = '1' and a.ddzt in ('04', '99'))))
110 and (a.XSJGFZGS_DM = '001081')
111 and (a.dd_id in (select /*+ qb_name(sb) */ dd_id
112 from swgl_ddhjxx hj
113 where (hj.hjlx = '03' or hj.hjlx = '06' or hj.hjlx = '07' or
114 hj.hjlx = '08' or hj.hjlx = '09')
115 and hj.JBR_ID = '9F923C36CF0BA0DCA76A5F30D642A75C'))
116 order by zxkprq desc, dd_id desc) z_results
117 where rownum <= 20)
118 where autorowno >= 1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4195696224
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 173 | 3323 (1)| 00:00:40 |
| 1 | SORT AGGREGATE | | 1 | 40 | | |
| 2 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | | | | |
| 11 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 40 | | |
| 17 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 18 | UNION-ALL | | | | | |
| 19 | NESTED LOOPS | | | | | |
| 20 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | | | | |
| 26 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 40 | | |
| 32 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 33 | UNION-ALL | | | | | |
| 34 | NESTED LOOPS | | | | | |
| 35 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | | | | |
| 41 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
| 46 | SORT AGGREGATE | | 1 | 40 | | |
| 47 | VIEW | | 6 | 240 | 20 (0)| 00:00:01 |
| 48 | UNION-ALL | | | | | |
| 49 | NESTED LOOPS | | | | | |
| 50 | NESTED LOOPS | | 3 | 153 | 9 (0)| 00:00:01 |
|* 51 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX | 1 | | 1 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX | 1 | 25 | 2 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | | | | |
| 56 | NESTED LOOPS | | 3 | 153 | 11 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID| PJKGL_PJGLXX | 3 | 78 | 5 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_PJKGL_PJGLXX_PJID | 3 | | 4 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | PK_PJKGL_FPXX_DISABLED | 1 | | 1 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | PJKGL_FPXX_DISABLED | 1 | 25 | 2 (0)| 00:00:01 |
|* 61 | VIEW | | 1 | 173 | 3323 (1)| 00:00:40 |
|* 62 | COUNT STOPKEY | | | | | |
| 63 | VIEW | | 1 | 160 | 3323 (1)| 00:00:40 |
|* 64 | SORT ORDER BY STOPKEY | | 1 | 184 | 3323 (1)| 00:00:40 |
| 65 | NESTED LOOPS | | | | | |
| 66 | NESTED LOOPS | | 1 | 184 | 3322 (1)| 00:00:40 |
| 67 | SORT UNIQUE | | 1078 | 53900 | 1637 (1)| 00:00:20 |
|* 68 | TABLE ACCESS BY INDEX ROWID | SWGL_DDHJXX | 1078 | 53900 | 1637 (1)| 00:00:20 |
|* 69 | INDEX RANGE SCAN | IDX_SWGL_DDHJXX_JBRID | 5632 | | 50 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | PK_SWGL_DDJBXX | 1 | | 2 (0)| 00:00:01 |
|* 71 | TABLE ACCESS BY INDEX ROWID | SWGL_DDJBXX | 1 | 134 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("G"."ZXFS"='1')
7 - access("G"."KPPJ_ID"=:B1)
8 - access("F"."FP_ID"="G"."FP_ID")
9 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
12 - filter("G"."ZXFS"='1')
13 - access("G"."KPPJ_ID"=:B1)
14 - access("F"."FP_ID"="G"."FP_ID")
15 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
21 - filter("G"."ZXFS"='1')
22 - access("G"."KPPJ_ID"=:B1)
23 - access("F"."FP_ID"="G"."FP_ID")
24 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
27 - filter("G"."ZXFS"='1')
28 - access("G"."KPPJ_ID"=:B1)
29 - access("F"."FP_ID"="G"."FP_ID")
30 - filter("F"."PJLX"='2' AND "F"."ZFBZ"='0')
36 - filter("G"."ZXFS"='1')
37 - access("G"."KPPJ_ID"=:B1)
38 - access("F"."FP_ID"="G"."FP_ID")
39 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
42 - filter("G"."ZXFS"='1')
43 - access("G"."KPPJ_ID"=:B1)
44 - access("F"."FP_ID"="G"."FP_ID")
45 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
51 - filter("G"."ZXFS"='1')
52 - access("G"."KPPJ_ID"=:B1)
53 - access("F"."FP_ID"="G"."FP_ID")
54 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
57 - filter("G"."ZXFS"='1')
58 - access("G"."KPPJ_ID"=:B1)
59 - access("F"."FP_ID"="G"."FP_ID")
60 - filter("F"."PJLX"='1' AND "F"."ZFBZ"='0')
61 - filter("AUTOROWNO">=1)
62 - filter(ROWNUM<=20)
64 - filter(ROWNUM<=20)
68 - filter("HJ"."HJLX"='03' OR "HJ"."HJLX"='06' OR "HJ"."HJLX"='07' OR "HJ"."HJLX"='08' OR "HJ"."HJLX"='09')
69 - access("HJ"."JBR_ID"='9F923C36CF0BA0DCA76A5F30D642A75C')
70 - access("A"."DD_ID"="DD_ID")
71 - filter("A"."XJBZ"='9999' AND ("A"."ZXKPRQ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
"A"."KJJSDSJ">=TO_DATE(' 2014-10-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND ("A"."ZXKPRQ"<=TO_DATE(' 2014-11-05
23:59:59', 'syyyy-mm-dd hh24:mi:ss') OR "A"."KJJSDSJ"<=TO_DATE(' 2014-11-05 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
AND "A"."XSJGFZGS_DM"='001081' AND "A"."ZFBZ"='0' AND ("A"."DDZT"='12' AND ("A"."DDLX"='0' OR "A"."DDLX"='2') OR
"A"."DDLX"='1' AND ("A"."DDZT"='04' OR "A"."DDZT"='99')) AND ("A"."YSJE"<>0 OR "A"."YSJE"=0 AND "A"."KPZT"='3'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12314 consistent gets
0 physical reads
0 redo size
1405 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed然后搞了几个hint 最终 逻辑读 12314 还过得去，好吧，就这样吧，不继续优化了。 真他奶奶的蛋疼。
select t.LAST_LOAD_TIME ,t.*
from v$sqlarea t where upper(t.SQL_TEXT) like '%表名%'
and t.SQL_TEXT like '%SELECT%'
order by t.LAST_LOAD_TIME desc
Maclean教你读Oracle 10046 SQL TRACE
有同学一直向Maclean反应希望做一期10046 SQL trace的教程， 正好这几天有空 我们就理一理。
为什么我们要使用10046 trace?
10046 trace帮助我们解析 一条/多条SQL、PL/SQL语句的运行状态 ，这些状态包括 ：Parse/Fetch/Execute三个阶段中遇到的等待事件、消耗的物理和逻辑读、CPU时间、执行计划等等。
即10046 为我们揭示了 一条/多条SQL 的运行情况， 对于 以点入手的 SQL调优是很好的辅助工具，特别是在 10g之前没有ASH的情况下。 但整体系统调优 不是10046 所擅长的， 10046 是 性能调优的起钉器 ， AWR是性能调优 的锤子。
10046还能帮助我们分析 一些 DDL维护命令的内部工作原理， RMAN、Data Pump Expdp/Impdp等工具的缓慢问题等， 是研究 oracle 数据库原理的 居家旅行必备良品。
10046 和SQL TRACE的区别？
10046 比 SQL_TRACE参数提供更多的控制选项， 更详细的内容输出， 一般Maclean只用10046 而不用sql_trace
10046 和10053 的区别？
10053 是最常用的Oracle 优化器optimizer 跟踪trace， 10053 可以作为我们解析 优化器为什么选择某个执行计划，其中的理由的辅助工具，但并不告诉我们这个执行计划 到底运行地如何。
而10046 并不解释 optimizer优化器的工作， 但它同样说明了在SQL解析parse阶段所遇到的等待事件和所消耗的CPU等资源，以及Execute执行和Fetch阶段的各项指标。
简而言之10046 告诉我们SQL(执行计划)运行地如何， 10053告诉我们 优化器为什么为这个SQL选择某个执行计划。
10046 TRACE的LEVEL:
不同的Level 对应不同的跟踪级别
1 启用标准的SQL_TRACE功能 ( 默认) 包含了 SQL语句、响应时间、服务时间、处理的行数，物理读和写的数目、执行计划以及其他一些额外信息。 到版本10.2中 执行计划写入到 trace 的条件是仅当相关游标 已经关闭时， 且与之相关的执行统计信息是所有执行次数的总和数据。 到版本11.1中仅在每次游标的第一次执行后将执行计划写入到trace ， 执行统计信息仅仅和这第一次执行相关
4 比level 1时多出 绑定变量的 trace
8 比level 1多出等待事件，特别对于9i中指出 latch free等待事件很有用，对于分析全表扫描和索引扫描也很有用
12 比level 1 多出 绑定变量和 等待事件
16 在11g中为每一次执行生成STAT信息，仅在11.1之后可用
32 比level 1少执行计划
64 和level 1 相比 在第一次执行后还可能生成执行计划信息 ； 条件是某个游标在前一次执行的前提下 运行耗时变长了一分钟。仅在 11.2.0.2中可用
Level 28 (4 + 8 + 16) 代表 同时启用 level 4 、level 8、level 16
level 68 ( 64 + 4 ) 代表 同时启用 level 64、level 4
设置的方法如下：
session 级别： alter session set even......余下全文>>
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。