Complete Oracle Optimization-optimized memory

Memory optimization has always been the top priority of database or operating system optimization. In the Oracle database architecture, memory optimization is also the key to performance optimization. The memory optimization mainly improves the system performance by optimizing the memory structure. The memory structure mentioned here mainly consists of dedicated SQL and PL/SQL zones, shared pools, log buffers, and high-speed buffer storage zones.
Because the memory structure requirements of 0racle are closely related to applications, the memory structure optimization should be performed after the optimization of SQL statements and applications and before the optimization of input/output (I/O. First, optimize the operating system and consider the private SQL and PL/SQL zones before processing the Shared Pool.

SGA Optimization
SGA is a global system zone for quick access to database data. Its size is determined when the instance is started. If the SGA itself needs to be released and allocated frequently, it is impossible to quickly access data. Therefore, in order to optimize the system performance, we should ensure that all SGA will reside in the actual memory, instead of using virtual memory, so as not to degrade the overall system performance.

Tip: SGA tips
SGA is a group of shared memory buffers allocated by Oracle for an instance. It contains the data and control information of the instance. If multiple users are 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 the shared global zone. SGA is automatically allocated when the instance is started, and is withdrawn when the instance is closed. Data and control information in SGA can be used by multiple processes, but only several special processes can perform write operations on them. The storage areas in SGA can be roughly divided into four areas based on their information types: "database Buffer Storage Area", "log buffer", "Shared Pool", and "request and response queue "..
View SGA size
You can view the size of the SGA using two command methods.
(1) use the "show sga" command
Note: you can connect to Sever Manager and database, and then run the "show sga" command to check the size of SGA. If the database instance is not started, the "show sga" command returns an error.
(2) Use "SELECT * from v $ SGA;" to query the command
Note: To use "SELECT * from v $ SGA;", run the "SVRMGR>" prompt.
Pre-installed SGA
You can set the value of the PRE_PAGE_SGA parameter to "yes" in the init. ora file, and preload the SGA parameter to the physical memory, that is, set PRE_PAGE_SGA = yes.

Optimize the dedicated SQL zone and PL/SQL Zone
To optimize the dedicated SQL zone and PL/SQL zone, you must know whether there are unnecessary syntax analysis calls. You must call Parse as few as possible. If there are too many unnecessary syntax analysis calls, you should try to reduce it.
The trace function can be used to output trace information for each SQL statement to check the statistical count of its syntax analysis step. If the count statistical value of the analysis step is very close to the count statistical value of the execution step, you should reduce the number of syntax analysis calls.
The method for reducing syntax analysis calls is through the application development tools used (such as PRO * C, OCI, and SQL * Forms) to control the frequency of syntax analysis calls and to allocate and release dedicated SQL zones. For example, when PRO * C is used, the HOLD_CURSOR, RELEASE_CURSOR, and MAX_OPENCURSOR parameters can be used to control the dedicated SQL zone. When using OCI, you can use the OSQL3 or OPARSE call to allocate a dedicated SQL zone for SQL statements. Use the OCLOSE call to close the cursor and release the dedicated SQL zone. When using SQL * Forms, you can also control whether to reuse a dedicated SQL zone: You can reduce syntax analysis calls at the trigger, Form, or runtime, to reduce or reuse the dedicated SQL zone.

Optimize the log Buffer
The log buffer is also called the redo log buffer, which contains all the changed data blocks. These changed data blocks are written to the redo log file in an adjacent way by the Oracle log writing process.
(1) obtain the buffer Activity
When optimizing the log buffer, you should first understand the activity of the buffer. This pair is obtained by querying the dynamic performance TABLE (with the select any table Privilege required) V $ SYSSTAT.
SVRMGR> select sum (value) "Redo Buffer Waits" from v $ sysstat
2> where name = 'redo log space wait time ';
(2) Calculate the application failure rate of the log buffer.
The database administrator can execute the following statements:
SQL> select name, value from v $ sysstat where name in ('redo entries', 'redo log space requests'); view the usage of the log buffer. The query results can calculate the request failure rate of the log buffer.
Application Failure Rate = requests/entries. The application failure rate should be close to 0. Otherwise, the log buffer is too small and the log buffer of the ORACLE database needs to be increased.
(3) Optimize the log Buffer
If the wait time for the redo buffer is greater than zero, the process will wait for the space in the redo log buffer to become available. In this case, the database performance will be affected. You can optimize the log buffer by gradually increasing the value of LOG_BUFFER in init. ora. When adding the value of LOG_BUFFER, You can query the dynamic performance table to understand and check the activity of the log buffer.

Optimized sharing pool
The Shared Pool consists of database cache, Dictionary cache, and SQL zone. The size of the entire shared pool is determined by the SHARED_POOL_SIZE parameter. The Optimization of the Sharing pool mainly takes into account the optimization of the database high-speed buffer, data dictionary high-speed buffer, and information during the session.

Optimize library High-Speed Buffer
To optimize the high-speed buffer of the database, you must first understand the activity of the buffer. The activity statistics of the Library's high-speed buffer are retained in the Dynamic Performance Table V $ LIBRARYCACHE. During optimization, the data dictionary cache should contain as many memory database blocks as possible.
(1) determine the database cache Performance
Query the V $ librarycache table (with the select any table Privilege required) to learn about its activity and determine how to optimize it. For example:
SQL> SELECT SUM (pins), SUM (reloads) FROM V $ LIBRARYCACHE;
Note: The Dynamic Performance Table V $ LIBRARYCACHE contains NAMESPACE, PINS, RELOAD, and other columns. The NAMESPACE column reflects the activity of the SQL statement and the database buffer of the PL/SQL block. Its value may be 'SQL AREA', 'table/PROCEDURE ', the 'body' and 'trigger' columns, and the PINS and RELOADS columns provide the error information for executing the call. The PINS column lists the total number of SQL statements, PL/SQL blocks, and definitions of accessed objects; RELOADS provides SQL statements, implicit Analysis of PL/SQL blocks, or errors that occur in the Library Buffer during object definition reloading.
(2) view the ratio of reloads to pins
You can use the following query statement to view the ratio of reloads to pins:
SELECT (SUM (reloads)/SUM (pins) × 100 "Library Cache Ratio" from v $ LIBRARYCACHE;
The user must ensure that the ratio of reloads to pins is as low as possible, and its value should be lower than 1%. If RELOADS/PINS> 1%, additional storage should be allocated to the buffer and equivalent SQL statements should be written, share SQL statements with PL/SQL blocks in a shared SQL zone to reduce errors. If there is no error in the database high-speed buffer, you can set the initialization parameter CUTSOR_SPACE_FOR_TIME to TRUE to accelerate the invocation. This can slightly improve the performance. If each user has insufficient private SQL zones, do not set CUTSOR_SPACE_FOR_TIME to TRUE.
(3) Optimize the cache area of the database
When optimizing the library cache, you can increase the value of SHARED_POOL_SIZE or OPEN_CURSORS In the init. ora file to a satisfactory ratio.

Optimize the Data Dictionary Buffer
The Data Dictionary Buffer function is similar to the database cache function, but it is mainly used for Oracle dictionary high-speed SQL statements. To optimize the Data Dictionary Buffer, you must first view the usage and effect of the Data Dictionary Buffer, and then optimize it accordingly. The usage of the Data Dictionary Buffer is recorded in the Dynamic Performance Table V $ ROWCHACHE, which has the following columns:
PARAMETER: records the statistics of a type of close-up data dictionary items. Its value starts with 'de _ '. For example, the statistics described in a file are 'de _ files '.
GETS: The number of requests for the corresponding item.
GETTMISSES: the number of data requests that cause buffer errors.
You can use the following statement to query the V $ ROWCHACHE table:
SQL> SELECT (SUM (getmisses)/SUM (gets) * 100 "DaTa Dictionary Cache Ratio" FROM V $ ROWCHACHE;
For the data dictionary buffer with poor and complex access, the ratio of GETMISSES to GETS is less than 10% to 15%. If the value is greater than the percentage, you should consider increasing the capacity of the Data Dictionary Buffer, that is, increasing the value of the SHARED_POOL_SIZE or DB_BLOCK_BUFFERS initialization parameter.
Optimization of sharing pool when using multi-clue servers
In the multi-clue server structure, session information is stored in the shared pool, which includes the SQL dedicated zone and sorting zone. When using a multi-clue server, you need to increase the sharing pool to meet your needs. This can also be achieved by adding the SHARED_POOL_SIZE parameter value.
You can query (requires the select any table privilege) the dynamic performance table v $ SESSTAT to measure the session information. For example:
SQL> SELEC SUM (value) | 'bytes '"Total memory for all session"
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 ';
Among them, "session memory" is the number of memory bytes allocated to the session; "max session memory" is the maximum number of memory bytes allocated to the session.

Optimize the Buffer Zone
(1) obtain the buffer Activity
To optimize the buffer zone, you should first understand the activity of the buffer zone. This can be obtained by querying a dynamic performance TABLE (with the select any table Privilege required) 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.
The values of db block gets and consistent gets are the total number of reads in the request data buffer. "Physical reads" indicates the number of times files are read from the disk when data is requested.

(2) buffer hit rate
The possibility of reading from a buffer zone is called the buffer hit rate. It can be calculated using the following formula:
Hot Ratio = 1-(physical reads/(db block gets + consistent gets)
The higher the buffer hit rate, the faster the speed. If the hit rate is lower than 60% or 70%. Add a buffer (DB_BLOCK_BUFFERS) to improve performance. According to the formula, we can calculate the Hot Ratio = 1-(1963/(3437 + 30500) = 92% in this example. If the buffer hit rate is high and you want to reduce the buffer while maintaining good performance, you can reduce the value of DB_BLOCK_BUFFERS. The minimum value is 4.

Author "Roby sky"