Oracle Database Shared Pool optimization process

Oracle DatabaseOfShared poolIt is mainly composed of data_dictionary that stores data dictionaries, and library cache that stores SQL and PL/SQL code and execution plans. It also includes several other buffers for different system features and technologies, such as the UGA provided for the shared server mode. This article describes the optimization process of the Shared Pool. Let's take a look at it.

Optimization of shared pool:

1) Based on the Setup experience, for example, you can set shared_pool_size = sga_target * (10% ~ 15% ).

2) focus on saving the library cache metrics of SQL and PL/SQL code and execution plans. View the Load Profile section of the AWR report and analyze indicators such as Hard Parses/s. Analyze Library Hit %, Execute to Parse %, Soft Parse %, and so on in Instance Efficiency Percentages (Target 100%.

Wait events that require attention:

Latch: library cache.

Latch: shared pool.

3) view the parse Time elapsed and hard parse elapsed time indicators related to the shared pool in the time Model Statistics ).

If the proportion of hard parse elapsed time is high, it indicates that the statement sharing of the application has a serious problem.

Optimization Method:

1) Evaluate statement sharing

Execute to Parse % = (execute times-Parse times)/Execute times X 100%

If Execute to Parse % is too low, it indicates that the resolution frequency is very high and the overall system sharing is poor. Generally, this metric is more than 70%, which indicates that the statement sharing is good.

Library Hit %, Soft Parse %, and Hard Parses/s in the AWR report. Parse contains Hard Parse and Soft Parse times, but we should pay attention to Hard Parses.

1 ):

 
 

  
  
select sql_text from v$sqlarea where executions=1 order by upper(sql_text);  
 
 

2) use the shared pool advisory to set a reasonable shared_pool_size. You can also set the shared_pool_reserved_size parameter to make some large PL/SQL objects resident in the memory to reduce the possibility of fragmentation in the shared pool.

 
 

  
  
SQL> set lines 100    
  
  
 
  
  
SQL> set pages 999    
  
  
 
  
  
SQL> column c1     heading 'Pool |Size(M)'    
  
  
 
  
  
SQL> column c2     heading 'Size|Factor'    
  
  
 
  
  
SQL> column c3     heading 'Est|LC(M)  '    
  
  
 
  
  
SQL> column c4     heading 'Est LC|Mem. Obj.'    
  
  
 
  
  
SQL> column c5     heading 'Est|Time|Saved|(sec)'    
  
  
 
  
  
SQL> column c6     heading 'Est|Parse|Saved|Factor'    
  
  
 
  
  
SQL> column c7     heading 'Est|Object Hits'   format 999,999,999    
  
  
 
  
  
SQL> SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,    
  
  
 
  
  
2  estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,    
  
  
 
  
  
3  estd_lc_time_saved_factor c6,estd_lc_memory_object_hits c7 FROM V$SHARED_POOL_ADVICE;    
  
  
 
  
  
                                                 Est        Est    
  
  
 
  
  
                                                Time      Parse    
  
  
 
  
  
   Pool        Size        Est     Est LC      Saved      Saved          Est    
  
  
 
  
  
 Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits    
  
  
 
  
  
---------- ---------- ---------- ---------- ---------- ---------- ------------     
  
  
 
  
  
      64         .4         18       2799        510      .9677       38,723    
  
  
 
  
  
      80         .5         33       4192        518      .9829       39,201    
  
  
 
  
  
      96         .6         48       5700        527          1       39,890    
  
  
 
  
  
     112         .7         60       7288        527          1       40,104    
  
  
 
  
  
     128         .8         60       7288        527          1       40,106    
  
  
 
  
  
     144         .9         60       7288        527          1       40,106    
  
  
 
  
  
     160          1         60       7288        527          1       40,106    
  
  
 
  
  
     176        1.1         60       7288        527          1       40,106    
  
  
 
  
  
     192        1.2         60       7288        527          1       40,106    
  
  
 
  
  
     208        1.3         60       7288        527          1       40,106    
  
  
 
  
  
     224        1.4         60       7288        527          1       40,106    
  
  
 
  
  
     240        1.5         60       7288        527          1       40,106    
  
  
 
  
  
     256        1.6         60       7288        527          1       40,106    
  
  
 
  
  
     272        1.7         60       7288        527          1       40,106    
  
  
 
  
  
     288        1.8         60       7288        527          1       40,106    
  
  
 
  
  
     304        1.9         60       7288        527          1       40,106    
  
  
 
  
  
     320          2         60        7288        527          1       40,106   
 
 

3) Reasonably set the large_pool_size Parameter

The large pool buffer is used in scenarios such as backup recovery, parallel processing, ASM, shared connection mode, and simulation of asynchronous I/O operations. A reasonable large_pool_size should be set to avoid using the shared pool buffer, increase the shortage of shared pool buffer space and the possibility of fragmentation.

Note:Not all SQL statements need to be shared. for Statistical Report SQL statements that consume a large amount of resources for a single transaction and have a low concurrency, the execution plan should be optimal, you do not need to bind variables.

The optimization process of the Oracle database Shared Pool is introduced here. If you want to know