Oracle SGA Optimization

Oracle's SGA: The system global area of a database, the SGA consists mainly of three parts: a shared pool, a data buffer, and a log buffer.

1. A shared pool is made up of two parts: a shared SQL area and a data dictionary buffer.

   the shared SQL zone is dedicated to user SQL commands and Oracle uses the least recently used priority algorithm to update the overlay; the data dictionary buffer (library cache) holds dynamic information for the database to run. After the database has been running for some time, the DBA needs to look at the hit ratios of these memory areas to tune the database performance from a database perspective. View by executing the following statement:
   Select(sum(pins - reloads))/sum(pins) "Lib Cache" From v$librarycache;
   --look at the reuse rate of the shared SQL area, preferably above 90%, otherwise you need to increase the size of the shared pool.
   select (sum ( gets - getmisses -usage< Span style= "Color:navy" >- Fixed< Span style= "Color:navy" >/sum (gets) "Row Cache" from V$rowcache

   --check the hit ratio of the data dictionary buffers, preferably above 90%, or you need to increase the size of the shared pool.

2. Data buffer: Storage of SQL running results fetched data block;

   Selectname,value

   From V$sysstat

   wherenamein(' db block gets ', 'consistent gets ',' physical reads ');

   --View the usage of the database data buffer. The results of the query can be calculated using the data buffer usage hit rate =1-(physical reads/(DB block gets + consistent gets). The hit rate should be above 90%, otherwise the size of the data buffer needs to be increased.

3. Log buffers: Stores the logs generated by the database run.

   Selectname,value

   From V$sysstat

   wherenamein (' Redo entries ',' redo log space requests ');

   --View the usage of the log buffers. The query results can calculate the request failure rate of the log buffer: The request failure rate =requests/entries, the request failure rate should be close to 0, otherwise the log buffer is opened too small, need to increase the log buffer of the Oracle database.

Oracle SGA Optimization