Everything has its source, to solve the problem, it has to start from the source, the impact of Oracle performance is very many sources, mainly including the following:
Ø Database hardware configuration: CPU, memory, network conditions 1. CPU: CPU data processing power in any machine is often a symbol of the performance of the computer, and Oracle is a database system to provide parallel capabilities, the CPU requirements are higher, if the number of running queues exceeded the number of CPU processing, performance will decline, The problem we have to solve is to increase the number of CPUs appropriately, and of course we can kill processes that require a lot of resources; 2. Memory: Another metric for measuring machine performance is the amount of memory, in the exchange of memory in Oracle and in the Exchange area of our database under construction, disk I/O must wait for physical I/O to complete when reading data, and the first consideration when Oracle memory bottlenecks occur is to increase memory , because I/O response time is the primary parameter that affects Oracle performance, I will explain in detail in this regard 3. Network conditions: Net*sql is responsible for the data on the network, a large number of SQL will slow down the network speed. For example, 10M network card and 100 of the network card has a very obvious impact on the net*sql, as well as switches, hubs and so on network equipment performance impact on the network is obvious, it is recommended that in any network do not attempt to use 3 hubs to interconnect the network segment. Setting of the Øos parameter The following table shows the parameter settings and instructions for the OS, which can be set by the DBA according to the actual needs
| The name of the kernel parameter |
Description |
| Bufpages |
The buffer space is not statically allocated and dynamically distributed, so that the Bufpages value is dynamically distributed with NBUF. |
| Create_fastlinks |
Allows quick symbolic links to the HFS file system, |
| dbc_max_pct |
Increase the percentage of the maximum dynamic buffer space to the physical memory to meet the needs of the application system for read and write hits. |
| dbc_min_pct |
Set the percentage of physical memory that the minimum dynamic buffer space occupies |
| Desfree |
Increase the minimum free memory lower limit of start Exchange operation, guarantee system stability, prevent unforeseen system crashes (Crash). |
| Fs_async |
Allows for disk asynchronous operations to improve CPU and disk utilization |
| Lotsfree |
Increase the upper limit of free memory for the system to remove the paging operation, ensuring that the application has enough free memory space. |
| Maxdsiz |
In view of the characteristic of the system data quantity, enlarge the maximum data segment size, guarantee the application need. (32-bit) |
| Maxdsiz_64bit |
Maximum process data segment size for 64_bit |
| Maxssiz |
Increase the size of the maximum stack segment. (32_bit) |
| Maxssiz_64bit |
Increase the size of the maximum stack segment (64_bit) |
| Maxtsiz |
Increase maximum code segment size to meet application requirements |
| Maxtsiz_64bit |
The original value is too large, should be adjusted small |
| Minfree |
Increase the upper limit of free memory to stop a swap operation |
| Shmem |
Allow memory sharing to increase memory utilization. |
| Shmmax |
Set the maximum shared memory segment size to fully meet your current needs. |
| TimeSlice |
Because the bottleneck of the system is mainly reflected in the disk I/O, so reduce the size of the time slice, on the one hand, because the disk I/O is not smooth caused by the CPU waiting, so as to improve the overall utilization of the CPU. On the other hand, the blocking amount of the process is reduced. |
| Unlockable_mem |
Increase the size of the lock memory, so that can be used for paging and exchange of memory space to expand to meet the system's memory management requirements. |
The user SQL quality is all about hardware, and we can adjust the SQL quality of the application under limited conditions: 1. Do not perform full table scans (full table Scan): All table scans cause a large number of I/O 2. Try to build and use a good index: indexing is also exquisite, when you are building an index, it is not the more the index, the better, Oracle performance may not improve when one table has an index of more than 4, because OLTP systems can degrade performance by more than 5 indexes per table, and in one SQL Oracle No more than 5 indexes can be used; Oracle automatically sorts the data when we use the group by and order by, and Oracle determines the size of the Sort_area_size area in Init.ora, and when the sort cannot be done in our given sort area, Oracle will sort on disk, which is the sort of temp table space we speak of, and too much disk sorting will make the value of free buffer waits higher, and this interval is not just for sorting, but for developers I would like to advise: 1, select,update, A subquery in a DELETE statement should regularly find a table row less than 20%. If a statement finds more rows than 20% of the total, it will not be able to gain performance gains by using the index. 2), the index may produce fragmentation because when the record is deleted from the table, it is also removed from the table's index. The space that the table frees can be used again, while the space that the index frees is no longer available. Indexed tables that perform frequent deletions should periodically rebuild the index to avoid space fragmentation in the index. Affect performance. Under the permitted conditions, you can also periodically truncate the table, truncate the command to delete all the records in the table, and also delete the index fragments. 3), when using the index must be referenced in the order of the corresponding fields in the index. 4), use (+) is more efficient than using not in. ø lowers Oracle's competition: start with several Oracle parameters, which are related to Oracle's competition: 1, Freelists and Freelist Group: they are responsible for Oracle's processing table and index space management; 2, Pctfree and pctused:This parameter determines the behavior of the freelists and freelist groups, and the sole purpose of the Pctfree and pctused parameters is to control how the blocks are in and out of the freelists and set up Pctfree and Pctused is important for the removal and reading of blocks in freelists. ø other parameters (1), including the SGA area (System global Area): The system global Area (SGA) is an assignment to Oracle that contains a The control information memory segment of the database for the Oracle instance. This includes the database cache cache, the replay log cache (the redo log buffer), the shared pool, the data dictionary cache (the dictionary C Ache) and other aspects of Information 2, db_block_buffers (data cache) access to the data are placed in this area of memory, the larger the parameter, Oracle in memory to find the same data, the greater the likelihood, that is, faster query speed. 3), share_pool_size (SQL shared buffer pool): This parameter is the cache of the library cache and the data dictionary. 4), Log_buffer (replay log buffer) 5, sort_area_size (sorted area) 6), processes (simultaneous number of processes) 7), Db_block_size (Database block size): Oracle default block 2KB, too small, Because if we have a 8KB of data, then 2KB block of the database to read 4 times to read, and 8KB block of database as long as 1 times read, greatly reducing I/O operations. After the database is installed, you can no longer change the value of the db_block_size, you can only re-establish the database and build the library, choose to install the database manually. 8), Open_links (number of links opened at the same time) 9, dml_locks 10), open_cursors (open cursor number) 11), Dbwr_io_slaves (number of background write processes)
