Most of the time disk I/O remains 100% and system goes to damn slow even to open a login page takes time more than two minutes or stucked. Sometime disk3(where all datafiles and redo files are located) remains 100% and system went to slow but sometime disk0 and disk1(where paging is defined) system went stuck.What type of file system you are on?
what i explored there are some query when it runs system takes most I/O of paging disk and system goes to damn slow.FYI CPU utilization of that query would be very less it is approximately only 2%.Please see old threads for Performance Tuning and Query Tuning docs.
I am not expert to tune the query or understand the query so whenever i tell to users that donot run it on office time they tell it is very simple query and i donot have the way to proof him.
I am planing to move some of datafiles to separate disk but how could i know which datafiles actually are taking more I/O so that i can move them?Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]
If possible please answer me how to reduce the I/O, how could i know which datafiles actually are taking more I/O and how to proof that query is culprit?
FYI on d01 Oracle home and all datafiles are located and on d02 are located archives,1 control file and one group of redo files
$lspv hdisk0 00c33aa09b17b577 rootvg active hdisk1 00c33aa0a58db3b8 rootvg active hdisk2 none None hdisk3 00c33aa02bafba9b datavg active hdisk5 00c33aa0a3d4e0c8 datavg active hdisk4 00c33aa046c936bb datavg2 active $lsvg -l rootvg rootvg: LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT hd5 boot 1 2 2 closed/syncd N/A hd6 paging 73 146 2 open/syncd N/A hd8 jfs2log 1 2 2 open/syncd N/A hd4 jfs2 4 8 2 open/syncd / hd2 jfs2 12 24 2 open/syncd /usr hd9var jfs2 8 16 2 open/syncd /var hd3 jfs2 8 16 2 open/syncd /tmp hd1 jfs2 4 8 2 open/syncd /home hd10opt jfs2 1 2 2 open/syncd /opt lg_dumplv sysdump 8 8 1 open/syncd N/A $lsvg -l datavg datavg: LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT loglv00 jfs2log 1 1 1 open/syncd N/A fslv00 jfs2 598 598 2 open/syncd /d01 fslv01 jfs2 80 80 1 open/syncd /d02 fslv02 jfs2 830 830 1 closed/syncd /delr loglv01 jfslog 1 1 1 open/syncd N/A lv00 jfs 1 1 1 open/syncd /audit $lsvg -l datavg2 datavg2: LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT loglv02 jfs2log 1 1 1 open/syncd N/A fslv03 jfs2 300 300 1 open/syncd /d03 $