Chapter one   Read this article ,Oracle SQL There is no need to look at the optimization article !

Catalog SQL The essence of optimization

SQL Optimize Road Map

2.1 To develop SQL Optimization objectives

2.2 Check the implementation plan

2.3 Check the statistics

2.4 Check the efficient access structure

2.5 Check the parameters that affect the optimizer

2.6 SQL Statement writing problems

2.7 SQL optimal ??\x2F Poor execution plans due to restrictions

SQL Optimization case

SQL Execute the plan to get

4.1 How to get an accurate execution plan

4.2 Understand the execution sequence of the execution plan

One SQL The essence of optimization


Generally speaking ,SQL Optimization is to let SQL Run faster , send SQL There are many faster ways , For example, improve the efficiency of index use , Or parallel queries . You can see the formula inside :


Execution efficiency or execution time in general , It's and it's done once SQL The total amount of resources that need to be accessed (S) And the amount of resources that can be accessed per unit time (V) In inverse proportion ,S The bigger it is , The less efficient ,V The bigger, the more efficient . For example, through parallel queries , Then the amount of resources accessed per unit time can be increased .

Of course , It's just about execution time ,SQL Optimization is certainly more than just execution time reduction , It's about finding a balance between resource use and reduced execution time , otherwise , Blind parallel , It may not improve efficiency , On the contrary, the system resources are exhausted .

http\x3A? say ,SQL The essence of optimization is :1、 Reduced response time ;2、 Improve system throughput ;3、 Improve the load capacity of the system . There are many ways to do it , In improving system throughput and increasing system load capacity , Improve the individual SQL Looking for a balance between efficiency . It's just to minimize one SQL The total amount of resources that need to be accessed , For example, it's better to take the index , So don't use full table scan .


Two SQL Optimize Road Map

One SQL The optimization roadmap is as follows :


Specific operation steps :

2.1 To develop SQL Optimization objectives

Get to be optimized SQL、 Set optimization goals : from AWR、ASH、ORA Tools, etc. take the initiative to find problems SQL、 Users report performance problems DBA Intervention, etc , Through to SQL To understand the implementation of , First, make a preliminary plan SQL The goal of optimization .

2.2 Check the implementation plan

explain Tools 、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql etc. . The implementation plan is for us to carry out SQL The core of optimization , No plan , No optimization . Look at the execution plan. There are some tips , There are many ways , There is a difference between the various ways .

2.3 Check the statistics

ORACLE Use DBMS_STATS Packages manage statistics , Involving system statistics 、 surface 、 Column 、 Indexes 、 Statistics of objects such as partitions , The statistics are SQL Be able to use the assurance of correct execution of the plan . We know ,ORACLE CBO The optimizer uses statistics to determine the correct execution path ,JOIN The way of , therefore , Accurate statistical information is the first condition to produce a correct implementation plan .

You can see from this picture that , One SQL What are the steps to generate an execution plan , in my opinion :1、 Correct query transformation ;2、 Accurate statistics , It is an important guarantee to produce a correct implementation plan . Of course , also BUG, Or optimizer limitations can also lead to SQL inefficiency , Unable to produce the right execution plan .

As shown in the figure :


2.4 check ��/ Access structure

Important access structures , Such as index 、 Partition and so on can quickly improve SQL Execution efficiency . The data stored in the table itself , If there are too many pieces 、 Data skew is serious 、 Data storage is discrete , It also affects efficiency .

2.5 Check the parameters that affect the optimizer

2016-02-21 23:17izer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable Wait right SQL The implementation plan has a great impact . For example, sometimes we disable _optimizer_mjc_enabled Parameters , Let the execution plan not use Cartesian product to improve efficiency , Because there are many problems with this parameter , Therefore, the general production library is required to disable .

What else can affect the implementation plan ? Yes ,new features, Each version new features, The purpose of introduction is good , But in practice , Could trigger BUG. such as 11g Of ACS( Adaptive cursor sharing )、automatic serial direct path( Automatic serial direct path read )、extended statistics、SQL query result cache etc. . There are new features that can cause problems , So we need to use it carefully .

