DBA常用脚本性能监控.docx
《DBA常用脚本性能监控.docx》由会员分享，可在线阅读，更多相关《DBA常用脚本性能监控.docx（17页珍藏版）》请在冰豆网上搜索。
DBA常用脚本性能监控
DBA常用脚本
（二）性能监控
Tags:
oracle
二、性能监控
1、数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的databuffer和一个高的命中率。
这个语句可以获得整体的数据缓冲命中率，越高越好
Code:
[Copytoclipboard]
SELECTa.VALUE+b.VALUElogical_reads,
c.VALUEphys_reads,
round（100*（1-c.value/（a.value+b.value））,4）hit_ratio
FROMv$sysstata,v$sysstatb,v$sysstatc
WHEREa.NAME='dbblockgets'
ANDb.NAME='consistentgets'
ANDc.NAME='physicalreads'
2、库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用
以下语句查询了Sql语句的重载率，越低越好
Code:
[Copytoclipboard]
SELECTSUM（pins）total_pins,SUM（reloads）total_reloads,
SUM（reloads）/SUM（pins）*100libcache_reload_ratio
FROMv$librarycache
3、用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过altersystemkillsession‘sid,serial#’来杀掉会话
Code:
[Copytoclipboard]
SELECT/*+rule*/s.username,
decode（l.type,'TM','TABLELOCK',
'TX','ROWLOCK',
NULL）LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROMv$sessions,v$lockl,dba_objectso
WHEREl.sid=s.sid
ANDl.id1=o.object_id（+）
ANDs.usernameisNOTNULL
4、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
Code:
[Copytoclipboard]
SELECT/*+rule*/lpad（'',decode（l.xidusn,0,3,0））||l.oracle_usernameUser_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROMv$locked_objectl,dba_objectso,v$sessions
WHEREl.object_id=o.object_id
ANDl.session_id=s.sid
ORDERBYo.object_id,xidusnDESC
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
5、如果发生了事务或锁，想知道哪些回滚段正在被使用吗？
其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。
同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。
Code:
[Copytoclipboard]
SELECTs.USERNAME,s.SID,s.SERIAL#,t.UBAFIL"UBAfilenum",
t.UBABLK"UBABlocknumber",t.USED_UBLK"NumberosundoBlocksUsed",
t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSNRollID,r.NAMERollName
FROMv$sessions,v$transactiont,v$rollnamer
WHEREs.SADDR=t.SES_ADDR
ANDt.XIDUSN=r.usn
7、如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。
Code:
[Copytoclipboard]
SELECTp1.value||'\'||p2.value||'_ora_'||p.spidfilename
FROM
v$processp,
v$sessions,
v$parameterp1,
v$parameterp2
WHEREp1.name='user_dump_dest'
ANDp2.name='db_name'
ANDp.addr=s.paddr
ANDs.audsid=USERENV（'SESSIONID'）;
8、在ORACLE9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。
以下就是开始索引监控与停止索引监控的脚本
Code:
[Copytoclipboard]
setheadingoff
setechooff
setfeedbackoff
setpages10000
spoolstart_index_monitor.sql
SELECT'alterindex'||owner||'.'||index_name||'monitoringusage;'
FROMdba_indexes
WHEREowner=USER;
spooloff
setheadingon
setechoon
setfeedbackon
------------------------------------------------
setheadingoff
setechooff
setfeedbackoff
setpages10000
spoolstop_index_monitor.sql
SELECT'alterindex'||owner||'.'||index_name||'nomonitoringusage;'
FROMdba_indexes
WHEREowner=USER;
spooloff
setheadingon
setechoon
setfeedbackon
如果需要监控更多的用户，可以将owner=User改写成别的
监控结果在视图v$object_usage中查询
感谢fenng，他提供了一个更新版的show_space脚本
Code:
[Copytoclipboard]
CREATEORREPLACEPROCEDUREshow_space
（p_segnameINVARCHAR2,
p_ownerINVARCHAR2DEFAULTUSER,
p_typeINVARCHAR2DEFAULT'TABLE',
p_partitionINVARCHAR2DEFAULTNULL）
--ThisprocedureusesAUTHIDCURRENTUSERsoitcanqueryDBA_*
--viewsusingprivilegesfromaROLEandsoitcanbeinstalled
--onceperdatabase,insteadofonceperuserwhowantedtouseit.
AUTHIDCURRENT_USER
as
l_free_blksnumber;
l_total_blocksnumber;
l_total_bytesnumber;
l_unused_blocksnumber;
l_unused_bytesnumber;
l_LastUsedExtFileIdnumber;
l_LastUsedExtBlockIdnumber;
l_LAST_USED_BLOCKnumber;
l_segment_space_mgmtvarchar2（255）;
l_unformatted_blocksnumber;
l_unformatted_bytesnumber;
l_fs1_blocksnumber;l_fs1_bytesnumber;
l_fs2_blocksnumber;l_fs2_bytesnumber;
l_fs3_blocksnumber;l_fs3_bytesnumber;
l_fs4_blocksnumber;l_fs4_bytesnumber;
l_full_blocksnumber;l_full_bytesnumber;
--Inlineproceduretoprintoutnumbersnicelyformatted
--withasimplelabel.
PROCEDUREp（p_labelinvarchar2,p_numinnumber）
IS
BEGIN
dbms_output.put_line（rpad（p_label,40,'.'）||
to_char（p_num,'999,999,999,999'））;
END;
BEGIN
--Thisqueryisexecuteddynamicallyinordertoallowthisprocedure
--tobecreatedbyauserwhohasaccesstoDBA_SEGMENTS/TABLESPACES
--viaaroleasiscustomary.
--NOTE:
atruntime,theinvokerMUSThaveaccesstothesetwo
--views!
--ThisquerydeterminesiftheobjectisanASSMobjectornot.
BEGIN
EXECUTEIMMEDIATE
'selectts.segment_space_management
FROMdba_segmentsseg,dba_tablespacests
WHEREseg.segment_name=:
p_segname
AND（:
p_partitionisnullor
seg.partition_name=:
p_partition）
ANDseg.owner=:
p_owner
ANDseg.tablespace_name=ts.tablespace_name'
INTOl_segment_space_mgmt
USINGp_segname,p_partition,p_partition,p_owner;
EXCEPTION
WHENtoo_many_rowsTHEN
dbms_output.put_line
（'Thismustbeapartitionedtable,usep_partition=>'）;
RETURN;
END;
--IftheobjectisinanASSMtablespace,wemustusethisAPI
--calltogetspaceinformation;elseweusetheFREE_BLOCKS
--APIfortheusermanagedsegments.
IFl_segment_space_mgmt='AUTO'
THEN
dbms_space.space_usage
（p_owner,p_segname,p_type,l_unformatted_blocks,
l_unformatted_bytes,l_fs1_blocks,l_fs1_bytes,
l_fs2_blocks,l_fs2_bytes,l_fs3_blocks,l_fs3_bytes,
l_fs4_blocks,l_fs4_bytes,l_full_blocks,l_full_bytes,p_partition）;
p（'UnformattedBlocks',l_unformatted_blocks）;
p（'FS1Blocks（0-25）',l_fs1_blocks）;
p（'FS2Blocks（25-50）',l_fs2_blocks）;
p（'FS3Blocks（50-75）',l_fs3_blocks）;
p（'FS4Blocks（75-100）',l_fs4_blocks）;
p（'FullBlocks',l_full_blocks）;
ELSE
dbms_space.free_blocks（
segment_owner=>p_owner,
segment_name=>p_segname,
segment_type=>p_type,
freelist_group_id=>0,
free_blks=>l_free_blks）;
p（'FreeBlocks',l_free_blks）;
ENDIF;
--AndthentheunusedspaceAPIcalltogettherestofthe
--information.
dbms_space.unused_space
（segment_owner=>p_owner,
segment_name=>p_segname,
segment_type=>p_type,
partition_name=>p_partition,
total_blocks=>l_total_blocks,
total_bytes=>l_total_bytes,
unused_blocks=>l_unused_blocks,
unused_bytes=>l_unused_bytes,
LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,
LAST_USED_BLOCK=>l_LAST_USED_BLOCK）;
p（'TotalBlocks',l_total_blocks）;
p（'TotalBytes',l_total_bytes）;
p（'TotalMBytes',trunc（l_total_bytes/1024/1024））;
p（'UnusedBlocks',l_unused_blocks）;
p（'UnusedBytes',l_unused_bytes）;
p（'LastUsedExtFileId',l_LastUsedExtFileId）;
p（'LastUsedExtBlockId',l_LastUsedExtBlockId）;
p（'LastUsedBlock',l_LAST_USED_BLOCK）;
END;
隐含参数：
selecta.ksppinm"parameter",a.ksppdesc"descriptoin"
fromx$ksppia,x$ksppcvb,x$ksppsvc
wherea.indx=b.indxanda.indx=c.indxanda.ksppinmlike'/_%'escape'/';
回复：
DBA常用SQL语句系列，欢迎补充，在不断更新中
CheckOSprocessidfromOraclesid
Code:
[Copytoclipboard]
selectspidfromv$process
whereaddrin（selectpaddrfromv$sessionwheresid=[$sid）]
CheckOraclesidfromOSprocessid
Code:
[Copytoclipboard]
selectsidfromv$session
wherepaddrin（selectaddrfromv$processwherespid=[$pid）]
CheckcurrentSQLinasession
Code:
[Copytoclipboard]
selectSQL_TEXTfromV$SQLTEXT
whereHASH_VALUE=
（selectSQL_HASH_VALUEfromv$session
wheresid=&sid）
orderbyPIECE
Checkingv$session_wait
Code:
[Copytoclipboard]
select*fromv$session_wait
whereeventnotlike'rdbms%'
andeventnotlike'SQL*N%'
andeventnotlike'%timer';
DictionaryCacheHits
Code:
[Copytoclipboard]
SELECTsum（getmisses）/sum（gets）FROMv$rowcache;
/*Itshouldbe<15%,otherwiseAddshare_pool_size*/
CheckDBobjectnamefromfileidandblock#
Code:
[Copytoclipboard]
selectowner,segment_name,segment_type
fromdba_extents
wherefile_id=[$fnoand&dnobetweenblock_idandblock_id+blocks–1]
回复：
DBA常用SQL语句系列，欢迎补充，在不断更新中
#寻找hotblock
select/*+ordered*/
e.owner||'.'||e.segment_namesegment_name,
e.extent_idextent#,
x.dbablk-e.block_id+1block#,
x.tch,
l.child#
from
sys.v$latch_childrenl,
sys.x$bhx,
sys.dba_extentse
where
l.name='cachebufferschains'and
l.sleeps>&sleep_countand
x.hladdr=l.addrand
e.file_id=x.file#and
x.dbablkbetweene.block_idande.block_id+e.blocks-1;
#找出每个文件上的等待事件
selectdf.name,kf.countfromv$datafiledf,x$kcbfwaitkfwhere（kf.indx+1）=df.file#;
#找出引起等待事件的SQL语句.
selectsql_textfromv$sqlareaa,v$sessionb,v$session_waitcwherea.address=b.sql_addressandb.sid=c.sidandc.event=[$ll]
#监控共享池中哪个对象引起了大的内存分配
SELECT*FROMX$KSMLRUWHEREksmlrsiz>0;
判断你是从pfile启动还是spfile启动的简单方法！
！
！
判断你是从pfile启动还是spfile启动的简单方法！
！
！
selectdecode（count（*）,1,'spfile','pfile'）
fromv$spparameter
whererownum=1
andisspecified='TRUE'
/
DECODE
------
spfile
ORACLE常用技巧和脚本
ORACLE常用技巧和脚本
1.如何查看ORACLE的隐含参数？
ORACLE的显式参数，除了在INIT.ORA文件中定义的外，在svrmgrl中用"showparameter*"，可以显示。
但ORACLE还有一些参数是以“_”，开头的。
如我们非常熟悉的“_offline_rollback_segments”等。
这些参数可在sys.x$ksppi表中查出。
语句：
“selectksppinmfromx$ksppiwheresubstr（ksppinm,1,1）='_';”
2.如何查看安装了哪些ORACLE组件？
进入${ORACLE_HOME}/orainst/，运行./inspdver，显示安装组件和版本号。
3.如何查看ORACLE所占用共享内存的大小？
可用UNIX命令“ipcs”查看共享内存的起始地址、信号量、消息队列。
升级会员 