"Reprint" Oracle performance optimization in my eyes

Oracle performance optimization in my eyes

There are several aspects to the operation of a business system: functionality, stability, efficiency, security. The performance of a system includes network performance, application performance, middleware performance, database performance, and so on.

Today, from the perspective of database performance, some views on Oracle performance optimization are discussed.

First of all, for performance problems, we first touch the general is a business function slow, speed customer unacceptable. The performance for the system is as follows:

When a performance problem arises, many people guess the reasons for each aspect of the problem.
Today, we mainly talk about the performance of database, as far as the problem is concerned, after analyzing the database performance, we can find the problem of database performance, then we need to optimize the problem of database performance.

From a performance optimization perspective, everyone wants to optimize the database system to run faster and better. But hopefully, ultimately, the degree to which system optimization can eventually reach is related to many aspects of the database system.

From the point of view of optimization, the performance of database can be divided into several kinds of problems: operating system performance problem, IO performance problem, database system performance problem, SQL performance problem, network performance problem and so on.

According to the actual results, more than 80% of database performance problems are caused by SQL performance problems. The following talk about SQL performance issues, a frequently encountered a system configuration problem: received the customer phone said "today to do trading system of historical data archiving, after the historical data archive, the original history of 3 years of historical data, now the history table only 1 years of data, but the history of the menu is much slower than the original, Look at the SQL execution plan comparison, the original is to walk the index, now go is the full table scan, how to deal with it? ”

In view of this problem, based on experience:
? First verify that the history table is a partitioned table
? Then let the customer check the database parameters Optimizer_index_cost_adj, according to the standard parameters configuration optimizer_index_cost_adj=10, we recommend that the customer set this parameter to 5 try, the customer after the proposal changed, the problem solved.

This problem appears to be a SQL problem, but it is solved by database parameters, which indicates that many problems of database are interrelated and affected.

Since more than 80% of database performance issues are caused by SQL performance issues, let's talk about some of the personal views of SQL optimization.

Before speaking on the internet or during an interview, most people agree that the SQL optimization solution is "first to identify performance problems from the AWR report (the time-consuming, logical-read, physical-read SQL in the AWR top SQL), the SQL execution plan, and the SQL execution plan. , focus on having a full table scan, have no cost operation, and then try to create new indexes, adjust indexes, adjust table access order, and execute SQL again. ”

This step is actually not too big problem, 80% to 90% of the SQL problem can basically solve, the problem of personal existence is the effect of optimization is uncertain.

My view is that SQL optimization should be optimized by optimization methods, so that the effect of SQL optimization is better deterministic, because there are a lot of environments can not let us do this adjustment to try.

My view is this, in the SQL optimization, first of all, we have to read the same as the interpretation of SQL, such as the main role of SQL, there are no special operations such as group by, order by, sum, paging query, etc., involving a few tables, table data size, What are the criteria for the table, and what are the selectivity of these conditions for the table's data? Once you understand this information, you can basically determine what the SQL optimal execution plan should be, and when you look at the execution plan, it's easy to find out about the execution plan and how to fix it. If you do not know the above information, look at the execution plan is relatively blind.

There are a few things to focus on when looking at the execution plan:
? which tables were accessed
? How tables are accessed (the order in which tables are accessed, whether the table is an index scan or a full table scan)
? How tables are connected (Nest Loop join, hash join, sort merge Join, connection existence Cartesian product)
? Whether the selectivity of field conditions is reasonable (related field index, index selectivity)
? Sorting methods (sort by what field)
? How data is fetched (returns the size of a result set)

After confirming the above aspects, after the optimization, then confirm the above implementation plan information can basically confirm the effect of optimization is how. The purpose of SQL optimization is to reduce the consumption of system resources in SQL, and to reduce the number of accesses to the table in the SQL execution plan, such as a table if it is accessed once in SQL, not multiple accesses.

Some people will say that some SQL is very long, it is possible to have 20 tables in a SQL access, if you encounter such SQL, you need to solve SQL based on the structure of SQL disassembly, and then gradually optimize, you can be more accurate SQL optimization.
For SQL optimization, I hope you want to read the same as the algorithm to interpret SQL, summed up their own SQL optimization Method!

"Reprint" Oracle performance optimization in my eyes