What are the measures for database performance optimization?

1, 1, adjust the design of data structure. This section is done before the development of the information system, and the programmer needs to consider whether to use the partitioning capabilities of the Oracle database, whether or not indexes are required for frequently accessed database tables.

2, 2, adjust the application structure design. This part is also done before the development of the information system, where the programmer needs to consider what architecture the application uses, whether it uses a traditional client/server two-tier architecture or a three-tier architecture using Browser/web/database. Different application architectures require different database resources.

3, 3, adjust the database SQL statement. The execution of the application ultimately boils down to the execution of the SQL statement in the database, so the efficiency 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 tune optimized SQL statements.

4, 4, adjust the server memory allocation. Memory allocation is optimized during the operation of the information system, and the database administrator can adjust the size of the database system global (SGA) data buffers, log buffers, and shared pools based on the database health, as well as the size of the program Global Zone (PGA Zone). It is important to note that the SGA area is not as large as the better, the SGA area over the convention occupies the memory used by the operating system and causes the virtual memory page exchange, which will reduce the system.

5, 5, adjust the hard disk I/O, this step is completed before the development of information systems. The database administrator can place the data files that comprise the same tablespace on different hard disks to achieve I/O load balancing between the drives.

6, 6, adjust operating system parameters, such as: The Oracle database running on the UNIX operating system, you can adjust the size of the UNIX data buffer pool, each process can use the size of the memory and other parameters.

In fact, the above-mentioned database optimization measures are interlinked. Oracle database performance Deterioration performance is basically the user response time is relatively long, users need to wait long time. However, the deterioration of performance is due to a variety of factors, sometimes caused by a combination of performance deterioration results, which requires the database administrator has a relatively comprehensive computer knowledge, can be sensitive to detect the impact of database performance is the main reason. In addition, good database management tools are important for optimizing database performance.

Oracle Database Performance Optimization Tool

Common database performance optimization tools are:

1, 1, Oracle Database online data dictionary, Oracle Online data dictionary can reflect the dynamic operation of Oracle, for tuning database performance is very helpful.

2, 2, operating system tools, such as Vmstat,iostat of the UNIX operating system, can look at system-level memory and the use of hard disk I/O, tools that are sometimes useful for administrators to figure out where a system bottleneck is occurring.

3, 3, SQL Language Tracking tool (SQL Trace FACILITY), SQL language Tracing tool can record the execution of SQL statements, administrators can use virtual tables to adjust the instance, using SQL statement tracking files to adjust application performance. The SQL language Tracing tool outputs the results as a file for an operating system, which administrators can use to view these files using the Tkprof tool.

4, 4, ORACLE Enterprise Manager (OEM), a graphical user management interface that allows users to easily manage databases without having to memorize complex ORACLE database management commands.

5, 5, EXPLAIN plan--sql language optimization command, use this command to help programmers write efficient SQL language.

System performance assessment of Oracle database

The types of information systems are different, and the database parameters that need attention are also different. Database administrators need to focus on different database parameters depending on the type of their information system.

1, 1, online transaction processing Information System (OLTP), this type of information system generally need to have a large number of INSERT, update operation, the typical system includes the Civil Aviation ticket sale system, Bank savings system and so on. OLTP systems need to ensure database concurrency, reliability, and end-user speed, and the Oracle database used by such systems needs to be primarily considered for the following parameters:

is the database rollback segment sufficient?

l l need to establish an Oracle database index, aggregation, hash?

is the system global Area (SGA) size sufficient?

is the L-SQL statement efficient?

2, 2, Data Warehouse system (warehousing), the main task of this information system is to query from Oracle's massive data to get some rules between data. The database administrator needs to focus on the following parameters for this type of Oracle database:

L l use b*-index or bitmap index?

L l Do you use parallel SQL queries to improve query efficiency?

L l How do I write a stored procedure using PL/SQL functions?

l l If necessary, we need to establish parallel database to improve the efficiency of database query.

Tuning Principles for SQL statements

The SQL language is a flexible language, and the same functionality can be implemented using different statements, but the execution efficiency of the statements is quite distinct. Programmers can use the explain plan statement to compare various implementations and to select the optimal implementation scheme. In general, programmers writing SQL statements need to meet the following rules:

1, 1, try to use the index. Try comparing the following two SQL statements:

Statement A:select Dname, deptno from dept WHERE Deptno not in

(SELECT deptno from EMP);

Statement B:select Dname, deptno from dept WHERE not EXISTS

(SELECT deptno from emp WHERE dept.deptno = Emp.deptno);

The results of these two query statements are the same, but when you execute statement A, Oracle scans the entire EMP table, does not use the DEPTNO index built on the EMP table, executes statement B, because a federated query is used in a subquery. Oracle is only a partial data scan of the EMP table and takes advantage of the index of the Deptno column, so statement B is more efficient than statement a.

2, 2, select the joint order of joint queries. Consider the following example:

SELECT stuff from Taba A, Tabb B, tabc C

WHERE A.acol Between:alow And:ahigh

and B.bcol Between:blow And:bhigh

and C.ccol Between:clow And:chigh

and a.key1 = B.key1

AMD A.key2 = C.key2;

