Disk Reads are high!!

[badrinathn]

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

[Queyon]

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.

[gpsingh]

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

[jmodic]

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]

[chrisrlong]

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