DB常用性能查询语句.docx

?常用的一些性能查询sql语句--查看表锁select * from sys.v_$sqlarea where disk_reads100--监控事例的等待select event,?????? sum(decode(wait_Time, 0, 0, 1)) Prev,?????? sum(decode(wait_Time, 0, 1, 0)) Curr,?????? count(*) Tot? from v$session_Wait?group by event?order by 4--回滚段的争用情况select name, waits, gets, waits / gets Ratio? from v$rollstat a, v$rollname b?where a.usn = b.usn--查看前台正在发出的SQL语句select user_name, sql_text 　　? from v$open_cursor 　　?where sid in (select sid???????????????? from (select sid, serial#, username, program 　　???????????????????????? from v$session 　　??????????????????????? where status = ACTIVE))--数据表占用空间大小情况select segment_name, tablespace_name, bytes, blocks? from user_segments?where segment_type = TABLE?ORDER BY bytes DESC, blocks DESC--查看表空间碎片大小select tablespace_name,?????? round(sqrt(max(blocks) / sum(blocks)) *???????????? (100 / sqrt(sqrt(count(blocks)))),???????????? 2) FSFI? from dba_free_space?group by tablespace_name?order by 1--查看碎片程度高的表SELECT segment_name table_name, COUNT(*) extents? FROM dba_segments?WHERE owner NOT IN (SYS, SYSTEM)?GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*))???????????????????? FROM dba_segments??????????????????? GROUP BY segment_name);? ?--查看表空间占用磁盘情况select b.file_id 文件id,?????? b.tablespace_name 表空间名,?????? b.bytes / 1024 / 1024 总大小,?????? (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用大小,?????? sum(nvl(a.bytes, 0)) / 1024 / 1024 剩余空间,?????? sum(nvl(a.bytes, 0)) / (b.bytes) * 100 剩余百分比? from dba_free_space a, dba_data_files b?where a.file_id = b.file_id?group by b.tablespace_name, b.file_id, b.bytes?order by b.file_id;--查看session使用回滚段SELECT r.name 回滚段名,?????? s.sid,?????? s.serial#,?????? s.username 用户名,?????? t.status,?????? t.cr_get,?????? t.phy_io,?????? t.used_ublk,?????? t.noundo,?????? substr(s.program, 1, 78) 操作程序? FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r?WHERE t.addr = s.taddr?? and t.xidusn = r.usn?ORDER BY t.cr_get, t.phy_io--查看SGA区剩余可用内存select name,?????? 　　　　　　sgasize / 1024 / 1024