您當前位置: 唯學網 » IT認證培訓 » IT認證培訓教育新聞 »

java新手必看:經驗總結oracle性能調優:Oracle調優命中率

java新手必看:經驗總結oracle性能調優:Oracle調優命中率

唯學網 • 教育培訓

2016-9-20 14:02

IT認證

唯學網 • 中國教育電子商務平臺

加入收藏

oracle性能調優:Oracle調優命中率這些你都知道嗎?熟練的掌握一些知識,對自己是有很大幫助的,下面小編分享給大家。

1)共享區字典緩存區命中率

計算公式:SUM(gets - getmisses - usage -fixed) / SUM(gets)

命中率應大于0.85

SQL>select sum(gets-getmisses-usage-fixed)/sum(gets)

from v$rowcache;

2)數據高速緩存區命中率

計算公式:1-(physical reads / (db block gets + consistent gets))

命中率應大于0.90最好

SQL>select name,value

from v$sysstat

where name in ('physical reads','db block gets','consistent gets');

3)共享區庫緩存區命中率

計算公式:SUM(pins - reloads) / SUM(pins)

命中率應大于0.99

SQL>select sum(pins-reloads)/sum(pins)

from v$librarycache;

4)檢測回滾段的爭用

SUM(waits)值應小于SUM(gets)值的1%

SQL>select sum(gets),sum(waits),sum(waits)/sum(gets)

from v$rollstat;

5)檢測回滾段收縮次數

SQL>select name,shrinks

from v$rollstat, v$rollname

where v$rollstat.usn = v$rollname.usn;

幾個常用的檢查語句

1. 查找排序最多的SQL:

SQL>SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS

FROM V$SQLAREA

ORDER BY SORTS DESC;

2.查找磁盤讀寫最多的SQL:

SQL>SELECT * FROM

(SELECT sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" FROM v$sql

WHERE executions>0 and is_obsolete='N' ORDER BY 4 desc)

WHERE ROWNUM<11 ;

3.查找工作量最大的SQL(實際上也是按磁盤讀寫來排序的)

SQL>select substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes,p.sql_text

from(select address,disk_reads,executions,pct,rank() over (order by disk_reads desc) ranking from

(select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over () pct

from sys.v_$sql

where command_type != 47)

where disk_reads > 50 * executions) s,sys.v_$sqltext p

where s.ranking <= 5 and p.address = s.address

order by 1, s.address, p.piece;

4. 用下列SQL工具找出低效SQL:

SQL>select executions,disk_reads,buffer_gets,round((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,round(disk_reads/executions,2) reads_per_run,sql_text

From v$sqlarea

Where executions>0 and buffer_gets >0 and (buffer_gets-disk_reads)/buffer_gets<0.8

Order by 4 desc;

5、根據sid查看對應連接正在運行的sql

SQL>select /*+ push_subq */command_type,sql_text,sharable_mem,persistent_mem,

runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,

users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,

rows_processed,sysdate start_time,sysdate finish_time,'>'||address sql_address,'N'status

From v$sqlarea

Where address=(select sql_address from v$session where sid=&sid);

***************Oracle 緩沖區命中率低的分析及解決辦法******************

首先確定下面的查詢結果:

1,緩沖區命中率的查詢(是否低于90%):

select round((1 - sum(decode(name,'physical reads',value,0)) /

(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0))) ),4) *100 || '%' chitrati

from v$sysstat;

2,使用率的查詢(有無free狀態的數據快。):

select count(*), status from v$bh group by status ;

3,相關等待事件的查詢(是否有相關等待事件)

select event,total_waits from v$system_event where event in ('free buffer waits');

4,當前大小(是否已經很大)

select value/1024/1024 cache_size from v$parameter where name='db_cache_size'

5,top等待事件分析(Db file scatered read的比率是否大)

select event ,total_waits,suml

from

(select event,total_waits,round(total_waits/sumt*100,2)||'%' suml

from

(select event,total_waits from v$system_event ),

(select sum(total_waits) sumt from v$system_event)

order by total_waits desc)

where rownum<6

and event not like 'rdbms%'

and event not like 'pmon%'

and event not like 'SQL*Net%'

and event not like 'smon%';

6,db_cache_advice建議值(9i后的新特性,可以根據他更好的調整cache_size)

select block_size,size_for_estimate,size_factor,estd_physical_reads from v$db_cache_advice;

以上內容是關于java新手必看:經驗總結oracle性能調優:Oracle調優命中率的介紹,要想了解更多相關信息、教育培訓內容,請隨時關注唯學網,小編會第一時間為大家更新、跟進最新信息。

0% (0)
0% (10)
已有條評論