[Reserved] Adjustment of Oracle Database Performance of the Railways Operation Financial Revenue System
Http://www.powerba.com/develop/Data "Du Qing Sigs, according to the problem of computer configuration and installation process of the Railways operation financial income system, the adjustment of the performance of the Alphaaserver Oracle database Brief description. This is an important role in the use of future applications. Key words income environmental performance Adjust Oracle1 Introduction The Railway Department The Financial Income System is an important part of the railway financial system. The system mainly completes the management of full-service passenger ticket review, income analysis, with data processing capacity and safety. . In this paper, based on the system configuration, system installation and debugging process, the system is proposed to propose a system performance adjustment. 2 Operation Financial Income System Small Machine Configuration Background Railway Operation Financial Revenue System is a system with the Railway Department, the main network of the VAX3100 small machine. With the development of the railway operation income accounting, the original VAX3100 upgrades the ALPHA1200 mini-machine of the advanced 64-bit chip. The new application is a system based on UNIX operating system, the Oracle database, which makes a local area network (100M / s Ethernet), and implements communication and data sharing between different levels through railway bartending. . Centered at 10 / 100m / s adaptive hub, the new system is connected to each other with an Ethernet of 100m / s, and is connected to the original Ethernet, and the VAX system local area network (10M / s Ethernet) is associated with the same one. local area network. At the same time, run TCP / IP and Decnet network protocol to ensure data exchange with the original system. The AlphaServer 1200 communicates with the PC client via the TCP / IP protocol, communicating with the original VAX via the DECNET. The original VAX system can be logged in to the new system through the terminal server that is hung through the Terminal Server (Decserver90L ). Digital UNIX supports the LAT protocol (see Figure 1). Figure 1 Revenue System Network Structure Figure 3 Oracle Database Performance Adjust Oracle is a high-performance database software. Users can achieve performance optimization through the adjustment of the parameters. Performance optimization is mainly divided into two parts: First, the database administrator achieves optimization of the adjustment of system parameters, the developer has achieved adjustments by the developer's optimization of the application. Here, only the adjustment of the system parameters is discussed without the optimization of the application. For the adjustment of the system parameters, it can be divided into the following sections: (1) Adjusting the Memory Allocation System The global zone (SGA) is a memory segment assigned to Oracle containing Oracle Database instance control information. The size of the SGA has great impact on system performance, and its default parameter setting is only available to configure low computers, and does not adapt to the needs of existing devices in the income system. If these parameters are not adjusted, it will cause huge waste on system resources. For the Alpha 1200 of the income system, the SGA size is preferably around 160 megabit. Some parameters in the initialization parameter file have a decisive impact on the size of the SGA. Parameter DB-block-buffers (number of buffers in SGA cache buffer), parameter shared-pool-size (number of bytes assigned to shared SQL zones) is the main influence of SGA size. The DB-block-buffers parameter is the most important determinant of SGA size and database performance. This value is high, and the hit rate of the system can be improved, and I / O is reduced. The size of each buffer is equal to the size of the parameter db-block-size. Oracle database blocks indicate in bytes.
The Oracle SGA zone shared pool part consists of a library cache, a dictionary cache, and some other users and server session information, and the shared pool is the largest consumption ingredient. Adjust the size of each structure of the SGA area, which can greatly improve the performance of the system. Adjust the library cache containing private and shared SQL zones and PL / SQL zones in the library cache. An important issue for adjusting SGA is to make sure that the library cache is large enough to allow Oracle to maintain statements in a shared pool, improve statement analysis and execution efficiency, and reduce resource consumption. By comparing the hit rate of library cache to determine its size. Query the V $ librarycache data dictionary view (where Pins represents the cache hits, Reloads represents cache failed) SQL> Select Sum (Pins), SUM (Reloads) from V $ librarycache; if SUM (Reload) / Sum (Pins) ≈0, indicating that the hit rate of library cache is suitable, and if it is more than 1, it is necessary to increase the size of the shared-pool-size (in the initialization parameter file). Adjusting the Dictionary Cache data dictionary cache includes structures, users, entity information, etc. of the database. The hit rate of the data dictionary has a big impact on the system. In the calculation of hit rate, getMisses represents the number of failed, and Get is the number of successful success. Query V $ ROWCACHE Table: SQL> SELECT (1- (SUM (getMisses) / (SUM (Gets) SUM (getMisses))) * 100 from v $ rowcache; if this value is> 90%, the hit rate is appropriate. Otherwise, the size of the shared pool should be increased. Adjusting the database buffer cache Oracle Cache read / write data to the database during operation, the cache hits indicates that the information has been in memory, and the cache fails means that Oracle must perform disk I / O. The key to keeping the cache failure rate is to ensure the size of the cache. Initialization Parameter DB-Block-Buffers Controls the Size of Cache Cache in Database Buffer. You can query the V $ sysstat hit rate to determine if the value of DB-Block-buffers should be added. SQL> SELECT NAME, VALUE FROM V $ SSISSTAT WHERE NAME IN ('dbblock gets', 'consistent gets', 'physical reads'); by query results hit rate = 1-Physical Reads / (DBBLOCK GETS CONSITENT GETS) if hit The rate of DB-block-buffers should be increased <0.6 ~ 0.7. (2) Adjust the disk I / O disk I / O is the bottleneck of system performance, solve disk I / O, which can significantly improve performance. By querying V $ fileStat, you can know the frequency of usage of each physical file (Phyrds means each data file reading, phywrts means each data file write) SQL> SELECT NAME, PHYRDS, PHYWRTS from V $ DataFile DF, V $ FILESTAT FS Where DF.File # = fs.file #; For physical files with high frequency, the following policies can be used:. Point I / O on average as possible on as many disks as possible. Establish different tablespaces for tables and indexes. Separate data files with redo log files on different disks. Reduce disk I / O without Oracle Server.
(3) Adjusting the competition When multiple processes issued an application for the same resource, it produces competition. . Modifying the Process parameter This parameter definition can be connected to the maximum number of processes in the Oracle database, and the default is 50. Note that Oracle's background process is also included in this number, it is recommended to change this value to 200. Reduce the competition of the scheduled process to reduce the competition of the schedule process, through query V $ dispatcher table to determine the competition of the scheduled process SQL> Select Network, Sum (BUSY) / SUM (Busy) Sum (IDLE) from V $ dispatcher group by Network If a certain protocol is busy with more than 50%, the value of MTS-DISPATCHERS should be added. Reduce multithreaded service process competition first query V $ sysstat table to determine if multi-threaded service process competition: SQL> Select Decode (Totalq, 0, 'No Request', Wait / Totalq || 'Hunderths of Seconds') from V $ sysstat where type = 'common'; if the number of shared service processes has reached the maximum value specified in the initialization parameter file, the average request waiting time continues to grow, then increase the MTS-MAX. -Servers' value. Reduce the redo log buffer competition By querying the V $ sysstat table to determine if the Redo Log file buffer is sufficient. SQL> SELECT NAME, VALUE FROM V $ sysstat where name = 'redo log space request'; the value of value here should be close to 0, otherwise, the value of the log-buffeqs of the initialization parameter file should be increased. Reduce the return segment of the return section also has an impact on performance, and the appropriate return segment is assigned according to the size of the matter. First, it is determined whether the number of returned segments can meet the needs of the system: Query V $ WaitStat table with V $ sysstat table SQL> SELECT CLASS, Count From V $ Waitstat WHERE Class in ('System undo header', System undo block ", 'undo header', 'undo block'); SQL> SELECT SUM (Value) from V $ sysstat where name in ('db block gets', 'consistent gets'); if any class / sum (value)> 10% So consider adding a return. The quantity of the return segment is generally set as follows: the number of users will be refunded by N <16 416
During the operation of the application system, there will be many changes, and the system needs to adjust the system according to the actual situation, so that the performance of the related applications is improved. Due to the high complexity of the application system, it is difficult to adjust parameters to the optimal performance of all links to the application system, and we can do it only to weigh the performance of the application system, and adjust the system to one. The user is more satisfactory. This requires system administrators to closely cooperate with application developers and users in accordance with the technical points described herein, to achieve the purpose of performance optimization. Change this: http://www.itpub.net/4358.html
