oracle資料庫巡檢優化-快速定位資料庫瓶頸（轉）

--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%優化問題都能解決。