How to see what is really at the file level

Ideally all the data we want to work with are in memory and noI/O is needed. In reality, you usually can't count on this being the case. So, our goal is to try to minimize the disk physical movement for any given data request. For example, if the index and the data are on the same disk, there is movement needed for the index and then the same disk must move for the data. If the next read wants the next record, then we must move back for the index and back again for the data. We have made the read for the data and the read for the index get in each other's way.

All objects that might be used in the same transaction should be on different mount points.

• System tablespace

• ƒ Data tablespace

• ƒ Index tablespace

• ƒ Rollback segments

• ƒ Archive logs

• ƒ Temporary tablespace

These six mount points would give us our basic good system setup.

To check the current blocks in memory

SELECT   b.file_name, a.file#, a.cnt
    FROM   (  SELECT   file#, COUNT (1) cnt
                FROM   v$bh
            GROUP BY   file#) a, dba_data_files b
   WHERE   a.file# = b.file_id
ORDER BY   cnt DESC

The v$filestat tell us the time spent performing reads and writes, in hundredths of a second, if timed_statistics is set to true. When I look at a system that has been up for a longer time, I see that the average time to write a block of data is about ten times longer than the average time to read a block.

Take a look at your  v$filestat and v$tempstat views. Mine have shown me that even though Oracle works in memory as much as possible, I still need to be very aware of I/O-level contention. I also see that wherever I can, I will try to minimize the number of write actions performed.

Watch yours for a while to see what is really going on.