Oracle Performance Tuning principles

There are many sources that affect ORACLE performance, including:

Database hardware configuration: CPU, memory, and network conditions

1. CPU: In any machine, the CPU's data processing capability is often a sign of computer performance measurement. ORACLE is a database system that provides parallel capabilities, which has higher CPU requirements, if the number of running queues exceeds the number of CPU processing, the performance will decrease. The problem we need to solve is to increase the number of CPUs as appropriate, of course, we can also KILL processes that require many resources;

2. memory: Another metric to measure machine performance is the memory size. in ORACLE, the memory is used to exchange data with the swap area in the database we are creating. When reading data, disk I/O must wait for the completion of physical I/O operations. When ORACLE memory bottleneck occurs, we must first consider increasing the memory, since the response time of I/O is the main parameter affecting ORACLE performance, I will explain it in detail.

3. network conditions: NET * SQL is responsible for the exchange of data on the network. A large number of SQL statements will slow down the network speed. For example, 10 m Nic and 100 NIC have a significant impact on NET * SQL, and the performance of network devices such as switches and hubs has a significant impact on the network, we recommend that you do not try to connect network segments with three hubs in any network.

OS parameter settings

The following table provides the OS parameter settings and descriptions. DBA can set these parameters based on actual needs.

Description

Bufpages does not distribute buffer space statically. It uses dynamic allocation to dynamically allocate the bufpages value along with nbuf.
Create_fastlinks allows fast symbolic links to the HFS File System
Dbc_max_pct increases the percentage of physical memory occupied by the maximum dynamic buffer space to meet the needs of the application system's read/write hit rate.
Dbc_min_pct sets the percentage of physical memory occupied by the minimum dynamic buffer space
Desfree increases the minimum idle memory limit for switching operations to ensure system stability and prevent unforeseen system crashes ).
Fs_async allows asynchronous disk operations to improve CPU and disk Utilization
Lotsfree increases the upper limit of idle memory for page swap operations and ensures that the application has sufficient available memory.
Maxdsiz increases the size of the maximum data segment to meet the needs of applications. 32-bit)
Maxdsiz_64bit maximum process data segment size for 64_bit
Maxssiz increases the size of the maximum stack segment. 32_bit)
Maxssiz_64bit: increase the size of the maximum stack segment. 64_bit)
Maxtsiz increases the maximum code segment size to meet Application Requirements
The original value of maxtsiz_64bit is too large and should be adjusted to a smaller value.
Minfree increases the upper limit of free memory for stopping swap operations
Shmem allows Memory sharing to improve memory utilization
Shmmax sets the maximum size of the shared memory segment to meet current needs.
Timeslice system bottlenecks are mainly reflected in disk I/O, so reduce the size of time slice. On the one hand, it can avoid CPU waiting due to poor disk I/O, this improves the overall CPU utilization. On the other hand, the congestion of processes is reduced.
Unlockable_mem increases the size of the unlockable memory so that the memory space that can be used for page feed and swap is expanded to meet the system's requirements for memory management.

User SQL Quality

The above is about hardware. With limited conditions, we can adjust the SQL quality of applications:

1. Do not perform Full Table Scan): Full Table Scan causes a large number of I/O

2. try to build and use indexes as well: index creation is also exquisite. During index creation, the more indexes, the better. When a table has more than four indexes, the performance of ORACLE may not be improved, because the OLTP system reduces the performance by more than five indexes per table, and Oracle cannot use more than five indexes in one SQL statement; when group by and order by are used, ORACLE Automatically sorts the data. ORA determines the size of the sort_area_size area. When the sorting cannot be completed in the given sorting area, ORACLE will sort it in the disk, that is, in the temporary tablespace we talk about, too much disk sorting will increase the value of free buffer waits, and this interval is not just used for sorting. for developers, I would like to give the following advice:

1) subqueries in select, update, and delete statements should regularly find less than 20% of table rows. if the number of rows queried by a statement exceeds 20% of the total number of rows, it cannot improve the performance by using indexes.

2) The index may produce fragments, because when the record is deleted from the table, it is also deleted from the table index. the space released by the table can be reused, but the space released by the index cannot be reused. index reconstruction should be performed on indexed tables that frequently perform deletion operations to avoid space fragmentation and performance impact. the truncate command can also be used to delete all records in the table or index fragments in a staged truncate table.

3) when using an index, you must reference the index in the order of corresponding fields.

4) use +) is more efficient than use not in.

Reduce ORACLE competition:

Let's talk about several ORACLE parameters. These parameters are related to ORACLE competition:

1) freelists and freelist groups: they are responsible for ORACLE's space management for processing tables and indexes;

2) pctfree and pctused: this parameter determines the behavior of freelists and freelist groups. The unique purpose of pctfree and pctused parameters is to control how a block is imported and exported in freelists.

Setting pctfree and pctused is important for removing and reading chunks from freelists.

Other parameter settings

1) including the Global zone of the SGA system): The global zone of the system sga is the memory segment of the Control Information allocated to the Oracle database that contains an Oracle instance.

It mainly includes the database buffer cache ),

Repeat the log cache the redo log buffer ),

Shared pool the shared pool ),

The data dictionary cache) and other information

2) db_block_buffers data high-speed buffer zone) The accessed data is stored in this memory area. The larger the parameter, the more likely Oracle will find the same data in the memory, that is, the query speed is accelerated.

3) share_pool_size SQL shared buffer pool): this parameter is used for high-speed database cache and data dictionary cache.

4). Log_buffer replays the log buffer)

5) sort_area_size sorting area)

6) number of processes simultaneously connected by processes)

7) db_block_size database block size): the default Oracle block size is 2 kb, which is too small, because if we have an 8 KB data, the 2 kb database needs to read four disks, i/O operations are greatly reduced as long as the 8 KB database is read once. After the database is installed, you can no longer change the value of db_block_size. You can only re-create the database and choose to manually install the database when creating the database.

8) Number of connections simultaneously opened by open_links)

9), dml_locks

10) open_cursors)

11) Number of background write processes in dbwr_io_slaves)