plan table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: plan table

  1. #1
    Join Date
    Dec 2001
    Posts
    2
    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.
    FZ

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width