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”查看共享内存的起始地址、信号量、消息队列。