oracle資料庫巡檢最佳化-使用sql語句快速定位資料庫瓶頸

--資料庫巡檢或效能最佳化方法各異，但首要的是要發現資料庫效能瓶頸，系統自帶的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 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') ) 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: 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') ) 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
 
-- 10g

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語句，發現執行次數最多、記憶體、硬碟讀取量大的sql,根據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%最佳化問題都能解決。