Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Go to solution
Deanna Tran
Frequent Advisor

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

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
10 REPLIES
John Bolene
Honored Contributor
Solution

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

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.
It is always a good day when you are launching rockets! http://tripolioklahoma.org, Mostly Missiles http://mostlymissiles.com
Brian Crabtree
Honored Contributor

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

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
Yogeeraj_1
Honored Contributor

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

 
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Tim D Fulford
Honored Contributor

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

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
Honored Contributor

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

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
Frequent Advisor

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

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

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

The free one would either be sar or using the trial version of glance until it expired.
It is always a good day when you are launching rockets! http://tripolioklahoma.org, Mostly Missiles http://mostlymissiles.com
Deanna Tran
Frequent Advisor

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

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

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Marcin Mirkowski_1
Occasional Contributor

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

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...