Introduction to the method of optimizing Oracle Database application System

Introduction to the method of optimizing Oracle Database application System

From the following aspects: Database server, network I/O, application and so on the whole system to adjust, give full play to the efficiency of Oracle, improve the performance of the whole system Oracle database is widely used in all areas 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 Adjusting the operating system for Oracle database server Operations 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. Select sum (Pins), sum (reloads) from V$librarycache; The pins column gives the total number of SQL statements, Pl/sql blocks, and the definitions of the accessed objects; The reloads column gives an implicit analysis of the SQL and Pl/sql blocks or an error that occurs in the library program buffer when the object defines the reload. If the sum (Pins)/sum (reloads) ≈0, the hit ratio of the library buffer is appropriate, and if sum (Pins)/sum (reloads) >1, adjust the initialization parameter shared_pool_size to resize the amount of memory allocated to the shared pool. 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. Select sum (gets), sum (getmisses) from V$rowcache; The gets column is the statistic of the corresponding number of requests; The getmisses column is the number of requests for data that caused a buffer error. For frequently accessed data dictionary buffers, sum (getmisses)/sum (gets) <10%~15%. If this percentage is greater than this, consider increasing the capacity of the data dictionary buffer by adjusting the initialization parameter shared_pool_size to adjust the amount of memory allocated to the shared pool. 　　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. Select Name,value from V$sysstat where name in (' Dbblock gets ', ' consistent gets ', ' physical reads '); The value of the dbblock gets and consistent gets is the total number of reads in the request data buffer. The value of the physical reads is the number of times the file was read from the disk when the data was requested. The probability of reading from the buffer cache is called the hit ratio of the buffer, and the formula is calculated: Hit ratio=1-(Physical reds/(Dbblock gets+consistent)) If hit ratio<60%~70%, you should increase the Db_block_buffers parameter value. Db_block_buffers can adjust the amount of memory allocated to the buffer cache, that is, db_block_buffers can set the number of data blocks that allocate buffer caches. The total number of bytes in the buffer cache =db_block_buffers the value of the *db_block_size value. The Db_block_size value represents the number of bytes in the block size, and can be queried for the V$parameter table: Select Name,value from V$parameter where name= ' db_block_size '; After you modify the initialization parameters for the above database, you must close the database before restarting the database to make the new settings work. 　　1.3 Adjusting disk I/O The disk's I/O speed has a significant impact on the overall system performance. Resolving disk I/O problems can significantly improve performance. The main reasons that affect disk I/O performance are disk competition, excessive I/O and allocation management of the data block space. When you create a new file for an Oracle database server, you should carefully consider the available disk resources on the database server, whether it is a data file used in a tablespace or a log file used by a data transaction to log on. If you have more than one disk on your server, you can distribute the files to each available disk, reducing the competition for data files and transaction log files on your database, thereby effectively improving your server's performance. For different application systems have their own data sets, should be creative different table space to store their respective application system data, and as far as possible to the table space corresponding to the data files on different disks, this physical to each application system of the table space scattered storage method, You can exclude the possibility of two application systems competing for disks. Data files, transaction log files are stored on separate disks, so that the disk access performed by the transaction does not prevent disk access to the corresponding things log registrations. If you have more than one disk available, you can eliminate the disk contention that the log files may produce by putting two of things log members on separate disk drives. The table data and index data of an application should be scattered on different table spaces, and the different types of tablespaces should be kept on different disks so as to eliminate the disk competition of table data and index data. 1.4 Adjusting the rollback segment of the database server A rollback segment is a storage area that uses the storage area to store the original data values of rows that were previously updated or deleted by a transaction. If a user wants to roll back a change made by a transaction, the database changes the data from the rollback segment and the rows affected by the transaction to their original state. The rollback segment, which controls the ability of the database to process transactions, plays a key role in the success of the database, regardless of how well the other parts of the database are designed, and if it is designed unreasonably, it will seriously affect the performance of the system. The principles for establishing and adjusting rollback segments are as follows. 　　1.4.1 Separate rollback segment A separate rollback segment is the creation of more than one table space for the rollback segment, separating the rollback segment from the data dictionary, user data, index, and so on. Since the write of the rollback segment is performed in parallel with the data and index writes, separating it can reduce I/O contention. If the rollback segment is not separated from the data, you cannot take the tablespace offline or undo it until each rollback segment in the tablespace is offline or undone. Once the tablespace is unavailable, all of the rollback segments in the tablespace are not available, which wastes all allocated disk space. Therefore, the independent rollback segment makes database management easier. The recurrent contraction of the rollback segment makes the free block of the tablespace more easily fragmented. Separating the rollback segment can reduce the fragmentation of the database tablespace. 　　1.4.2 Create different sizes of rollback segment groups For some online things, they often modify a small amount of data, creating many small rollback segments to their advantage. The entry of each thing can only be limited to a rollback segment, the rollback segment should be sufficiently large to accommodate a complete thing processing, so for some larger things, need a larger rollback segment. The very individual processing of things offline produces a lot of rollback information, which requires an oversize rollback segment to handle. Based on these theories, three groups are created for the above 3 things in Oracle database servers: The small group of things, the larger things group, the big things group and other size of the rolling back segment group, and dispersed to 3 different table spaces, the same size in the group, should be able to meet the maximum requirements of the object processing. 　　1.4.3 Create an appropriate number of rollback segments The number of the general rollback segment and the number of concurrent things, the following gives the number of concurrent things to establish a rollback segment reference number: Concurrent Things (n) rollback segment number N<16 4 16≤n<32 8 N≥32 N/4 　　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. For example, the record in the current table A history is transferred from table A to History table B, expressed as LSBs. Implementing in an application: Using a Database trigger: Beign Create Trigger Delete1 Update A set lsbs= ' T '; After update of LSBs on A Insert into B for each row Select * from A where lsbs= ' T '; Insert into B Delete A where lsbs= ' T '; SELECT * from A where:new.lsbs= ' T '; End; Delete A where:new.lsbs= ' T '; End Delete1; When implemented in an application, all SQL commands request that the transferred data be exchanged over the network on both the client side and the server side, unlike database triggers, where SQL itself does not need to transmit data over the network, as it does on the server side. When the amount of data is very large, and when multiple users operate simultaneously, by implementing complex control in the application, the load of network I/O will be increased, and the performance of the whole system can be reduced, and database triggers will avoid this situation completely. 　　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. For example, the definition of base table A and B is: Name Char;d etail char (10); A table 1 million records, the application retrieves a record of the detail column from base table A that conforms to the given criteria and inserts it into base table B. Declare Cursor Cursor1 is Select*from a;poin cursor1%type; Con1 number (2); res1 char (4) = ' ABCD '; Begin Insert into B values (poin.name,poin.detail); For poin in Cursor1 loop end loop; End Loop; For Con1 in 1..7 loop Commit; If substr (Poin.detail, con1,4) =res1 then end; If the button trigger in developer/2000 from is implemented directly with Pl/sql and overwrites it as an Oracle stored procedure, and then calls this procedure implementation comparison in from, the latter performance is significantly improved. When considering using the 3 methods above: Consider the use of full constraints first. For database triggers and stored procedures, a database trigger is used if all programs accessing the database are required to automatically implement certain rules or checks, and if only a few programs are implemented with certain rules or checks, a procedure can be created to allow the program to call the procedure. 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 to multiple tables: (1) The columns used for the JOIN clause should be indexed, and the index should be used as much as possible in the WHERE clause, rather than avoiding the index. 　　(2) The connection operation should be driven from the return of fewer rows. 　　(3) If the linked table A and B,a table length is far greater than table B, it is recommended to drive from the larger table A. 　　(4) If the WHERE clause contains selectivity conditions, where no=20 places the most selective part at the end of the expression. (5) If only one table has an index and the other table has no index, the table without indexes is usually used as the driver table. 　　If the no column in table A is indexed, and no column of table B is indexed, then table B should be used as the Driver table and table A as the driven table. (6) If the column used for the connection and the other selection criteria column in the WHERE clause are indexed, then according to each index to query the validity and selectivity of the different levels, combined with the specific data composition of the table, select the optimization path, generally need to consider: which columns in the clause can use the index, Which indexes are unique and are queried for the number of rows in the table. 　　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.