DB常用性能查询语句.docx
《DB常用性能查询语句.docx》由会员分享，可在线阅读，更多相关《DB常用性能查询语句.docx（11页珍藏版）》请在冰豆网上搜索。
DB常用性能查询语句
常用的一些性能查询sql语句
--查看表锁
select*fromsys.v_$sqlareawheredisk_reads>100
--监控事例的等待
selectevent,
sum（decode（wait_Time,0,0,1））"Prev",
sum（decode（wait_Time,0,1,0））"Curr",
count（*）"Tot"
fromv$session_Wait
groupbyevent
orderby4
--回滚段的争用情况
selectname,waits,gets,waits/gets"Ratio"
fromv$rollstata,v$rollnameb
wherea.usn=b.usn
--查看前台正在发出的SQL语句
selectuser_name,sql_text
fromv$open_cursor
wheresidin（selectsid
from（selectsid,serial#,username,program
fromv$session
wherestatus='ACTIVE'））
--数据表占用空间大小情况
selectsegment_name,tablespace_name,bytes,blocks
fromuser_segments
wheresegment_type='TABLE'
ORDERBYbytesDESC,blocksDESC
--查看表空间碎片大小
selecttablespace_name,
round（sqrt（max（blocks）/sum（blocks））*
（100/sqrt（sqrt（count（blocks））））,
2）FSFI
fromdba_free_space
groupbytablespace_name
orderby1
--查看碎片程度高的表
SELECTsegment_nametable_name,COUNT（*）extents
FROMdba_segments
WHEREownerNOTIN（'SYS','SYSTEM'）
GROUPBYsegment_name
HAVINGCOUNT（*）=（SELECTMAX（COUNT（*））
FROMdba_segments
GROUPBYsegment_name）;
--查看表空间占用磁盘情况
selectb.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剩余百分比
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_id,b.bytes
orderbyb.file_id;
--查看session使用回滚段
SELECTr.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）操作程序
FROMsys.v_$sessions,sys.v_$transactiont,sys.v_$rollnamer
WHEREt.addr=s.taddr
andt.xidusn=r.usn
ORDERBYt.cr_get,t.phy_io
--查看SGA区剩余可用内存
selectname,
sgasize/1024/1024 "Allocated（M）",
bytes/1024/1024 "自由空间（M）",
round（bytes/sgasize*100,2） "自由空间百分比（%）"
from（selectsum（bytes）sgasizefromsys.v_$sgastat）s,
sys.v_$sgastatf
wheref.name='freememory'
--监控表空间I/O比例
selectdf.tablespace_namename,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
fromv$filestatf,dba_data_filesdf
wheref.file#=df.file_id
orderbydf.tablespace_name;
--监控文件系统的I/O比例
selectsubstr（a.file#,1,2）"#",
substr（a.name,1,30）"name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
fromv$datafilea,v$filestatb
wherea.file#=b.file#
--在某个用户下找所有的索引：
selectuser_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
fromuser_ind_columns,user_indexes
whereuser_ind_columns.index_name=user_indexes.index_name
anduser_ind_columns.table_name=user_indexes.table_name
orderbyuser_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
--表、索引的存储情况检查
selectsegment_name,sum（bytes）,count（*）ext_quan
fromdba_extents
wheretablespace_name='&tablespace_name'
andsegment_type='TABLE'
groupbytablespace_name,segment_name;
selectsegment_name,count（*）
fromdba_extents
wheresegment_type='INDEX'
andowner='&owner'
groupbysegment_name;
--监控SGA命中率
selecta.value+b.value"logical_reads",
c.value"phys_reads",
round（100*（（a.value+b.value）-c.value）/（a.value+b.value））"BUFFERHITRATIO"
fromv$sysstata,v$sysstatb,v$sysstatc
wherea.statistic#=48
andb.statistic#=51
andc.statistic#=55;
--监控SGA中字典缓冲区的命中率
selectparameter,
gets,
Getmisses,
getmisses/（gets+getmisses）*100"missratio",
（1-（sum（getmisses）/（sum（gets）+sum（getmisses））））*100"Hitratio"
fromv$rowcache
wheregets+getmisses<>0
groupbyparameter,gets,getmisses;
--监控SGA中共享缓存区的命中率，应该小于1%
selectsum（pins）"TotalPins",
sum（reloads）"TotalReloads",
sum（reloads）/sum（pins）*100libcache
fromv$librarycache;
--监控SGA中重做日志缓存区的命中率，应该小于1%
SELECTname,
gets,
misses,
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
FROMv$latch
WHEREnameIN（'redoallocation','redocopy'）;
--监控内存和硬盘的排序比率，最好使它小于.10
SELECTname,valueFROMv$sysstatWHEREnameIN（'sorts（memory）','sorts（disk）'）;
--监控字典缓冲区
SELECTSUM（GETS）"DICTIONARYGETS",SUM（GETMISSES）"DICTIONARYCACHEGETMISSES"FROMV$ROWCACHE
--显示所有数据库对象的类别和大小
selectcount（name）num_instances,
type,
sum（source_size）source_size,
sum（parsed_size）parsed_size,
sum（code_size）code_size,
sum（error_size）error_size,
sum（source_size）+sum（parsed_size）+sum（code_size）+
sum（error_size）size_required
fromdba_object_size
groupbytype
orderby2;
--监控当前数据库谁在运行什么SQL语句
SELECTosuser,username,sql_text
fromv$sessiona,v$sqltextb
wherea.sql_address=b.address
orderbyaddress,piece;
--v$dispatcher
selectbusy/（busy+idle）"sharedserversbusy"fromv$dispatcher;--此值大于0.5时，参数需加大
selectsum（wait）/sum（totalq）"dispatcherwaits"fromv$queuewheretype='dispatcher';
selectcount（*）fromv$dispatcher;
selectservers_highwaterfromV$SHARED_SERVER_MONITOR;--servers_highwater接近MAX_SHARED_SERVERS时，参数需加大
--非系统用户建在SYSTEM表空间中的表
SELECTowner,table_name
FROMDBA_TABLES
WHEREtablespace_namein（'SYSTEM','USER_DATA'）
ANDownerNOTIN
（'SYSTEM','SYS','OUTLN','ORDSYS','MDSYS','SCOTT','HOSTEAC'）
--性能最差的SQL
SELECT*FROM（SELECTPARSING_USER_IDEXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
FROMv$sqlarea
ORDERBYdisk_readsDESC）
WHEREROWNUM<100;
--读磁盘数超100次的sql
select*fromsys.v_$sqlareawheredisk_reads>100;
--最频繁执行的sql
select*fromsys.v_$sqlareawhereexecutions>100
--查询使用CPU多的用户session
selecta.sid,
spid,
status,
substr（a.program,1,40）prog,
a.terminal,
osuser,
value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=12
andc.sid=a.sid
anda.paddr=b.addr
orderbyvaluedesc
--当前每个会话使用的对象数
SELECTa.sid,s.terminal,s.program,count（a.sid）
FROMV$ACCESSa,V$SESSIONs
WHEREa.owner<>'SYS'
ANDs.sid=a.sid
GROUPBYa.sid,s.terminal,s.program
ORDERBYcount（a.sid）
--监控log_buffer的使用情况：
（值最好小于1%，否则增加log_buffer的大小）
selectrbar.name,
rbar.value,
re.name,
re.value,
（rbar.value*100）/re.value||'%'"radio"
fromv$sysstatrbar,v$sysstatre
whererbar.name='redobufferallocationretries'
andre.name='redoentries';
--查看运行过的SQL语句：
SELECTSQL_TEXTFROMV$SQL
--客户端主机信息 SYS_CONTEXTsys_context函数调用userenv命名空间来获取相关信息
selectSYS_CONTEXT（'USERENV','TERMINAL'）terminal,
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,
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_userid,
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（'USERENV','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_CONTEXT（'USERENV','AUTHENTICATION_TYPE'）authentication_type,
SYS_CONTEXT（'USERENV','AUTHENTICATION_DATA'）authentication_data
fromdual;
--查看回滚段名称及大小
SELECTa.owner||'.'||a.segment_nameroll_name,
a.tablespace_nametablespace,
TO_CHAR（a.initial_extent）||'/'||TO_CHAR（a.next_extent）in_extents,
TO_CHAR（a.min_extents）||'/'||TO_CHAR（a.max_extents）m_extents,
a.statusstatus,
b.bytesbytes,
b.extentsextents,
d.shrinksshrinks,
d.wrapswraps,
d.optsizeopt
FROMdba_rollback_segsa,dba_segmentsb,v$rollnamec,v$rollstatd
WHEREa.segment_name=b.segment_name
ANDa.segment_name=c.name（+）
ANDc.usn=d.usn（+）
ORDERBYa.segment_name;
--parsetoexcuteratio（数据库的SQL语句执行和分析的比例）越大越好
selectround（（1-a.VALUE/b.VALUE）*100,2）"parsetoexcuteratio"
fromv
升级会员 