Shulou(Shulou.com)06/01 Report--

The Oracle Real-World Performance team is a small and talented team with more than 400 person-years of experience in database performance optimization. Team members are located in the United States, China and Europe, constantly looking for and creating new ways to analyze and diagnose the performance of business systems in the world today. The Oracle Real-World Performance team has a lot of outstanding achievements, and it is not uncommon to improve customer system performance by dozens of times and 1000 times or more in their work.

In China, industry colleagues refer to the Oracle Real-World Performance team as the RWP team for short. At present, there are 7 members of the RWP team in China.

When it comes to SQL optimization, what is the first reaction in your mind as a reader? Indexes? Hint? Division? Parameters? Carry out the plan? Ha, ha. Today, let's talk about the overall idea of SQL optimization, hoping to enlighten you.

1. Set a high goal

If you optimize an SQL from one hour to one minute, will you stop working? Will you consider whether it can be optimized to 1 second?

At work, everyone has pressure, under pressure, it is easy to neglect to think. How long a SQL can run depends on what kind of hardware and software environment it runs in. Whether a SQL can run faster is essentially whether it can make full use of hardware resources and software capabilities.

It is very important to set a high goal for yourself to optimize SQL!

two。 To optimize those good SQL

With a high goal, next, you need to find those good SQL to optimize. So, what is a good SQL?

(1) valid SQL

The database is designed to execute SQL, not an invalid SQL that reports an error as soon as it is executed.

If you execute a SQL that reports an error in ORA, then this is an invalid SQL, which should not exist in your system and certainly should not be your optimized object.

If you execute a SQL that reports an error in ORA, it will be a failure parse in the database. If you have failure parse in your system's AWR report, you should be aware that the consequences can be serious.

(2) SQL that you know the meaning of business

In many cases, some SQL and PL/SQL stored procedures do not need to be executed at all. But for a variety of reasons, those SQL and PL/SQL stored procedures have been in the system for a long time, and the people who wrote those SQL and PL/SQL stored procedures may have changed jobs a long time ago, and no one moves those SQL and PL/SQL stored procedures for the sake of so-called "stability." It certainly doesn't make any sense to optimize these SQL and PL/SQL stored procedures that don't need to be executed at all.

Therefore, before optimizing any SQL, you should first know the meaning of that SQL business, make sure that it really needs to be executed, and then optimize it.

(3) constructed SQL

If there is an IN list in a SQL statement and hundreds of values in the IN list, then those hundreds of values are likely to come from another SQL rather than manual input. Because there is an upper limit on the number of values in the IN list, some SQL may even grow like the following:

Tens of millions of values are in the IN list. Is that because SQL is not well constructed? should it be changed to a JOIN before considering others?

(4) there is no wrong SQL.

If N tables are used as JOIN, there should be 1 JOIN condition for NMI in general. If the JOIN condition is less than 1, there will be CARTESIAN JOIN, and there will be duplicate values in the result set. Adding DISTINCT to SELECT LIST usually allows SQL to get the functionally correct result set. This is like you go to the bank to withdraw money, actually only withdraw 1000 yuan, but you first withdraw 2000 yuan, and then save the remaining 1000 back, superfluous, although the actual result is correct, you did withdraw 1000 yuan.

When the amount of data processed by SQL is small, this superfluous move does not have a significant impact on response time. However, when the amount of data processed by SQL is large, this impact will be fully highlighted. Again, in the case of withdrawal, if you actually only withdraw 1000 yuan, but you withdraw 10001000 yuan first, and then deposit the remaining 10000000 yuan back. In the end, you will also get 1000 yuan, but the time it takes for the bank staff to count 10001000 yuan when they withdraw the money for you, and the time to count 10000000 yuan when they deposit the money back, is the time for you to do business, and it will take you quite a long time to withdraw the money.

