SQL> alter session set events='immediate trace name flush_cache';
Session altered.
SQL> alter system set events='immediate trace name flush_cache';
System altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL>
SQL> select *
2 from (select /*+first_rows(100)*/
3 z_results.*, rownum autorowno
4 from (select (select swjg.swjg_dm
5 from pt_dim_swjg swjg
6 where swjg.swjg_mc = a.mc) dm,
7 nvl(a.mc, '合计') mc,
8 '3' swjgcc,
9 count(distinct hs) hs,
10 sum(a.ss_bq) / 10000 ss_bq,
11 sum(a.ss_sq) / 10000 ss_sq,
12 sum(a.ss_bq - a.ss_sq) / 10000 ss_zje,
13 case
14 when sum(a.ss_sq) = 0 and sum(a.ss_bq) != 0 then
15 1
16 when sum(a.ss_sq) = 0 then
17 null
18 else
19 round(sum(a.ss_bq - a.ss_sq) / sum(a.ss_sq), 4)
20 end ss_bl,
21 sum(a.ssnj_bq) / 10000 ssnj_bq,
22 sum(a.ssnj_sq) / 10000 ssnj_sq,
23 sum(a.ssnj_bq - a.ssnj_sq) / 10000 ssnj_zje,
24 case
25 when sum(a.ssnj_sq) = 0 and sum(a.ssnj_bq) != 0 then
26 1
27 when sum(a.ssnj_sq) = 0 then
28 null
29 else
30 round(sum(a.ssnj_bq - a.ssnj_sq) / sum(a.ssnj_sq), 4)
31 end ssnj_bl,
32 sum(a.yjss_bq) / 10000 yjss_bq,
33 sum(a.yjss_sq) / 10000 yjss_sq,
34 sum(a.yjss_bq - a.yjss_sq) / 10000 yjss_zje,
35 case
36 when sum(a.yjss_sq) = 0 and sum(a.yjss_bq) != 0 then
37 1
38 when sum(a.yjss_sq) = 0 then
39 null
40 else
41 round(sum(a.yjss_bq - a.yjss_sq) / sum(a.yjss_sq), 4)
42 end yjss_bl
43 from (select nvl(s.SWJG_DSSWJG_MC, s.SWJG_SJSWJG_MC) mc,
44 dqy.nsrsbh hs,
45 (case
46 when (zs.sssq_q >=
47 to_date('2010-03-01', 'yyyy-mm-dd') and
48 zs.sssq_z <=
49 to_date('2010-03-31', 'yyyy-mm-dd')) then
50 nvl(zs.se, 0)
51 else
52 0
53 end) ss_bq,
54 (case
55 when (zs.sssq_q >=
56 to_date('2009-03-01', 'yyyy-mm-dd') and
57 zs.sssq_z <=
58 to_date('2009-03-31', 'yyyy-mm-dd')) then
59 nvl(zs.se, 0)
60 else
61 0
62 end) ss_sq,
63 (case
64 when (zs.sssq_q >=
65 to_date('2010-01-01', 'yyyy-mm-dd') and
66 zs.sssq_z <=
67 to_date('2010-03-31', 'yyyy-mm-dd')) then
68 nvl(zs.se, 0)
69 else
70 0
71 end) ssnj_bq,
72 (case
73 when (zs.sssq_q >=
74 to_date('2009-01-01', 'yyyy-mm-dd') and
75 zs.sssq_z <=
76 to_date('2009-03-31', 'yyyy-mm-dd')) then
77 nvl(zs.se, 0)
78 else
79 0
80 end) ssnj_sq,
81 (case
82 when (zs.sssq_q >=
83 to_date('2010-03-01', 'yyyy-mm-dd') and
84 zs.sssq_z <=
85 to_date('2010-03-31', 'yyyy-mm-dd') and
86 zs.rkrq is not null) then
87 nvl(zs.se, 0)
88 else
89 0
90 end) yjss_bq,
91 (case
92 when (zs.sssq_q >=
93 to_date('2009-03-01', 'yyyy-mm-dd') and
94 zs.sssq_z <=
95 to_date('2009-03-31', 'yyyy-mm-dd') and
96 zs.rkrq is not null) then
97 nvl(zs.se, 0)
98 else
99 0
100 end) yjss_sq
101 from dqy_jbxx dqy, zs_zsjbxx zs, pt_dim_swjg s
102 where dqy.nsrsbh = zs.nsrsbh
103 and dqy.swjg_dm = s.swjg_dm
104 and dqy.swjg_dm like '165%'
105 and ((zs.sssq_q >=
106 to_date('2010-01-01', 'yyyy-mm-dd') and
107 zs.sssq_z <=
108 to_date('2010-03-31', 'yyyy-mm-dd')) or
109 (zs.sssq_q >=
110 to_date('2009-01-01', 'yyyy-mm-dd') and
111 zs.sssq_z <=
112 to_date('2009-03-31', 'yyyy-mm-dd')))
113 and zs.zsxm_dm not in ('81', '61', '68', '69')
114 and zs.skzl_dm in ('10', '20')
115 union all
116 select nvl(s.SWJG_DSSWJG_MC, s.SWJG_SJSWJG_MC) mc,
117 dqy.nsrsbh,
118 null,
119 null,
120 null,
121 null,
122 null,
123 null
124 from dqy_jbxx dqy, pt_dim_swjg s
125 where dqy.swjg_dm = s.swjg_dm
126 and s.swjg_dm like '165%'
127 union all
128 select distinct s.SWJG_DSSWJG_MC,
129 null,
130 null,
131 null,
132 null,
133 null,
134 null,
135 null
136 from pt_dim_swjg s
137 where s.swjg_dm like '165%'
138 and s.swjg_cclx = '3'
139 and s.SWJG_DSSWJG_MC is not null) a
140 group by rollup(mc)
141 order by dm nulls first) z_results
142 where rownum <= 20)
143 where autorowno >= 1;
D:\oracle\admin\udump>tkprof xjsygl_ora_3328.trc trace.txt print=1000 record=sql.txt sys=no
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/241379/viewspace-721670/ ，如需转载，请注明出处，否则将追究法律责任。
