Look at the medicine in the gourd. Oracle Performance Tuning Principles

Oracle| performance Everything has its source, to solve the problem, also have to start from the source, the impact of Oracle performance is very many sources, mainly including the following:

Hardware configuration of the database: CPU, memory, network conditions

1. CPU: CPU data processing capacity 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 indicator of the performance of the machine is the amount of memory, in Oracle memory and in our database in the Exchange area for data exchange, read data, disk I/O must wait for physical I/O operations complete, in the presence of Oracle memory bottleneck, The first consideration is to increase the memory, because I/O response time is the main parameter affecting Oracle performance, I will explain in detail in this respect

3. Network conditions: Net*sql responsible for 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.

Settings for OS parameters

The following table shows the parameter settings and instructions for the OS, and the DBA can set these parameters 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

Allow 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 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.

User SQL Quality

All of this is about hardware, and we can adjust the SQL quality of the application under limited conditions:

1. Do not perform a full table scan (Scan): Full table scanning results in a large number of I/O

2. Try to build and use a good index: The index is also fastidious, in the construction of the index, is not the more the better, when a table index of more than 4, Oracle's performance may not improve because OLTP systems degrade performance by more than 5 indexes per table, and Oracle cannot use more than 5 indexes in one SQL, and Oracle automatically sorts data when we use Group by and order by. Oracle determines the size of the Sort_area_size area in the Init.ora, and when the sort cannot be done in our given sort area, Oracle sorts on the disk, which is the sort of temp table space we speak of, and too much disk sort will make free buffer The value of the waits is higher, and this interval is not just for sorting, but for developers I'd like to suggest that subqueries in

1, select,update,delete statements should regularly look for less than 20% 　　Table rows. If a statement finds more rows than 20% of the Total row count, it will not be able to gain performance gains by using the index.

2), the index can 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) and must be referenced in the order of the corresponding fields in the index when using the index.

4) and is more efficient with (+) than with not.

Reduce the competition for Oracle :

Start with several Oracle parameters, which are related to Oracle's competition:
1, freelists and Freelist Group: They are responsible for the space management of Oracle's processing tables and indexes;
2), Pctfree, and pctused: This parameter determines the behavior of the freelists and freelist groups, and the only purpose of the Pctfree and pctused parameters is to control how the block is Freelists in and out of the
setting up Pctfree and pctused is important for removing and reading blocks in freelists.

Settings for other parameters
1), including the SGA area (System global Area): The system global Area (SGA) is a control information memory segment assigned to Oracle that contains an Oracle instance database.
Mainly includes the database cache cache (buffer cache),
Replay log cache (the redo log buffer)
Shared Pool,
Data dictionary caching (the dictionary cache) and other information
2, Db_block_buffers (data cache) access to the data are placed in this piece of memory area, the larger the parameter, Oracle in memory to find the same data, the greater the likelihood, that is, speed up the 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 connected)
7), Db_block_size (Database block size): Oracle default block is 2KB, too small, because if we have a 8KB of data, the 2KB block of the database to read 4 times to read, and 8KB block database as long as 1 times read, greatly reduced 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 open at the same time)
9), Dml_locks
10), Open_cursors (number of open cursors)
11), Dbwr_io_slaves (background write process number)