查找磁盘的IO问题（使用经典的sql语句进行分析）
下面的一些sql写的不错，大家要走、认真的理解和掌握，总结以后成为自己的工具Set TrimSpool On
Set Line 142
Set Pages 57
Set NewPage 0
Set FeedBack Off
Set Verify Off
Set Term On
TTitle Off
BTitle Off
Clear Breaks
Break On Tablespace_Name
Column TableSpace_Name For A12 Head "Tablespace"
Column Name For A45 Head "File Name"
Column Total For 999,999,990 Head "Total"
Column Phyrds For 999,999,990 Head "Physical|Reads "
Column Phywrts For 999,999,990 Head "Physical| Writes "
Column PhyblkrdFor 999,999,990 Head "Physical |Block Reads"
Column Phyblkwrt For 999,999,990 Head "Physical |Block Writes"
Column Avg_Rd_Time For 90.9999999 Head "Average |Read Time|Per Block"
Column Avg_Wrt_Time For 90.9999999 Head "Average |Write Time|Per Block"
Column Instance New_Value _Instance NoPrint
Column Today New_Value _Date NoPrint
SelectGlobal_Name Instance, To_Char(SysDate, 'FXDay, Month DD, YYYY HH:MI') Today
From Global_Name;
TTitle On
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Data File I/O' Skip 1 -
Center 'Instance Name: ' _Instance Skip 1
select C.TableSpace_Name, B.Name, A.Phyblkrd +A.Phyblkwrt Total,
A.Phyrds, A.Phywrts,A.Phyblkrd, A.Phyblkwrt
From V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
and B.File# = C.File_Id
order byTableSpace_Name, A.File#
/
select object_name, statistic_name, value
from v$segment_statistics
where value > 100000
order by value;
Column TableSpace_Name For A12 Head "Tablespace"
Column TotalFor 9,999,999,990 Head "Total"
Column Phyrds For 9,999,999,990 Head "Physical|Reads "
Column Phywrts For 9,999,999,990 Head "Physical| Writes "
Column Phyblkrd For 9,999,999,990 Head "Physical |Block Reads"
Column PhyblkwrtFor 9,999,999,990 Head "Physical |Block Writes"
Column Avg_Rd_Time For 9,999,990.9999Head "Average|Read Time|Per Block"
Column Avg_Wrt_TimeFor 9,999,990.9999Head "Average |Write Time|Per Block"
Clear Breaks
Break on Disk Skip 1
Compute Sum Of Total On Disk
Compute Sum Of Phyrds On Disk
Compute Sum Of Phywrts On Disk
Compute Sum Of Phyblkrd On Disk
Compute Sum Of Phyblkwrt On Disk
TTitle Left 'Date Run: ' _Date Skip 1-
Center 'Disk I/O' Skip 1 -
Center 'Instance Name: ' _Instance Skip 2
select SubStr(B.Name, 1, 13) Disk, C.TableSpace_Name,
A.Phyblkrd + A.Phyblkwrt Total,
A.Phyrds, A.Phywrts,A.Phyblkrd, A.Phyblkwrt,
((A.ReadTim /Decode(A.Phyrds,0,1,A.Phyblkrd))/100) Avg_Rd_Time,
((A.WriteTim / Decode(A.PhyWrts,0,1,A.PhyblkWrt)) /100) Avg_Wrt_Time
from V$FileStat A, V$DataFile B, Sys.DBA_Data_Files C
where B.File# = A.File#
and B.File# = C.File_Id
order byDisk,C.Tablespace_Name, A.File#
/
Set FeedBackOn
Set Verify On
Set Term On
Ttitle Off
Btitle Off
顶一下
顶一下，相老师老厉害了
页:
[1]