This is hopefully a simple question. We have a large DB deployment that we need to scale up over the next year. For scaling we have been relying on our vendor, but recent developments have caused us to doubt their ability to provide good information about the DB. So, what I'd like to do is go into oracle and see what actual IO numbers look like. Is there any easy way to do this?
There is no short answer to this question, the ways to monitor the behavior of an Oracle database depend on the tools available, your database edition, your database version, do you use ASM or a filesytem, etc.
A general approach that works for any database edition and version (that is from Oracle 8 on) is to install statspack (see http://www.orafaq.com/wiki/Statspack or the documentation in $ORACLE_HOME/rdbms/admin) and take snapshots. The snapshots will be taken every hour by default so the data is aggregated over this period of time.
This will give you an overview of the behavior of the database (e.g. amount of physical reads).
This is just a starting point as you may want to check, if your SAN partitions are correctly aligned, do your FS block size match the DB block size (8k by default) and so on.
You can run AWR reports to see statistics about database performance. Log in with SQLPlus and run:
Choose as small a window as possible during a period of heavy load for the most pertinent results. Howeer, these can be difficult for a newbie to interpret, so I'd recommend running an ADDM report also. This will run automated diagnostics and give recommendations on how to improve performance:
If I/O is a major bottleneck this will be highlighted by the ADDM. But you should try to run as many of the tuning recommendations as possible first before assuming that the hardware is limited. A well-tuned database will go to disk as little as possible, and storage arrays can perform significantly better when their load profiles are reduced. If disk resources really are a problem for your database, you should see suggestions to "improve the throughput of the I/O subsystem" very high up the list of recommendations.
Hard to say. But generally there is "no" way. Simply because Oracle knows only about "logical reads" - read request to OS. But some of them may need no disc IO at all - as data my be found on OS's buffer cache. Also Oracle uses different block size then kernel.
- On HP-UX when you use glance you can see both logical reads and physical reads per device. (assuming that disk device is used for database data only)
- On linux detailed disc IOPS accounting must be turned on
- When using access path O_DIRECT or when using ASM instance Oracle bypasses kernel's buffer cache. Then you can assume then every read reported by STATSPACK/AWR stats is really a physical read.
- For statspack I recommend Oracle Statspack Survival Guide
I'm not entirely clear on the specific data you are looking for, so I might get this wrong, but if I do understand your question at all, it sounds like it
relates to the underlying infrastructure more than the database.
Oracle dbms have grown into a well tooled and exceptionally well instrumented system. There are any number of ways for getting to and working with just about any metric you can dream up. However, if all you really want to know about is accurately observed somewhere external to the DBMS, like the system calls for I/O operations that Oracle makes, or the actual I/O requests hitting your storage array,
you may instead ask the platform, operating system, virtual infrastructure or storage subsystem.
If you want to know at what speed a car is going through your neighbourhood, you can opt to measure it as it passes, rather than track down the vehicle and interrogate it's driver.
Keep in mind that even if the second method gets an answer, the accuracy will be at at the mercy of that driver's equipment, abilities and personality.
Of course, if you find that he was speeding and want to know what he meant by it, then it's time to get to know the driver.
PS. To see the dbms' subjective view if the world, if only so you can compare with other available metrics, you can use AWS snapshots and look up the selection of counters that relates to physical I/O operations.