Oracle Database performance adjustment (strongly recommended. This article describes in detail under what circumstances the system parameters need to be adjusted, where the query statements have been tested by myself. I do not understand one sentence, I hope
You can modify it .) Oracle is a high-performance database software. You can adjust parameters to optimize the performance. Performance optimization is mainly divided into two parts: one is that the database administrator optimizes the system parameters, and the other is that the developers optimize the application. 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:
I. Adjust Memory AllocationThe 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. Parameter DB-BLOCK-BUFFERS (number of buffer cached in the storage area in SGA), parameter shared-pool-size (allocated to the number of bytes in the shared SQL area), is the primary influence of 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 CacheLibrary cache includes private and shared SQL and PL/SQL zones. An important issue for adjusting SGA is to ensure that the database cache is large enough to enable Oracle to maintain analysis and statement execution in the Shared Pool, improve Statement Analysis and execution efficiency, and reduce resource consumption. It is determined by comparing the hit rate of library cache. Query the data dictionary view of V $ librarycache (where pins indicates high-speed cache hit rate, and reloads indicates high-speed cache failure) 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 BufferWhen 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 = any ('dbblock gets', 'consistent gets', 'Physical reads'); query results
Hit rate = 1-physical reads/(dbblock gets + consistent gets) If hit rate <0.6 ~ 0.7, increase DB-block-buffers.
Ii. 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, 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 policy: l evenly allocate I/O to as many disks as possible. L create different tablespaces for tables and indexes. L separate the data file from the redo log file on different disks. L reduce disk I/O Without Oracle server.
(3)
Adjust CompetitionWhen multiple processes apply for the same resource, competition arises.
Modify Process ParametersThis 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 ProcessesReduce 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%, you should increase the value of the MTS-DISPATCHERS.
Reduce multi-threaded service process competitionFirst, query the V $ sysstat table to determine whether multi-threaded service process competition occurs: 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, the average request wait time continues to grow while the application is running, so, should increase the value of MTS-MAX-SERVERS. (Txfy: This sentence cannot be executed normally. I don't know how to use it. I hope you can help adjust it)
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 requests'; the value here should be close to 0. Otherwise, the LOG-BUFFEQS value for the initialization parameter file should be increased.
Reduce competition in rollback segmentsRollback 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 the V $ sysstat table 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 according to the following rules: number of user rollback segments n <16 416 32 <= N/4 but not more than 50
Reduce free list competitionWhen 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.
