Oracle性能异常查询及调整脚本.docx
- 文档编号:11733969
- 上传时间:2023-03-31
- 格式:DOCX
- 页数:19
- 大小:29.11KB
Oracle性能异常查询及调整脚本.docx
《Oracle性能异常查询及调整脚本.docx》由会员分享,可在线阅读,更多相关《Oracle性能异常查询及调整脚本.docx(19页珍藏版)》请在冰豆网上搜索。
Oracle性能异常查询及调整脚本
Oracle性能异常查询及调整脚本
ITPUB个人空间3l_@"{_sSu*|
1.查询长时间操作的SQL(或者通过OEM图形界面查看)
V$SESSION_LONGOPS
查询运行超过6秒钟的操作,这些操作包括很多备份恢复功能,统计信息收集,查询操作,
r_U$w!
t_P_~0不同版本可能有更多的操作加入。
OracleOEM中的长时间操作就是读取这个视图。
要监控queryexecutionprogress,必须满足以下前提条件:
1)Oracle优化器使用CBO;
7y_T#F_p_l_C_cj5\6X,g3p02)数据对象已经收集了统计信息;
"\A_n_w"f5Cj&T03)初始化参数TIMED_STATISTICS=true或是SQL_TRACE=true;
ITPUB个人空间Y+z_p5l+G
T_]_G9j_d:
n
如果是OracleRAC,可以使用以下脚本:
SELECT a.opname,ITPUB个人空间$l&p9v3X2G;g%`/?
a.SID,ITPUB个人空间)\r"X*T"R#q
a.serial#,ITPUB个人空间2Y9I*|}_C_}
a.sql_id,
9{!
Gc_x8h_Lb-K_u_]#Q"U_S0 a.start_time,
_i,h_}/^_e_J0 a.time_remaining,
_cP_B6VJc0 a.elapsed_seconds,
_r#H7O_G0g_Qp%W0 b.sql_fulltext,ITPUB个人空间'y_H1SO(E
a.MESSAGE
A)m2SYg_j8v9`!
Q0 FROM gv$session_longopsa,gv$sqlbITPUB个人空间_Y(im'mV
WHERE a.start_time>SYSDATE-0.1
-N4P$R9@:
P0 ANDa.time_remaining>0
2K_ne_L_x4N_Q__0 ANDa.sql_id=b.sql_idITPUB个人空间(\%e!
o_Q)u
ORDERBY a.start_timeDESC;
_CZ*H!
s2{C_Jj3U3Z0如果是单机,可以使用以下脚本:
SELECT a.opname,
_A_@_E_v4O_R_F6z0 a.SID,ITPUB个人空间2R-F_]W&J_S)C_wI
a.serial#,ITPUB个人空间'^8B%x_fR_[-Y_nc+e
a.sql_id,
_KD_a,jT_hu0 a.start_time,
I_]'h%Y6v_~0 a.time_remaining,ITPUB个人空间+]_D_y#p%@H
a.elapsed_seconds,
_i_a_K,{4?
_i
}/T0 b.sql_fulltext,ITPUB个人空间_L0g_ER?
_u
a.MESSAGE,ITPUB个人空间/Y$z_qk6e1y#x|
b.module,
_m0~n.L
N_0 b.executionsITPUB个人空间_a){)G(Z{_B_G
FROM v$session_longopsa,v$sqlbITPUB个人空间_D_B;D_]']'h]0_
WHERE a.start_time>SYSDATE-0.1 ITPUB个人空间_Z;`-J_J8T4a/f*e
ANDa.time_remaining>0ITPUB个人空间)[%@_u6Y
`h!
U
ANDa.sql_id=b.sql_idITPUB个人空间_v_Z_o_b9k.k.|
ORDERBY a.start_timeDESC;
r_k%N_B;|_R_o.j-o7s02.通过LinuxPID及SID查询相关SQL及程序:
select a.sid,a.serial#,a.program,b.spidfromv$sessiona,v$processb
P_E_}
P!
r%b0wherea.paddr=b.addrandb.spidin('1245','2985','5884');
spid为top查看到的os段的processid.
ITPUB个人空间7v_@(M___gu_T3d
紧急处理时候可以通过killsession方式或直接killosprocess来结束进程。
ITPUB个人空间#p$d5q_ib_e_@6X)}_t
Altersystemkillsession'SID,SERIAL#' ;
{_f5X_b_w_P_H_V0Kill-9 SPID (unix,linux)ITPUB个人空间%?
_l)w_I8Ef
orakill sid(oracle_instance_name) spid (windows)
+P)o0g_b,y_{/u_a0selecta.username,a.machine,a.program,b.spid,c.sql_text
_D2i_E*RA_e_p6d0fromv$sessiona,v$processb,v$sqlareacITPUB个人空间1F_M)c_K_J_h-L/E!
\
wherea.paddr=b.addrand c.hash_value=a.sql_hash_value
_Q8t"[_D(z1}_K_^_]0andc.address=a.sql_addressandb.spid=12984;ITPUB个人空间*W`_z+z%B5y_`
ITPUB个人空间(\_s#O_R7jX(@
知道有问题的SPID情况下查看正在运行的SQL.
SELECTa.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_textITPUB个人空间_G_@%W_E_Q
fromv$sessiona,v$processb,v$sqltextcITPUB个人空间-h_X_Y)ns.T
WHEREb.spid='14150'ITPUB个人空间,y_t7c%S&g_h_ls_C
ANDb.addr=a.paddrANDa.sql_address=c.address(+)orderBYc.piece
ITPUB个人空间W%q_}_`_r9|(U
3.查询Oracle库中的Lock(或通过Toad中sessionbrowser参看lock情况)
查看数据库中的锁(LOCK),找出程序及SQL
SELECT se.inst_id,se.SID,se.serial#,lk.SID,
"^\*T_z_?
0 se.username,se.OSUser,se.Machine,se.program,ITPUB个人空间;V3|R%C,`1a_O_}"y,{_D
DECODE(lk.TYPE,ITPUB个人空间__*Pr2s)D_q9j
'TX','Transaction',
q
s_PX"`5p0E0 'TM','DML',
\[.I_j5{0j8p.R;J*t0 'UL','PL/SQLUserLock',ITPUB个人空间_v_Z*K_r_M_?
___Zr:
A
lk.TYPE)
_b_A;V_iD_E0 lock_type,
x_L(Q_jG0 DECODE(lk.lmode,
_r#u;o_L6i(]0 0,'None',ITPUB个人空间A"E_\_~1U_i
1,'Null',
$V0c_c_u"j1K;[_R_V-}0 2,'Row-S(SS)',ITPUB个人空间_Y_{_b5s_Z__8J_\
3,'Row-X(SX)',ITPUB个人空间"Z_b1J_?
#f1T$F5ech
4,'Share',ITPUB个人空间r'O*L2X_|_j's
5,'S/Row-X(SSX)',
5a.m;d4a,k%g&[H_B0 6,'Exclusive',ITPUB个人空间$W,M1~\/O$i_y_S
TO_CHAR(lk.lmode))ITPUB个人空间2w_l;S3__B&O
mode_held,ITPUB个人空间G)u_L_`_C_(n_F
DECODE(lk.request,
_r_][_}_PVTX_S0Z0 0,'None',ITPUB个人空间*R$~+{-^`_kS+q_R4y*B_d
1,'Null',
_E_G3C7|_@_[%N8n0 2,'Row-S(SS)',
_c)G_B_@_N"KE_E8G)g0 3,'Row-X(SX)',ITPUB个人空间-F3h_B.l_KF?
-e
4,'Share',
_F_i2U'K_l_T0 5,'S/Row-X(SSX)',
+|_J"Y%N_M_[H_u0 6,'Exclusive',
_p_[_D_?
0q&o0 TO_CHAR(lk.request))ITPUB个人空间!
A_k-j4X_m:
sL;u&o
mode_requested,
_b_g__VH_[_D_s0 TO_CHAR(lk.id1)lock_id1,ITPUB个人空间w_rFK!
zd
TO_CHAR(lk.id2)lock_id2,
F_dz,?
_z:
X_O_Q_[/p0 ob.owner,ITPUB个人空间*A_d`_S_h;?
_]
ob.object_type,
_H$W_@_kU5F0 ob.object_name,
_T1}_\_J_f&M7@0 DECODE(lk.Block,
0d}0S__9m_O*T0 0,ITPUB个人空间-V_^_F!
q(P*a2O
'No',ITPUB个人空间.?
_X*K7M,q+V_i_m
1,ITPUB个人空间_A
u_W-o*`_y
'Yes',ITPUB个人空间c_P_E1h8z#Q9H
2,ITPUB个人空间3|d_{-h_f
'Global')ITPUB个人空间"h_e_I!
Tl_l_f_[
block,ITPUB个人空间7J3}4U4l_{_V;K
se.lockwait,
5c_`d;U_f_O[_`p_l&C0 sq.sql_fulltextITPUB个人空间_~_X&[c_K2g'Oh2R[
FROM GV$locklk,dba_objectsob,GV$sessionse,GV$sqlsq
!
L&^_f!
z9F_f0 WHERE lk.TYPEIN('TM','UL')ITPUB个人空间_~_n_`Z$i+H
\
ANDlk.SID=se.SIDITPUB个人空间Ik&u_Q&q_o0G_v
ANDlk.id1=ob.object_id(+)
%keo.s_g_t_Z2{0 AND(lk.inst_id=se.inst_id)ITPUB个人空间6l5Y/n_F"H_O_Q
ANDsq.address=se.sql_address;
4.通过AWR查询性能问题(默认有1小时延迟,不能查询当前时间的session)
SQL>conn/ASSYSDBAITPUB个人空间_A4P_R+@1Pv$x;}_u)r
SQL>@/u01/product/oracle/rdbms/admin/awrrpt.sql
!
I[_b_]_Y_p)P%j0输入report_type的值:
R
F
输入num_days的值:
2 ---现在到过去两天时间内的snapid(可以查看到).
E]_V's_t}(cD0输入begin_snap的值:
2147 ---输入的开始及结束的snapid对应您要查找的出现问题的时间段。
ITPUB个人空间#GO_@-dw!
a_^_Y
输入end_snap的值:
2182
_R9t;my:
H_l_C&i_L0输入report_name的值:
%\_n*|c_d7W1Z_g5z%`_I0Reportwrittentoawrrpt_1_2177_2182.html
:
@_l_x8t_I_|(K_{+Q0SQL>exit
下载awrrpt_1_2177_2182.html并打开查看。
ITPUB个人空间-w9h_B/J_I_@_M8[J5p
ASH保存了系统最新的处于等待的会话记录,可以用来诊断数据库的当前状态;ITPUB个人空间_h_W0e0{_L_Bzq_B)g4T
而AWR中的信息最长可能有1小时的延迟,所以其采样信息并不能用于诊断数据
$BEZ_M_[_s_K0库的当前状态,但可以用来作为一段时期内数据库性能调整的参考。
ITPUB个人空间)k_x_nxC_c_z
5. 查询物理读写严重的SQL及查询哪个SID最消耗资源
)p/|K3hxF5A0查看占I/O较大的正在运行的session
SELECTse.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,ITPUB个人空间:
f_}:
I_]%z:
^_?
se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changesITPUB个人空间_M/\_p_B_^
FROM v$sessionse,v$session_waitst,v$sess_iosi,v$processprITPUB个人空间_f_H_W!
M_c_Y
N"L%P
WHERE st.sid=se.sidANDst.sid=si.sidANDse.PADDR=pr.ADDRANDse.sid>6ANDst.wait_time=0
2z8r_v)Z_p_Lf#Z0 ANDst.eventNOTLIKE'%SQL%'ITPUB个人空间&]Q_@,m7u
ORDERBYphysical_readsDESC;
查询物理读写严重的SQL
SELECT*ITPUB个人空间_XM)^_Tq+H_i0Q0|_|"L__s
FROM (SELECT sql_text,module,ITPUB个人空间8z_k@_cs_w2Y
disk_reads/DECODE(executions,0,1,executions)AStt
_J7{"l_M'Q_e0 FROMv$sqlareaa
d&\_Ve:
N^0 ORDERBYttDESC)
m/Xt'O5I1j_{0 WHEREROWNUM<=20
查询哪个SID最消耗资源
selects.sid,s.value"CPUUsed"ITPUB个人空间_l_c
An8l'p_p
fromv$sesstats,v$statnamenITPUB个人空间1____N!
c8fd
wheres.statistic#=n.statistic#andn.name='CPUusedbythissession'ITPUB个人空间_`_S6D6gO_W_g!
U/t_y_K)b
ands.value>0
M2@___G_O5`,O_|!
L+R0orderby2desc;
ITPUB个人空间_@;n
X_k4h_t
查找前十条性能差的SQL
SELECT*FROM
)R_^_o_R.[^r0 (selectPARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text
_b0G0v`x_^0 FROMv$sqlareaorderBYdisk_readsDESC)ITPUB个人空间5F3j_W6{_N(C1t
whereROWNUM<10;
6. 查询物件统计信息是否不是最新的。
select*from dba_tables
T_\7C_F-O-R0wherewner='DFMS'andtable_name='TEST'andLAST_ANALYZED>=sysdate-1ITPUB个人空间j-a7q_gjIp+G_g
andstatus='VALID';
select*from dba_indexeswhereLAST_ANALYZEDITPUB个人空间'u3b_Am_F_{8C
wherewner='DFMS'andindex_name='IDX_TEST'andLAST_ANALYZED>=sysdate-1;
select*from dba_tab_columnswhereLAST_ANALYZEDITPUB个人空间;_'[Z!
K4w2M
wherewner='DFMS'andtable_name='TEST'andLAST_ANALYZED>=sysdate-1ITPUB个人空间_~_e1Vf_i+t_fb
and COLUMN_NAME='XXXXX';
_i2\z6k'B07.绑定变量窥视(Peeking)问题
9i,10g绑定变量窥视使得执行计划出现变化。
11g有改善。
如果出现此类ITPUB个人空间#}_K9Yv_|,t5^f
问题导致的性能问题,需要升级到11g,或者加入hint进行强制改变执行计划。
ITPUB个人空间6A_i_M_t_?
_d#c!
D
8.坏块导致系统性能(当然一般alertlog中都有error,查询坏块)
SELECTsegment_name,segment_type,extent_id,block_id,blocksITPUB个人空间_E_D9G5[%`7{
fromdba_extentst
_j,J_?
_W_C_p0where file_id=10
'y_`;ZI,e_dO0AND51896 betweenblock_idand(block_id+blocks-1)
_Y_g_qu_T_L7@08.Oracle9i,10gbuffercache及LibraryCache的命中率及其他
9i命中率:
K2o$`/u_Z_z_]_M0//oracle9idatabufferhitratio.
selecta.value+b.value"logical_reads",c.value"phys_reads",ITPUB个人空间_J)C;h/TJ'O
round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"
_S"`"v_P_Z_fk&}i0fromv$sysstata,v$sysstatb,v$sysstatcITPUB个人空间|5X&eZL
z_H\y
wherea.statistic#=40andb.statistic#=41andc.statistic#=42;
ITPUB个人空间'hD.?
_]_f_M
10g命中率:
_w$m_t+U_N
J0//oracle10gdatabufferhitratio.
selecta.value+b.value"logical_reads",c.value"phys_reads",
_e_Vb_H([#b_c0round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"ITPUB个人空间ZC_X_v"`_h)R_t7u9]
fromv$sysstata,v$sysstatb,v$sysstatc
9^"Y_u_s_^+S;Fi_k0wherea.statistic#=47andb.statistic#=50andc.statistic#=54;
监控SGALibraryCache的命中率,应该小于1%
selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",
~_G_x$Pc](W_B_C0sum(reloads)/sum(pins)*100libcache
uR_C%T.x_s_d0fromv$librarycache;
selectsum(pinhits-reloads)/sum(pins)"hitradio",s
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 性能 异常 查询 调整 脚本