oracle性能监控sql.docx
《oracle性能监控sql.docx》由会员分享，可在线阅读，更多相关《oracle性能监控sql.docx（30页珍藏版）》请在冰豆网上搜索。
oracle性能监控sql
1.根据进程号查找占用资源的sql语句
selectsess.username,sql1.SQL_TEXTfromv$sessionsess,v$sqltextsql1,v$processpro
wheresess.SQL_ADDRESS=sql1.ADDRESSandpro.ADDR=sess.PADDRandpro.SPID='&spid'orderbysql1.PIECE;
————————————————————————————————————————————————
2.查找相关对象的锁
selectoracle_username,os_user_name,process,object_name,o.SESSION_IDfromv$locked_objecto,dba_objectsdwhereo.OBJECT_ID=d.object_idandobject_name='T_PLY_BASE';
————————————————————————————————————————————————
3.杀掉某模块的session
SELECTdistinct'ALTERSYSTEMKILLSESSION'''||s.SID||','||s.SERIAL#||''';',p.PID
FROMV$SESSIONS,v$processp
WHEREs.PADDR=p.ADDR
ands.PROGRAMlike'&1%';
————————————————————————————————————————————————
4.杀掉某台机器的session
selects.username,s.sid,s.SERIAL#,machine,s.terminal,spidfromv$sessions,v$processpwheremachinelike'%NZF%'ands.PADDR=p.ADDR;
对上面问题的补充，当上面语句不能解决问题时
select'kill-9'||p.spid
fromv$processp,v$sessionswherep.ADDR=s.PADDRands.PROGRAMlike'&1%';
————————————————————————————————————————————————
5.决定pga_aggregate_target的最小值
selectmin（pga_target_for_estimate）
fromv$pga_target_advice
whereestd_pga_cache_hit_percentage>95;
————————————————————————————————————————————————
6.察看shared_pool_size实用情况
colvaluefor999,999,999,999heading"SharedPoolSize";
colbytesfor999,999,999,999heading"FreeBytes";
selectto_number（v$parameter.value）value,v$sgastat.bytes,
（v$sgastat.bytes/v$parameter.value）*100"PercentFree"
fromv$sgastat,v$parameter
wherev$sgastat.name='freememory'
andv$parameter.name='shared_pool_size'
andv$sgastat.pool='sharedpool';
————————————————————————————————————————————————
7.如何监控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;
————————————————————————————————————————————————
8.如何监控SGA中共享缓存区的命中率，应该小于1%？
selectsum（pins）"TotalPins",sum（reloads）"TotalReloads",
sum（reloads）/sum（pins）*100libcache
fromv$librarycache;
selectsum（pinhits-reloads）/sum（pins）"hitradio",sum（reloads）/sum（pins） "reloadpercent"
fromv$librarycache;
————————————————————————————————————————————————
9.如何显示所有数据库对象的类别和大小？
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
groupbytypeorderby2;
————————————————————————————————————————————————
10.监控SGA中重做日志缓存区的命中率，应该小于1%
columnnameformatA20;
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$latchWHEREnameIN（'redoallocation','redocopy'）;
————————————————————————————————————————————————
11.如何知道使用CPU多的用户session?
是cpuusedbythissession
selecta.sid,spid,status,substr（a.program,1,40）prog,a.terminal,osuser,value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=11andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;
————————————————————————————————————————————————
12.SQLPLUS下如何修改编辑器?
DEFINE_EDITOR="<编辑器的完整路经>" --必须加上双引号
来定义新的编辑器，也可以把这个写在$ORACLE_HOME/sqlplus/admin/glogin.sql里面使它永久有效。
————————————————————————————————————————————————
13.LINUX下查询磁盘竞争状况命令?
sar -d
————————————————————————————————————————————————
14.察看日志切换速度
selectb.recid,to_char（b.first_time,'dd-mon-yyhh24:
mi:
ss'）start_time,a.recid,
to_char（a.first_time,'dd-mon-yyhh24:
mi:
ss'）end_time,round（（（a.first_time-b.first_time）*24）*60,2）minutes
fromv$log_historya,v$log_historybwherea.recid=b.recid+1orderbya.first_timeasc;
————————————————————————————————————————————————
15.察看锁的情况
columnsessformata15;
selectdecode（request,0,'Holder:
','Waiter:
'）||sidsess,id1,id2,lmode,request,type
fromv$lockwhere（id1,id2,type）in（selectid1,id2,typefromv$lockwhererequest>0）
orderbyid1,request;
查出锁的情况和进程
selects.sid,s.serial#,s.program,s.machine,s.username,s.last_call_et,
s.logon_time,sw.eventfromv$sessions,v$session_waitswwheres.sid=sw.sidands.sid=2883625;
根据进程号查出是什么东西锁住了
selects.sid,s.serial#,s.program,s.machine,s.username,s.sql_hash_value,s.row_wait_obj#fromv$sessions
where.sid=377'
select'altersystemkillsession'''||sid||','||serial#||''';'fromv$sessions,v$sqltextsq
wheres.SQL_HASH_VALUE=sq.HASH_VALUEandsq.SQL_TEXTlike'%CUX_GL_TAX%'orderbylogon_time
————————————————————————————————————————————————
16.产生删除某个进程session的sql;
selectdistinct'ALTERSYSTEMKILLSESSION'''||SID||','||s.SERIAL#||''';'fromv$sessions,v$processpwheres.PADDR=p.ADDRandp.sPIDlike'%&1%';
产生删除某个进程模块的sql;
selectdistinct'ALTERSYSTEMKILLSESSION'''||SID||','||s.SERIAL#||''';'fromv$sessions,v$processpwheres.PADDR=p.ADDRands.PROGRAMlike'svClaim%';
查询模块，机器，共执行的数目
selectmachine,schemaname,program,count（*）from（
selects.serial#,s.sid,s.MACHINE,p.pid,p.spid,s.SCHEMANAME,s.PROGRAMfromv$processp,v$sessionswherep.ADDR=s.PADDRands.SCHEMANAMEnotin（'SYS'）
）groupbymachine,schemaname,program;
————————————————————————————————————————————————
17.logminer检查执行步骤
executedbms_logmnr.add_logfile（'/oradata1/archive/abstandby/1_28456.dbf',dbms_logmnr.new）;
selectlow_time,high_time,low_scn,next_scnfromv$logmnr_logs;
execdbms_logmnr.start_logmnr（dictfilename=>'/oradata1/utlfile/shwdict.ora',startscn=>2379878362,endscn=>2379976020,starttime=>to_date（'2006093001:
15:
00','yyyymmddhh24:
mi:
ss'）,endtime=>to_date（'2006093021:
40:
30','yyyymmddhh24:
mi:
ss'））;
SelectSCN,timestamp,session#session_num,sql_redo
FromV$LOGMNR_CONTENTS
Orderby1
————————————————————————————————————————————————
18.清除已删除的archlog信息
crosscheckarchivelogall;
deleteexpiredarchivelogall;
————————————————————————————————————————————————
19.查找等待时间最长的语句
selecta.USERNAME,a.OSUSER,a.PROCESS,a.MACHINE,a.ACTION,a.sid,a.last_call_et,b.sql_text
fromv$sessiona
,v$sqltextb
wherea.usernameisnotnull
and a.status='ACTIVE'
and a.sql_address=b.address
orderbya.last_call_et,a.sid,b.piece;
根据查出来的sid判断等待的对象
selectowner,segment_name,segment_type
from（selectp1file#,p2block#fromv$session_wait
wheresid=284
andeventin（'bufferbusywaits'
,'dbfilesequentialread'
,'dbfilescatteredread'
,'freebufferwaits'））b
dba_extentsa
wherea.file_id=b.file#
and b.block#betweena.block_idand（a.block_id+blocks-1）;
————————————————————————————————————————————————
20.监控索引使用
select*fromV$OBJECT_USAGEwhereused='YES'
select*fromV$OBJECT_USAGEwhereused='NO'
select'alterindex'||index_name||'monitoringusage;'fromdba_indexeswhereowner='ABSYS';
select'alterindex'||index_name||'nomonitoringusage;'fromdba_indexeswhereowner='ABSYS';
————————————————————————————————————————————————
21.查找正在执行的存储过程
createorreplaceproceduresys.who_is_using（obj_namevarchar2）is
begin
dbms_output.enable（1000000）;
foriin（SELECTdistinctb.username,b.sid
FROMSYS.x$kglpna,v$sessionb,SYS.x$kglobc
WHEREa.KGLPNUSE=b.saddr
andupper（c.KGLNAOBJ）likeupper（OBJ_NAME）
anda.KGLPNHDL=c.KGLHDADR）
loop
dbms_output.put_line（'（'||to_char（i.sid）||'）-'||i.username）;
endloop;
end;
————————————————————————————————————————————————
22.查找全表扫描的SQL语句
Selectsql_textfromv$sqltextt,v$sql_planp
Wheret.hash_value=p.hash_value
Andp.operation='INDEX'
Andp.OPTIONS='FULLSCAN'
Orderbyp.HASH_VALUE,t.piece;
查找FastFullindex扫描的Sql语句可以这样；
Selectsql_textfromv$sqltextt,v$sql_planp
Wheret.hash_value=p.hash_value
Andp.operation='INDEX'
Andp.OPTIONS='FULLSCAN'
Orderbyp.HASH_VALUE,t.piece;
————————————————————————————————————————————————
23.已经altersystemkillsession但是没有kill干净，查找进程号
selectp.addrfromv$processpwherepid<>1
minus
selects.paddrfromv$sessions;
————————————————————————————————————————————————
24.10g自动收集数据
selectJOB_NAME,ENABLED,statefromdba_scheduler_jobs;
execdbms_scheduler.disable（'GATHER_STATS_JOB'）;
execdbms_scheduler.enable（'GATHER_STATS_JOB'）;
————————————————————————————————————————————————
25.查询有enqueue等待的事件
SELECT b.SID,b.serial#,b.username,machine,a.event,a.wait_time,
CHR（BITAND（a.p1,-16777216）/16777215）||CHR（BITAND（a.p1,16711680）/65535）"EnqueueType"
FROMv$session_waita,v$sessionb
WHEREa.eventNOTLIKE'SQL*N%'
ANDa.eventNOTLIKE'rdbms%'
ANDa.SID=b.SID
ANDb.SID>8
ANDa.event='enqueue'
ORDERBYusername;
————————————————————————————————————————————————
26.如何确定哪个表空间读写频繁?
selectname,phyrds,phywrts,readtim,writetim
fromv$filestata,v$dbfileb
wherea.file#=b.file#
orderbyreadtimdesc
————————————————————————————————————————————————
27.在磁盘上的物理写入和读取次数上如果出现很大的差别，就表明肯定有哪个磁盘负载过多！
如果出现磁盘负载不平衡，可以通过移动数据文件来均衡文件I/O：
altertablespacetablespace_nameoffline;
$cp/disk1/a.dbf/disk2/a.dbf;
altertablespacetablespace_namerenamedatafile'/disk1/a.dbf'to'/disk2/a.dbf';
altertablespacetablespaceonline;
$rm/disk1/a.dbf
————————————————————————————————————————————————
28.查询SQL语句执行时,硬语法分析的次数
selectname,value
fromv$sysstat
wherenamelike'parsecount%';
————————————————————————————————————————————————
29.查询SQL语句中没有帮定变量的SQL语句,,按执行次数排序
SELECTSUBSTR（sql_text,1,40）"SQL",COUNT（*）,
SUM（executions）"TotExecs"FROMv$sqlareaWHEREexecutions<
升级会员 