In this SQL example, the programmer first needs to select the primary table to query, because the primary table is scanned for the entire table data, so the primary table should have the least amount of data, so the Acol column of table A in the example should have a smaller range than the corresponding columns in table B and Table C.

3, 3, in the subquery use in or not the statement carefully, using where (not) exists the effect is much better.

4, 4, prudent use of the views of the federated query, especially the more complex views of the joint query. Generally, a query on a view is best decomposed into a direct query effect on a data table.

5, 5, you can set the Shared_pool_reserved_size parameter in the parameter file, this parameter keeps a contiguous memory space in the SGA shared pool, and the contiguous memory space is beneficial for storing the large SQL package.

6, 6, the Oracle company provides the DBMS_SHARED_POOL program can help programmers to "nail" some frequently used stored procedures in the SQL area without being swapped out of memory, programmers for frequently used and memory-intensive stored procedures "nails" To memory helps improve end-user response times.

Tuning of CPU parameters

CPU is an important resource of the server, the server is in good working state is at the peak of work CPU utilization at more than 90%. If the idle time CPU utilization is above 90%, indicates the server lacks the CPU resources, if the CPU utilization is still very low at the peak of work, the server CPU resources are also more abundant.

Use the same command to see CPU usage, General UNIX operating system server, you can use the Sar–u command to view CPU usage, NT operating system server, you can use NT Performance Manager to view CPU usage.

The database administrator can see the CPU time used by the Oracle database by looking at the CPU used by this session statistic in the V$sysstat data dictionary to view the OS User level CPU times Statistic items know the CPU time under the operating system user state, check the "OS system call CPU time" statistic to know the CPU times under OS State, the total CPU time of the operating system is the sum of the user state and the system state time. If the Oracle database uses CPU time over 90% of the total CPU time of the operating system, it is reasonable that the server CPU is basically used by the Oracle database, which, conversely, indicates that the server CPU is being consumed by other programs and the Oracle database cannot get more CPU time.

The database administrator can also view the V$sesstat data dictionary to obtain the CPU time consumed by each session of the currently connected Oracle database, thus knowing what session consumes more server CPUs.

There are many situations where there is a shortage of CPU resources: The reparse of SQL statements, inefficient SQL statements, and lock conflicts all cause CPU resources to be low.

1. The database administrator can execute the following statement to view the parsing of the SQL statement:

SELECT * from V$sysstat

WHERE NAME in

(' Parse Time CPU ', ' Parse time elapsed ', ' parse count (hard) ');

Here the parse time CPU is the system service times, the parse time elapsed is the response times, the user waits the time

Waite time = Parse Time Elapsed–parse time CPU

Thus, the average parsing wait time =waite time/parse count of the user SQL statement can be obtained. This average wait time should be close to 0, if the average parsing wait time is too long, the database administrator can use the following statement

SELECT Sql_text, Parse_calls, executions from V$sqlarea

ORDER by Parse_calls;

To find out what SQL statement parsing efficiency is low. Programmers can optimize these statements, or increase the value of the Oracle parameter session_cached_cursors.

2. The database administrator can also use the following statement:

SELECT buffer_gets, executions, sql_text from V$sqlarea;

Looking at inefficient SQL statements, optimizing these statements can also help improve CPU utilization.

3, 3, the database administrator can use the v$system_event data dictionary "latch Free" statistics to see the conflict of the Oracle database, if there is no conflict, latch free query out no results. If the conflict is too large, the database administrator can reduce the Spin_count parameter value to eliminate high CPU usage.

Adjustment of memory parameters

The adjustment of memory parameters mainly refers to the system global Area (SGA) Adjustment of the Oracle database. The SGA consists mainly of three parts: a shared pool, a data buffer, and a log buffer.

1, 1, the shared pool consists of two parts: the shared SQL area and the data dictionary buffer, the shared SQL area is the area that holds the user's SQL command, and the data dictionary buffer holds the dynamic information of the database running. The database administrator executes the following statement:

Select (sum (pins-reloads))/sum (Pins) "Lib Cache" from V$librarycache;

To view the usage of the shared SQL area. This usage should be above 90%, otherwise the size of the shared pool needs to be increased. The database administrator can also execute the following statement:

Select (sum (gets-getmisses-usage-fixed))/sum (gets) "Row Cache" from V$rowcache;

View the usage of the data dictionary buffers, which should also be above 90%, otherwise you need to increase the size of the shared pool.

2, 2, data buffer. The database administrator can use the following statement:

SELECT name, value from V$sysstat WHERE name in (' db block gets ', ' consistent gets ', ' physical reads ');

To view the usage of the database data buffer. The results of the query can be calculated using the data buffer usage hit rate =1-(physical reads/(DB block gets + consistent gets).

This hit rate should be above 90%, otherwise you need to increase the size of the data buffer.

3, 3, log buffer. The database administrator can execute the following statement:

Select Name,value from V$sysstat where name in (' Redo entries ', ' Redo log space requests '); View the usage of the log buffer. The query results can calculate the request failure rate for the log buffers:

Application failure rate =requests/entries, the application failure rate should be close to 0, otherwise the log buffer is opened too small, you need to increase the log buffer of the Oracle database.

What are the measures for database performance optimization?