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

Thread: ful table scans

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Folks..

    I traced a session for a timely transaction and found about 30 of these :

    SELECT ROWID
    FROM
    NominalBudgetNames


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 2 0.04 0.15 2 0 2 0
    Execute 2 0.00 0.00 0 0 0 0
    Fetch 2 0.01 0.02 2 2 8 2
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 6 0.05 0.17 4 2 10 2

    Misses in library cache during parse: 2
    Optimizer goal: CHOOSE
    Parsing user id: 25 (MAGIC8)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    1 TABLE ACCESS FULL NOMINALBUDGETNAMES


    Rows Execution Plan
    ------- ---------------------------------------------------
    0 SELECT STATEMENT GOAL: CHOOSE
    1 TABLE ACCESS (FULL) OF 'NOMINALBUDGETNAMES'

    ********************************************************************************

    You will notice that although the table is accessed via a full table scan, the cost of the operation is mininmal. Is it safe to say that this type of full table scan can be ignored as a peformance issue.

    The queries which were costly were those which had many buffer/disk blocks queried but few rows fetched.


    Thanks

    Suresh
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    This is normal way for Oracle optimazer, when
    WHERE construction doesn't exists.

    If this table has PK, then you can try statment

    select PK_FIELD, ROWID from ....;

    May be optimazer change execution plan to

    ... FAST FULL SCAN ON INDEX(PK_INDEX)




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