Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009

    Unhappy Unanswered: Strugling to Optimise SQL

    Dear all,

    I am optimising a SQL first time, can anyone help me how should I start
    and what steps I need to take.
    Any PDF or doc or web content would be appreciated.

    I will be using Autotrace to optimise SQL.
    I will not be able to post SQL, as it is ristricted.

    This is what I am getting from Autotrace.
    129 recursive calls
    0 db block gets
    18 consistent gets
    0 physical reads
    0 redo size
    2086 bytes sent via SQL*Net to client
    377 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    Check this for some more information.

  3. #3
    Join Date
    Aug 2009

    Strugling to Optimise SQL

    Thanks Littelfoot,

    Now, I need help to read result of auttrace.

    Can you help in this?

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=65124 Card=1630575 Bytes=598421025)
    1 0 VIEW OF 'View1' (VIEW) (Cost=65124 Card=1630575 Bytes=598421025)
    2 1 HASH (UNIQUE) (Cost=65124 Card=1630575 Bytes=265783725)
    3 2 HASH JOIN (RIGHT OUTER) (Cost=5862 Card=1630575 Bytes= 265783725)
    4 3 VIEW OF 'View2' (VIEW) (Cost= 4842 Card=21273 Bytes=489279)
    5 4 HASH JOIN (Cost=4842 Card=21273 Bytes=999831)
    6 5 TABLE ACCESS (FULL) OF 'Table1' (TABLE ) (Cost=2 Card=24 Bytes=168)
    7 5 HASH JOIN (Cost=4839 Card=23046 Bytes=921840)
    8 7 TABLE ACCESS (FULL) OF 'Table2' (TABLE) (Cost=1502 Card=32435 Bytes=454090)
    9 7 VIEW OF 'View3' (VIEW) (Cost=3334 Card=23046 Bytes=599196)
    10 9 SORT (UNIQUE) (Cost=3334 Card=23046 Bytes=530058)
    11 10 UNION-ALL
    12 11 HASH (GROUP BY) (Cost=1508 Card=868 Bytes=19964)
    13 12 NESTED LOOPS (Cost=1505 Card=2618 Bytes=60214)
    14 13 TABLE ACCESS (BY INDEX ROWID) OF 'Table1' (TABLE) (Cost=1 Card=1 Bytes=7)
    15 14 INDEX (UNIQUE SCAN) OF 'indx1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    16 13 TABLE ACCESS (FULL) OF 'Table2' (TABLE) (Cost=1504 Card=2618 Bytes=41888)
    17 11 HASH (GROUP BY) (Cost=1826 Card=22178 By tes=510094)
    18 17 HASH JOIN (Cost=1509 Card=22178 Bytes= 510094)
    19 18 TABLE ACCESS (FULL) OF 'Table2' (TABLE) (Cost=2 Card=24 Bytes=168)
    20 18 TABLE ACCESS (FULL) OF 'Table2' (TABLE) (Cost=1506 Card=24026 Bytes=384416)
    21 3 HASH JOIN (Cost=966 Card=7665 Bytes=1073100)
    22 21 HASH JOIN (Cost=417 Card=7665 Bytes=735840)
    23 22 HASH JOIN (Cost=217 Card=7665 Bytes=544215)
    24 23 TABLE ACCESS (FULL) OF 'Table3' (TABLE) (Cost=27 Card=7665 Bytes=375585)
    25 23 TABLE ACCESS (FULL) OF 'Table4' (TABLE) (Cost=187 Card=54871 Bytes=1207162)
    26 22 TABLE ACCESS (FULL) OF 'Table5' (TABLE) (Cost=197 Card=54870 Bytes=1371750)
    27 21 TABLE ACCESS (FULL) OF 'Table6' (TABLE) Cost=546 Card=85352 Bytes=3755488)

    264202 recursive calls
    0 db block gets
    7035261 consistent gets
    19741 physical reads
    0 redo size
    1322303 bytes sent via SQL*Net to client
    6133 bytes received via SQL*Net from client
    513 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    7678 rows processed
    Last edited by ssutar; 03-26-10 at 08:33.

  4. #4
    Join Date
    Dec 2003
    For every table listed with a FULL SCAN, make sure that the columns in BOTH tables used to define the table relationships are indexed.

    Also make sure that your table statistics are up-to-date.


  5. #5
    Join Date
    Mar 2010
    Vienna, Austria

    I guess, you don't really expect a serious answer without posting your select statement and the table- and index structures.

    But - according to the posted execution plan - with 9 full table scans on 6 tables you will be dead as a rock in every real environment with real data in it ...
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

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