【赛迪网－IT技术报道】性能相关内容

1、捕捉运行很久的SQL

column username format a12
column opname format a16
column progress format a8
SELECT Username, Sid, Opname,
Round(Sofar * 100 / Totalwork, 0)

'%' AS Progress, Time_Remaining,
Sql_Text
FROM V$session_Longops, V$sql
WHERE Time_Remaining <> 0
AND Sql_Address = Address
AND Sql_Hash_Value = Hash_Value;

2、求DISK READ较多的SQL

SELECT St.Sql_Text
FROM V$sql s, V$sqltext St
WHERE s.Address = St.Address
AND s.Hash_Value = St.Hash_Value
AND s.Disk_Reads > 300;

3、求DISK SORT严重的SQL

SELECT Sess.Username, SQL.Sql_Text, Sort1.Blocks
FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1
WHERE Sess.Serial# = Sort1.Session_Num
AND Sort1.Sqladdr = SQL.Address
AND Sort1.Sqlhash = SQL.Hash_Value
AND Sort1.Blocks > 200;

4、监控索引是否使用

alter index &index_name monitoring usage;
alter index &index_name nomonitoring usage;
select * from v$object_usage where index_name = &index_name;

5、求数据文件的I/O分布

SELECT Df.NAME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim,
Writetim
FROM V$filestat Fs, V$dbfile Df
WHERE Fs.File# = Df.File#
ORDER BY Df.NAME;

6、查看还没提交的事务

select * from v$locked_object;
select * from v$transaction;

7、回滚段查看

SELECT Rownum, Sys.Dba_Rollback_Segs.Segment_Name NAME,
V$rollstat.Extents Extents, V$rollstat.Rssize Size_In_Bytes,
V$rollstat.Xacts Xacts, V$rollstat.Gets Gets, V$rollstat.Waits Waits,
V$rollstat.Writes Writes, Sys.Dba_Rollback_Segs.Status Status
FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname
WHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_Name
AND V$rollstat.Usn(+) = V$rollname.Usn
ORDER BY Rownum

8、查看系统请求情况

SELECT Decode(NAME, 'summed dirty write queue length', VALUE) /
Decode(NAME, 'write requests', VALUE) "Write Request Length"
FROM V$sysstat
WHERE NAME IN ('summed dirty queue length', 'write requests')
AND VALUE > 0;