StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Impossible to answer without knowing the table structure, constraints, indexes, data volume, resultset size, network speed, level of concurrency, execution plans etcetera.</p> <p>Some things I would investigate:</p> <p>If the table is partitioned, does statistics exist for the partition the query is hitting? A common problem is that statistics are gathered on an empty partition before data has been inserted. Then when you query it (before the statistics are refreshed) Oracle chooses an index scan, when in fact it should use an FTS on that partition.</p> <p>Also related to statistics: Make sure that </p> <pre><code>WHERE DT >=TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'YYYYMMDD')) AND DT < TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM'),'MM'),'YYYYMMDD')) </code></pre> <p>generates the same execution plan as:</p> <pre><code>WHERE DT >= 20101201 AND DT < 20110101 </code></pre> <p><strong>Updated</strong> What version of Oracle are you on? The reason I'm asking is that on Oracle 10g and later, there is another implementation of group by that should have been selected in this case (hashing rather than sorting). It looks like you are basically sorting the 342 million rows returned from the date filter (14 gigabytes). Do you have the RAM to back that up? Otherwise you will be doing a multipass sort, spilling to disk. This is likely what is happening. </p> <p>According to the plan, about 790 rows will be returned. Is that in the right ballpark? If so, you can rule out network issues :)</p> <p>Also, I'm not entirely familiar with the format on that plan. Is the table sub partitioned? Otherwise I don't get the partition #11 reference.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload