Oracle Database System Performance Optimization policies

The lifecycle of a database system can be divided into three stages: design, development, and product. The lowest cost and maximum benefit of database performance optimization during the design phase. In the final stage, database performance optimization has the highest cost and the lowest benefit. Database optimization can be performed by optimizing the network, hardware, operating system, database parameters, and applications. The most common optimization method is hardware upgrade. According to statistics, the performance improvement obtained by optimizing network, hardware, operating system, and database parameters only accounts for about 40% of the database system performance improvement, the remaining 60% system performance improvement comes from application optimization. Many optimization experts believe that application optimization can improve system performance by 80%.

I. database performance optimization

Database Design is the foundation of application design. Its performance directly affects the performance of applications. Database performance includes the size of the storage space required and the length of query response time. To optimize the database performance, you need to standardize the tables in the database. The normalization paradigm can be divided into the first paradigm, the second paradigm, the third paradigm, the BCNF paradigm, the fourth paradigm, and the fifth paradigm. Generally, the logical database design meets the first three standards of standardization, but the table structure meeting the third paradigm is easy to maintain and basically meets the requirements of practical applications. Therefore, in practical application, it is generally normalized according to the standard of the third paradigm. However, normalization also has disadvantages: because a table is split into multiple tables, multiple table connections are required during query, reducing the query speed.

Due to the slow query speed caused by normalization, some applications need to respond quickly. Some tables should be de-normalized when designing tables. The following methods can be used for anti-Normalization:

1. Split table

Split tables include horizontal and vertical partitions.

Horizontal segmentation divides a table into multiple tables by row, which improves the query speed of each table. However, when querying and updating a table, you must select different tables. Multiple tables must be summarized during statistics, therefore, applications are more complex.

Vertical segmentation is used for tables with many columns. If some columns are accessed more frequently than other columns, the primary key and these columns can be used as a table, use the primary key and other columns as another table. By reducing the column width and increasing the number of rows on each data page, I/O can scan more rows at a time, thus improving the speed of accessing each table. However, due to the multi-Table connection, it is recommended that you query or update columns in different split tables at the same time.

2. Retain redundant Columns

When two or more tables need to be connected frequently in queries, you can add redundant columns to one of the tables to avoid too frequent connections between tables. Since the update operation on redundant columns must synchronize multiple tables, it is generally used when the data of the redundant columns is not changed frequently.

3. Add a derived Column

A derived column is calculated by multiple other columns in the table. Adding a derived column can reduce the statistical operation and greatly shorten the operation time during data aggregation.

2. Application Performance Optimization

Application optimization can be divided into two aspects: source code and SQL statements. Due to changes in program logic, source code optimization costs a lot in terms of time and risk, while the improvement in database system performance is limited, therefore, the optimization of applications should focus on the optimization of SQL statements. For massive data, the speed difference between inferior SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that a system can not simply implement its functions, instead, we need to write high-quality SQL statements to improve system availability.

The following describes the issues that need to be paid attention to when writing the where clause of some SQL statements. In these where clauses, even if some columns have indexes, the system cannot use these indexes when running the SQL statement because of poor SQL writing. The full table scan is also used, this greatly reduces the response speed.

1. is null and IS NOT NULL

Null cannot be used as an index. Any column containing null values will not be included in the index. Even if the index contains multiple columns, this column will be excluded from the index as long as one of these columns contains null. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved.

Any statement optimizer that uses is null or is not null in the where clause cannot use indexes.

2. Join Columns

For a joined column, the optimizer does not use an index even if the last joined value is a static value. For example, assume that there is an employee table (employee). For an employee's surname and name are divided into two columns (FIRST_NAME and LAST_NAME), you need to query a table named George Bush). The following is an SQL statement using join query:

Select * from employee where first_name | ''| last_name = 'George Bush ';

The preceding statement can be used to check whether there are employees of George Bush. However, you must note that the system optimizer does not use indexes created based on last_name.

