Oracle数据库维护常用SQL语句集合（3）-计算机等级-树人网

　性能相关内容

　　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';

第页

责任编辑:qwer19871212

“蓝焰杯”第二届河南教育十大新闻人物火热评选中！！