DB常用性能查询语句.docx

1、DB常用性能查询语句常用的一些性能查询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_Waitgroup by eventorder by 4-回滚段的争用情况select name, waits, gets, waits / gets Ratio from v$rollstat a

2、, v$rollname bwhere 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_segmentswhere segment_typ

3、e = TABLEORDER BY bytes DESC, blocks DESC-查看表空间碎片大小select tablespace_name, round(sqrt(max(blocks) / sum(blocks) * (100 / sqrt(sqrt(count(blocks), 2) FSFI from dba_free_spacegroup by tablespace_nameorder by 1-查看碎片程度高的表SELECT segment_name table_name, COUNT(*) extents FROM dba_segmentsWHERE owner NOT I

4、N (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(n

5、vl(a.bytes, 0) / (b.bytes) * 100 剩余百分比 from dba_free_space a, dba_data_files bwhere a.file_id = b.file_idgroup by b.tablespace_name, b.file_id, b.bytesorder 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

6、.program, 1, 78) 操作程序 FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname rWHERE t.addr = s.taddr and t.xidusn = r.usnORDER BY t.cr_get, t.phy_io-查看SGA区剩余可用内存select name, sgasize / 1024 / 1024Allocated(M), bytes / 1024/1024自由空间(M), round(bytes / sgasize * 100, 2) 自由空间百分比(%) from (select sum

7、(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f where f.name = free memory-监控表空间I/O比例select df.tablespace_name name, df.file_name file, f.phyrds pyr, f.phyblkrd pbr, f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files dfwhere f.file# = df.file_idorder by df.tablespace_name;-监控文

8、件系统的I/O比例select substr(a.file#, 1, 2) #, substr(a.name, 1, 30) name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat bwhere a.file# = b.file#-在某个用户下找所有的索引：select user_indexes.table_name, user_indexes.index_name, uniqueness, column_name from user_ind_columns, user_indexeswhere us

9、er_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_nameorder by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;-表、索引的存储情况检查select segment_name, sum(bytes), count(*) ext_quan from dba_extentswhere tables

10、pace_name = &tablespace_name and segment_type = TABLEgroup by tablespace_name, segment_name;select segment_name, count(*) from dba_extentswhere segment_type = INDEX and owner = &ownergroup by segment_name;-监控SGA命中率 select a.value + b.value logical_reads, c.value phys_reads, round(100 * (a.value + b.

11、value) - c.value) / (a.value + b.value) BUFFER HIT RATIO from v$sysstat a, v$sysstat b, v$sysstat cwhere a.statistic# = 48 and b.statistic# = 51 and c.statistic# = 55;-监控SGA中字典缓冲区的命中率select parameter, gets, Getmisses, getmisses / (gets + getmisses) * 100 miss ratio, (1 - (sum(getmisses) / (sum(gets)

12、 + sum(getmisses) * 100 Hit ratio from v$rowcachewhere gets + getmisses 0group by parameter, gets, getmisses;-监控 SGA 中共享缓存区的命中率，应该小于1%select sum(pins) Total Pins, sum(reloads) Total Reloads, sum(reloads) / sum(pins) * 100 libcache from v$librarycache;-监控 SGA 中重做日志缓存区的命中率，应该小于1%SELECT name, gets, mis

13、ses, immediate_gets, immediate_misses, Decode(gets, 0, 0, misses / gets * 100) ratio1, Decode(immediate_gets + immediate_misses, 0, 0, immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2 FROM v$latchWHERE name IN (redo allocation, redo copy);-监控内存和硬盘的排序比率，最好使它小于 .10SELECT name, valu

14、e FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk);-监控字典缓冲区SELECT SUM(GETS) DICTIONARY GETS,SUM(GETMISSES) DICTIONARY CACHE GET MISSES FROM V$ROWCACHE-显示所有数据库对象的类别和大小select count(name) num_instances, type, sum(source_size) source_size, sum(parsed_size) parsed_size, sum(code_size) code_size

15、, sum(error_size) error_size, sum(source_size) + sum(parsed_size) + sum(code_size) + sum(error_size) size_required from dba_object_sizegroup by typeorder by 2;-监控当前数据库谁在运行什么SQL 语句SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address = b.addressorder by address, piece;-v$

16、dispatcherselect busy/(busy+idle) shared servers busy from v$dispatcher; -此值大于0.5时，参数需加大select sum(wait)/sum(totalq) dispatcher waits from v$queue where type=dispatcher;select count(*) from v$dispatcher;select servers_highwater from V$SHARED_SERVER_MONITOR;-servers_highwater接近MAX_SHARED_SERVERS时，参数需

17、加大-非系统用户建在SYSTEM表空间中的表SELECT owner, table_name FROM DBA_TABLESWHERE tablespace_name in (SYSTEM, USER_DATA) AND owner NOT IN (SYSTEM, SYS, OUTLN, ORDSYS, MDSYS, SCOTT, HOSTEAC)-性能最差的SQLSELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ORDER BY dis

18、k_reads DESC)WHERE ROWNUM100;-最频繁执行的sqlselect * from sys.v_$sqlarea where executions100-查询使用CPU多的用户sessionselect a.sid, spid, status, substr(a.program, 1, 40) prog, a.terminal, osuser, value / 60 / 100 value from v$session a, v$process b, v$sesstat cwhere c.statistic# = 12 and c.sid = a.sid and a.pa

19、ddr = b.addrorder by value desc-当前每个会话使用的对象数SELECT a.sid, s.terminal, s.program, count(a.sid) FROM V$ACCESS a, V$SESSION sWHERE a.owner SYS AND s.sid = a.sidGROUP BY a.sid, s.terminal, s.programORDER BY count(a.sid)-监控log_buffer的使用情况：(值最好小于1%，否则增加log_buffer 的大小)select rbar.name, rbar.value, re.name,

20、 re.value, (rbar.value * 100) / re.value | % radio from v$sysstat rbar, v$sysstat rewhere rbar.name = redo buffer allocation retries and re.name = redo entries;-查看运行过的SQL 语句：SELECT SQL_TEXT FROM V$SQL-客户端主机信息 SYS_CONTEXT sys_context函数调用userenv命名空间来获取相关信息select SYS_CONTEXT(USERENV, TERMINAL) terminal

21、, SYS_CONTEXT(USERENV, LANGUAGE) language, SYS_CONTEXT(USERENV, SESSIONID) sessionid, SYS_CONTEXT(USERENV, INSTANCE) instance, SYS_CONTEXT(USERENV, ENTRYID) entryid, SYS_CONTEXT(USERENV, ISDBA) isdba, SYS_CONTEXT(USERENV, NLS_TERRITORY) nls_territory, SYS_CONTEXT(USERENV, NLS_CURRENCY) nls_currency,

22、 SYS_CONTEXT(USERENV, NLS_CALENDAR) nls_calendar, SYS_CONTEXT(USERENV, NLS_DATE_FORMAT) nls_date_format, SYS_CONTEXT(USERENV, NLS_DATE_LANGUAGE) nls_date_language, SYS_CONTEXT(USERENV, NLS_SORT) nls_sort, SYS_CONTEXT(USERENV, CURRENT_USER) current_user, SYS_CONTEXT(USERENV, CURRENT_USERID) current_u

23、serid, SYS_CONTEXT(USERENV, SESSION_USER) session_user, SYS_CONTEXT(USERENV, SESSION_USERID) session_userid, SYS_CONTEXT(USERENV, PROXY_USER) proxy_user, SYS_CONTEXT(USERENV, PROXY_USERID) proxy_userid, SYS_CONTEXT(USERENV, DB_DOMAIN) db_domain, SYS_CONTEXT(USERENV, DB_NAME) db_name, SYS_CONTEXT(USE

24、RENV, HOST) host, SYS_CONTEXT(USERENV, OS_USER) os_user, SYS_CONTEXT(USERENV, EXTERNAL_NAME) external_name, SYS_CONTEXT(USERENV, IP_ADDRESS) ip_address, SYS_CONTEXT(USERENV, NETWORK_PROTOCOL) network_protocol, SYS_CONTEXT(USERENV, BG_JOB_ID) bg_job_id, SYS_CONTEXT(USERENV, FG_JOB_ID) fg_job_id, SYS_

25、CONTEXT(USERENV, AUTHENTICATION_TYPE) authentication_type, SYS_CONTEXT(USERENV, AUTHENTICATION_DATA) authentication_data from dual;-查看回滚段名称及大小SELECT a.owner | . | a.segment_name roll_name, a.tablespace_name tablespace, TO_CHAR(a.initial_extent) | / | TO_CHAR(a.next_extent) in_extents, TO_CHAR(a.min_

26、extents) | / | TO_CHAR(a.max_extents) m_extents, a.status status, b.bytes bytes, b.extents extents, d.shrinks shrinks, d.wraps wraps, d.optsize opt FROM dba_rollback_segs a, dba_segments b, v$rollname c, v$rollstat dWHERE a.segment_name = b.segment_name AND a.segment_name = c.name(+) AND c.usn = d.usn(+)ORDER BY a.segment_name;-parse to excute ratio（数据库的SQL语句执行和分析的比例） 越大越好 select round(1 - a.VALUE / b.VALUE )* 100, 2) parse to excute ratio from v