How to I monitor the I/O rate on an oracle database

[Deanna Tran]

I used iostat to monitor the I/O rate that hit the disks. I 'd like to be able to have the database at the best performance. I have read the documentation about OFA, but that is what I currently I have. thanks for your help

[John Bolene · Accepted solution]

To get the best performance means that you need to minimize the bottlenecks.

If the bottlenecks are in I/O, then you can stripe the disk areas. This works if multiple users are hitting the database at the same time. It does not help much if there is only one user at a time in the database.

The performance stats given by HPUX are by disk and logical volume. You can't get stats on each file individually unless there is only one file on each logical volume.

After you fix I/O, you may have memory or CPU bottlenecks.

If you are into pretty pictures that are much easier for a human to understand, Datametrics makes a product called Siteline.

While there are pretty pictures in glance and perfview, I find it much easier to customize and view the data in Sightline.

[Brian Crabtree]

Also, sourcing active objects would help as well. You can put your most active objects in different tablespaces, and separate the tablespaces across the disks. Using RAID 0+1 is also good, and will help with write performance. For non-updated data (read-only or low changes), you can put the tablespaces on a RAID 5 configuration. You should try to keep the RBS, TEMP, and redo logs on RAID 0+1 if possible, however it is not a requirement. If you can help it, dont use logical mirroring, but physical mirroring, and make sure that your arrays are on different I/O paths to keep contention on the controller down.

Brian

[Tim D Fulford]

The above seems to analyse the IO from within Oracle. However the effect on the disk subsystem is unknown as Oracle has buffers and if you are using cooked files so does the OS.

My suggestion is utilising MeasureWare to monitor the disks. Version C.2.40 shows individual disk IO/s and % utilisation and disk queues. More recent versions of MeasureWare also show kB/s. and service times.

I use:
DATA TYPE DISK
DATE
TIME
BYDSK_DEVNAME
BYDSK_PHYS_READ_RATE
BYDSK_PHYS_WRITE_RATE
BYDSK_PHYS_IO_RATE
BYDSK_SYSTEM_IO_RATE
BYDSK_UTIL
BYDSK_REQUEST_QUEUE

I also plot BYDSK_PHYS_IO_RATE and BYDSK_UTIL to show how well each disk is performing (this is a rough estimate of time to perform each IO or service time).

As far as getting the best out of you database see the SAME paper (Stripe & Mirror Eveything) http://forums.itrc.hp.com/cm/components/FileAttachment/0,,0x4a8a8cc5e03fd6118fff0090279cd0f9,00.pdf

Tim

[T G Manikandan]

Hello,
YOu can install Spotlight for Oracle on your windows machine and monitor your database.
It has some good hints to tune the database.

check the site

questsoftware.com
for some nice products

Thanks

[Deanna Tran]

Hi all,
Of all the products that you have mentioned, which one that I can use for a free?

[John Bolene]

The free one would either be sar or using the trial version of glance until it expired.

[Deanna Tran]

hi john,
can you show me the website for those?

[Yogeeraj_1]

hello

Glance is a separate product from the HP-ux O/S install. The free trial version is available in the CD set which comes along with the O/S. You can install it using swinstall.
To use it beyond 60 days, you would need to purchase the license.

Should be available on your installation cds (application cd 2).

i don't think it is available for download.
However, you may request an evaluation CD from HP.

hope this helps
Best Regards
Yogeeraj

[Marcin Mirkowski_1]

Hi,

As I known, You can speed Oracle database by using raw partitions, async I/O driver in kernel, one parameter from OnlineJFS for a mounting filesystem with Oracle datafiles. If You have any questions, please write, I can write a bit ore about it, if you would like...