Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to check the Oracle hit rate, the article is very detailed, has a certain reference value, interested friends must read it!

During normal database use, you can test database performance with the following SQL:

SQL > select (1-(sum (decode (name,' physical reads',value,0)) / (sum (decode (name,' db block gets',value,0)) + sum (decode (name,'consistent gets',value,0) * 100 "HIT RATE" from v$sysstat)

HIT RATE

-

99.8493296

SQL > select (1-(sum (getmisses) / sum (gets) * 100 "HIT RATE" from v$rowcache

HIT RATE

-

98.1750126

SQL > select Sum (Pins) / (Sum (Pins) + Sum (Reloads)) * 100 "HIT RATE" from V$LibraryCache

HIT RATE

-

99.9905342

SQL > select a.value "Disk Sorts", b.value "Memory Sorts", round ((100*b.value) / decode ((a.value+b.value), 0from v$sysstat 1, (a.value+b.value)), 2) "Memory Sorts%" from v$sysstat a, v$sysstat b wherea.name = 'sorts (disk)' and b.name = 'sorts (memory)'

Disk Sorts Memory Sorts Memory Sorts%

SQL > select decode (state,0,'FREE',1,decode (lrba_seq,0,'AVAILABLE','BEING USED'), 3, 'BEING USED', state) "BLOCK STATUS", count (*) from x$bh group by decode (state,0,'FREE',1,decode (lrba_seq,0,'AVAILABLE','BEING USED'), 3,' BEING USED', state)

BLOCK STATUSCOUNT (*)

BEING USED1876

SQL > select sum (pct_bufgets) "Percent" from (select rank () over (order by buffer_gets desc) as rank_bufgets,to_char (100 * ratio_to_report (buffer_gets) over (), '999.99') pct_bufgets from v$sqlarea) where rank_bufgets

Select disk_reads, substr (sql_text,1,4000) from v$sqlareaorder by disk_reads desc

Tables and the indexes associated with them should be placed on different physical disks in order to reduce the number of files.

The above tests can also be viewed through AWR and STATSPACK. In the results of the analysis, the first ten things we need to look at are:

1. Top 5 waiting times (scheduled events)

two。 Load profile (Load profile)

3. Instance efficiency click-through rate (Instance efficiency hit ratios)

4. Wait time (Wait events)

5. Latch wait (Latch waits)

6. Primary SQL (Top SQL)

7. Instance activity (Instance activity)

8. File Imax 0 and segment statistics (File Imax 0 and segement statistics)

9. Memory allocation (Memory allocation)

10. Buffer wait (Buffer waits)

-- End

The above is all the contents of the article "how to check Oracle hit rate". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.