Re: 11g query taking 24 seconds, same query was instant in 9i.

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 26 May 2010 19:09:56 +0200
Message-ID: <4bfd55e4$0$22937$e4fe514c_at_news.xs4all.nl>



Op 26-5-2010 18:25, gs schreef:
> gs wrote:
>> Users of the app for the database I recently updated are saying some
>> reports are taking an unusually long time to run now, up to 1/2 a
>> minute whereas it was almost instant before the upgrade.
>>
>> I managed to find a sample of the sql that runs for the report, and
>> ran it from command line SQL and the 9i was running around .7 seconds
>> while the 11g was in the 24 second range. They also report that the
>> 11g is also not sorting the report like it was being sorted in 9i.
>>
>> I haven't had as much time as I want to spend with tuning, but from
>> reading the explain plans I can see that it looks like there is quite
>> a difference here in how the two databases handle the query. I'm
>> wading through technet etc. trying to decipher this but maybe another
>> set of eyes can point out something that I'm missing here.
>>
>> The SQL:
>> ========================================================================
>>> SELECT TRUNC(sample_date) as samp_date,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0000', sample_value)) samp1,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0100', sample_value)) samp2,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0200', sample_value)) samp3,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0300', sample_value)) samp4,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0400', sample_value)) samp5,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0500', sample_value)) samp6,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0600', sample_value)) samp7,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0700', sample_value)) samp8,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0800', sample_value)) samp9,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '0900', sample_value)) samp10,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1000', sample_value)) samp11,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1100', sample_value)) samp12,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1200', sample_value)) samp13,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1300', sample_value)) samp14,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1400', sample_value)) samp15,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1500', sample_value)) samp16,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1600', sample_value)) samp17,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1700', sample_value)) samp18,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1800', sample_value)) samp19,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '1900', sample_value)) samp20,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '2000', sample_value)) samp21,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '2100', sample_value)) samp22,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '2200', sample_value)) samp23,
>>> SUM(decode(to_char(sample_date,'HH24MI'), '2300', sample_value)) samp24,
>>> avg(sample_value) avg_value,
>>> max(a.sample_avg) s_avg, max(a.sample_stddev) s_stddev, max(a.cv_pct)
>>> s_cv
>>> FROM vw_cem_corr, (SELECT Avg(sample_value) sample_avg,
>>> Stddev(sample_value) sample_stddev,
>>> stddev(sample_value)/avg(sample_value)*100 CV_PCT
>>> FROM vw_cem_corr
>>> WHERE (sample_date >= '01-apr-09') and (TRUNC(sample_date) <=
>>> '30-apr-09')
>>> and (mill_area = 'KLN')
>>> and (tagname = 'FLOWDRY')) a
>>> WHERE (sample_date >= '01-apr-09') and (TRUNC(sample_date) <=
>>> '30-apr-09')
>>> and (mill_area = 'KLN')
>>> and (tagname = 'FLOWDRY')
>>> GROUP BY trunc(SAMPLE_DATE) /
>>
>>
>> ================================================================================
>
>
> pls excuse the formatting, my newsreader for some reason posted that as
> quotes, I'll try a plain paste:
>
> 9i explain plan:
> SQL> SELECT * FROM TABLE(dbms_xplan.display);
>
>
> -------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost |
> -------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 10 | 760 | 172 |
> | 1 | SORT GROUP BY | | 10 | 760 | 172 |
> | 2 | MERGE JOIN CARTESIAN | | 10 | 760 | 170 |
> | 3 | VIEW | | 1 | 39 | 87 |
> | 4 | SORT AGGREGATE | | 1 | 37 | |
> | 5 | VIEW | VW_CEM_CORR | 10 | 370 | 87 |
> | 6 | SORT UNIQUE | | 10 | 710 | 90 |
> | 7 | UNION-ALL | | | | |
> |* 8 | FILTER | | | | |
> | 9 | NESTED LOOPS | | 1 | 71 | 7 |
> | 10 | NESTED LOOPS | | 1 | 47 | 5 |
> | 11 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 12 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 13 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 14 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 15 | SORT AGGREGATE | | 1 | 13 | |
> | 16 | FIRST ROW | | 1 | 13 | 1 |
> |* 17 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 18 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 19 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |* 20 | FILTER | | | | |
> | 21 | NESTED LOOPS | | 1 | 71 | 7 |
> | 22 | NESTED LOOPS | | 1 | 47 | 5 |
> | 23 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 24 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 25 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 26 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 27 | SORT AGGREGATE | | 1 | 13 | |
> | 28 | FIRST ROW | | 1 | 13 | 1 |
> |* 29 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 30 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 31 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |* 32 | FILTER | | | | |
> | 33 | NESTED LOOPS | | 1 | 71 | 7 |
> | 34 | NESTED LOOPS | | 1 | 47 | 5 |
> | 35 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 36 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 37 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 38 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 39 | SORT AGGREGATE | | 1 | 13 | |
> | 40 | FIRST ROW | | 1 | 13 | 1 |
> |* 41 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 42 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 43 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |* 44 | FILTER | | | | |
> | 45 | NESTED LOOPS | | 1 | 71 | 7 |
> | 46 | NESTED LOOPS | | 1 | 47 | 5 |
> | 47 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 48 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 49 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 50 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 51 | SORT AGGREGATE | | 1 | 13 | |
> | 52 | FIRST ROW | | 1 | 13 | 1 |
> |* 53 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 54 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 55 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |* 56 | FILTER | | | | |
> | 57 | NESTED LOOPS | | 1 | 71 | 7 |
> | 58 | NESTED LOOPS | | 1 | 47 | 5 |
> | 59 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 60 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 61 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 62 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 63 | SORT AGGREGATE | | 1 | 13 | |
> | 64 | FIRST ROW | | 1 | 13 | 1 |
> |* 65 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 66 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 67 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |* 68 | FILTER | | | | |
> | 69 | NESTED LOOPS | | 1 | 71 | 7 |
> | 70 | NESTED LOOPS | | 1 | 47 | 5 |
> | 71 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 72 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 73 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 74 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 75 | SORT AGGREGATE | | 1 | 13 | |
> | 76 | FIRST ROW | | 1 | 13 | 1 |
> |* 77 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 78 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 79 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> | 80 | NESTED LOOPS | | 1 | 71 | 7 |
> | 81 | NESTED LOOPS | | 1 | 47 | 5 |
> | 82 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 83 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 84 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 85 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 86 | SORT AGGREGATE | | 1 | 13 | |
> | 87 | FIRST ROW | | 1 | 13 | 1 |
> |* 88 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 89 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |* 90 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |* 91 | FILTER | | | | |
> | 92 | NESTED LOOPS | | 1 | 71 | 7 |
> | 93 | NESTED LOOPS | | 1 | 47 | 5 |
> | 94 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |* 95 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 96 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |* 97 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 98 | SORT AGGREGATE | | 1 | 13 | |
> | 99 | FIRST ROW | | 1 | 13 | 1 |
> |*100 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 101 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*102 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*103 | FILTER | | | | |
> | 104 | NESTED LOOPS | | 1 | 71 | 7 |
> | 105 | NESTED LOOPS | | 1 | 47 | 5 |
> | 106 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*107 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 108 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*109 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 110 | SORT AGGREGATE | | 1 | 13 | |
> | 111 | FIRST ROW | | 1 | 13 | 1 |
> |*112 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 113 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*114 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*115 | FILTER | | | | |
> | 116 | NESTED LOOPS | | 1 | 71 | 7 |
> | 117 | NESTED LOOPS | | 1 | 47 | 5 |
> | 118 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*119 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 120 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*121 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 122 | SORT AGGREGATE | | 1 | 13 | |
> | 123 | FIRST ROW | | 1 | 13 | 1 |
> |*124 | INDEX RANGE SCAN (MIN/MAX)| PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 125 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*126 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> | 127 | VIEW | VW_CEM_CORR | 10 | 370 | 85 |
> | 128 | SORT UNIQUE | | 10 | 710 | 90 |
> | 129 | UNION-ALL | | | | |
> |*130 | FILTER | | | | |
> | 131 | NESTED LOOPS | | 1 | 71 | 7 |
> | 132 | NESTED LOOPS | | 1 | 47 | 5 |
> | 133 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*134 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 135 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*136 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 137 | SORT AGGREGATE | | 1 | 13 | |
> | 138 | FIRST ROW | | 1 | 13 | 1 |
> |*139 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 140 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*141 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*142 | FILTER | | | | |
> | 143 | NESTED LOOPS | | 1 | 71 | 7 |
> | 144 | NESTED LOOPS | | 1 | 47 | 5 |
> | 145 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*146 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 147 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*148 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 149 | SORT AGGREGATE | | 1 | 13 | |
> | 150 | FIRST ROW | | 1 | 13 | 1 |
> |*151 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 152 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*153 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*154 | FILTER | | | | |
> | 155 | NESTED LOOPS | | 1 | 71 | 7 |
> | 156 | NESTED LOOPS | | 1 | 47 | 5 |
> | 157 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*158 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 159 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*160 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 161 | SORT AGGREGATE | | 1 | 13 | |
> | 162 | FIRST ROW | | 1 | 13 | 1 |
> |*163 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 164 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*165 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*166 | FILTER | | | | |
> | 167 | NESTED LOOPS | | 1 | 71 | 7 |
> | 168 | NESTED LOOPS | | 1 | 47 | 5 |
> | 169 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*170 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 171 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*172 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 173 | SORT AGGREGATE | | 1 | 13 | |
> | 174 | FIRST ROW | | 1 | 13 | 1 |
> |*175 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 176 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*177 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*178 | FILTER | | | | |
> | 179 | NESTED LOOPS | | 1 | 71 | 7 |
> | 180 | NESTED LOOPS | | 1 | 47 | 5 |
> | 181 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*182 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 183 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*184 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 185 | SORT AGGREGATE | | 1 | 13 | |
> | 186 | FIRST ROW | | 1 | 13 | 1 |
> |*187 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 188 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*189 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*190 | FILTER | | | | |
> | 191 | NESTED LOOPS | | 1 | 71 | 7 |
> | 192 | NESTED LOOPS | | 1 | 47 | 5 |
> | 193 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*194 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 195 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*196 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 197 | SORT AGGREGATE | | 1 | 13 | |
> | 198 | FIRST ROW | | 1 | 13 | 1 |
> |*199 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 200 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*201 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> | 202 | NESTED LOOPS | | 1 | 71 | 7 |
> | 203 | NESTED LOOPS | | 1 | 47 | 5 |
> | 204 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*205 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 206 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*207 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 208 | SORT AGGREGATE | | 1 | 13 | |
> | 209 | FIRST ROW | | 1 | 13 | 1 |
> |*210 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 211 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*212 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*213 | FILTER | | | | |
> | 214 | NESTED LOOPS | | 1 | 71 | 7 |
> | 215 | NESTED LOOPS | | 1 | 47 | 5 |
> | 216 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*217 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 218 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*219 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 220 | SORT AGGREGATE | | 1 | 13 | |
> | 221 | FIRST ROW | | 1 | 13 | 1 |
> |*222 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 223 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*224 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*225 | FILTER | | | | |
> | 226 | NESTED LOOPS | | 1 | 71 | 7 |
> | 227 | NESTED LOOPS | | 1 | 47 | 5 |
> | 228 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*229 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 230 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*231 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 232 | SORT AGGREGATE | | 1 | 13 | |
> | 233 | FIRST ROW | | 1 | 13 | 1 |
> |*234 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 235 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*236 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> |*237 | FILTER | | | | |
> | 238 | NESTED LOOPS | | 1 | 71 | 7 |
> | 239 | NESTED LOOPS | | 1 | 47 | 5 |
> | 240 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 4 |
> |*241 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 1 | | 3 |
> | 242 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 1 | 17 | 1 |
> |*243 | INDEX UNIQUE SCAN | PK_DWQSTATIONSTATUS | 1 | | |
> | 244 | SORT AGGREGATE | | 1 | 13 | |
> | 245 | FIRST ROW | | 1 | 13 | 1 |
> |*246 | INDEX RANGE SCAN (MIN/MAX) | PK_DWQSTATIONSTATUS | 187 | | 1 |
> | 247 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 24 | 2 |
> |*248 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 |
> -------------------------------------------------------------------------------------------------
>
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>
> 8 - filter('TRSC12HR'='FLOWDRY')
> 12 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='TRSC12HR')
> filter("SYS_ALIAS_2"."TAGNAME"='TRSC12HR' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 14 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 17 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 19 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='TRSC12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 20 - filter('TRSM12HR'='FLOWDRY')
> 24 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='TRSM12HR')
> filter("SYS_ALIAS_2"."TAGNAME"='TRSM12HR' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 26 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 29 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 31 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='TRSM12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 32 - filter('SO2M12HR'='FLOWDRY')
> 36 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='SO2M12HR')
> filter("SYS_ALIAS_2"."TAGNAME"='SO2M12HR' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 38 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 41 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 43 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='SO2M12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 44 - filter('SO2COMP'='FLOWDRY')
> 48 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='SO2COMP')
> filter("SYS_ALIAS_2"."TAGNAME"='SO2COMP' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 50 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 53 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 55 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='SO2C12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 56 - filter('O2'='FLOWDRY')
> 60 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='O2')
> filter("SYS_ALIAS_2"."TAGNAME"='O2' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 62 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 65 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 67 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='O2V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 68 - filter('CO'='FLOWDRY')
> 72 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='CO')
> filter("SYS_ALIAS_2"."TAGNAME"='CO' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 74 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 77 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 79 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='COV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 83 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='FLOWDRY')
> filter("SYS_ALIAS_2"."TAGNAME"='FLOWDRY' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 85 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 88 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 90 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='FLOWV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 91 - filter('OPACMB1H'='FLOWDRY' AND 'OPAC1H'='FLOWDRY')
> 95 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09')
> filter(TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 97 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 100 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 102 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='OPACV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 103 - filter('TEMP'='FLOWDRY')
> 107 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='TEMP')
> filter("SYS_ALIAS_2"."TAGNAME"='TEMP' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 109 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 112 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 114 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='TEMPV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 115 - filter('OPACMB6M'='FLOWDRY' AND 'OPAC6M'='FLOWDRY')
> 119 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09')
> filter(TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 121 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 124 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 126 - access("B"."MILL_AREA"='KLN' AND
> "B"."SAMPLE_DATE"=TRUNC("SYS_ALIAS_2"."SAMPLE_DATE",'fmhh
> ') AND "B"."TAGNAME"='OPACV')
> 130 - filter('TRSC12HR'='FLOWDRY')
> 134 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='TRSC12HR')
> filter("SYS_ALIAS_2"."TAGNAME"='TRSC12HR' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 136 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 139 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 141 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='TRSC12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 142 - filter('TRSM12HR'='FLOWDRY')
> 146 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='TRSM12HR')
> filter("SYS_ALIAS_2"."TAGNAME"='TRSM12HR' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 148 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 151 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 153 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='TRSM12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 154 - filter('SO2M12HR'='FLOWDRY')
> 158 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='SO2M12HR')
> filter("SYS_ALIAS_2"."TAGNAME"='SO2M12HR' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 160 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 163 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 165 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='SO2M12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 166 - filter('SO2COMP'='FLOWDRY')
> 170 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='SO2COMP')
> filter("SYS_ALIAS_2"."TAGNAME"='SO2COMP' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 172 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 175 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 177 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='SO2C12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 178 - filter('O2'='FLOWDRY')
> 182 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='O2')
> filter("SYS_ALIAS_2"."TAGNAME"='O2' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 184 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 187 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 189 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='O2V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 190 - filter('CO'='FLOWDRY')
> 194 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='CO')
> filter("SYS_ALIAS_2"."TAGNAME"='CO' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 196 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 199 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 201 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='COV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 205 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='FLOWDRY')
> filter("SYS_ALIAS_2"."TAGNAME"='FLOWDRY' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 207 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 210 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 212 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='FLOWV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 213 - filter('OPACMB1H'='FLOWDRY' AND 'OPAC1H'='FLOWDRY')
> 217 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09')
> filter(TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 219 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 222 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 224 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='OPACV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 225 - filter('TEMP'='FLOWDRY')
> 229 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09' AND
> "SYS_ALIAS_2"."TAGNAME"='TEMP')
> filter("SYS_ALIAS_2"."TAGNAME"='TEMP' AND
> TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 231 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 234 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 236 - access("B"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND
> "B"."TAGNAME"='TEMPV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 237 - filter('OPACMB6M'='FLOWDRY' AND 'OPAC6M'='FLOWDRY')
> 241 - access("SYS_ALIAS_2"."MILL_AREA"='KLN' AND
> "SYS_ALIAS_2"."SAMPLE_DATE">='01-apr-09')
> filter(TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')
> 243 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN' AND
> "EMSSTATIONSTATUS"."EFFECTDATE"= (SELECT
> /*+ */ MAX("EMSSTATIONSTATUS"."EFFECTDATE") FROM "EMSSTATIONSTATUS"
> "EMSSTATIONSTATUS" WHERE
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B1 AND
> "EMSSTATIONSTATUS"."STATIONNUM"=:B2))
> 246 - access("EMSSTATIONSTATUS"."STATIONNUM"=:B1 AND
> "EMSSTATIONSTATUS"."EFFECTDATE"<=:B2)
> 248 - access("B"."MILL_AREA"='KLN' AND
> "B"."SAMPLE_DATE"=TRUNC("SYS_ALIAS_2"."SAMPLE_DATE",'fmhh
> ') AND "B"."TAGNAME"='OPACV')
>
>
> Note: cpu costing is off
>
> 482 rows selected.
>
> Elapsed: 00:00:00.07
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 258 recursive calls
> 0 db block gets
> 2921 consistent gets
> 0 physical reads
> 0 redo size
> 46495 bytes sent via SQL*Net to client
> 851 bytes received via SQL*Net from client
> 34 SQL*Net roundtrips to/from client
> 33 sorts (memory)
> 0 sorts (disk)
> 482 rows processed
>
> ================================================================================================================================================
>
>
> 11g explain plan:
>
> SQL> select * from table(dbms_xplan.display);
> Plan hash value: 3154918406
>
>
> -----------------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> -----------------------------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 2305 | 137K| 4253 (4)| 00:00:52 |
> | 1 | HASH GROUP BY | | 2305 | 137K| 4253 (4)| 00:00:52 |
> | 2 | NESTED LOOPS | | 2305 | 137K| 4252 (4)| 00:00:52 |
> | 3 | VIEW | | 1 | 39 | 2126 (4)| 00:00:26 |
> | 4 | SORT AGGREGATE | | 1 | 13 | | |
> | 5 | VIEW | VW_CEM_CORR | 2305 | 29965 | 2126 (4)| 00:00:26 |
> | 6 | SORT UNIQUE | | 2305 | 211K| 2126 (100)| 00:00:26 |
> | 7 | UNION-ALL | | | | | |
> |* 8 | FILTER | | | | | |
> | 9 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |* 10 | FILTER | | | | | |
> |* 11 | HASH JOIN | | 19291 | 1770K| 950 (2)| 00:00:12 |
> | 12 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 13 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 14 | HASH JOIN | | 436 | 31828 | 947 (2)| 00:00:12 |
> | 15 | NESTED LOOPS | | | | | |
> | 16 | NESTED LOOPS | | 14 | 840 | 945 (2)| 00:00:12 |
> | 17 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 14 | 420 | 917 (2)|
> 00:00:12 |
> |* 18 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 14 | | 906 (2)| 00:00:11 |
> |* 19 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 20 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |* 21 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |* 22 | FILTER | | | | | |
> | 23 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |* 24 | FILTER | | | | | |
> |* 25 | HASH JOIN | | 24785 | 2275K| 961 (2)| 00:00:12 |
> | 26 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 27 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 28 | HASH JOIN | | 560 | 40880 | 958 (2)| 00:00:12 |
> | 29 | NESTED LOOPS | | | | | |
> | 30 | NESTED LOOPS | | 18 | 1080 | 956 (2)| 00:00:12 |
> | 31 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 18 | 540 | 920 (2)|
> 00:00:12 |
> |* 32 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 18 | | 906 (2)| 00:00:11 |
> |* 33 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 34 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |* 35 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |* 36 | FILTER | | | | | |
> | 37 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |* 38 | FILTER | | | | | |
> |* 39 | HASH JOIN | | 19269 | 1768K| 950 (2)| 00:00:12 |
> | 40 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 41 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 42 | HASH JOIN | | 435 | 31755 | 947 (2)| 00:00:12 |
> | 43 | NESTED LOOPS | | | | | |
> | 44 | NESTED LOOPS | | 14 | 840 | 945 (2)| 00:00:12 |
> | 45 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 14 | 420 | 917 (2)|
> 00:00:12 |
> |* 46 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 14 | | 906 (2)| 00:00:11 |
> |* 47 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 48 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |* 49 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |* 50 | FILTER | | | | | |
> | 51 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |* 52 | FILTER | | | | | |
> |* 53 | HASH JOIN | | 19269 | 1768K| 950 (2)| 00:00:12 |
> | 54 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 55 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 56 | HASH JOIN | | 435 | 31755 | 947 (2)| 00:00:12 |
> | 57 | NESTED LOOPS | | | | | |
> | 58 | NESTED LOOPS | | 14 | 840 | 945 (2)| 00:00:12 |
> | 59 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 14 | 420 | 917 (2)|
> 00:00:12 |
> |* 60 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 14 | | 906 (2)| 00:00:11 |
> |* 61 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 62 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |* 63 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |* 64 | FILTER | | | | | |
> | 65 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |* 66 | FILTER | | | | | |
> |* 67 | HASH JOIN | | 589K| 52M| 2088 (1)| 00:00:26 |
> | 68 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 69 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 70 | HASH JOIN | | 13306 | 948K| 2082 (1)| 00:00:25 |
> |* 71 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 72 | NESTED LOOPS | | | | | |
> | 73 | NESTED LOOPS | | 429 | 25740 | 2081 (1)| 00:00:25 |
> | 74 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 425 | 12750 | 1230
> (1)| 00:00:15 |
> |* 75 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 425 | | 906 (2)| 00:00:11 |
> |* 76 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 77 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |* 78 | FILTER | | | | | |
> | 79 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |* 80 | FILTER | | | | | |
> |* 81 | HASH JOIN | | 577K| 51M| 2066 (1)| 00:00:25 |
> | 82 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 83 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 84 | HASH JOIN | | 13040 | 929K| 2060 (1)| 00:00:25 |
> |* 85 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 86 | NESTED LOOPS | | | | | |
> | 87 | NESTED LOOPS | | 420 | 25200 | 2059 (1)| 00:00:25 |
> | 88 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 417 | 12510 | 1224
> (1)| 00:00:15 |
> |* 89 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 417 | | 906 (2)| 00:00:11 |
> |* 90 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 91 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |* 92 | FILTER | | | | | |
> | 93 | HASH GROUP BY | | 2296 | 210K| 2117 (3)| 00:00:26 |
> |* 94 | HASH JOIN | | 580K| 52M| 2071 (1)| 00:00:25 |
> | 95 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |* 96 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |* 97 | HASH JOIN | | 13112 | 934K| 2065 (1)| 00:00:25 |
> |* 98 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 99 | NESTED LOOPS | | | | | |
> | 100 | NESTED LOOPS | | 422 | 25320 | 2064 (1)| 00:00:25 |
> | 101 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 419 | 12570 | 1225
> (1)| 00:00:15 |
> |*102 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 419 | | 906 (2)| 00:00:11 |
> |*103 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 104 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*105 | FILTER | | | | | |
> | 106 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*107 | FILTER | | | | | |
> |*108 | HASH JOIN | | 580K| 52M| 2071 (1)| 00:00:25 |
> | 109 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*110 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*111 | HASH JOIN | | 13109 | 934K| 2065 (1)| 00:00:25 |
> |*112 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 113 | NESTED LOOPS | | | | | |
> | 114 | NESTED LOOPS | | 422 | 25320 | 2064 (1)| 00:00:25 |
> | 115 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 419 | 12570 | 1225
> (1)| 00:00:15 |
> |*116 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 419 | | 906 (2)| 00:00:11 |
> |*117 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 118 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*119 | FILTER | | | | | |
> | 120 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*121 | FILTER | | | | | |
> |*122 | HASH JOIN | | 564K| 50M| 2038 (1)| 00:00:25 |
> | 123 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*124 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*125 | HASH JOIN | | 12740 | 908K| 2032 (1)| 00:00:25 |
> |*126 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 127 | NESTED LOOPS | | | | | |
> | 128 | NESTED LOOPS | | 410 | 24600 | 2031 (1)| 00:00:25 |
> | 129 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 407 | 12210 | 1216
> (1)| 00:00:15 |
> |*130 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 407 | | 906 (2)| 00:00:11 |
> |*131 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 132 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*133 | FILTER | | | | | |
> | 134 | HASH GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*135 | FILTER | | | | | |
> |*136 | HASH JOIN | | 47153 | 4328K| 2069 (1)| 00:00:25 |
> | 137 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*138 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*139 | HASH JOIN | | 1065 | 77745 | 2065 (1)| 00:00:25 |
> | 140 | NESTED LOOPS | | | | | |
> | 141 | NESTED LOOPS | | 34 | 2040 | 2064 (1)| 00:00:25 |
> | 142 | TABLE ACCESS BY INDEX ROWID| EMSCEMDETAIL | 419 | 12570 | 1225
> (1)| 00:00:15 |
> |*143 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 419 | | 906 (2)| 00:00:11 |
> |*144 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 145 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*146 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 147 | VIEW | VW_CEM_CORR | 2305 | 50710 | 2126 (4)| 00:00:26 |
> | 148 | SORT UNIQUE | | 2305 | 211K| 2126 (100)| 00:00:26 |
> | 149 | UNION-ALL | | | | | |
> |*150 | FILTER | | | | | |
> | 151 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*152 | FILTER | | | | | |
> |*153 | HASH JOIN | | 19291 | 1770K| 950 (2)| 00:00:12 |
> | 154 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*155 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*156 | HASH JOIN | | 436 | 31828 | 947 (2)| 00:00:12 |
> | 157 | NESTED LOOPS | | | | | |
> | 158 | NESTED LOOPS | | 14 | 840 | 945 (2)| 00:00:12 |
> | 159 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 14 | 420 | 917 (2)|
> 00:00:12 |
> |*160 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 14 | | 906 (2)| 00:00:11 |
> |*161 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 162 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*163 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |*164 | FILTER | | | | | |
> | 165 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*166 | FILTER | | | | | |
> |*167 | HASH JOIN | | 24785 | 2275K| 961 (2)| 00:00:12 |
> | 168 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*169 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*170 | HASH JOIN | | 560 | 40880 | 958 (2)| 00:00:12 |
> | 171 | NESTED LOOPS | | | | | |
> | 172 | NESTED LOOPS | | 18 | 1080 | 956 (2)| 00:00:12 |
> | 173 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 18 | 540 | 920 (2)|
> 00:00:12 |
> |*174 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 18 | | 906 (2)| 00:00:11 |
> |*175 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 176 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*177 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |*178 | FILTER | | | | | |
> | 179 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*180 | FILTER | | | | | |
> |*181 | HASH JOIN | | 19269 | 1768K| 950 (2)| 00:00:12 |
> | 182 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*183 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*184 | HASH JOIN | | 435 | 31755 | 947 (2)| 00:00:12 |
> | 185 | NESTED LOOPS | | | | | |
> | 186 | NESTED LOOPS | | 14 | 840 | 945 (2)| 00:00:12 |
> | 187 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 14 | 420 | 917 (2)|
> 00:00:12 |
> |*188 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 14 | | 906 (2)| 00:00:11 |
> |*189 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 190 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*191 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |*192 | FILTER | | | | | |
> | 193 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*194 | FILTER | | | | | |
> |*195 | HASH JOIN | | 19269 | 1768K| 950 (2)| 00:00:12 |
> | 196 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*197 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*198 | HASH JOIN | | 435 | 31755 | 947 (2)| 00:00:12 |
> | 199 | NESTED LOOPS | | | | | |
> | 200 | NESTED LOOPS | | 14 | 840 | 945 (2)| 00:00:12 |
> | 201 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 14 | 420 | 917 (2)|
> 00:00:12 |
> |*202 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 14 | | 906 (2)| 00:00:11 |
> |*203 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 204 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*205 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> |*206 | FILTER | | | | | |
> | 207 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*208 | FILTER | | | | | |
> |*209 | HASH JOIN | | 589K| 52M| 2088 (1)| 00:00:26 |
> | 210 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*211 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*212 | HASH JOIN | | 13306 | 948K| 2082 (1)| 00:00:25 |
> |*213 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 214 | NESTED LOOPS | | | | | |
> | 215 | NESTED LOOPS | | 429 | 25740 | 2081 (1)| 00:00:25 |
> | 216 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 425 | 12750 | 1230
> (1)| 00:00:15 |
> |*217 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 425 | | 906 (2)| 00:00:11 |
> |*218 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 219 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*220 | FILTER | | | | | |
> | 221 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*222 | FILTER | | | | | |
> |*223 | HASH JOIN | | 577K| 51M| 2066 (1)| 00:00:25 |
> | 224 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*225 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*226 | HASH JOIN | | 13040 | 929K| 2060 (1)| 00:00:25 |
> |*227 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 228 | NESTED LOOPS | | | | | |
> | 229 | NESTED LOOPS | | 420 | 25200 | 2059 (1)| 00:00:25 |
> | 230 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 417 | 12510 | 1224
> (1)| 00:00:15 |
> |*231 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 417 | | 906 (2)| 00:00:11 |
> |*232 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 233 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*234 | FILTER | | | | | |
> | 235 | SORT GROUP BY | | 2296 | 210K| 2117 (3)| 00:00:26 |
> |*236 | HASH JOIN | | 580K| 52M| 2071 (1)| 00:00:25 |
> | 237 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*238 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*239 | HASH JOIN | | 13112 | 934K| 2065 (1)| 00:00:25 |
> |*240 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 241 | NESTED LOOPS | | | | | |
> | 242 | NESTED LOOPS | | 422 | 25320 | 2064 (1)| 00:00:25 |
> | 243 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 419 | 12570 | 1225
> (1)| 00:00:15 |
> |*244 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 419 | | 906 (2)| 00:00:11 |
> |*245 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 246 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*247 | FILTER | | | | | |
> | 248 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*249 | FILTER | | | | | |
> |*250 | HASH JOIN | | 580K| 52M| 2071 (1)| 00:00:25 |
> | 251 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*252 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*253 | HASH JOIN | | 13109 | 934K| 2065 (1)| 00:00:25 |
> |*254 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 255 | NESTED LOOPS | | | | | |
> | 256 | NESTED LOOPS | | 422 | 25320 | 2064 (1)| 00:00:25 |
> | 257 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 419 | 12570 | 1225
> (1)| 00:00:15 |
> |*258 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 419 | | 906 (2)| 00:00:11 |
> |*259 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 260 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*261 | FILTER | | | | | |
> | 262 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*263 | FILTER | | | | | |
> |*264 | HASH JOIN | | 564K| 50M| 2038 (1)| 00:00:25 |
> | 265 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*266 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*267 | HASH JOIN | | 12740 | 908K| 2032 (1)| 00:00:25 |
> |*268 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> | 269 | NESTED LOOPS | | | | | |
> | 270 | NESTED LOOPS | | 410 | 24600 | 2031 (1)| 00:00:25 |
> | 271 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 407 | 12210 | 1216
> (1)| 00:00:15 |
> |*272 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 407 | | 906 (2)| 00:00:11 |
> |*273 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 274 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*275 | FILTER | | | | | |
> | 276 | SORT GROUP BY | | 1 | 94 | 1 (100)| 00:00:01 |
> |*277 | FILTER | | | | | |
> |*278 | HASH JOIN | | 47153 | 4328K| 2069 (1)| 00:00:25 |
> | 279 | TABLE ACCESS BY INDEX ROWID | EMSSTATIONSTATUS | 44 | 924 | 3
> (0)| 00:00:01 |
> |*280 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | | 1 (0)| 00:00:01 |
> |*281 | HASH JOIN | | 1065 | 77745 | 2065 (1)| 00:00:25 |
> | 282 | NESTED LOOPS | | | | | |
> | 283 | NESTED LOOPS | | 34 | 2040 | 2064 (1)| 00:00:25 |
> | 284 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 419 | 12570 | 1225
> (1)| 00:00:15 |
> |*285 | INDEX RANGE SCAN | PK_EMSCEMDETAIL | 419 | | 906 (2)| 00:00:11 |
> |*286 | INDEX UNIQUE SCAN | PK_EMSCEMDETAIL | 1 | | 1 (0)| 00:00:01 |
> | 287 | TABLE ACCESS BY INDEX ROWID | EMSCEMDETAIL | 1 | 30 | 2 (0)|
> 00:00:01 |
> |*288 | INDEX RANGE SCAN | PK_DWQSTATIONSTATUS | 44 | 572 | 1 (0)|
> 00:00:01 |
> -----------------------------------------------------------------------------------------------------------------
>
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>
> 8 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 10 - filter(NULL IS NOT NULL)
> 11 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 13 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 14 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 18 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09' AND
> "A"."TAGNAME"='TRSC12HR')
> filter("A"."TAGNAME"='TRSC12HR' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 19 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='TRSC12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 21 - access("STATIONNUM"='KLN')
> 22 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 24 - filter(NULL IS NOT NULL)
> 25 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 27 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 28 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 32 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09' AND
> "A"."TAGNAME"='TRSM12HR')
> filter("A"."TAGNAME"='TRSM12HR' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 33 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='TRSM12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 35 - access("STATIONNUM"='KLN')
> 36 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 38 - filter(NULL IS NOT NULL)
> 39 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 41 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 42 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 46 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09' AND
> "A"."TAGNAME"='SO2M12HR')
> filter("A"."TAGNAME"='SO2M12HR' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 47 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='SO2M12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 49 - access("STATIONNUM"='KLN')
> 50 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 52 - filter(NULL IS NOT NULL)
> 53 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 55 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 56 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 60 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09' AND
> "A"."TAGNAME"='SO2COMP')
> filter("A"."TAGNAME"='SO2COMP' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 61 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='SO2C12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 63 - access("STATIONNUM"='KLN')
> 64 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 66 - filter(NULL IS NOT NULL)
> 67 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 69 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 70 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 71 - access("STATIONNUM"='KLN')
> 75 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09' AND
> "A"."TAGNAME"='O2')
> filter("A"."TAGNAME"='O2' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 76 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='O2V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 78 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 80 - filter(NULL IS NOT NULL)
> 81 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 83 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 84 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 85 - access("STATIONNUM"='KLN')
> 89 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09' AND
> "A"."TAGNAME"='CO')
> filter("A"."TAGNAME"='CO' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 90 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='COV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 92 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 94 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 96 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 97 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 98 - access("STATIONNUM"='KLN')
> 102 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='FLOWDRY')
> filter("A"."TAGNAME"='FLOWDRY' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 103 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='FLOWV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 105 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 107 - filter(NULL IS NOT NULL)
> 108 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 110 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 111 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 112 - access("STATIONNUM"='KLN')
> 116 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='FLOWDRY')
> filter("A"."TAGNAME"='FLOWDRY' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 117 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='OPACV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 119 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 121 - filter(NULL IS NOT NULL)
> 122 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 124 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 125 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 126 - access("STATIONNUM"='KLN')
> 130 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='TEMP')
> filter("A"."TAGNAME"='TEMP' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 131 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='TEMPV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 133 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 135 - filter(NULL IS NOT NULL)
> 136 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 138 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 139 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 143 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='FLOWDRY')
> filter("A"."TAGNAME"='FLOWDRY' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 144 - access("B"."MILL_AREA"='KLN' AND
> "B"."SAMPLE_DATE"=TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"),'fmh
> h') AND "B"."TAGNAME"='OPACV')
> 146 - access("STATIONNUM"='KLN')
> 150 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 152 - filter(NULL IS NOT NULL)
> 153 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 155 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 156 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 160 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='TRSC12HR')
> filter("A"."TAGNAME"='TRSC12HR' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 161 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='TRSC12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 163 - access("STATIONNUM"='KLN')
> 164 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 166 - filter(NULL IS NOT NULL)
> 167 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 169 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 170 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 174 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='TRSM12HR')
> filter("A"."TAGNAME"='TRSM12HR' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 175 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='TRSM12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 177 - access("STATIONNUM"='KLN')
> 178 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 180 - filter(NULL IS NOT NULL)
> 181 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 183 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 184 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 188 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='SO2M12HR')
> filter("A"."TAGNAME"='SO2M12HR' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 189 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='SO2M12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 191 - access("STATIONNUM"='KLN')
> 192 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 194 - filter(NULL IS NOT NULL)
> 195 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 197 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 198 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 202 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='SO2COMP')
> filter("A"."TAGNAME"='SO2COMP' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 203 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='SO2C12V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 205 - access("STATIONNUM"='KLN')
> 206 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 208 - filter(NULL IS NOT NULL)
> 209 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 211 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 212 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 213 - access("STATIONNUM"='KLN')
> 217 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='O2')
> filter("A"."TAGNAME"='O2' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 218 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='O2V')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 220 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 222 - filter(NULL IS NOT NULL)
> 223 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 225 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 226 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 227 - access("STATIONNUM"='KLN')
> 231 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='CO')
> filter("A"."TAGNAME"='CO' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 232 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='COV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 234 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 236 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 238 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 239 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 240 - access("STATIONNUM"='KLN')
> 244 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='FLOWDRY')
> filter("A"."TAGNAME"='FLOWDRY' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 245 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='FLOWV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 247 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 249 - filter(NULL IS NOT NULL)
> 250 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 252 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 253 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 254 - access("STATIONNUM"='KLN')
> 258 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='FLOWDRY')
> filter("A"."TAGNAME"='FLOWDRY' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 259 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='OPACV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 261 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 263 - filter(NULL IS NOT NULL)
> 264 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 266 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 267 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 268 - access("STATIONNUM"='KLN')
> 272 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='TEMP')
> filter("A"."TAGNAME"='TEMP' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 273 - access("B"."MILL_AREA"='KLN' AND
> "A"."SAMPLE_DATE"="B"."SAMPLE_DATE" AND "B"."TAGNAME"='TEMPV')
> filter("B"."SAMPLE_DATE">='01-apr-09')
> 275 - filter("EFFECTDATE"=MAX("EFFECTDATE"))
> 277 - filter(NULL IS NOT NULL)
> 278 - access("EMSSTATIONSTATUS"."STATIONNUM"="A"."MILL_AREA")
> 280 - access("EMSSTATIONSTATUS"."STATIONNUM"='KLN')
> 281 - access("STATIONNUM"="A"."MILL_AREA")
> filter("EFFECTDATE"<="A"."SAMPLE_DATE")
> 285 - access("A"."MILL_AREA"='KLN' AND "A"."SAMPLE_DATE">='01-apr-09'
> AND "A"."TAGNAME"='FLOWDRY')
> filter("A"."TAGNAME"='FLOWDRY' AND
> TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09')
> 286 - access("B"."MILL_AREA"='KLN' AND
> "B"."SAMPLE_DATE"=TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"),'fmh
> h') AND "B"."TAGNAME"='OPACV')
> 288 - access("STATIONNUM"='KLN')
>
> 517 rows selected.
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 2137789089
>
>
> ---------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> ---------------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
> | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29
> (0)| 00:00:01 |
> ---------------------------------------------------------------------------------------------
>
>
>
> Statistics
> ----------------------------------------------------------
> 4029 recursive calls
> 48 db block gets
> 3786 consistent gets
> 77 physical reads
> 0 redo size
> 54286 bytes sent via SQL*Net to client
> 894 bytes received via SQL*Net from client
> 36 SQL*Net roundtrips to/from client
> 78 sorts (memory)
> 0 sorts (disk)
> 517 rows processed

What I notice is a different way of handling the implicit date conversions when dates are compared to strings like

WHERE (sample_date >= '01-apr-09') and (TRUNC(sample_date) <='30-apr-09')

which in 9i are treated as

TRUNC("SYS_ALIAS_2"."SAMPLE_DATE")<='30-apr-09')

  and in 11g as

TRUNC(INTERNAL_FUNCTION("A"."SAMPLE_DATE"))<='30-apr-09').

You might check if this causes an index to be ignored.

Try
WHERE (sample_date >= to_date('01-apr-09', 'DD-MON-YY')) and (TRUNC(sample_date) <=to_date('30-apr-09','DD-MON-YY'))

And: a different estimate of rows returned by

 > | 5 | VIEW | VW_CEM_CORR | *10* | 370 | 87 | (*9i*)

 > | 5 | VIEW | VW_CEM_CORR | *2305* | 29965 | 2126 (4)| 00:00:26 | (*11g*)

  I wonder if your statistics are correct, or if you're using the same data.

Shakespeare Received on Wed May 26 2010 - 12:09:56 CDT

Original text of this message