StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You should probably start with <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9010.htm" rel="nofollow">EXPLAIN PLAN</a>.</p> <blockquote> <p>Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. You can also issue the EXPLAIN PLAN statement as part of the SQL trace facility.</p> <p>This statement also determines the cost of executing the statement. If any domain indexes are defined on the table, then user-defined CPU and I/O costs will also be inserted.</p> </blockquote> <p>Then edit your question, and post the SQL statement and the output of EXPLAIN PLAN.</p> <p><strong>Later . . .</strong></p> <p>I'm not going to be much help to you on that query. 269 lines, at least 29 SELECTs, parallel queries, remote databases, outer joins (old style), and so on.</p> <p>The best advice I can give you is </p> <ul> <li>get more information from EXPLAIN PLAN, and</li> <li>simplify the problem.</li> </ul> <p>The <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i18300" rel="nofollow">plan table</a> has more columns than are commonly posted. The columns COST, CARDINALITY, BYTES, and TIME might be useful in prioritizing your tuning effort.</p> <p>You've got 10 full table scans in that query. ("TABLE ACCESS FULL" in the query plan.) That's usually a bad sign; full table scans often take a relatively long time to run. It's not <em>always</em> a bad sign. A full scan of a tiny table might be faster than an index scan.</p> <p>Start by getting EXPLAIN PLAN output for each of the 29 SELECT statements in your query. If any of them show a full table scan, you can probably improve their performance with <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#i2062403" rel="nofollow">suitable indexes</a>. (Oracle supports many different kinds of indexes. Don't overlook opportunities for multi-column indexes.) In any case, EXPLAIN PLAN output will help you identify the slowest of the 29 SELECTs.</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