Chinaunix
標(biāo)題: statspack使用筆記 3-4 [打印本頁]
作者: roothomes 時(shí)間: 2011-12-23 03:06
標(biāo)題: statspack使用筆記 3-4
statspack使用筆記 3
SQL優(yōu)化
Response Time(響應(yīng)時(shí)間) = Service Time(服務(wù)時(shí)間) + Wait Time(等待時(shí)間)
Service Time = CPU Parse + CPU Recursive + CPU Other
CPU other = CPU used by this session - parse time cpu - recursive cpu usage
Service Time from CPU used by this session or CPU used when call started(會(huì)話占用CPU的時(shí)間，CPU的服務(wù)時(shí)間)
Wait Time is the sum of time waited for non-idle Wait Events(會(huì)話脫離CPU處于等待狀態(tài)，非空閑等待時(shí)間的總和，空閑等待表示一個(gè)會(huì)話在數(shù)據(jù)庫實(shí)例中未工作，非空閑等待表示會(huì)話在數(shù)據(jù)庫實(shí)例中調(diào)用了其他的進(jìn)程進(jìn)行工作，例如DBWR從磁盤讀數(shù)據(jù)，此時(shí)沒有使用CPU)
CPU Parse from parse time cpu(CPU分析時(shí)間)
CPU Recursive from recursive cpu usage(CPU遞歸調(diào)用時(shí)間)
CPU Other from block accesses(邏輯讀，CPU工作時(shí)間)
例如：
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 4,776 73.32
db file sequential read 185,588 1,577 24.22
log file sync 115,489 93 1.43
log file sequential read 1,012 31 .48
db file parallel write 2,816 13 .20
-------------------------------------------------------------
Statistic Total (cs) per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 477,566 513.5 4.0
CPU used when call started 477,560 513.5 4.0
parse time cpu 7,374 7.9 0.1
recursive cpu usage 255,893 275.2 2.2
由上述數(shù)據(jù)計(jì)算出來:
Service Time = CPU time = CPU used when call started = 477560/100 s = 4776 s
Response Time = 4776/0.7332 = 6514 s
Wait Time = Response Time - Service Time = 6514 – 4776 = 1738 s
CPU Other = Service Time - parse time cpu - recursive cpu usage = 4776 – 74 - 2559
= 2143 s
計(jì)算各項(xiàng)數(shù)據(jù)響應(yīng)百分比:
CPU time% = CPU time/Response Time = 4776/6514 = 73.32%
CPU Other% = CPU Other/Response Time = 2143/6514 = 32.90%
CPU Parse% = parse time cpu/Response Time = 74/6514 = 1.14%
CPU Recursive% = recursive cpu usage/Response Time = 2559/6514 = 39.29%
Wait Time% = Wait Time/Response Time = 1738/6514 = 26.68%
db file sequential read% = db file sequential read/Response Time = 1577/6514 = 24.21%
statspack使用筆記 4
根據(jù)計(jì)算出來的響應(yīng)百分比，可以大致了解當(dāng)前系統(tǒng)的性能瓶頸。
如果CPU time占用的百分比較高，則觀察消耗CPU的是哪一部分：
主要用于CPU Parse，那么很有可能是大量SQL語句正在進(jìn)行硬分析(未綁定變量造成)。需要察看SQL ordered by Parse Calls for DB中的語句。
主要用于CPU Other，那么很有可能是SQL語句正在訪問大量的數(shù)據(jù)塊(全表掃描，全索引掃描，錯(cuò)誤的索引范圍掃描)。需要察看SQL ordered by Gets for DB中的語句。
如果磁盤I/O占用的百分比較高(db file scattered read，db file sequential read)，需要察看SQL ordered by Reads for DB中的語句
從操作系統(tǒng)中分析過高的CPU占用和磁盤I/O是由一個(gè)還是多個(gè)Oracle進(jìn)程引起的。
如果操作系統(tǒng)工具顯示大量CPU的消耗是由某些Oracle進(jìn)程占用，那么需要分析這些進(jìn)程在長時(shí)間的執(zhí)行那些SQL語句，可以通過作SQL TRACE進(jìn)行跟蹤或者觀察Session調(diào)用的SQL語句主要是那一些。
--檢查進(jìn)程的sql語句 通過TOP檢查性能
select p.spid, s.sid, q.sql_text, s.last_call_et, s.status
from v$process p,
v$session s,
v$sqltext q
where p.addr = s.paddr
and s.sql_address = q.address
and p.spid = 27053 --操作系統(tǒng)進(jìn)程號(hào)，需要根據(jù)實(shí)際情況進(jìn)行更改
order by s.sid, q.piece
如果CPU消耗分布在大多數(shù)的Oracle進(jìn)程中，Cpu Other在相應(yīng)時(shí)間中占有大部分，那么，需要分析statspack報(bào)告中的SQL ordered by Gets的語句，從中找到邏輯讀最高的SQL語句進(jìn)行優(yōu)化。
如果從操作系統(tǒng)中觀察到磁盤I/O很高(sar –d進(jìn)行觀察)，那么需要分析statspack報(bào)告中SQL ordered by Reads的語句，從中找到物理讀最高的SQL語句進(jìn)行優(yōu)化。
statspack使用筆記 4
檢查后臺(tái)作業(yè)和批量處理的事務(wù)，以及長時(shí)間處理的session。
--檢查運(yùn)行時(shí)間超過20s的session和sql語句
select p.spid, s.sid, q.sql_text, s.last_call_et, s.status
from v$process p,
v$session s,
v$sqltext q
where p.addr = s.paddr
and p.background <> 1
and s.sql_address = q.address
and s.status = 'ACTIVE'
and s.last_call_et > 20
order by s.sid, q.piece
| 歡迎光臨 Chinaunix (http://www.72891.cn/) |
Powered by Discuz! X3.2 |