Oracle Thorough optimization--Optimizing memory __oracle

Memory optimization has always been a database or operating system optimization of the most important. Memory optimization is also the key to performance optimization in Oracle database architecture. Optimizing memory primarily improves system performance by optimizing the memory structure. The memory structure described here consists primarily of dedicated SQL and pl/sql, shared pools, log buffers, and cache buffers.
Since 0racle memory structure requirements are closely related to applications, memory structure optimization should be performed after SQL statements and application optimizations, and before input/output (I/O) optimizations. and first, optimize the operating system and consider the private SQL and Pl/sql area before processing the shared pool.

Optimize SGA
The SGA is a system global area that quickly accesses the database data, and its size is determined when the instance is started. If the SGA itself needs to be released and distributed frequently, it is impossible to achieve fast access to the data. Therefore, in order to optimize the performance of the system, should ensure that the SGA is all resident in real memory, rather than using virtual memory, so as not to reduce the overall performance of the system.

Editorial tips: SGA Small common sense
The SGA is a set of shared memory buffers that Oracle assigns to an instance. It contains the data and control information for the instance. If more than one user is connected to the same instance at the same time, the data in the SGA can be shared by multiple users, so it is also called a shared global zone. The SGA is automatically allocated when the instance is started, and is retracted when the instance is closed. The data and control information in the SGA can be used by several processes, but can only be written by several holding processes. The storage area contained in the SGA can be roughly divided into the four regions of the database buffer store, the log buffer, the shared pool, and the request and response queues according to the type of information it holds.
View the SGA size
Users can view the size of the SGA in two different commands.
(1) Use "Show SGA" command
Description: Users can connect to the Sever manager and database, and then perform the show SGA command to see the size of the SGA. If the database instance does not start, then using the show SGA command will cause an error.
(2) using the "SELECT * from V$SGA;" Query to try to command
Description: Use "SELECT * from V$SGA;" To be at the "svrmgr>" prompt.
Pre-installed SGA
You can set the value of the parameter PRE_PAGE_SGA to "Yes" in the Init.ora file and preinstall the SGA into physical memory, which sets the Pre_page_sga=yes.

Optimize dedicated SQL and Pl/sql areas
To optimize a dedicated SQL zone and Pl/sql area, you must know if there is an unnecessary parsing call, and the user must call parse as little as possible. If you have too many unnecessary parsing calls, you should try to reduce it.
You can use the trace feature to output trace information for each SQL statement to detect the statistics count for its parsing step. If the analysis step's count statistic is very close to the count statistic of the execution step, you should reduce the parsing call.
The way to reduce parsing calls is to control the parsing call and the frequency of allocating and releasing a dedicated SQL area using the application development tools used (such as Pro*c, OCI, and Sql*forms), for example: When using Pro*c, Hold_cursor, Release_ Cursor and Max_opencursor parameters to control the private SQL area. When using OCI, you can use OSQL3 or Oparse to assign a dedicated SQL area to a SQL statement, close the cursor with a oclose call, and release the private SQL zone. When using SQL *forms, you can also control whether a private SQL zone is reused: You can reduce or reuse a private SQL zone by reducing the parsing call at the trigger level, form level, or at run time.

Optimizing Log Buffers
The log buffer is also known as the Redo log buffer, which contains all the changed blocks of data. These changed blocks of data are written in a contiguous manner to the redo log file through the Oracle log writing process.
(1) Get buffer activity
When you optimize the log buffer, you should first understand the activity of the buffer. This is obtained by querying the dynamic performance table (which requires the Select any table privilege) V$sysstat.
Svrmgr> select sum (value) "Redo Buffer Waits" from V$sysstat
2> where name= ' redo log spaces wait ';
(2) Calculate the application failure rate of the log buffer
The database administrator can do this by executing the following statement:
Sql>select Name,value from V$sysstat where name in (' Redo entries ', ' Redo log spaces requests '); View the usage of the log buffer. The results of the query can calculate the request failure rate for the log buffer.
Application failure rate =requests/entries. The application failure rate should be close to 0, otherwise the log buffer opening is too small to increase the log buffer for the Oracle database.
(3) Optimizing log Buffers
If the redo buffer waits more than 0, the process waits for the space of the redo log buffer to become available. This can affect the performance of the database. You can optimize the log buffer by progressively increasing the value of the parameter Log_buffer in Init.ora. When you increase the value of a log_buffer, you can query the dynamic performance table to understand and check the activity of the log buffer.

Optimizing shared Pools
A shared pool consists of a library cache, a dictionary cache, and a SQL area. The size of the entire shared pool is determined by the parameter shared_pool_size. The optimization of the shared pool mainly takes into account the buffer zone, the data dictionary buffer zone and the information optimization during the session.