such as 11g adaptive cursor sharing, Adaptive cursor sharing , It is introduced to solve the problem of using bound variables and data skew values , To generate a diversity implementation plan . Because variables are bound to share execution plans , But the data is skewed , Some values require indexing , Some values require the full table , This conflicts with the use of bound variables . It used to be through cursor_sharing=similar Such a setting can solve , But there are a lot of BUG, Will produce version count Too high a problem , Or we're looking at different values ( If the value is small ), You can write more than one SQL To solve , It's not a good plan ,11g acs The introduction is to solve these problems , Let's give these things to oracle To do it . But it didn't work out , In the future, when you come across the implementation plan, it will change for a while , A fast is slow , First, you can check acs Is it closed .

alter system set “_optimizer_extended_cursor_sharing_rel”=’NONE';

2.6 SQL Statement writing problems

SQL The sentence structure is complex 、 Using unreasonable grammar , such as UNION Instead of UNION ALL Can lead to poor performance . Is not to say that ORACLE The optimizer is powerful , We can write whatever we want SQL 了 , That's not true .SQL Is a programming language , It can be executed fast , There are some general rules , Following this programming language feature , Simplify the sentence , To write a good program .SQL There's something wrong with the statement , We need to rewrite , You need to adjust your business , The change involves .

2.7 SQL Poor execution plan due to optimizer limitations

This is very important , The statistics are accurate ,SQL Don't complicated , The index also has ... All satisfied with , Why my SQL Still bad , So consider the optimizer limitations . Here it is 1 Common execution plan constraints , When semi join And or When used together ( That is to say exists(subquery) or ... perhaps in (subquery) or..., If the implementation plan is because OR Leading to FILTER The operator , You have to pay attention to , Maybe the slow factor is related to OR of . Now we have to rewrite SQL, Of course, rewrite it as UNION or UNION ALL 了 .

OK, All of the above are checked , My system is still poor , The function is still very slow , Or you can't get it from SQL Adjust itself to improve performance , To do that ? optimal design , This is the ultimate way . Some things can't be solved without optimized design , For example, during the peak period of business, there are a lot of people SQL,CPU Already very tight , Not to increase , Suddenly launched a resource consuming business , other SQL Can't adjust . That can only optimize the design , For example, some resource consuming businesses can be executed in different time periods .

Above points , It's something we need to consider when we optimize , It can be checked step by step . Of course ,80% To 90% Pure SQL Performance tuning , We build an index , Collect the right Statistics , Rewriting avoids optimizer limitations , Has been able to solve .

3、 ... and SQL Optimization case

Look at the first one to get optimized SQL....... If active optimization , Generally from AWR、ASH Wait until you find something with poor performance SQL, And then optimize it .



Look at a case , Occupy CPU 72% Of SQL The first mock exam is from the same module , The first line is stored procedures , Through the green box below SQL Compare with the first line , Mainly through EXECUTION, Basically, the green box below SQL It's in the stored procedure . You can also confirm with the business ,OK, these SQL The frequency of implementation is very high , Because it's a marketing business , If you want to optimize , That's all we have to do SQL.

these SQL, Single SQL Of buffer gets It's just 1000 Multipoint , It's still very efficient , But because it's carried out too often , So resource consumption is huge , therefore , I have to check , Can it be better ?

By the end of 1 strip SQL:58q183atbusat For example :

















SQL It's very simple , A query built A surface , One TABLE Functionally constructed B Table correlation ..... I don't know about this TABLE Is the function familiar with ? That is to turn a set into a table , yes PL/SQL The material in that

that collection Part is TABLE function , The following table has been scanned :



Step by step check , No problem , But know , Probably because HASH JOIN It leads to the problem of full table scanning , Whether to go or not NESTED LOOPS+INDEX Better , Obviously , To check TABLE How many lines does the function return .

Confirmed , At most, I will return to 200-300 That's ok , The final result set is just a few hundred lines .

So guess , The problem is TABLE function , go HASH JOIN, The execution plan above ,TABLE Function part ,ROWS It's empty .

