Knowledge about the sharing pool of Oracle 10 Gb Memory Structure and Its Usage

Previously we introduced:Oracle 10 Gb Memory StructureIn this section, we will introduce the 10 Gb memory structure of Oracle.Sharing poolNext, let's take a look at this part.

The third component of SGA is the shared pool. The shared pool is the memory area for syntax analysis, compilation, and execution of SQL, pl, and SQL programs. The shared pool includes the library cache of the library Buffer and the Data Dictionary Buffer Data Directory Cache ). The database buffer contains the SQL statement analysis code and execution plan. The Data Dictionary Buffer contains the Table, column definition, and permissions obtained from the data dictionary. The user's global zone contains the user's MTS session information.

The shared pool is mainly used for syntax analysis, compilation, and execution of SQL and pl/SQL program statements. Therefore, if you use traditional Chinese medicine to run a large number of stored procedures or packages, you need to increase the size of the Shared Pool. The size of the Shared Pool is determined by the parameter pai_pool_size. To understand the size of the Shared Pool, use the following methods:

Method 1:

 
 

  
  
………  
  
  
Shared_Pool_size = 52428800 
  
  
….. 
 
 

Method 2:

 
 

  
  
SQL> select name,value from v$parameter where name like ‘%size’; 
 
 

Method 3:

 
 

  
  
SQL> show parameter share_pool_size 
 
 

The success rate of stored procedures and packages should be calculated in the shared pool.

You can query the data dictionary v $ rowcache to learn the number of successes and failures of the data dictionary.

 
 

  
  
SQL> select sum(gets) “dictionary gets”,  
  
  
Sum(getmisses) “dictionary cache getmisses”  
  
  
From v$rowcache ; 
 
 

Gets indicates the number of successful reads to a data dictionary type, and getsmisses indicates the number of failures to read a data dictionary type. In addition, you can use the query results to calculate the success rate of reading data dictionaries in the shared pool.

 
 

  
  
SQL> select parameter, get, getmisses, getmisses/(getmisses+gets)*100 “miss ratio”,  
  
  
(1-    (sum(getmisses)/(sum(getmisses)+sum(gets)) ) ) *100 “hit ratio”  
  
  
From v$rowcache  
  
  
Where gets+getmisses<>0  
  
  
Group by parameter,gets,getmisses; 
 
 

Querying the data dictionary v $ librarycache can calculate the failure rate of the database cache in the shared pool. The result should be less than 1%.

 
 

  
  
SQL>select sum(pins) “ total pins”, sum(reloads) “ total reloads”, sum(reloads)/sum(pins)*100 libarycache  from  v$librarycache; 
 
 

Total pins indicates the number of times the memory is resident, total reloads indicates the number of times the memory is reloaded, and librarycache indicates the failure rate.

We have analyzed the three components of the global system zone-data buffer, log buffer, and shared pool. To get the total size of SGA, you can use the show sga command in SQL * Plus.

SQL> show sga or query data dictionary

SQL> select * from v $ sga;

If you want to query the size of a parameter, You can query the data dictionary v _ $ sagstat. Through calculation, you can know the ratio of the space used by sga to the free space.

 
 

  
  
SQL> col OBJECT_NAME format a20
  
  
SQL> col idle space percentage (%) format 90.99
  
  
SQL> select name,
  
  
Sgasize/1024/1024 "allocated (M )",
  
  
Bytes/1024 "Free Space (k )",
  
  
Round (bytes/sagsize *, 2) "percentage of free space (% )"
  
  
From (select sum (bytes) sgasize from sys. v _ $ sgastat) s, sys. v _ $ sgastat f
  
  
Where f. name = 'free memory ';
 
 

Here is an introduction to the knowledge and usage of the Sharing pool of Oracle 10 Gb memory structure. I hope this introduction will be helpful to you!