Home > Oracle 10g datafile I/O statistics
Chapter Download:
EMAIL THIS LICENSING & REPRINTS

Oracle 10g datafile I/O statistics

13 Jun 2005 | Rampant TechPress

Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   

The Oracle10g database significantly improves the I/O subsystem, but it is still important to constantly monitor the I/O workload on the database. This excerpt, taken from the book Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts by Mike Ault, Harry Conway and Don Burleson, offers a few tips and scripts to help you do so. Click here to view the entire chapter.

Oracle 10g Datafile I/O Statistics

The Oracle10g database significantly improves the I/O subsystem through the addition of such features as the Automatic Storage Management (ASM) facility. However, it is important to constantly monitor the I/O workload on the database because in a well tuned application, I/O remains a bound factor that can cause significant wait times in data access. I/O layout design is a complex process and includes the consideration of the following points:

  • Sufficient disk capacity for business needs.
  • Appropriate data protection level using RAID levels, hardware, LVM, etc.
  • Sufficient I/O throughput that does not exceed disk I/O bandwidth.
The AWR has several views that can be used to isolate datafile I/O related statistics as well as tablespace space usage statistics.

The dba_hist_filestatxs and dba_hist_tempstatxs views display information about I/O activity for data and temporary database files, respectively:

SQL> desc DBA_HIST_FILESTATXS

Name              Null?    Type
----------------- -------- -------------
SNAP_ID                    NUMBER
DBID                       NUMBER
INSTANCE_NUMBER            NUMBER
FILE#                      NUMBER
CREATION_CHANGE#           NUMBER
FILENAME                   VARCHAR2(513)
TS#                        NUMBER
TSNAME                     VARCHAR2(30)
BLOCK_SIZE                 NUMBER
PHYRDS                     NUMBER
PHYWRTS                    NUMBER
SINGLEBLKRDS               NUMBER
READTIM                    NUMBER
WRITETIM                   NUMBER
SINGLEBLKRDTIM             NUMBER
PHYBLKRD                   NUMBER
PHYBLKWRT                  NUMBER
WAIT_COUNT                 NUMBER
TIME                       NUMBER
The view, dba_hist_tempstatxs, has the identical structure. Both views can be queried to monitor overall database I/O activity for a particular snapshot interval grouped by tablespaces using the query db_tbsp_io_10g.sql.

Click here to view the script and the entire chapter.



Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us   


RELATED CONTENT
Oracle internals
LAST_OPER_TYPE column in v$sga_dynamic_components
Inside the Oracle 11g SQL Performance Advisor, part 1
Inside the Oracle 11g SQL Performance Advisor, part 2
How to read a STATSPACK report
Tuning the Oracle database with initialization parameters
Cannot create services in Oracle 9i
Move tables from system tablespace to users tablespace
Move Oracle home to another partition
Limit on CLOB datatype in Oracle 10g
Logging into Oracle utilities when database is down
Oracle internals Research

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
autonomous transaction  (SearchOracle.com)
delimiter  (SearchOracle.com)
extent  (SearchOracle.com)
flexfield  (SearchOracle.com)
responsibility  (SearchOracle.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 2003 - 2008, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts