SQL statement for Oracle check hit rate

The following SQL statement can be used to test the database performance 2 hours after the Oracle check hit rate starts. buffer hit rate: www.2cto.com buffer hit rate indicates the 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 Ratio" from v $ sysstat; greater than 98% is the best 2. data Dictionary cache hit rate: the data dictionary cache hit rate shows the percentage of memory read operations on the data dictionary and other objects. Select (1-(sum (getmisses)/sum (gets) * 100 "Hit Ratio" from v $ rowcache; greater than 98% is the best www.2cto.com 3. library cache hit rate: the Library cache hit rate shows the percentage of memory read operations on actual statements and PL/SQL objects. Note that a high hit rate is not always a good thing. Select Sum (Pins)/(Sum (Pins) + Sum (Reloads) * 100 "Hit Ratio" from V $ LibraryCache; greater than 98% is the best 4. PGA memory sorting hit rate automatic PGA memory management simplifies the method for allocating PGA memory. Oracle dynamically adjusts the PGA memory size in the workspace (based on 20% of the SGA memory size ). When running in automatic PGA memory management mode, the workspace size of all sessions is automatic. The total amount of available PGA memory in the active Workspace of the instance is automatically exported by SORT_AREA_SIZE or PGA _ AGGREGATE _ TARGET (preferred) initialization parameter. The PGA memory sorting rate should be greater than 98%. Based on the value of the initialization parameter PGA_AGGREGATE_TARGET (or SORT _ AREA _ SIZE for backward compatibility), user sorting may be completed in the memory or on the disk in the specified temporary tablespace, if the initialization parameter is not too high. Select. value "Disk Sorts", B. value "Memory Sorts", round (100 * B. value)/decode (. value + B. value), 0, 1, (. value + B. value), 2) "Pct Memory Sorts" from v $ sysstat a, v $ sysstat B where. name = 'sorts (disk) 'and B. name = 'sorts (memory) '; 5. the proportion of idle data buffers starts from the day when you first started the Oracle database, and users' queries start to use the memory. The number of idle records divided by the total number of records in the X $ BH table (that is, the total number of allocated data block buffers. Note that you must run the query with the SYS permission. In addition, having a large number of idle buffers is not necessarily the best environment. 5%-10% is the best. When the idle ratio is higher than 25%, the data buffer is too large and may waste resources. 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); 6. the ratio of the first 10 statements that waste the most memory to all statements is not adjusted, the access volume of the 10 most commonly used SQL statements in most systems accounts for more than 50% of the memory read operations in the system. This section measures the severity of the hazard that the Code most affects the performance has on the entire system, as a percentage. 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 <11; less than 5% is the best. 7. adjust the primary statement www.2cto.com for misuse of disk read operations. I found that in most systems, disk Read operations of statements with the first 25 visits occupy 75% of all disk and/or memory read operations of the system. Select disk_reads, substr (SQL _text, 1,4000) from v $ sqlarea order by disk_reads desc; 8. tables and their associated indexes should be placed on different physical disks to reduce file I/O. The above test can also be viewed through AWR and STATSPACK. in the analysis results, we should first look at ten items: 1. the first five waiting times (Scheduled Events) 2. load profile 3. instance efficiency (hit ratios) 4. wait time (Wait events) 5. latch waits 6. top SQL 7. instance activity 8. file I/0 and segment statistics (File I/0 and segement statistics) 9. memory allocation (Memory allocation) 10. buffer wait (Buffer waits)