Oracle Database debugging and Optimization

This article mainly introduces the debugging and optimization of Oracle databases, as well as answers to questions related to the life rate in Oracle databases, including performance comparison between different algorithms. The following describes the related content.

Computing of various hit rates in Oracle and related Tuning

1) hit rate of Library Cache:

Calculation formula:

 
 

  
  
Library Cache Hit Ratio = sum(pinhits) / sum(pins)  
  
  
SQL>SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;  
 
 

Generally, it is more than 98%. Otherwise, you need to consider increasing the sharing pool, binding variables, and modifying cursor_sharing and other parameters.

2) Calculate the memory usage of the Shared Pool:

 
 

  
  
SQL>SELECT (1 - ROUND(BYTES / 
(&TSP_IN_M * 1024 * 1024), 2)) * 100 
|| '%' FROM V$SGASTAT WHERE NAME = 
'free memory' AND POOL = 'shared pool'; 
 
 

Among them: & TSP_IN_M is the SIZE (M) of your total shared pool. The memory usage of the shared pool should be stable between 75% and 90%, which is too small to waste memory. If it is too large, the memory is insufficient.

Query idle Shared Pool memory:

 
 

  
  
SQL>SELECT * FROM V$SGASTAT WHERE NAME = 
'free memory' AND POOL = 'shared pool'; 
 
 

3) db buffer cache hit rate:

Calculation formula:

 
 

  
  
Hit ratio = 1 - [physical reads/(block gets + consistent gets)]  
  
  
SQL>SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) 
"Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';  
 
 

Generally, it should be above 90%. Otherwise, you need to adjust and increase DB_CACHE_SIZE. Another method for calculating the hit rate (from the official ORACLE documentation <Oracle database performance optimization> ): the hit rate is calculated as follows:

 
 

  
  
Hit Ratio = 1 - ((physical reads - physical reads 
direct - physical reads direct (lob)) / 
(db block gets + consistent gets - physical 
reads direct - physical reads direct (lob)) 
 
 

The Buffer cache hit rate is obtained by inserting the result values in the previous query.

 
 

  
  
SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME 
IN('session logical reads', 'physical reads', 
'physical reads direct', 'physical reads direct 
(lob)', 'db block gets', 'consistent gets'); 
 
 

4) data buffer hit rate:

 
 

  
  
SQL> select value from v$sysstat where name =
'physical reads'; SQL> select value from v$sysstat where name 
='physical reads direct'; SQL> select value from v$sysstat 
where name ='physical reads direct (lob)'; SQL> 
select value from v$sysstat where name ='consistent gets'; 
SQL> select value from v$sysstat where name = 'db block gets'; 
 
 

Here, the hit rate is calculated to make

 
 

  
  
x = physical reads direct + physical reads direct (lob) 
 
 

Hit rate

 
 

  
  
=100 - ( physical reads - x) / (consistent gets + db block gets - x)*100 
 
 

If the hit rate is lower than 90%, you should adjust the application to determine whether to increase the data buffer.

5) hit rate of the Shared Pool:

 
 

  
  
SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache; 
 
 

If the hit rate of the Shared Pool is lower than 95%, you should consider adjusting the application (usually not using bind var) or increasing the memory.

6) calculate the ratio of sorting in memory:

 
 

  
  
SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)'; 
 
 

-Query the memory sorting count

 
 

  
  
SQL>SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)'; 
 
 

-Query the number of disk orders

 
 

  
  
--caculate sort in memory ratio SQL>SELECT 
round(&sort_in_memory/
(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual; 
 
 

The larger the ratio, the better. If the ratio is too small, you need to consider adjusting and increasing the PGA.

7) PGA hit rate:

Calculation formula: BP x 100/(BP + EBP)

BP: bytes processed

EBP: extra bytes read/written

 
 

  
  
SQL>SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage'; 
 
 

Alternatively, you can view a view in the OEM graphic interface to obtain the recommended Oracle values:

 
 

  
  
SQL>SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) 
target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, 
ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE; The output 
of this query might look like the following: TARGET_MB 
CACHE_HIT_PERC ESTD_OVERALLOC_COUNT 63 23 367 125 24 30 
250 30 3 375 39 0 500 58 0 600 59 0 700 59 0 800 60 0 900 60 0 
 
 

In this example, the PGA should be allocated at least 375 mb. I personally think that the PGA hit rate should not be lower than 50%. The following SQL statements count the number of times SQL statements are executed in three modes:

 
 

  
  
optimal memory size, one-pass memory size, multi-pass memory size:  
  
  
SQL>SELECT name profile, cnt, decode
(total, 0, 0, round(cnt*100/total,4)) percentage FROM 
(SELECT name, value cnt, (sum(value) over ()) 
total FROM V$SYSSTAT WHERE name like 'workarea exec%');