Explain several Oracle Performance Tuning Methods

Oracle is a high-performance database software. You can adjust parameters to optimize the performance. Performance optimization is mainly divided into two parts:

First, the database administrator optimizes the system parameters;

Second, developers can adjust their applications through optimization.

Here, we will only discuss the adjustment of system parameters, not the application optimization. The adjustment of system parameters can be divided into the following parts:

Adjust Memory Allocation

System global zone (SGA) is a memory segment allocated to Oracle that contains the control information of Oracle database instances. The size of SGA has a great impact on system performance. The default parameter setting is only applicable to computers with low configurations and does not meet the needs of existing devices in the income system. If these parameters are not adjusted, system resources will be greatly wasted. In terms of Alpha 1200 of the revenue system, the size of SGA is about 160 MB.

Some parameters in the initialization parameter file have a decisive influence on the size of the SGA. The number of buffer cached in the storage area in the parameter DB-BLOCK-BUFFERSSGA), the number of bytes allocated to the shared SQL area by the parameter SHARED-POOL-SIZE), is the primary influence of the SGA SIZE.

DB-BLOCK-BUFFERS parameters are the most important factors determining SGA size and database performance. A high value can increase the system hit rate and reduce I/O. The size of each buffer is equal to the size of the parameter DB-BLOCK-SIZE. Oracle Database blocks are in bytes.

The shared pool in the Oracle SGA area is composed of the database cache, Dictionary cache, and other user and server session information. The shared pool is the largest component to consume. Adjusting the size of each structure in the SGA area can greatly improve the system performance.

Adjust Library Cache

Library Cache) includes private and shared SQL and PL/SQL. An important issue for adjusting SGA is to ensure that the database cache is large enough to enable Oracle to maintain analysis and execute statements in the shared pool, improve sentence Analysis and execution efficiency of the V $ LIBRARYCACHE data dictionary view to reduce resource consumption. It is determined by comparing the hit rate of Library Cache. Pins indicates the high-speed cache hit rate, and reloads indicates that the high-speed cache fails)
SQL> SELECT SUM (pins), SUM (reloads) FROM v $ librarycache;

If sum (reload)/sum (pins) ≈ 0, it indicates that the Library Cache hit rate is suitable. If it is greater than 1, you need to increase the shared pool shared-POOL-SIZE) in the initialization parameter file ).

Adjust the data Dictionary high-speed Cache Dictionary Cache)

The data dictionary cache includes the structure, user, and entity information of the database. The data dictionary hit rate has a great impact on the system. In the hit rate calculation, getmisses indicates the number of failures, and gets indicates the number of successes.

Query the V $ ROWCACHE table:

SQL>SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100 FROM v$rowcache;

If this value is greater than 90%, the hit rate is suitable. Otherwise, the size of the shared pool should be increased.

Adjust the cache of the database Buffer

When Oracle is running, it caches and reads data to the database. When the cache hits, it indicates that the information is in the memory. If the cache fails, it means that Oracle must perform disk I/O. The key to keeping the cache failure rate at least is to ensure the cache size. The initialization parameter DB-BLOCK-BUFFERS controls the size of the database buffer cache. You can query the V $ SYSSTAT hit rate to determine whether the value of the DB-BLOCK-BUFFERS should be increased.

SQL>SELECT name,value FROM V$SYSSTAT WHERE name IN (’dbblock gets’,’consistent gets’,’physical reads’);

Query results

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

If the hit rate is <0.6 ~ 0.7, increase DB-BLOCK-BUFFERS.

Adjust disk I/O

Disk I/O is the bottleneck of the system performance. It solves the problem of disk I/O and can significantly improve the performance. By querying V $ FILESTAT, you can know the usage frequency of each physical file. phyrds indicates the number of reads to each data file, and phywrts indicates the number of writes to each data file)

SQL>SELECT name,phyrds,phywrts FROM v$datafile df,v$filestat fs WHERE df.file# =fs.file#;

For physical files that are frequently used, use the following policies:

Distribute I/O evenly to as many disks as possible.

Create different tablespaces for tables and indexes.

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

Reduce disk I/O Without Oracle SERVER.

Adjust Competition

When multiple processes apply for the same resource, competition arises.

Modify Process Parameters

This parameter defines the maximum number of processes that can be connected to the Oracle database at the same time. The default value is 50. Note that the number of background processes in Oracle is also included. We recommend that you change the value to 200.

Reduce competition in scheduling Processes

Reduce competition among scheduling processes. query the v $ dispatcher table to determine competition among scheduling processes.

SQL>SELECT network ,sum(busy)/sum(busy)+sum(idle) FROM v$dispatcher GROUP BY network;

If a protocol is busy with more than 50%, the value of the MTS-DISPATCHERS should be increased.

Reduce multi-threaded service process competition

First, query the V $ SYSSTAT table to determine whether multi-threaded service process competition has occurred:

SQL>SELECT DECODE(totalq,0,’No request’,wait/totalq||’hunderths of seconds’) FROM v$sysstat WHERE type=’common’;

If the number of shared service processes has reached the maximum value specified by the MTS-MAX-SERVERS in the initialization parameter file, but the average request wait time continues to grow while the application is running, increase the value of the MTS-MAX-SERVERS.

Reduce competition in redo log Buffer

Query the V $ SYSSTAT table to determine whether the redo log file buffer is sufficient.

SQL>SELECT name,value FROM v$sysstat WHERE name=’redo log space request’;

The value here should be close to 0; otherwise, the value of the LOG-BUFFEQS of the initialization parameter file should be increased.

Reduce competition in rollback segments

Rollback segments also affect the performance. Appropriate rollback segments are allocated based on the transaction size.

First, determine whether the number of rollback segments can meet the requirements of system operation:

Query the V $ WAITSTAT table and V $ SYSSTAT table

SQL>SELECT class,count FROM v$waitstat WHERE class IN (’system undo header’,system undo block’, ’undo header’,’undo block’); SQL>SELECT sum(value) FROM v$sysstat WHERE name IN (’db block gets’,’consistent gets’);

If any class/sum (value)> 10%, consider adding a rollback segment. The number of rollback segments is generally set as follows:
Number of user rollback segments
N< 164
16 32 <= n/4 but not more than 50

Reduce Free List competition

When multiple processes insert data to a table at the same time, Free List competition is generated.

SQL>SELECT class,count FROM v$waitstat WHERE class=’free list’; SQL>SELECT sum(value) FROM v$sysstat WHERE name IN (’db block gets’,’consistent gets’);

If class/sum (value)> 1%, the value of the Free List of the table should be increased.

1. Oracle stored procedure development instance
2. In-depth analysis of Oracle data block principles
3. Analysis of Oracle Database management scripts