Oracle tuning expert secret

Note: This article is not to give you a case, but to explain the optimization method. The Ancients cloud: it is better to teach people to fish than to teach people to fish. Here we will talk about how to fish.

The SQL optimization mentioned here is very interesting. We should start with thanksgiving.

Thanksgiving Day (Thanksgiving Day) is a festival in the United States and Canada. It was originally created by the American people to thank God for the wonderful harvest. The fourth Thursday of March November is Thanksgiving Day. Thanksgiving is an ancient festival created by the American people and a family of Americans. Therefore, Americans always feel friendly when talking about Thanksgiving. Thanksgiving Day is the most authentic and American-style holiday in the United States.

The first day after Thanksgiving Day is Friday. On this day, Americans have the habit of crazy shopping, so it is called Black Friday. Recently, they have changed from traditional shopping malls to online shopping, so my stunned operation was born from this, and my DB was hacked by crazy shopping on this day.

The customer provided an AWR report during peak hours. After careful analysis, it was found that it was a high-CPU-consuming SQL statement, which only took 85% of the CPU, this gives us an in-depth understanding of SQL optimization. We have also done some DB optimization before, basically through analysis tables, index creation, parameter adjustment, PATCH, and so on. However, this time, it lasted for a week. After learning for a few days, it has not been solved yet. Finally, I decided to study SQL optimization and draw the following optimization conclusions:

I. 1/9 principle, referring to the original statement of Oracle Daniel Tom: 90% of database performance problems can be solved by adjusting 10% of SQL statements.

Tom say: SQL tuning constitute a good 90 percent or more of the effort. that sounds s right; before we even get the DBA into s involved, we the developers have done 90 percent of the work. this is why I think most people do not understand database tuning.

Second, SQL optimization is always available only for connections between large tables. Do not doubt. First of all, the Big Table I mentioned refers to a large table, whether it is a large table or a small table, as long as there is a large table, because there is no performance problem between small tables and small tables. Secondly, there is a connection to optimize the value. I really don't know what optimization is available for single-Table SQL, you can query a single table at three points: full table/index/partition table.

Article 3: note that, as mentioned in the previous two articles, database 90% is optimized in SQL, and the key to SQL optimization is "large table join ", therefore, the SQL optimization mentioned below refers to large table join.

In SQL Execution, the execution plan provided by Oracle is not a mysterious thing. In fact, it is an algorithm used by oracle to search for the results in the table. The algorithm is too familiar, I learned this course in the Data Structure Course of the University (Sorry, this course has always been used to go to bed, because I didn't know what the old man was talking about at the time). The algorithm is the computing process, here, it refers to the search path. To put it bluntly, execution plan = algorithm = search path. To find the optimal execution plan, you need to find the optimal algorithm. Is the optimal algorithm familiar? I am not familiar with the following terms: "bubble algorithm", "compromise algorithm", and "Quick Search". Many people who have been to software company interviews must have done this. In the Execution Plan provided by Oracle, these detailed processes are provided. You can see which table/index Oracle goes to fetch data, how many rows are taken based on the conditions, and which table is connected with the obtained results. After a series of operations, what is the final result for you.

However, ORACLE is also a software, not a god. You can give it an SQL statement. before it starts operations, it does not know which algorithm is optimal. It is impossible to give it a try, let's make a comparison. In addition, there are hundreds of execution plans for each statement, and each statement is executed hundreds of times. Then the server simply does not have to survive. Therefore, it can only predict an algorithm that is best suited to this SQL statement and then execute it. Note:

1. There will never be an optimal algorithm. If there is an optimal algorithm a, Will optimization be required? Every time we make a line based on optimal algorithm.

2. Oracle considers that the optimal algorithm is not necessarily the optimal algorithm. If so, do we still need to do it?

Here: We understand that the ORACLE optimization 90% = SQL optimization = large table connection optimization = Execution Plan Optimization = algorithm optimization, while the large table connection is 2 points: 1. connection sequence. 2. Join Algorithms (nested/HASH/merge ).

Conclusion: The optimization of SQL is to manually design the execution plan of the optimal connection sequence and algorithm.