Let's check one alone : return 8168 That's ok , return 8000 Multiple lines , Enough to lead to HASH JOIN 了 .... And the fact , The best we can do is return to 200-300 That's ok :


So the lines returned by each step , yes JOIN An important factor in the choice of mode , You can google it ,TABLE The function returns 8168 It's a fixed value ,block_size=8K It was this big when I was young , so to speak , This is a ORACLE A limitation of .


As long as you use TABLE function , They tend to walk HASH JOIN 了 Interested people can see the content of this link .

There are many solutions , That's to go NESTED LOOPS+index, since 8168 It's big , So let the optimizer know TABLE Function returns fewer lines , Only a hundred lines or so .

All of the following are OK , You can also use it hint:use_nl etc.

CARDINALITY hint (9i+) undocumented;

OPT_ESTIMATE hint (10g+) undocumented;


Extensible Optimiser (10g+).

because SQL Of SELECT Some only visit B, All from TABLE function , So just rewrite it as a subquery , Use subquery , natural distinct It's not necessary , the reason being that semi join( Half a connection ).

In the end cardinality hint Let the optimizer know B The only lines returned are 100 That's ok , You go for me NESTED LOOPS+INDEX, And solve it .

The original sql:


The modified sql:


The efficiency has been increased by dozens of times :


One takes up 72% Application , After we've increased dozens of times , That's obviously excellent for system performance . Final , As the number of execution increases 50% Under the circumstances ,w4sd08pa host CPU From the original peak utilization rate 47% The utilization rate of is reduced to 23%.

This problem can be solved in two ways :

1、 Guess and test the limitations of the optimizer (table Function fixed return line 8168);2、 The line actually returned 200-300. You can't have one without the other . If the actual number of returned rows is tens of thousands , that , Just through optimization SQL, It can not achieve good results .

Scan the QR code at the end of the text , Focus on DBA+ Community WeChat official account (dbaplus), Downloadable DBA+ Community Technology Salon 、OOW The conference 、2015GOPS、DCon2015 And so on PPT.

Four SQL Execute the plan to get

The execution plan is SQL The core of tuning , above SQL It's also by seeing the implementation plan go HASH JOIN There may be a problem .


So the first thing to do is 2 A question :

1、 How to get the execution plan I want ( Accurate planning );

2、 How to understand and find out the problems in the implementation plan .

4.1 How to get an accurate execution plan

obtain SQL The way to carry out the plan :



Ignore bound variables

Non executive


The real plan , Need to use TKPROF Tool parsing

You can get the value of the bound variable

EVENT 10053

The real plan

Study the causes of the implementation plan


For internal use EXPLAIN PLAN




The real plan


Such as awrsqrpt、sqlt、pl/sql、sql developer、toad etc.

How do you get the execution plan ? I usually use dbms_xplan.display_cursor, The advantages are obvious :1、 What you get is a plan that's actually executed ;2、 A variety of parameters . You can also get the value of the bound variable to facilitate verification .

10053 It's about checking optimizer behavior , I really don't understand why we can take a look at that plan , Use less .

10046 You can check the contents of some waiting Events , You can also get bound variables , Generally, it is less used .

set autotrace traceonly perhaps explain, Their implementation plans are from the same source , remember , Come from plan_table, It's estimated , It may not be a real plan , Maybe it's not allowed .

therefore , There's something wrong with you , You have to question its accuracy ,autotrace traceonly The advantage is that you can see consistency reading , Physical reads , Return lines, etc , This is true . Because you can read with consistency , Physical reading to verify the optimization effect

Other , such as awrsqrpt And so on , But I rarely use , especially plsq developer Such tools ,F5 Look at the plan , I hardly use it , He is also plan table The estimation plan in . If it's long , That can't be analyzed .

I suggest you look at the real plan , Say something , I often go through alter session set statistics_level=all perhaps gather_plan_statistics hint, And then execute sql, And then through

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); Look at the actual execution information

The benefits are clear , Be able to see every step of the implementation plan E-ROWS( The estimated line ),A-ROWS( The real line ),STARTS,BUFFER GETS,A-TIME( Real execution time ) Etc ... We compare the difference between the estimated and the real , You can determine which table statistics may have problems , Is it the wrong way to carry out the plan , This leads us to calculate the number of lines we need to return .

