Performance Tuning for Oracle databases

oracle| Data | database | Performance Oracle is a high-performance database software. The user can adjust the parameters to achieve the optimization of performance. Performance optimization is mainly divided into two parts: first, the database administrator through the adjustment of system parameters to achieve the goal of optimization, the second is the development of the application of the optimization to achieve the goal of adjustment.

Here, only the tuning of the system parameters is discussed, without involving application optimization. The adjustment of system parameters can be divided into the following parts:

(1) Adjust memory allocation

The system global Area (SGA) is a memory segment assigned to Oracle containing Oracle database instance control information. The size of the SGA has a significant impact on system performance, and its default parameter settings apply only to computers that are configured to be very low, and do not meet the needs of existing devices in the revenue system. If these parameters are not adjusted, it will cause great waste to the system resources. In the case of Alpha 1200 for the revenue system, the SGA is about 160 megabytes in size.

Some parameters in the initialization parameter file have a decisive effect on the size of the SGA. Parameter db-block-buffers (number of buffers cached in the SGA), parameter shared-pool-size (number of bytes allocated to the shared SQL area), is the main influence of the SGA size.

The Db-block-buffers parameter is the most important determinant of the SGA size and database performance. This value is higher and can increase the system's hit ratio and reduce I/O. The size of each buffer is equal to the size of the parameter db-block-size. Oracle database blocks are sized in bytes.

The Oracle SGA Zone share pool is composed of the library cache, the dictionary cache, and some other user and server session information, and the shared pool is the largest consumption component. Adjusting the size of each structure in the SGA area can greatly improve the performance of the system.

. Adjust Library Cache

The library cache contains private and shared SQL and Pl/sql areas. The important issue of adjusting the SGA is to ensure that the library cache is large enough to allow Oracle to maintain parse and execute statements in the shared pool, improve statement analysis and execution efficiency, and reduce resource consumption. Determine the size of the library cache by comparing the hit ratio. Query V$librarycache Data dictionary view (where pins represents cache hit rate, reloads indicates cache failure)

Sql〉select sum (Pins), sum (reloads)
From V$librarycache;
If sum (reload)/sum (Pins) ≈0, it is appropriate for the library cache hit ratio, if greater than 1, the size of the shared pool (shared-pool-size) needs to be increased (in the initialization parameter file).

. Adjust the data dictionary cache (Dictionary cache)

Data dictionary caching includes the structure of the database, user, entity information, and so on. The hit rate of the data dictionary has a great effect on the system. In the calculation of the hit ratio, getmisses represents the number of failures, and gets represents the number of successes.

Query V$rowcache table:

Sql>select (SUM (getmisses)/(SUM (gets) +sum (getmisses))) *100
From V$rowcache;

If the value is >90%, the hit rate is appropriate. Otherwise, you should increase the size of the shared pool.

. Adjusting the database buffer cache

Oracle caches read and write data to the database while it is running, and a cache hit indicates that the information is already in memory, and a cache failure means that Oracle requires disk I/O. The key to maintaining the minimum cache failure rate is to ensure the cache size. Initialization parameter Db-block-buffers controls the size of the database buffer cache. You can query the V$sysstat hit ratio to determine whether the Db-block-buffers value should be increased.

Sql>select Name,value
From V$sysstat
WHERE name in (' Dbblock gets ', ' consistent gets ', ' physical reads ');

Through query results

Hit Rate =1-physical reads/(dbblock gets+consistent gets)

If the hit rate <0.6~0.7, you should increase the db-block-buffers.

(2) Adjust disk I/O

Disk I/O is a bottleneck in system performance, which solves disk I/O and can significantly improve performance. Query V$filestat lets you know how often each physical file is used (Phyrds indicates the number of times each data file was read, phywrts the number of times each data file was written)

Sql>select Name,phyrds,phywrts
From V$datafile Df,v$filestat FS
WHERE df.file# =fs.file#;

For physical files with high frequency, you can use the following strategies:

. Allocate I/O as evenly as possible on as many disks as I can.

Create a different table space for the table and index.

Separate the data files from the redo log files on different disks.

Reduce disk I/O without Oracle server.

[1] [2] Next page