DB常用性能查询语句文档格式.docx
- 文档编号:16450632
- 上传时间:2022-11-23
- 格式:DOCX
- 页数:11
- 大小:20.36KB
DB常用性能查询语句文档格式.docx
《DB常用性能查询语句文档格式.docx》由会员分享,可在线阅读,更多相关《DB常用性能查询语句文档格式.docx(11页珍藏版)》请在冰豆网上搜索。
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(*))
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,
column_position;
--表、索引的存储情况检查
selectsegment_name,sum(bytes),count(*)ext_quan
fromdba_extents
wheretablespace_name='
&
tablespace_name'
andsegment_type='
groupbytablespace_name,segment_name;
selectsegment_name,count(*)
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,
misses,
immediate_gets,
immediate_misses,
Decode(gets,0,0,misses/gets*100)ratio1,
Decode(immediate_gets+immediate_misses,
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('
USER_DATA'
ANDownerNOTIN
('
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
--最频繁执行的sql
select*fromsys.v_$sqlareawhereexecutions>
--查询使用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<
'
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('
LANGUAGE'
)language,
SESSIONID'
)sessionid,
INSTANCE'
)instance,
ENTRYID'
)entryid,
ISDBA'
)isdba,
NLS_TERRITORY'
)nls_territory,
NLS_CURRENCY'
)nls_currency,
NLS_CALENDAR'
)nls_calendar,
NLS_DATE_FORMAT'
)nls_date_format,
NLS_DATE_LANGUAGE'
)nls_date_language,
NLS_SORT'
)nls_sort,
CURRENT_USER'
)current_user,
CURRENT_USERID'
)current_userid,
SESSION_USER'
)session_user,
SESSION_USERID'
)session_userid,
PROXY_USER'
)proxy_user,
PROXY_USERID'
)proxy_userid,
DB_DOMAIN'
)db_domain,
DB_NAME'
)db_name,
HOST'
)host,
OS_USER'
)os_user,
EXTERNAL_NAME'
)external_name,
IP_ADDRESS'
)ip_address,
NETWORK_PROTOCOL'
)network_protocol,
BG_JOB_ID'
)bg_job_id,
FG_JOB_ID'
)fg_job_id,
AUTHENTICATION_TYPE'
)authentication_type,
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB 常用 性能 查询 语句
![提示](https://static.bdocx.com/images/bang_tan.gif)