Be careful. , If one SQL For a long time , Look at the plan in the way above , We can stop , Such as execution 2 I don't play for hours SQL, Generally I don't have patience , most 5 minute , I'll stop . It's over , adopt display_cursor You can also see the execution information .

For example, a certain step is executed 100 Ten thousand times , I have this SQL To finish , want 3 It's only an hour , I 5 I've been doing it for a few minutes 100 Time , I terminated SQL What I want to see is a ratio , It can be judged by this ratio , Which step takes the longest , There's probably something wrong with it , And solve it .

The optimizer has many limitations , Like just now TABLE Function returns 8168, Or algorithmic limitations ..... A lot of things are wrong , If the algorithm is very different from the real one , That could lead to problems . Sometimes statistics can't be collected accurately , Like histogram , There are a lot of problems , therefore 12c There are a few more histograms .... Before, there were only contour and frequency histograms .

Just now set statistics_level Writing directly will output results , We can make him not output results :

1、sql Put the content in the file , prefix set termout off ( In this way, the output result can not be output )

2、 then display_cursor In file


Look at the implementation plan with this kind of thing , Sometimes it's easy to find problems , Otherwise, we have to manually determine the predicate for each step , Write it yourself SQL To calculate the actual returned rows , And then compare , Use this ,ORACLE Help us all .

4.2 Understand the execution sequence of the execution plan

What do you think of the implementation plan ?


COPY To UE In go to .


Use the cursor , Find the entrance , First executed , Cursor positioning ID=0 Of , And then all the way down , If it's blocked , So this part is the entrance .

such as ID=10 Continue indexing , I was ID=11 It's blocking , So the first 10 Step is the entrance .


After finding the entrance , Reverse the cursor , The most parallel execution on the level , The right first principle , Look at the relationship between the parent operation and the child operation , Move the cursor .

Like the second one here 13 Step , I just need to position the cursor at PARTITION This P front , And then move up , You know right away , Its driver table is ID=5 Of VIEW, Because they're aligned .


And then look at the JOIN Is there something wrong with the relationship , The returned row estimates, etc .

Execution plan right top first execution rule , There is an exception , Do you know ?? It's through the above rules , It's not true .

( Scalar subquery )

SELECT a.employee_id,



WHERE a.department_id=b.department_id

) cnt

FROM emp_a a;

Like this ID=2 It's on the front , But it was actually ID=3 Driven by , It means being emp_a Driven , This goes against the general order of execution plan , Just pay attention , Bound variables appear in scalar subquery predicates , Like here :B1, Because it drives subqueries with one value at a time .


Figure out how to carry out the plan , So what do you see in the implementation plan ?

1、 see JOIN The way

2、 Look at how the table is accessed , Walk the whole watch , Go to the index

3、 See if there are some operations that often affect performance , such as FILTER

4、 see cardinality(rows) The gap with reality

Don't pay too much attention to COST,COST It's estimated , Big is not necessarily slow , Small is not necessarily fast …… Of course, like COST Very small ,rows The returns are very small , Very slowly . that , We may have to consider whether the statistics are too old .

Statistics are important , Just one example :


The index is gone ,COST Very small , Everything is perfect , however AWR Reality accounts for 80% Resources for . In general ? Simply from SQL Look up , That is to say, the implementation plan is not correct , Test it yourself , Very slowly . That is to say COST Very small ,ROWS Very small , Go to the index , The perfect plan is wrong , So obviously , It's basically statistical information .

In fact, the number one is 4 Walking sendtime Indexes , Should return to 1689393 That's ok , But the execution plan estimates return 1 That's ok , The statistics are not accurate , Check the statistics again. The date of collection is 5 A month ago .


? COUNT(1)


? ?1689393

Collect statistics ,for all columns size repeat Keep the original histogram information

?exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'MSP',tabname=>'T_MS_MEDIA_TASK',estimate_percent=>10,method_opt=>'for all columns size repeat', no_invalidate=>false,cascade=>true,degree => 10);

