wait time for db file scattered reads
716430Aug 9 2009 — edited Aug 9 2009" Your average wait time for db file scattered read is 18 milliseconds, which is very slow. The scattered read events occur when Oracle reads multiple blocks of a table or index (a full table scan of fast full index scan). Full-table scans may indicate missing indexes. Check for missing indexes and verify that you have optimized your db_file_multiblock_read_count. If these steps fail to reduce your disk wait times, moving some of your datafiles to solid state disk will reduce the amount of time spent waiting for scattered disk reads.
1. query to out tables with full table scan? fast full index scan?
2. query to find out sql/pl sql on the full table scan tables?
3. How to find out what additional table indexes could be desirable?
4. How to simulate and find out if index addition will be beneficial?
5. what are considerations and rules of thumb for table.index block size optmimzations?
6. what are considerations and rules for mutliblock read after table/index block optimization?