Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Arrow Unanswered: Performance tuning

    Hi,
    Please help me with the following...
    1) qUERY EXECUTION PLAN WITHOUT HINT
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=33)
    1 0 TABLE ACCESS (FULL) OF 'TCY' (Cost=28 Card=1 Bytes=33)
    Statistics
    ----------------------------------------------------------
    0 recursive calls
    11 db block gets
    1158 consistent gets
    635 physical reads
    0 redo size
    19146 bytes sent via SQL*Net to client
    2160 bytes received via SQL*Net from client
    30 SQL*Net roundtrips to/from client
    3 sorts (memory)
    0 sorts (disk)
    425 rows processed
    2).qUERY EXCUTION PLAN WITH /*+ INDEX(TABLE_NAME INDEX1) */
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=389 Card=1 Bytes=33)
    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TCY' (Cost=389 Card=1 By
    tes=33)

    2 1 INDEX (FULL SCAN) OF 'TCY_INDEX' (NON-UNIQUE) (Cost=385
    Card=1)


    Statistics
    ----------------------------------------------------------
    24 recursive calls
    0 db block gets
    868 consistent gets
    246 physical reads
    16884 redo size
    19355 bytes sent via SQL*Net to client
    2160 bytes received via SQL*Net from client
    30 SQL*Net roundtrips to/from client
    3 sorts (memory)
    0 sorts (disk)
    425 rows processed

    Can anyone tell me which one is the best among the above two and
    how it(the best one) is decided?
    Thanks
    Thanks & Regards
    Manikandan

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Manikandan,

    The Oracle optimiser is a clever bit of kit, often (as in your case) it looks like it has gone a bit haywire but no, it's doing it's job well.

    Imagine a table so small it only occupies one data block. An index for it also occupies one data block.

    To do a full table scan requires one data block to be read from disk.

    To do an index scan (either unique, or range scan) requires one data block to be read for the index - all results in the index point to the same data block for the table so it reads that too. So it has now read two blocks.

    So in this case, using the index is slower. A full table scan is quicker in terms of disk i/o *and* less memory intensive. I've seen this behaviour even on primary key indexes up to tables of 50,000 rows where the optimiser correctly determines that a full table scan offers the best performance.

    Looking at your explain plans, it suggests you are either using the RULE based optimiser, or using the COST based but with no statistics. I note your other posts regarding performance, maybe these can point you in the right direction.....

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    HTH
    Bill

  3. #3
    Join Date
    Aug 2002
    Location
    India
    Posts
    60

    Thumbs up

    Hi Bill,
    Thanks a lot for your valuable reply...
    Thanks & Regards
    Manikandan

Posting Permissions

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