trace_10046使用

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博客 ” ，链接：http://blog.itpub.net/241379/viewspace-721670/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/241379/viewspace-721670/