High I/O

Printable View

10-22-2003, 10:29 AM
vbadba

High I/O

Hi All:

We have 4 similar databases (8.1.7.4) on 4 different Sun Enterprise 3500 with Solaris 8. All these servers have same number of disks and all the data files are spread across the multiple disks. We have same number of users on all 4 databases and I can not say the database access is same but almost.

All the servers are on the same patch level OS wise. Same patch level Database wise. We used to have same amount of I/O on all the databases and we started having high I/O on one devise (A1000) in One server. On this devise we do not have other files except oracle datafiles. For sure we did not install any patches on the server. Verified all the objects, All the objects are valid and were analyzed.

Is there a way that I can find which process is causing this high I/O. We do not have anything else other than the OS and the Oracle Database on these servers.

Few other questions...

1) What can cause High I/O on devise,....Can a Corrupted Datafile?..
I have verified the datafiles with DBV, None of the datafiles have any problem.

2) Is there a way to find which process is causing this high I/O through a UNIX command?

3) If I rebuild all Indexes Will that help?

Thanks in Advance,
vbadba.
10-22-2003, 10:59 AM
marist89

Look at v$filestat to find out which files have the most I/O. Then figure out which objects are in that datafile. From there, you should have a clue as to WHAT and WHO and you can analyze this person's session to find out WHY.
10-22-2003, 02:37 PM
vbadba

Hi Jeff:

I have only one user for my database and I do know Which data file is having high I/O but How can I find which process is causing that High I/O.

Thanks,
vbadba
10-22-2003, 02:43 PM
marist89

Quote:

Originally posted by marist89
Look at v$filestat to find out which files have the most I/O. Then figure out which objects are in that datafile. From there, you should have a clue as to WHAT and WHO and you can analyze this person's session to find out WHY.
10-22-2003, 02:52 PM
vbadba

Hi Jeff:

For my database I have only one user but There are about 500 users to the my application using the same database ligoin.

Thanks,
vbadba
10-22-2003, 02:56 PM
marist89

I'm happy for you. The WHO is not as important as WHAT and WHERE. Find out WHAT file is being hit the hardest that is assigned to your mount point and then find out WHAT tables are assigned to that file.

BTW, have you verified that the physical device is OK? Like maybe a bad disk in a RAID 5 set?
10-22-2003, 03:32 PM
vbadba

Hi Jeff:

Initially Sun thought we have a Bad controller (They gave me Guds script which collects Performance statistics with the result they concluded it is a bad controller) and they replaced the controller twice after our problem. Now Sun is saying it is not a Controller problem anymore. The devise having High I/O is Sun A1000 and is RAID 5. My application is read intensive. And All the disks are good.

I have 11 tables and related indexes on this devise, Is there a way that I can find Which object is causing this high I/O? and Can a corrupted object cause high I/O

Thanks for your time.

Thanks,
vbadba
10-22-2003, 03:58 PM
marist89

Quote:

Originally posted by marist89
Look at v$filestat to find out which files have the most I/O. Then figure out which objects are in that datafile. From there, you should have a clue as to WHAT and WHO and you can analyze this person's session to find out WHY.
10-22-2003, 04:23 PM
vbadba

Hi Jeff:

Could you please give some more info on that please.

Thanks,
vbadba
10-22-2003, 04:33 PM
marist89

http://download-west.oracle.com/docs...htm#REFRN30022
10-22-2003, 04:45 PM
vbadba

Hi Jeff:

I think I am missing something, I know which file is having high I/O. How can I find Which object access caused that High I/O OR Which process caused that High I/O? With the V$filestat I am unable to get that information.

Thanks,
vbadba
10-22-2003, 09:24 PM
vinodkmg

Hi,

If you have found the file,check the tablespace associated to it.Once tablespace is indetified,check the objects on that tablespace from dba_segments.

Find if tables involved are doing full tables scans.
10-23-2003, 10:04 AM
mike9

You may have a look at V$SEGMENT_STATISTICS like:

select OWNER, OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, OBJECT_TYPE, STATISTIC_NAME, VALUE
from V$SEGMENT_STATISTICS s1
where s1.owner != 'SYS' and s1.STATISTIC# = 3
order by value desc
10-23-2003, 10:47 AM
vbadba

Quote:

Originally posted by mike9
You may have a look at V$SEGMENT_STATISTICS like:

select OWNER, OBJECT_NAME, SUBOBJECT_NAME, TABLESPACE_NAME, OBJECT_TYPE, STATISTIC_NAME, VALUE
from V$SEGMENT_STATISTICS s1
where s1.owner != 'SYS' and s1.STATISTIC# = 3
order by value desc

Hi Mike:

I could not find V$SEGMENT_STATISTICS. Is this available in 8i?

Thanks,
vbadba