Issues to be addressed in Oracle Optimization and performance tuning

In order to ensure the Oracle database running in the best performance state, the database optimization strategy should be considered before the information system development. The optimization strategy generally includes the server operating system parameter adjustment, the database parameter adjustment, the network performance adjustment, the application SQL statement analysis and the design and so on several aspects, in which the application analysis and the design is completes before the information system development.

The performance of Oracle database has two indexes, database throughput and database user response time. Database user response time can be divided into system service time and user wait time two items, namely:

Database user Response time = System service time + user wait time

Therefore, there are two ways to obtain satisfactory user response time: One is to reduce the system service time, that is to improve the throughput of the database, and the other is to reduce the user's waiting time, that is, to reduce the user access to the same database resources conflict rate.

Database performance optimization includes the following sections:

Adjusting the design of the data structure this part is done before the information system is developed, and programmers need to consider whether to use the partitioning capabilities of the Oracle database, whether or not to index frequently accessed database tables.

Adjusting the application structure design This part is also done before the information system is developed. In this step, programmers need to consider what architecture the application uses, whether it uses a traditional client/server two-tier architecture, or a three-tier architecture with Browser/web/database. Different application architectures require different database resources.

Adjusting the execution of the database SQL statement application ultimately boils down to the execution of SQL statements in the database, so the efficiency of the execution of the SQL statement ultimately determines the performance of the Oracle database. Oracle Company recommends using the Oracle Statement Optimizer (Oracle Optimizer) and the row lock manager (Row-level Manager) to adjust the optimized SQL statements.

Adjusting server memory allocation memory allocation is optimized in the process of information system operation. Depending on the health of the database, the database administrator can not only adjust the data buffers, log buffers, and shared pools of the database system global area (SGA area), but also adjust the size of the program global Area (PGA area).

Adjusting hard disk I/O This step is done before the information system is developed. The database administrator can put the data files that make up the same table space on a different hard disk and I/O load balance between the hard disks.

Adjusting operating system parameters such as the Oracle database running on UNIX operating systems, you can adjust the size of the UNIX data buffer, the size of memory that each process can use, and so on.

In fact, the database optimization measures mentioned above are interrelated. Oracle database performance degradation is basically the user response time is relatively long, users need to wait for a long time. The reason for the deterioration of performance is a variety of factors, and sometimes the results of the performance degradation caused by multiple elements, this requires the database administrator has a more comprehensive computer knowledge, can be sensitive to the impact of the database performance of the main reason. In addition, good database management tools are also important for optimizing database performance.

Database performance optimization tools commonly used in Oracle databases are:

Oracle Database online Data dictionary Oracle's online data dictionary can reflect the dynamic performance of Oracle, which is helpful for tuning the database.

Operating system tools such as the Vmstat, Iostat, and so on Unix operating systems can see the use of system-level memory and hard disk I/O, which can help administrators figure out where the system bottlenecks appear.

The SQL language Tracking tool can record the execution of SQL statements, and administrators can use virtual tables to adjust instances and use SQL statements to track files to adjust application performance. The SQL language Tracking tool outputs the results to an operating system file that the administrator can use to view these files using the Tkprof tool.

Oracle Enterprise Manager (OEM) This is a graphical user management interface that users can use to facilitate database management without having to remember commands for complex Oracle database management.

Explain plan--sql Language Optimization commands use this command to help programmers write efficient SQL languages.