I am rather confused about plan table results...
I have a datawarehouse tool (Business Obj) running on Oracle 8
My datawarehouse tool produces a complicated query which runs slow.
What I need to understand is: why is this query so slow?
Possible causes are:
-the datawarehouse metadata I produce are bad
-server-side problems (server is slow etc.)
My idea is: I produce the plan table for the query:
if the execution plan is bad I *must* change metadata,
if the execution plan is good the problems are on the server side.
(1) first question: is it correct what I wrote above?
then, since optimizer is rule-based I have to add an HINT to the select and the HINT must be /*+ ALL_ROWS */ which forces the system to use the cost based optimizer and searching the best throughput.
(2) second question: is the HINT correct?
exec plan that comes from plan table is (via a select ... connect):
0 SELECT STATEMENT Cost=16
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 TABLE ACCESS BY ROWID
7 6 INDEX RANGE SCAN
8 5 TABLE ACCESS BY ROWID
9 8 INDEX RANGE SCAN
10 4 TABLE ACCESS BY ROWID
11 10 INDEX UNIQUE SCAN
12 3 TABLE ACCESS FULL V78DCONTI
13 2 TABLE ACCESS BY ROWID
14 13 INDEX UNIQUE SCAN
15 1 TABLE ACCESS BY ROWID
16 15 INDEX UNIQUE SCAN
only in row id = 12 I have a full scan but that table is very small (20 rows) so the system MUST choose full scan (in this case is faster than using an index)
(3) third question: due to what is written above,
exec plan seems to be ok. Is it true?
cheers and Thank in advanced.
Click Here to Expand Forum to Full Width