oracle資料庫巡檢最佳化-快速定位資料庫瓶頸
--資料庫巡檢或效能最佳化方法各異，但首要的是要發現效能瓶頸，系統自帶的statspack,或awr太耗
時，
--以下是本人常用的方法，共享之
--1、查詢資料庫等待事件top10,關注前前幾個等待事件，關注前三個等待事件是否有因果或關
聯關係
--oracle 9i
select
t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent
from (
SELECT SUM(time_waited) w1 FROM v$system_event WHERE event
NOT IN
('smon timer','pmon timer','rdbms ipc message','Null
event','parallel query dequeue','pipe get',
'client message','SQL*Net
message to client','SQL*Net message from client','SQL*Net more from client',
'dispatcher
timer','virtual circuit status','lock manager wait for remote
message','PX Idle Wait',
'PX Deq: Execution Msg','PX Deq: Table Q
Normal','wakeup time manager','slave wait','i/o slave wait',
'jobq
slave wait','null event','gcs remote message','gcs for action','ges
remote message','queue messages') ) t1,
(select * from (
select
t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum
num from
(select
event,total_waits,total_timeouts,time_waited,average_wait from
v$system_event where event not in
('smon timer','pmon timer','rdbms
ipc message','Null event','parallel query dequeue','pipe get',
'client
message','SQL*Net message to client','SQL*Net message from
client','SQL*Net more data from client',
'dispatcher timer','virtual
circuit status','lock manager wait for remote message','PX Idle Wait',
'PX
Deq: Execution Msg','PX Deq: Table Q Normal','wakeup time
manager','slave wait','i/o slave wait',
'jobq slave wait','null
event','gcs remote message','gcs for action','ges remote message','queue
messages')
order by time_waited desc ) t) where num<11) t2,
(SELECT
VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' )
t3
--oracle10g
select
t2.event,round(100*t2.time_waited/(t1.w1+t3.cpu),2) event_wait_percent
from (
SELECT SUM(time_waited) w1 FROM v$system_event WHERE event
NOT IN
('smon timer','pmon timer','rdbms ipc message','Null
event','parallel query dequeue','pipe get','client message','SQL*Net
message to client','SQL*Net message from
client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX
Deq: Table Q Normal','wakeup time manager','slave wait',
'i/o
slave wait','jobq slave wait','null event','gcs remote message','gcs
for action','ges remote message','queue messages','wait for unread
message on broadcast
channel','PX Deq Credit: send blkd','PX Deq: Execute Reply','PX Deq: Signal ACK','PX Deque wait','PX Deq Credit: need buffer','STREAMS apply coord waiting for slave
message',
'STREAMS
apply slave waiting for coord message', 'Queue Monitor Wait', 'Queue
Monitor Slave Wait', 'wakeup event for builder', 'wakeup event for
preparer', 'wakeup
event for reader',
'wait for activate
message', 'PX Deq: Par Recov Execute','PX Deq: Table Q Sample','STREAMS
apply slave idle wait','STREAcapture process filter callback wait for
ruleset','STREAMS
fetch slave waiting for txns',
'STREAMS waiting for subscribers to
catch up','Queue Monitor Shutdown Wait','AQ Proxy Cleanup
Wait','knlqdeq','class slave wait','master wait','DIAG idle wait',
'ASM
background timer','KSV master wait','EMON idle wait','Streams AQ: qmn coordinator idle
wait','Streams AQ: qmn coordinator idle wait','Streams AQ: qmn slave
idle
wait',
'Streams AQ: waiting for time management or cleanup
tasks','Streams AQ: waiting for messages in the queue','Streams fetch
slave: waiting for txns','Streams AQ:
deallocate messages from
Streams Pool',
'Streams AQ: delete acknowledged messages','LNS ASYNC
archive log','LNS ASYNC dest activation','LNS ASYNC end of
log','LogMiner: client waiting for transaction',
'LogMiner: slave
waiting for activate message','LogMiner: wakeup event for
builder','LogMiner: wakeup event for preparer','LogMiner: wakeup event
for reader') ) t1,
(select * from (
select
t.event,t.total_waits,t.total_timeouts,t.time_waited,t.average_wait,rownum
num from
(select
event,total_waits,total_timeouts,time_waited,average_wait from
v$system_event where event not in
('smon timer','pmon timer','rdbms
ipc message','Null event','parallel query dequeue','pipe get','client
message','SQL*Net message to client','SQL*Net message from
client','SQL*Net more data from client','dispatcher timer','virtual circuit status','lock manager wait for remote message','PX Idle Wait','PX Deq: Execution Msg','PX
Deq: Table Q Normal','wakeup time
manager','slave wait',
'i/o slave wait','jobq slave wait','null
event','gcs remote message','gcs for action','ges remote message','queue
messages','wait for unread message on broadcast
channel','PX Deq Credit: send blkd','PX Deq: Execute Reply','PX Deq: Signal ACK','PX Deque wait','PX Deq Credit: need buffer','STREAMS apply coord waiting for slave
message',
'STREAMS apply slave waiting for coord
message', 'Queue Monitor Wait', 'Queue Monitor Slave Wait', 'wakeup
event for builder', 'wakeup event for preparer', 'wakeup
event for
reader',
'wait for activate message', 'PX Deq: Par Recov
Execute','PX Deq: Table Q Sample','STREAMS apply slave idle
wait','STREAcapture process filter callback wait for
ruleset','STREAMS
fetch slave waiting for txns',
'STREAMS waiting for subscribers to
catch up','Queue Monitor Shutdown Wait','AQ Proxy Cleanup
Wait','knlqdeq','class slave wait','master wait','DIAG idle wait',
'ASM
background timer','KSV master wait','EMON idle wait','Streams AQ: RAC
qmn coordinator idle wait','Streams AQ: qmn coordinator idle
wait','Streams AQ: qmn slave
idle wait',
'Streams AQ: waiting
for time management or cleanup tasks','Streams AQ: waiting for messages
in the queue','Streams fetch slave: waiting for txns','Streams AQ:
deallocate
messages from Streams Pool',
'Streams AQ: delete acknowledged
messages','LNS ASYNC archive log','LNS ASYNC dest activation','LNS ASYNC
end of log','LogMiner: client waiting for transaction',
'LogMiner:
slave waiting for activate message','LogMiner: wakeup event for
builder','LogMiner: wakeup event for preparer','LogMiner: wakeup event
for reader')
order by time_waited desc ) t) where num<11) t2,
(SELECT
VALUE CPU FROM v$sysstat WHERE NAME LIKE 'CPU used by this session' )
t3
--2、查詢各種指標，根據各種指標反映的問題是否和等待事件有關聯關係，進一步確定問題所在
--9i
select
a.cache_hit_percent,e.rowcache_hitratio,d.pin_ration_percent,d.get_ratio_percent,f.mem_sort_percent
,b.latch_ratio_percent from
(SELECT
ROUND((1-((s1.VALUE-s4.VALUE-s5.VALUE)/(s2.VALUE+s3.VALUE-s4.VALUE-s5.VALUE)))*100,2)
cache_hit_percent FROM v$sysstat s1, v$sysstat s2, v$sysstat s3,
v$sysstat
s4, v$sysstat s5
WHERE s1.NAME='physical reads' AND
s2.NAME='consistent gets'
AND s3.NAME='db block gets' AND
s4.NAME='physical reads direct (lob)' AND s5.NAME='physical reads
direct') a,
(select round(100*(1-sum(misses)/sum(gets)),2)
latch_ratio_percent from v$latch) b,
(select
round(100*c.pin_RATIO/b.total,2) pin_ration_percent,
round(100*(a.get_ratio/b.total),2) get_ratio_percent from (
select
sum(pinhitratio) pin_ratio from v$LIBRARYCACHE) c,(select
sum(gethitratio) get_ratio from v$LIBRARYCACHE) a,
(select count(*)
total from v$LIBRARYCACHE) b) d,(select
round(100*(1-sum(getmisses)/sum(gets)),2) rowcache_hitratio from
v$rowcache ) e,
(SELECT round(100*s1.VALUE/(s2.VALUE+s1.VALUE),2)
mem_sort_percent FROM v$sysstat s1 ,v$sysstat s2 WHERE s1.NAME='sorts
(memory)' AND s2.NAME='sorts (disk)')f
--
select
a.cache_hit_percent,e.rowcache_hitratio,d.pin_ration_percent,d.get_ratio_percent,f.mem_sort_percent
,b.latch_ratio_percent from
(SELECT
ROUND((1-((s1.VALUE-s4.VALUE-s5.VALUE)/(s2.VALUE+s3.VALUE-s4.VALUE-s5.VALUE)))*100,2)
cache_hit_percent FROM v$sysstat s1, v$sysstat s2, v$sysstat s3,
v$sysstat
s4, v$sysstat s5
WHERE s1.NAME='physical reads' AND
s2.NAME='consistent gets'
AND s3.NAME='db block gets' AND
s4.NAME='physical reads direct (lob)' AND s5.NAME='physical reads
direct') a,
(select round(100*(1-sum(misses)/sum(gets)),2)
latch_ratio_percent from v$latch) b,
(select
round(100*c.pin_RATIO/b.total,2) pin_ration_percent,
round(100*(a.get_ratio/b.total),2) get_ratio_percent from (
select
sum(pinhitratio) pin_ratio from v$LIBRARYCACHE) c,(select
sum(gethitratio) get_ratio from v$LIBRARYCACHE) a,
(select count(*)
total from v$LIBRARYCACHE) b) d,(select
round(100*(1-sum(getmisses)/sum(gets)),2) rowcache_hitratio from
v$rowcache ) e,
(SELECT round(100*s1.VALUE/(s2.VALUE+s1.VALUE),2)
mem_sort_percent FROM v$sysstat s1 ,v$sysstat s2 WHERE s1.NAME='sorts
(memory)' AND s2.NAME='sorts (disk)')f
--3、產看記憶體配置，檢視記憶體配置是否合理（結合查詢到
的各種指標，初步判斷記憶體是否合理）
--9i
SELECT NAME,ROUND(VALUE/1024/1024) size_M
FROM v$parameter WHERE NAME IN
('java_pool_size','large_pool_size','shared_pool_size','log_buffer','db_cache_size','pga_aggregate_target','sga_max_size','db_recycle_cache_size','db_keep_cache_size')
ORDER
BY size_m DESC;
--10g
select component,current_size/1024/1024
cur_size_M ,max_size/1024/1024
max_size_M from
v$sga_dynamic_components
--
4、查詢三個top10sql語句，發現執行次數最多、記憶體、硬碟讀取量大的,
根據address查詢在系統中的執行計劃，是否需要進一步最佳化
select '硬碟讀取量最大的top '||rownum
t1_id,sql_disk_reads,disk_reads,address from (select sql_text
sql_disk_reads ,disk_reads from v$sqlarea order by disk_reads desc)
where
rownum<11
union all
select '高速緩衝區使用最大的top '||rownum t2_id
,sql_buffer_gets ,buffer_gets,address from (select sql_text
sql_buffer_gets,buffer_gets from v$sqlarea order by
buffer_gets
desc) where rownum<11
union all
select '執行次數最多的top '||rownum
t3_id ,sql_executions ,executions,address from (select sql_text
sql_executions,executions from v$sqlarea order by executions desc)
where rownum<11
--根據address查詢在系統中的執行計劃（真實執行計劃），這個不同set autotrace
on（這個是預執行計劃，真實情況不一定，
--這也是很多時候我們執行很好，但實際效率較低不得其解的原因）
select
lpad(' ', 2*(level-1))||operation||' '||decode(id, 0, 'Cost =
'||position) "OPERATION",options, object_name
from v$sql_plan
start
with (address ='' and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value =
hash_value
and prior child_number = child_number
order
by id, position ;
--5、查詢系統執行較長的sql語句，該語句能將多數的耗費資源的sql抓取到
select
q.sql_text,s.elapsed_seconds,s.start_time,s.opname from
v$session_longops s,
v$sqlarea q where
s.sql_hash_value=q.hash_value order by s.ELAPSED_SECONDS desc;
-- 根據address查詢在系統中的執行計劃，是否需要最佳化
select lpad(' ',
2*(level-1))||operation||' '||decode(id, 0, 'Cost = '||position)
"OPERATION",options, object_name
from v$sql_plan
start with
(address ='' and id=0 )
connect by prior id = parent_id
and prior address = address
and prior hash_value =
hash_value
and prior child_number = child_number
order
by id, position ;
--6、找到需要最佳化的sql語句，嘗試最佳化，檢視執行計劃（set autotrace
traceonly）,直到滿意為止
--7、檢視各個表空間的使用狀況，注意收益率超過80%的表空間
SELECT
tablespace_name,ROUND(used_percent,2) used_percent,ROUND(total_M,2)
total_M,ROUND(used_M,2) used_M FROM (
SELECT d.tablespace_name ,
NVL(SUM(used_blocks),0)*8/1024 used_M,
SUM(blocks)*8/1024 total_m
,NVL(SUM(used_blocks),0)*100/SUM(blocks) used_percent
FROM
v$sort_segment v ,dba_temp_files d WHERE
d.tablespace_name=v.tablespace_name(+)
GROUP BY d.tablespace_name
UNION
ALL
SELECT ts.NAME tablespace_name, ts.size_M-fs.size_M used_M ,
ts.size_M total_M ,(ts.size_M-fs.size_M)/ts.size_M*100 used_percent
FROM
(SELECT ts.NAME ,SUM(bytes)/1024/1024 size_M
FROM v$datafile
df,v$tablespace ts WHERE df.ts#=ts.ts# GROUP BY ts.NAME ) ts , (SELECT
tablespace_name ,SUM(bytes/1024/1024) size_M
FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) fs WHERE ts.NAME=fs.tablespace_name )
ORDER BY used_percent DESC;
--8、檢視最大的前10大欄位，是不是耗費資源的sql都是和這些大段有關
Select
* from (Select segment_name,bytes/1024/1024
size_M,segment_type,tablespace_name
from dba_segments order by bytes desc ) where rownum<11
以上是一 個較為快捷的方式，短時間內定位問題，通常80%最佳化問題都能解決。
come from：http://space.itpub.net/24179204/viewspace-666873
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-667545/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫巡檢優化-快速定位資料庫瓶頸Oracle資料庫優化
- oracle資料庫巡檢優化-快速定位資料庫瓶頸（轉）Oracle資料庫優化
- oracle資料庫巡檢最佳化-使用sql語句快速定位資料庫瓶頸Oracle資料庫SQL
- oracle快速定位資料庫瓶頸Oracle資料庫
- Oracle資料庫巡檢Oracle資料庫
- SQL Server 資料庫 最佳化 效能瓶頸SQLServer資料庫
- oracle資料庫巡檢(轉）Oracle資料庫
- [轉]檢測SQLSERVER資料庫CPU瓶頸及記憶體瓶頸SQLServer資料庫記憶體
- oracle資料庫巡檢內容Oracle資料庫
- oracle資料庫巡檢（二）全面檢查Oracle資料庫
- 資料庫巡檢模版資料庫
- Oracle資料庫(RAC)巡檢報告Oracle資料庫
- Oracle資料庫巡檢參考項Oracle資料庫
- Oracle資料庫(單機)巡檢報告Oracle資料庫
- SQL Server資料庫巡檢SQLServer資料庫
- LightDB資料庫效能瓶頸分析（一）資料庫
- 資料庫效能監控瓶頸理論資料庫
- oracle資料庫巡檢（一）基本sql語句Oracle資料庫SQL
- 使用RDA巡檢MYSQL資料庫MySql資料庫
- 資料庫巡檢參考項資料庫
- 宜信資料庫實踐|解讀Oracle AWR效能分析報告，更快定位效能瓶頸資料庫Oracle
- MySQL資料庫健康檢查--MySQL巡檢MySql資料庫
- MSSQL資料庫健康檢查--SQL Server巡檢SQL資料庫Server
- 4篇論文入選資料庫頂會，騰訊雲突破資料庫效能瓶頸資料庫
- 陌生Oracle的資料庫環境的巡檢內容Oracle資料庫
- 資料庫巡檢常用的SQL語句資料庫SQL
- 讓資料庫不再成為業務發展瓶頸——分散式資料庫架構設計資料庫分散式架構
- 解決資料庫高併發訪問瓶頸問題資料庫
- DB2資料庫故障與效能瓶頸診斷思路DB2資料庫
- 小麥苗資料庫巡檢指令碼V7.0，支援Oracle、MySQL、SQL Server和PG資料庫資料庫指令碼OracleMySqlServer
- 資料庫叢集伺服器系統效能瓶頸分析(zt)資料庫伺服器
- 檢視oracle資料庫----sizeOracle資料庫
- Oracle 資料庫巡檢指令碼 單例項 RAC 輸出HTML格式Oracle資料庫指令碼單例HTML
- 紹Oracle資料庫的最佳化之資料庫磁碟I/OOracle資料庫
- 如何解決SQL Server資料庫的軟硬體效能瓶頸OCSQLServer資料庫
- Oracle資料庫快速Drop 大表Oracle資料庫
- oracle資料庫最佳化基礎Oracle資料庫
- 資料庫相關的系統巡檢參考項資料庫