SQL statement for Oracle check hit rate

2 hours after the database is started, you can use the following SQL to test the database performance.

 

1. Buffer hit rate:

Buffer hit rate indicates the frequency of finding common data blocks in the memory structure without disk access.

 

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;

98% or more is the best

 

2. Data Dictionary cache hit rate:

The cache hit rate of the data dictionary 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;

98% or more is the best

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;

98% or more is the best

 

4. PGA memory sorting hit rate

Automatic PGA memory management simplifies the method of 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. Percentage of idle data buffers

 

From the day when you first start the Oracle database, your 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. Percentage of the first 10 statements with the most wasted memory to all statements

Without adjustment, 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 entire 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 statements for misuse of disk read Operations

I found that, without making any adjustments, 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 tests 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 CTR (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)