Is there way to determine the I/O stats on a table and/or index? I have the I/O stats by file but would like to narrow it down even more.
It all depends on your Oracle version. If you are using pre-Oracle 9i, then the only way to get this information is to place the index or table in a separate tablespace and monitor V$FILESTAT for that tablespace. This is not an ideal method to be sure.
If you are using Oracle 9i, then look at the new V$SEGMENT_STATISTICS view for the segment (table or index) in question. There are lots of statistics about the I/O of that segment in this view.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.