Oracle after a long period of development, many users are very understanding of Oracle database server, and here I express my personal understanding, and we discuss the discussion.Oracle database server is the heart of the system, its performance will directly affect the performance of the whole system, in order to adjust the Oracle database server performance, mainly from the following considerations:
Adjusted to fit the operating system running Oracle database server
Oracle database server depend on the running server operating system, if the operating system can not provide the best performance, then the adjustment in any case, Oracle database server can not perform their proper performance.
1. For the Oracle database server resource planning system
It has a computer available resources, planning and resource allocation principles to the Oracle server: Oracle server as closely as possible to maximize use of resources, especially in the Client / Server as much as possible so that all resources on the server to run the Oracle service.
2. Adjust the memory configuration of computer system
Most operating systems use virtual memory to simulate more memory on the computer, it is actually hard on some disk space.When the actual memory space can not meet the requirements of applications, the operating system will use this part of the disk space information on the memory page replacement, which will cause a lot of disk I / O operations, so that the whole server's performance.In order to avoid excessive use of virtual memory, should increase the computer's memory.
3. For the Oracle database server to set the operating system process priority
Do not adjust the Oracle operating system process priority, because the Oracle database system, the background and foreground of all the database server process is as important as the work performed requires the same priority.So when installing, so that all database server processes are run using the default priority.
Adjust the memory allocation
Oracle database server to retain three basic memory cache, corresponding to 3 different types of data: library cache, dictionary cache and buffer cache.Library cache and dictionary cache together constitute the shared pool, shared pool buffer cache coupled with the system constitute the whole area (SGA).SGA database data is a system for quick access to the whole area, if the SGA itself needs to be released frequently, distribution, it can not achieve the purpose of quick access to data, it should be placed in main memory in the SGA, not on the virtual memory.Memory adjustment is mainly composed of SGA is to adjust the size of the memory structure to improve system performance, since the memory structure of Oracle database server and application requirements are closely related, so the memory structure of the adjustment should be in the disk I / O to adjust before.
1. Library buffer adjusted
Database buffer contains private and shared SQL and PL / SQL area, by comparing the database buffer hit ratio to determine its size.To adjust the database buffer, the buffer must first understand the activities of the library, the library buffer activity statistics remain in the dynamic performance table v $ librarycache data dictionary, you can query this table to learn through their activities, to determine howadjustment.Select sum (pins), sum (reloads) from v $ librarycache; Pins column shows the SQL statements, PL / SQL blocks and access the object defined by the total number; Reloads column shows the SQL and PL / SQL block implicit analysis orobject definition in the library when the program re-loading buffer errors.If the sum (pins) / sum (reloads) ≈ 0, then the database buffer hit ratio appropriate; if sum (pins) / sum (reloads)> 1, adjust the initialization parameters need to re-adjust the distribution shared_pool_size to sharethe amount of memory pool.
2. Adjustment of the buffer data dictionary
Data dictionary contains information about the buffer structure of the database, users, physical information.Data dictionary hit ratio, a great impact on system performance.Data Dictionary records the use of buffers in the dynamic performance table v $ librarycache, you can query this table to learn through their activities to determine how to adjust.Select sum (gets), sum (getmisses) from v $ rowcache; Gets column is the number of the corresponding requests statistics; Getmisses column is the data error caused by the buffer number of requests.For frequently accessed data dictionary buffer, sum (getmisses) / sum (gets) <10% ~ 15%.If larger than this percentage, you should consider increasing the capacity of the buffer data dictionary, which need to be adjusted to re-adjust the initialization parameter shared_pool_size assigned to the shared pool memory.
3. The adjustment of the buffer cache
All users access the process data is accessed through the buffer cache, so that part of the hit ratio, performance is critical.The use of buffer cache records in v $ sysstat dynamic performance table, you can query this table to learn through their activities to determine how to adjust.
Select name, value from v $ sysstat where name in ('dbblock gets', 'consistent gets', 'physical reads');
dbblock gets and consistent gets requests for the data value is the total number of read buffer.physical reads value is caused when the requested data file from the disk read times.Read from the buffer cache is called the level of the possibility of the buffer hit ratio, the formula:
Hit Ratio = 1 - (physical reds / (dbblock gets + consistent gets)) If the Hit Ratio <60% ~ 70%, should increase the value of the parameter db_block_buffers.db_block_buffers can adjust the allocated amount of memory buffer cache, which db_block_buffers buffer cache can be set to allocate the number of data blocks.Buffer cache bytes = db_block_buffers the total value of * db_block_size value.db_block_size value indicates the number of bytes of data block size can query v $ parameter table: select name, value from v $ parameter where name = 'db_block_size'; in modified after the database initialization parameter, you must first close the database, after you restart the database to make the new settings work.