常用的一些性能查询sql语句.docx
- 文档编号:14499272
- 上传时间:2023-06-24
- 格式:DOCX
- 页数:10
- 大小:18.65KB
常用的一些性能查询sql语句.docx
《常用的一些性能查询sql语句.docx》由会员分享,可在线阅读,更多相关《常用的一些性能查询sql语句.docx(10页珍藏版)》请在冰点文库上搜索。
常用的一些性能查询sql语句
常用的一些性能查询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.phyrdspyr,
f.phyblkrdpbr,
f.phywrtspyw,
f.phyblkwrtpbw
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$sysstata,v$sysstatb
wherea.STATISTIC#=331andb.STATISTIC#=330;
--ParseCPUtoParseElapsed(指数据库用在分析的CPU的时间和分析完成CPU时间对比)
selectround((1-a.VALUE/b.VALUE)*100,2)"ParseCPUtoParseElapsed"
fromv$sysstata,v$sysstatb
wherea.STATISTIC#=328andb.STATISTIC#=329;
--Non-ParseCPU(用在非分析的过程中CPU的等待了其它的资源)
selectround((1-a.VALUE/b.VALUE)*100,2)"Non-ParseCPU"
fromv$sysstata,v$sysstatb
wherea.STATISTIC#=328andb.STATISTIC#=12;
--Rollbacksegment(等待rollbacksegment的header比率,比率越小越好)
selectwaits,gets,round(waits/gets*100,2)fromv$rollstata,v$rollnamebwherea.USN=b.usn
--表字段的注释说明
select*fromuser_col_comments
数据字典:
select*fromdictorderbytable_name;
锁及资源信息:
select*fromv$lock;不包括DDL锁
数据库字符集:
select*fromsys.props$wherename='NLS_CHARACTERSET';
inin.ora参数:
selectname,valuefromv$parameterorderbyname;
SQL共享池:
selectsql_textfromv$sqlarea;
数据库:
select*fromv$database
控
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 常用 一些 性能 查询 sql 语句