Disk Reads are high!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Disk Reads are high!!

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi
    For one of my sql's, the disk reads are very high.
    For example

    Disk Read: 382470535

    Sql Stm:
    select count(*) from ecare_message_queue where message_status_code='X' and company='H' and to_number(sysdate-nvl(checkou
    t_date,(sysdate-1)))*24*60 >20



    By reloading the table, can I reduce the disk reads and there by make the retrieval faster
    Badrinath

  2. #2

    Angry Disk reads

    Try these:

    1. If you don't have index, create it and make sure it does use the index you created.

    2. Try to use "COUNT(ROWID)" instead of "COUNT(*)".

    3. If the table is very huge, try to spread it acrossing multiple disks.

    4. If it's not good enough, try parallel query.
    Queyon Zeng

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    Disk reads high indicate no usage of index properly,
    and less number of db_block_buffers.
    Check buffer hit ratio.Check explain plan .

    Also since you are using sysdate, this will cause for each row , to access dual table to get the sysdate.
    I would prefer u write a pl/sql , get the sysdate into variable once and use that variable in your query.

    Take Care
    GP

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by gpsingh
    Also since you are using sysdate, this will cause for each row , to access dual table to get the sysdate.
    I would prefer u write a pl/sql , get the sysdate into variable once and use that variable in your query.
    This part is wrong. Sysdate in SQL will be evaluated only *once for the entire query*, not for each row. This is different as in PL/SQL expressions, where each sysdate in each expression is evaluated separately, so in PL/SQL it can realy be usefull to load sysdate in a variable and then use that variable through the entire block.

    P.S. What realy seems to be wrong in the original question is the use of TO_NUMBER() in the following:
    to_number(sysdate-nvl(checkout_date,(sysdate-1)))*24*60 >20
    The subtraction of two dates allways evaluates in the NUMBER datatype, so the use of TO_NUMBER is not needed.

    [Edited by jmodic on 03-30-2001 at 02:32 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Also,
    --Try to use "COUNT(ROWID)" instead of "COUNT(*)". --

    ...is likewise incorrect. There is no difference between the two. There may have been at some point in Oracle's past, but not any more.

    However, the index, striping and partitioning points are all valid. The striping and partitioning help mainly with the time the query takes. To minimize the Physical reads, you need to examine your cache size and what you are caching, etc. More important is the Logical Read count (Buffer Gets). To help this, you really need to look at the proper indexing.

    - Chris


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