The data type of the value in the WHERE condition in the SQL statement should be the same as the data type of the corresponding column. Otherwise, although the SQL statement will not report an error, it will implicitly use the function to convert that column to match the data type of the corresponding value to execute SQL. This implicit data type conversion may lead to errors in ORA-01722, may cause indexes on the corresponding columns not to be used, and may lead to situations where partition clipping can be used but not, and the response time may be several orders of magnitude worse.

3. Give SQL a good execution environment

SQL needs to be executed in a good environment to perform well. So what is a good execution environment?

Patching the software correctly is the first step in creating a good execution environment. Obviously you have spent money to buy software, obviously other software manufacturers have patches to make the software run better and faster, why not patch it? Of course, patching is a technical job, how to correctly patch the software, must be in accordance with the instructions of the software manufacturer, or consult the software manufacturer.

Using the default init.ora parameter settings is also an important part of creating a good execution environment. Using the default init.ora parameter setting means that you are using it the way the Oracle internal development team designed the software, which means that you are using software that has been rigorously tested by the Oracle internal test team. Of course, some specific applications, such as Oracle's EBS, require changes to the init.ora parameters, which need to be modified because those changes have been strictly tested by the application software vendor.

If you need to modify some parameters as a temporary solution because of bug, you should change the corresponding parameters back in time after the bug is fixed, otherwise the consequences may be very serious.

In addition, if you modify the init.ora parameters at will, it may lead to after-sales problems.

4. Optimizing SQL from the point of view of database design

Oracle database software now uses Cost Based Optimizer (CBO), a cost-based optimizer.

In essence, an optimizer is a series of algorithms. The optimizer accepts the input information to generate an execution plan for the SQL. The information entered includes:

(1) Statistics

The statistical information includes two aspects, the statistical information of the system and the statistical information of the actual user data.

For the statistics of the system, it is recommended that you use the default settings. The most important thing for the statistical information of actual user data is to be representative and to be able to reflect the characteristics of the data.

(2) constraint

NOT NULL, competition, FK, UK and other constraints, if the actual data needs to conform to the constraints, then those constraints should exist in the database and should let the optimizer know that these constraints exist.

For instance. Multiple tables do JOIN. If a table is only JOIN, such as the following

The customer table appears only in the JOIN section, but not in the SELECTlist, nor in the query criteria, nor in the GROUP BY and ORDER BY sections. So if there is a foreign key constraint on JOIN key (lo_custkey) on the lineorder table, the optimizer will know that the JOIN of lo_custkey = c_custkey can always be JOIN, so it will not go to the JOIN customer table during actual execution. The execution plan can be something like this:

Will you open your eyes and see that the customer watch does not appear in the implementation plan! The fastest JOIN you can do is not JOIN. This situation is called JOIN elimination, and the prerequisite for it to occur is the existence of relevant constraints.

(3) Schema design

The design of Schema, including data model, index, partition, compression, clustering (data are physically stored together according to the corresponding key values), and so on, have a very important impact on SQL performance.

In some SQL, a table and its own JOIN dozens of times, because the data model is not well designed. At this point, if you just focus on SQL itself, the performance improvement you can achieve may be very limited.

Schema design is a big knowledge, and each aspect can affect the performance of SQL by several orders of magnitude. If you want to do a good job of SQL optimization, you must pay attention to schema design.

5. Optimize SQL from the point of view of execution

To optimize SQL from an implementation point of view, you should consider the following aspects:

Access method, whether to access data through an index or a full table scan.

The Join method, which is Nested Loop Join,Hash Join or Merge Join.

Join order, table A, Join table B, Join table C, or vice versa.

When executing in parallel, whether the data distribution method between the producer process group and the consumer process group is hash, broadcast, or other distribution methods.

Whether the data is skewed, whether some key values correspond to a lot of data, and other key values correspond to very little data.

Summary

The above points provide you with an overall idea of SQL optimization. The overall thinking is always important.

So specifically, if a poor-performing SQL is in front of you and you have to optimize it, where do you start? SQL Monitor Report will be your good friend. Welcome to fall in love at first sight. We will launch a series of articles to illustrate how to diagnose SQL performance problems with SQL Monitor Report. You are welcome to follow us.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.