As the network applications and e-commerce continues to develop, the various sites visited more and more, the database also will be continuously expanded the size of the database system’s performance problem is more and more prominent, therefore, how to tune the database to the of crucial importance: how to use computer systems with limited resources to serve more users of services? How to ensure the user’s response to the speed and quality of service? These issues are all areas of server performance optimization.
As the world’s largest database vendor, Oracle Database achieved many successes at home and abroad application, according to statistics, 93% of the market. COM Inc., 65 “Fortune Global 100” companies invariably use Oracle databases to e-commerce. China’s many businesses, government agencies and e-commerce site also uses Oracle as the database server. Oracle database server is highly optimized software products, regular adjustments can optimize application performance of the system to prevent system bottlenecks.
Database performance optimization of the basic principle is: The minimum amount of disk access to obtain the required data. To evaluate the performance of the database needs to adjust in the database before and after their evaluation indicators: response time and throughput trade-offs between, database availability, database, hit rate and the efficient use of memory as a way to measure the effectiveness of regulatory measures, and guidance adjust the direction.
On Oracle database performance tuning, it should be according to certain order, because the system carried out in the previous step to adjust the back to avoid unnecessary adjustments or costly adjustments. Generally speaking, in two stages from the start:
- The design phase: its logical structure and physical structure of the optimal design to make it meet the demand conditions, system performance to achieve the best, the system overhead to a minimum;
- Database operation phase: to take the operating system level, database, some of the optimization of system performance measures to make the best;
Design and development phase adjustment in the system database
In order to take full advantage of Oracle Database features, in the design of information systems, database design, need to be based on business (such as traffic or the number of clients), and the availability of resources (such as the database server configuration) to consider the logical system architecture and database Structural design:
- Adjust the structural design of the application.Namely, the application used in the traditional C / S two-tier architecture, or B / W / D three-tier architecture. Different applications architecture required database resources are different.
- Appropriate use of partitioning, indexing, and archiving capabilities.If a business growth in the amount of data very fast, you might consider storing the database table of the business is using Oracle database partition function; for frequently accessed database tables need to be indexed; for frequent visits completed, but when business processes are no longer changes in the data into the historical archives of the method can be used to implement application systems to access the minimal amount of data.
- Proper preparation of the SQL statements to access the data.A good database, SQL statements that can be reused and reduce the analysis time; the right to use the index to access the data blocks can significantly reduce reduce response time. The implementation of the application will eventually be reduced to a database in the SQL statement and, therefore, the implementation of the efficiency of SQL statements determines the Oracle database performance. Oracle Corporation recommends using the Oracle statement optimizer (Oracle Optimizer) and line lock manager (row-level manager) to adjust and optimize the SQL statements.
- Adjust the hard disk I / OThis step is completed prior to development of information systems. Database administrator can be composed of the same table space data file on a different hard disk, so that between the hard disk I / O load balancing.
- Determine the data block size and storage parameters.As the database block size in the database can not be created after the change (unless rebuild the database), so in order to reduce the data link and row migration, but also to improve disk space utilization, in the design of the database to determine the appropriate data block size and storage parameters. Usually is based on sample data to determine the data block size, and according to current status and future development trend of business to determine storage parameters.