Optimizing the library High speed buffer
To optimize the library cache area, you must first understand the activity of the buffer. The activity statistics for the library cache are kept in the dynamic performance table V$librarycache. Optimization should make the memory database blocks in the data dictionary cache as much as possible.
(1) Determine the performance of the library cache
Determine how to optimize by querying the V$librarycache table (which requires the Select any table privilege) to understand its activity. For example:
Sql>select sum (Pins), sum (reloads) from V$librarycache;
Description: Dynamic Performance table V$librarycache contains columns such as namespace, pins, and reload. Where the namespace column reflects the activity of the SQL statement and the library buffer of the Pl/sql block, the value may be ' SQL area ', ' table/procedure ', ' body ' and ' TRIGGER ', and pins and reloads columns give the error message when the call is made. The pins column gives the total number of times the SQL statement, the Pl/sql block, and the accessed object definition are defined; Reloads gives an implicit analysis of an SQL statement or Pl/sql block or an error in the library program buffer when the object definition is reloaded.
(2) View the ratio of reloads and pins
You can use the following query statement to view the reloads and pins ratios:
Select (SUM (reloads)/sum (pins)) x100 "Library Cache Ratio" from V$librarycache;
The user must ensure that the ratio of reloads and PINS is as low as possible and that the value should be less than 1%, and if reloads/pins>1%, the buffer should be allocated additional storage and write equivalent SQL statements so that the SQL statement shares a shared SQL area with the Pl/sql block, which reduces the error. If the library cache area has no errors, you can set the initialization parameter Cutsor_space_for_time to true to speed up the execution of the call. This can lead to slightly improved performance. Do not set Cutsor_space_for_time to True if the dedicated SQL area available to each user is insufficient.
(3) Optimizing the library cache area
When you optimize a library cache, you can achieve a satisfactory optimization rate by increasing the parameter values of Shared_pool_size or open_cursors in the Init.ora file.

Optimizing Data Dictionary Buffers
The data dictionary buffer is functionally similar to the library cache, but is primarily used for Oracle Dictionary high-speed SQL statements. To optimize the data dictionary buffer, you must first look at the usage and effects of the data dictionary buffer and then optimize it. The usage of the data dictionary buffers is recorded in the Dynamic performance table V$rowchache, which has the following columns:
PARAMETER: Records the statistics of a certain kind of close-up data dictionary item, whose value begins with ' De_ ', for example, the statistic described in a document is ' De_files '.
GETS: Is the count of the number of requests for the corresponding item.
Gettmisses: Is the number of data requests that caused a buffer error.
The following statement can be used to query the V$rowchache table:
Sql>select (SUM (getmisses)/sum (gets)) *100 "DaTa Dictionary Cache Ratio" from V$rowchache;
The ratio of getmisses to gets is less than 10% to 15% for data dictionary buffers with poor propagation access. If you are greater than this percentage, consider increasing the capacity of the data dictionary buffer to increase the value of the shared_pool_size or db_block_buffers initialization parameters.
Sharing pool optimization When using a multiple-thread server
In a multiple-threaded server structure, session information is stored in a shared pool, which includes the SQL private sector and the sorting area. When using a multiple-thread server, increase the shared pool to meet your needs. This can also be achieved by increasing the parameter values of the shared_pool_size.
You can measure the amount of session information by querying the dynamic performance table V$sesstat, which requires a select any table privilege. For example:
Sql>selec SUM (value) | | ' Bytes ' "Total memory to all"
2>from V$sesstat
3>wherk name= ' session memory ';
Sql>select UM (value) | | ' bytes ' "Total max men for all Sessions"
2>from V$sesstat
3>where Name= ' max session memory ';
Where "Session Memory" is the number of bytes of memory allocated to sessions, and Max session memory is the maximum number of bytes of memory allocated to sessions.


Optimizing buffers
(1) Get buffer activity
To optimize the buffer, you should first understand the activity of the buffer. This can be obtained by querying the dynamic performance table (which requires the Select any table privilege) V$sysstat.
Svrmgr> select name, value from V$sysstat
2> where name in (' db block gets ', ' consistent gets ', ' physical reads ');
NAME VALUE
DB Blockgets 3437
Consistent gets 30500
Physica Reads 1963
3 Rows selected.
Where the "db block gets" and "consistent gets" values are the total number of reads in the request data buffer. "Physical reads" is the number of times the file was read from the disk when requesting data.

(2) Buffer hit ratio
The probability of reading from the buffer is called the buffer hit ratio. It can be calculated with the following formula:
Hot ratio=1-(Physical reads/(db block gets+consistent gets)
The higher the buffer hit rate, the faster it will be. If the hit rate is less than 60% or 70%. You should increase the buffer (that is, db_block_buffers) to improve performance. The Hot ratio=1-(1963/3437+30500) =92% In this example can be calculated according to the formula. If the buffer has a high hit ratio, and you want to reduce the buffer appropriately under good performance, you can reduce the Db_block_buffers value by 4.