return 168 Line ten thousand , But the available statistics make cbo Think it's 1 That's ok , It's a big difference .

method_opt=>'for all columns size repeat', Here under , Update statistics , Best use for all columns size repeat...

repeat What are the benefits of , For example, columns have histograms , I'll keep it for you , Column has no statistics and will follow for all columns size 1 collect ... The others will be collected as they used to be .

You use one for all columns size 1 or size skewonly, Or don't write (auto) May change the original way of collecting statistical information , It can affect SQL Efficiency of execution .

Efficient access structure makes SQL faster , Let's not talk about this , It's mainly index building . How to build an index is also a very complicated problem , Say something , General composite index , The frequency of equivalent query condition is high , It's better to be a leading column . Because direct access may be more efficient than >,<... Equal height , The latter needs to be filtered .

Let's take a look at the performance problems caused by the parameters that affect the optimizer .

This is a 10g Implementation plan , One view is UNION ALL It's done , It's all indexed :


however The whole watch has been scanned .


10g Views have predicate recommendations , It's a kind of query transformation OJPPD=OLD JOIN PUSH PREDICATE

Upgrade to, In the view 10 Every watch becomes FULL SCAN.

Join predicate (A.“PAYIO_SN”=“B”.“WRTOFF_SN”) Not pushed into view .

Execution time from 0.01s To 4s,buffer gets from 212 To 99w.

Obviously , I want to check , There's no problem with the statistics , And then how to do it ?? witness 11g How about downgrading the optimizer .

stay Use in optimizer_features_enable The test respectively and Push words into the view and index them . So the problem is 了 , because It's all right . explain The algorithm of view predicate push has been changed . A lot of optimizer stuff ,oracle All have parameter control , In addition to parameters , And each complement corresponds to fix control. So check the patch first

from v$system_fix_control WHERE sql_feature LIKE ‘%JPPD%’

We found , All kinds of opening and closing , of no avail . Finally, let's see 10053, analysis 10053, See if it is BUG Lead to , Or optimizer improvement , Parameter setting :


10053 See that the default parameters are turned off , Check , About two parameters of query and transformation :



It's all shut down , Of course and It's OK to be locked up .


It's also based on CBO Query conversion failed for , Because the parameters are off ,OJPPD(10g That way ) It doesn't work …… Of course not ,JPPD yes 11g Of , It's not working .

Basically know how to look at the implementation plan , It's useful to focus on what , Don't focus too much on what COST The earlier Fine , To That won't be possible , That could have 2 One reason :1、 The algorithm has been changed ;2、BUG.

Of course, based on a normal understanding , View predicates recommend ,ORACLE It has to be supported , There is no problem , So there must be a formal solution . First look at the 2 individual BUG, Logically speaking , This common thing , Especially here SQL It's not complicated ,ORACLE It should not trigger BUG, Of course , There are various kinds of query transformation BUG Of ,11.2.0,4 A lot less MOS Search for , Like this JPPD, There's a lot of BUG, But I didn't see Corresponding .



Predicate Move-Around (PM)



OJPPD: OJPPD bypassed: View semijoined to table.

JPPD: JPPD bypassed: View not on right-side of outer-join.

By this judgment , That kind of OJPPD, Rule based query transformation doesn't work , That's algorithm change , because cost_base_query_transformation The parameters are off , We should go OJPPD Of . Now? JPPD I can't go either , Because the parameters are off , This is a cost based query conversion .

therefore , This is a problem caused by algorithm update , The requirements must be in accordance with ORACLE The official advice , Restore the default value of the corresponding query conversion parameter : Based on COST The query transformation part of , Can only go JPPD( and OJPPD similar ),ORACLE It is recommended to set CBQT Parameters , be based on COST Query transformation is more accurate .

Turn on COST Query conversion , Initialize optimizer parameters _optimizer_cost_based_transformation Set as default (linear).CBQT The parameters have the following values :

"exhaustive", "iterative", "linear", "on", "off".

In addition, through the test, we know that , You also need to set _optimizer_squ_bottomup (enables unnesting of subquery in a bottom-up manner)

Parameter default true.

