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.
This is a much more difficult question than it first appears. I'm not going to attempt to address the entire issue right now. However....
Having RULE-based optimization on for a Data Warehouse would be your biggest problem. Oracle has some wonderful functionality built specifically for star schemas, but these are not supported under RULE. Unless the manufacturer of this product specifically requires the RULE-based optimizer, you should absolutely switch, IMHO.