Oracle performance tuning principle
oracle performance impact of the source of many, including the following aspects:
Database hardware configuration: CPU, memory, network conditions
1. CPU: any machine in the data processing capacity of CPU is a measure of computer performance is often a sign of, and parallel ORACLE is a
capacity to provide the database system, in CPU requirements on the higher, if the run queue exceeds the number of CPU the number of handling,
performance will drop, we have to solve the problem is to appropriately increase the number of CPU, of course, we also will need to KILL many
resources out of the process;
2. Memory: Another measure of machine performance is an indicator of how much memory, and memory and in the ORACLE in to build the
database in the areas of exchange of the exchange of data, reading data, disk I / O must wait for physical I / O operation completed, in the event of
ORACLE memory bottleneck, we first need to consider is to increase the memory, as a result of I / O response time performance is the impact of the
main parameters of ORACLE, I will in this respect in detail
3. Network conditions: NET * SQL for data on the network from a large number of SQL will slow down the network. For example, 10M and 100, the
network adapter card on the NET * SQL has a very significant impact, as well as switches, hubs, etc. the performance of network equipment for the
network it is clear that the proposed network will not in any attempt to use three sets line segment will be used to the Internet.
OS parameter settings
The following table gives the parameters of os settings and explain, DBA can be based on actual need for these parameters were set kernel
bufpages of buffer space is not a static allocation, dynamic allocation of bufpages value nbuf together with dynamic allocation of buffer space.
create_fastlinks on the HFS file system to allow fast symbolic link
dbc_max_pct dynamic buffer space to increase the largest share of the percentage of physical memory to meet the application needs to read and
write hit rate.
dbc_min_pct set up buffer space for the smallest share of the dynamic of the percentage of physical memory
the beginning of the operation to improve the exchange of desfree the minimum free memory minimum to protect the stability of the system to
prevent unforeseen system crash (Crash).
fs_async allow asynchronous operation of the disk to improve the utilization of CPU and disk
lotsfree improve system operation to lift the free-for-page the upper limit of memory to ensure that applications have sufficient memory space
maxdsiz the system features a large amount of data, increase the size of the largest data segment, and ensure applications. (32)
maxdsiz_64bit maximum process data segment size for 64_bit
Paragraph Maxssiz increase the size of the largest stack. (32_bit)
paragraph maxssiz_64bit increase the size of the largest stack. (64_bit)
Code segment Maxtsiz the maximum size, to meet the application requirements
maxtsiz_64bit original value is too large, should be adjusted in small
Minfree improve the operation to stop the free exchange of the upper limit of memory
Shared memory allows Shmem in order to improve the utilization of memory
Shmmax set the maximum shared memory segment size, fully meet the current needs
Timeslice the main bottlenecks in the system are reflected in the disk I / O's, thus reducing the size of time slices, on the one hand, to avoid disk
I / O wait CPU caused by poor, resulting in improved utilization of CPU integrated. On the other hand, the process of reducing the amount of
unlockable_mem increased memory size can not be locked, so that can be used for pages of memory space and the expansion of the exchange to
meet the system requirements for memory management.
The quality of the user SQL
The above are all talking about what hardware, in limited circumstances, we can adjust the quality of SQL applications:
1. Not full-table scan (Full Table Scan): full-table scan led to a large number of I / O
2. Try to build and use a good index: the index is also about building and construction index, nor is the index the better, when the index reached a
table for more than four hours, ORACLE performance may not improve because OLTP system more than 5 per table will reduce the performance
index, and in a sql in, Oracle can not be used from the index more than 5; when we use GROUP BY and ORDER BY the time, ORACLE will
automatically sort the data, and ORACLE INIT.ORA decided in the size of the sort_area_size, when we can not sort the order of a given area is
completed, ORACLE will sort in the disk, that is, we are talking about the temporary table space to sort, excessive disk sorting free buffer waits will
become a high value, and this range is not just for the sort of, for developers, I make the following advice:
1), select, update, delete statements in the subquery to find the law should be less than 20% of the table row. If a statement the number of rows to
find a few more than 20% of the head office, it can not be used to obtain the performance index improved.
2), the index may have debris, because the deletion of records from the table, the corresponding index from the table to delete. Table space can be
reused release, and the release of the index can not reuse the space. Frequent deletion of the index table, it should be phased to rebuild the index in
order to avoid in the index caused by space debris, the impact of the performance. in the permit conditions, can also be phased to truncate table,
truncate table command to delete all records, but also to remove debris index.
3), using index when the index according to the order of the corresponding reference field.
4), with (+) than the more efficient use of NOT IN.
ORACLE reduced competition:
ORACLE to talk about a few of the several parameters, several parameters related to the ORACLE competition:
1), freelists and freelist groups: They are responsible for the handling of ORACLE and the index table space management;
2), pctfree and pctused: This parameter determines the freelist group freelists and behavior, pctfree and pctused the sole purpose of parameters is
how to control the block in and out of freelists
Pctfree and pctused set up on the block in the removal and freelists read is very important.
Other parameters settings
1), including the SGA area (System Global Area): The system global area (SGA) is allocated to the Oracle instance contains an Oracle database
memory segment control information.
Mainly include the database cache (the database buffer cache),
A repeat of the log buffer (the redo log buffer),
Shared pool (the shared pool),
Data dictionary cache (the data dictionary cache), as well as other information
2), db_block_buffers (high-speed data buffer) visited a piece of data on this memory region, the greater the parameter, Oracle in memory the same
data to find the possibility of greater, or to speed up the query speed.
3), share_pool_size (SQL shared buffer pool): This parameter is the library cache and data dictionary cache.
4), Log_buffer (a repeat of the log buffer)
5), sort_area_size (sort area)
6), processes (the process of the number of simultaneous connections)
7), db_block_size (database block size): Oracle default block 2KB, too, because if we have a 8KB data, 2KB database block 4 times to read disk can
be read, and as long as 8KB database block 1st finished school, greatly reducing the I / O operation. Database after the installation is complete, you
can not change the value of the db_block_size, only to re-establish the database and the database, it is necessary to choose to manually install the
8), open_links (open at the same time the number of links)
10), open_cursors (open cursor number)
11), dbwr_io_slaves (the background process to write a few)
About the Author