This problem , But it was SR, Foreigners don't know , And then I found out that 2 Parameters can be restored to the default values , First of all, of course cbqt I think the parameters must have something to do with , hinder squ_bottomup It's a test ... Later I told the foreigner , Foreigners also recognize the problems caused by algorithm changes . So the default values of the core parameters change , It's very dangerous , It may affect the overall situation , If these two parameters don't recover , Involving hundreds of cores SQL It doesn't work , That is, the system is not available .

One last word , A common optimizer flaw :
















FROM DBPRODADM.pd_prc_rel a


FROM DBPRODADM.pd_prc_dict b

WHERE a.element_ida = b.prod_prcid

AND b.prod_prc_type = '1')

AND a.exp_date > SYSDATE


FROM DBPRODADM.pd_prc_dict c

WHERE a.element_idb = c.prod_prcid

AND c.prod_prc_type = '1')

OR a.element_idb = 'X')

AND a.relation_type = '10'

When OR And semi join When put together , Will trigger the failure to proceed subquery unnest The problem of , That is, it may produce FILTER, Lead to SQL Very slow , Some even for a few days , Don't think it's over in a few days .


The first 5、6 Step by step 92 More than ten thousand times , That must be slow …… The problem is that there's a FILTER……

FILTER It's like a cycle , It's impossible to unnest There is... In the subquery , Similar to scalar subquery , There's also something in predicates that binds variables .

Their only advantage is internal construction HASH surface , If there are too many duplicate matches , So less detection , Efficient , But most of the time , There are not many repetitions , So it's a disaster

For this optimizer limitation , Generally, we have to rewrite it , because SQL The structure decides that we can't follow the efficient execution plan ... Because I'm gone, so , But too many execution times , If the number of execution is small , It doesn't matter .

After the rewrite sql:
















FROM DBPRODADM.pd_prc_rel a


FROM DBPRODADM.pd_prc_dict b

WHERE a.element_ida = b.prod_prcid

AND b.prod_prc_type = '1')

AND a.exp_date > SYSDATE


FROM DBPRODADM.pd_prc_dict c

WHERE a.element_idb = c.prod_prcid

AND c.prod_prc_type = '1')

OR a.element_idb = 'X')

AND a.relation_type = '10'

Obviously , The condition here is exists or ... So the rewriting has to use UNION or UNION ALL 了 , To avoid duplicate lines , use UNION




from DBPRODADM.pd_prc_rel a

where exists

(select 1

from DBPRODADM.pd_prc_dict b

where a.element_ida = b.prod_prcid

and b.prod_prc_type = '1')

and a.exp_date > sysdate

and exists (select 1

from DBPRODADM.pd_prc_dict c

where a.element_idb = c.prod_prcid

and c.prod_prc_type = '1')

and a.relation_type = '10'





from DBPRODADM.pd_prc_rel a

where exists

(select 1

from DBPRODADM.pd_prc_dict b

where a.element_ida = b.prod_prcid

and b.prod_prc_type = '1')

and a.exp_date > sysdate

and a.element_idb = 'X'

and a.relation_type = '10';

Both branches go HASH JOIN,starts All for 1, Although it's all full scan , But the efficiency of execution has improved significantly , Execution time from 12s To 7s,gets from 222w To 4.5w after , Is there still room for optimization ?


Especially the logic reading is much less . The follow-up to optimize :

1) Rewriting uses UNION, Can it be changed to UNION ALL Avoid sorting ?

2) So many full table scans , Whether we can make some index ? Of course , These can be done , But it's not the main job . This case tells us , There are many limitations to the optimizer , Not everything .


Except that the statistics are correct , good SQL structure , To be able to make SQL Correct query conversion , The right access structure , Such as index …… It's all about letting SQL Prerequisites for efficient execution . complex != Inefficient , Simple != Efficient . Let the optimizer understand , And there is an appropriate access structure to support , Is king !

ordinary SQL It's not a guarantee of speed , Complex is not necessarily slow , Efficient implementation of the plan is the most important , Index optimization SQL, The most important thing is to make the bad execution plan better .

That is to start from many aspects , Finally achieve our optimization goal .