Query doing lot of disk reads.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Query doing lot of disk reads.

  1. #1
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245

    Query doing lot of disk reads.

    Hi,
    In one of our application.One update query is doing lot of disk reads .It is using index but still it is slow.

    Query:-

    UPDATE REBOOKER SET REBOOK_CO='Y' WHERE RBK_ID=:B1

    Explain plan is this:-
    Rows Execution Plan
    ------- ---------------------------------------------------
    0 UPDATE STATEMENT GOAL: CHOOSE
    0 UPDATE OF 'REBOOKER'
    0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'I_RBK_ID' (NON-UNIQUE)

    Query is doing around 30831 disk reads for 123712 rows.
    This columns contains lot of repeated values. Is non-unique index ok.

    Anything else i need to do.

    Thanks in advance :-)

    Rgds
    Parag

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How many rows are there in that table, and can you read the clustering factor of the I_RBK_ID index from user_indexes? I'm wondering whether you might be better with a full table scan here.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    table contains around 1444422 records. So i dont think full scan will help. And yes clustering factor is "1240861"

    Rgds
    Parag

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You're reading about one tenth of the table then -- I'd try hinting the query to use a full scan and see how that performs
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Dave,
    Thanks for clue I read all about clustering factor. Hmmmm Full table scan i dont think is good idea. Will reducing clustering factor help. I read 2-3 ways to do it like export import. Creating table_bkp as select * from original. then drop original and again create original. Like that will that help.

    I read this article..
    http://www.dizwell.com/html/clustering_factor.html

    Rgds
    Parag

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    why dont you try the full table scan? you are updating 10% of the records - prime target for a FTS

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