 |
|
|
|
性能相关内容
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; |
9、计算data buffer 命中率
| SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads", Round(100 * ((a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE)) "BUFFER HIT RATIO" FROM V$sysstat a, V$sysstat b, V$sysstat c WHERE a.Statistic# = 40 AND b.Statistic# = 41 AND c.Statistic# = 42; SELECT NAME, (1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * 100 h_Ratio FROM V$buffer_Pool_Statistics; |
10、查看内存使用情况
| SELECT Least(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Used, MAX(b.VALUE) / (1024 * 1024) Shared_Pool_Size, Greatest(MAX(b.VALUE) / (1024 * 1024), SUM(a.Bytes) / (1024 * 1024)) - (SUM(a.Bytes) / (1024 * 1024)) Shared_Pool_Avail, ((SUM(a.Bytes) / (1024 * 1024)) / (MAX(b.VALUE) / (1024 * 1024))) * 100 Avail_Pool_Pct FROM V$sgastat a, V$parameter b WHERE (a.Pool = 'shared pool' AND a.NAME NOT IN ('free memory')) AND b.NAME = 'shared_pool_size'; |
相关文章 推荐文章
2008/7/7
