Method of optimizing Oracle Database system

Oracle database is widely used in all fields of society, especially in Client/server mode, but application developers often encounter the problem that the performance of the whole system decreases significantly with the increase of data volume, in order to solve this problem, from the following aspects: Database server, network I/O, Applications, such as the entire system to adjust to give full play to Oracle's effectiveness, improve the performance of the entire system.
1 tuning the performance of the database server
Oracle database server is the core of the whole system, its performance directly affects the performance of the entire system, in order to adjust the performance of the Oracle database server, mainly from the following considerations:
1.1 Adjustment
Operating system to fit Oracle database server
The Oracle database server relies heavily on the operating system running the server, and if the operating system does not provide the best performance, the Oracle database server will not be able to perform its due performance anyway.
1.1.1 Planning system resources for Oracle database servers
According to available resources for computers, the principle of planning assigned to Oracle server resources is to maximize the use of resources by Oracle servers, especially in Client/server, so that all resources on the server are running Oracle services as much as possible.
1.1.2 Adjust the memory configuration in the computer system
Most operating systems use virtual storage to simulate larger memory on a computer, which is actually a certain amount of disk space on the hard disk. When the actual memory space does not meet the requirements of the application software, the operating system will use this part of the disk space for the information in memory to replace the page, which will cause a lot of disk I/O operations, so that the performance of the entire server down. To avoid excessive use of virtual storage, you should increase the memory of your computer.
1.1.3 set operating system process priority for Oracle database servers
Do not adjust the priority of the Oracle process in the operating system, because all background and foreground database server processes perform equally important tasks in an Oracle database system, requiring equal priority. So at installation time, all the database server processes are running with the default priority.
1.2 Adjusting Memory Allocations
The Oracle database server retains 3 basic memory caches, corresponding to 3 different types of data: the library cache, the dictionary cache, and the buffer cache. The library cache, together with the dictionary cache, forms a shared pool, and a shared pool plus buffer cache forms the whole system area (SGA). SGA is a fast access to the database of a system of the whole area, if the SGA itself needs to be frequently released and distributed, it can not achieve the purpose of fast access to data, so the SGA should be placed in main memory, do not put in virtual memory. Memory adjustment mainly refers to adjusting the size of the memory structure of the SGA to improve system performance, because the memory structure requirements of the Oracle database server are closely related to the application, so the memory structure should be adjusted before disk I/O adjustment.
Adjustment of 1.2.1 Library buffer
The library buffer contains private and shared SQL and pl/sql extents, which determine its size by comparing the hit ratio of the library buffer. To adjust the library buffer, you must first understand the activity of the library buffer, and the activity statistics for the library buffer remain in the dynamic performance table V$librarycache data dictionary, and you can query the table for its activity to determine how to adjust it.
Adjustment of 1.2.2 Data dictionary buffer
The data dictionary buffer contains the structure, user, and entity information about the database. The hit rate of the data dictionary has a great impact on system performance. The use of the data dictionary buffer is recorded in the Dynamic performance table V$librarycache, which can be queried to determine how the activity is adjusted.
1.2.3 Buffer Cache Adjustment
All the data accessed by the user process is accessed through buffer cache, so the hit ratio of that part is critical to performance. The use of the buffer cache is recorded in the Dynamic performance table V$sysstat, which can be queried for its activity to determine how to adjust.
2 Adjust network I/O in client/server mode
The application processing in the Client/server environment is distributed between the client application and the database service program. The network I/O between client and server in the Client/server environment is the bottleneck of the performance of the whole system, the less network I/O caused by a client application, the better the performance of the application and the whole system. One of the most important principles for reducing network I/O is to centralize application logic in the database server.
2.1 Complete constraints using Oracle Databases
When building a table for application, you should add the appropriate integrity constraints for some data with special requirements, so that the database itself, rather than the application, can be used to constrain the data to meet certain conditions. The complete constraints on the database server are optimized for system mechanisms that are lower than the SQL statement level, independent of the client, run only on the server, and do not have to pass SQL statements between the client side and the server side to effectively mitigate network I/O burdens.
2.2 Using Database triggers
Complete constraints can only achieve some simple data constraints, some of the more complex rules of things can not be done, it is best not to implement complex program control in the application, but should use database triggers to implement complex rules of things. Database triggers can be implemented by the database itself, rather than the application, to constrain the data to conform to complex processing rules, and to easily create, manage, and avoid large amounts of network I/O.
2.3 Using stored procedures, stored functions, and packages
Oracle's stored procedures and storage functions are named collections of Pl/sql that perform certain functions and are stored on the server side. A package is a method that encapsulates the process and function organization into a database program unit. They store SQL commands on the server side relative to the application's procedures and functions. Using stored procedures and stored functions, applications do not have to have SQL statements that contain multiple network operations to perform database server operations, but simply call stored procedures and stored functions, transferring only the name and output of the calling procedure on the network, which reduces the amount of network I/O.
3 Adjustment of the application
Optimization of 3.1 SQL statements
The execution speed of SQL statements can be affected by many factors. But the main influencing factors are: the driving table, the order of execution and the application of index. These factors can be changed indirectly by many different methods to achieve the optimal execution speed. This article mainly explores the optimization principles to be followed when connecting queries to multiple tables:
3.2 Establishing and using views, indexes
Views allow you to cut columns or rows in a base table, hide a subset of the data, and provide a view of complex queries involving multiple tables, making application development simple and fast. Indexing can improve query performance, reduce disk I/O, optimize queries on data tables, and accelerate execution of SQL statements. But any time to build an index can improve performance, when indexing should follow the following principles: The table is commonly used to query on the index column, the table is not often updated, insert, delete, and other operations, query the results of the number of records should be controlled in the original table 2%~4%.
3.3 Array interface using Oracle
When a client application inserts a row or uses a query to request a row from the server, instead of sending a network packet with a single row, the array is used to buffer multiple rows or retrieved rows to be inserted in an array, and then pass the arrays over the Internet with few packets. For example, a given SELECT statement returns 2000 rows of data with an average size of 40 bytes per row, a packet size of 4kB, and an array size parameter (arraysize) set to 20, sending 100 packets from the server to the client. If you simply set (ArraySize) to 2000, the same operation requires only 20 packets to be routed. This reduces the amount of network traffic and improves the performance of all applications.
4 Summary
When we develop the application, we follow the above methods and principles to adjust the system and receive satisfactory results. It should be noted, however, that the 3 interdependent components of the client, network, and server must be adjusted and synchronized to produce the best performance, and therefore should be analyzed and adjusted according to the specific circumstances of the system.