Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to optimize complex SQL statements in oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to optimize complex SQL statements in oracle.

Follow the following ideas:

1. First check whether there is a good execution plan, if the previous execution plan is good, then you can use SQL Profile to fix the execution plan.

For example, you can follow the steps of note 1400903.1, such as:

a)。 We have two sentences.

Select ename from scott.emp where ename='MILLER'

Its SQL_ID is 329d885bxvrcr and its plan hash value is 3045807146.

Select / * + FULL (EMP) * / ename from scott.emp where ename='MILLER'

Its SQL_ID is 4f74t4ab7rd5y and its plan hash value is 2872589290.

Our goal is to transfer 4f74t4ab7rd5y's plan to 329d885bxvrcr.

b)。 Call coe_load_sql_profile.sql to transfer plan

For example:

SQL > @ coe_load_sql_profile.sql

Parameter 1:

ORIGINAL_SQL_ID (required)

Enter value for 1: 329d885bxvrcr = > enter the SQL_ID of the original SQL here

Parameter 2:

MODIFIED_SQL_ID (required)

Enter value for 2: 4f74t4ab7rd5y = > fill in the SQL_ID of SQL with hint here

Parameter 3:

PLAN_HASH_VALUE (required)

Enter value for 3: 2872589290 = > fill in the plan hash value of SQL with hint here (expected plan)

At this point, SQL Profile came into being.

c)。 You can verify again whether the plan of the original SQL has changed as we expected.

two。 If you don't have a good execution plan before, you need to collect statistics next; because the optimizer generates an execution plan based on statistics, the latest statistics can often correct a bad execution plan.

3. If collecting statistics doesn't improve SQL performance, then we need to use SQL Tuning Advisory to help us optimize and see if we can find some suggestions.

I've noticed that you've actually tried this approach.

4. If none of the above methods solve the problem, then you need to start with the SQL statement itself.

It is further optimized by contacting the application team to modify the SQL logic.

At this point, I believe you have a deeper understanding of "how to optimize complex SQL statements in oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.