When the following SQL statement is used, the Oracle system can use an index created based on last_name:

Select * From employee where first_name = 'George 'and last_name = 'Bush ';

What should we do in the following situations? If a variable (name) stores the name of the employee George Bush, how can we avoid full traversal and indexing? You can use a function to separate the surname and name in the variable name. However, note that this function cannot be used in the index column. The following is an SQL query script:

Select * from employee where first_name = SUBSTR ('& name', 1, INSTR (' & name', '')-1)

And last_name = SUBSTR ('& name', INSTR (' & name', '') + 1 );

3. like statement with wildcard (%)

The above example shows this situation. The current requirement is as follows: query the persons whose names contain Bush in the employee table. The following SQL statement can be used:

Select * from employee where last_name like '% Bush % ';

Here, because the wildcard (%) appears at the beginning of the search term, the Oracle system does not use the last_name index. This situation may not be avoided in many cases, but it must be well understood. Using wildcard characters will reduce the query speed. However, when a wildcard appears at another position of a string, the optimizer can use the index. For example, in the following query, the index is used:

Select * from employee where last_name like 'C % ';

4. Order by statement

The Order by statement determines how Oracle sorts the returned query results. The Order by statement has no special restrictions on the columns to be sorted. You can also add functions to the columns (such as joining or appending ). Any non-index item or computed expression in the Order by statement will reduce the query speed.

Check the order by statement carefully to find out non-index items or expressions, which will reduce performance. To solve this problem, rewrite the order by statement to use the index. You can also create another index for the column you are using. Avoid using an expression in the order by clause.

5. NOT

When querying, we often use some logical expressions in the where clause, such as greater than, less than, equal to, and not equal to. We can also use and (and), or (or) and not (not ). NOT can be used to reverse all logical operators. The following is an example of a NOT clause:

... Where not (status = 'valid ')

If you want to use NOT, brackets should be added before the phrase to be reversed, and the NOT operator should be added before the phrase. NOT operator is included in another logical operator, which is NOT equal to (<>) operator. In other words, even if the NOT word is NOT explicitly added to the query where clause, NOT is still in the operator. See the following example:

... Where status <> 'invalid ';

Let's look at the example below:

Select * from employee where salary <> 3000;

You can rewrite this query to a statement that does NOT use the NOT statement:

Select * from employee where salary <3000 or salary> 3000;

Although the results of these two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes for salary columns, while the first query does not.

6. IN and EXISTS

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. Subqueries in two formats can be used in the where clause.

The first format is to use the in operator:... where column in (select * from... where ...);

The second format is to use the exist operator:... where exists (select 'x' from... where ...);

Most people use the first format because it is easier to write. In fact, the second format is much more efficient than the first one. IN Oracle, almost all IN operator subqueries can be rewritten to subqueries using EXISTS.

In the second format, the subquery starts with 'select. Use the EXISTS clause to query the data extracted from the table without a pipe. It only displays the where clause. In this way, the optimizer does not have to traverse the entire table, but only performs the work based on the index (Here we assume that the column used in the where statement has an index ). Compared with the IN clause, EXISTS uses connected subqueries, which is more difficult to construct than IN subqueries.

By using EXISTS, the Oracle system first checks the primary query, and then runs the subquery until the first match is found, which saves time. When executing an IN subquery, the Oracle system first executes the subquery and stores the obtained result list IN a temporary table with an index. Before executing a subquery, the system suspends the primary query. After the subquery is executed, it is stored in the temporary table and then executes the primary query. This is why EXISTS is faster than IN queries.

At the same time, do not exists should be used as much as possible to replace not in. Although both use NOT (the index cannot be used to reduce the speed), not exists is more efficient than not in query.

Original text from [than the Internet], reproduced Please retain the original link: http://www.chinabyte.com/biz/cbfwq/242/2678242.shtml