SQL optimization work in DBA work take up too much of a piece of work, we in the usual work, too, often encounter some inefficient execution of SQL statements, and these low execution efficiency of SQL, have a plenty of business system has just launched, have already implemented for a long time but because the execution environment change and led to the emergence of. Here to share a SQL optimization case analysis.


According to our monitoring system, we found that a SQL in a core business database of online OLTP was executed slowly. How slow? The database is executed 278 times in half an hour, with an average of 28 seconds each time, occupying 56% of the entire DB resources. In OLTP systems, it’s too slow.


Here is the information about SQL execution.






In SQL optimization, many people always look at the execution plan first, so let’s take a look at this SQL execution plan. As can be seen below, a single execution takes about 3 seconds, the cost is 2, consistent gets is relatively high, and INDEX RANGE SCAN is also carried out in the execution plan.




In fact, only looking at the above execution plan Cost is relatively low, SQL optimization, there are many people always the first to see is the execution plan, but to see the execution plan must be combined with structural information, structural information here is table, index and other structural information and data distribution information.


Let’s take a look at the SQL statement. The following SQL statement is simple and has indexes on the CN and C_date fields.




Table data volume is about 360 million data



 


On the basis of the above execution plan, according to the understanding of business, my question is why not go to CN index?

In fact, we can query the result according to the predicate condition, we can see the result according to the cn query 3, and the c_date condition query 76W. Go to cn index.

 


If you use the HINT HINT to HINT the CN index, the execution time is milliseconds.

 




In addition to understanding structural information (table, index), the accuracy of statistical information is very key.

It is found here that the last statistical information was analyzed in May, a gap of more than 3 months, so the statistical information is incorrect.



 


Why are statistics inaccurate?

In Oracle 10G, the default GATHER_STATS_JOB is not started. Start the default GATHER_STATS_JOB and collect table statistics separately.

 




After collecting statistics, the execution time of this SQL has been reduced to the millisecond level, the execution plan has been changed to the RANGE SACN of IDX_REC_LOG_CN index, and consistent gets has been reduced from the original 19409 to 7. The effect is still obvious.

 




 


The same SQL is slow again, and now the execution plan starts to use IDX_C_LOG_DATE index again, and the execution time is back to 2 seconds. Consistent gets is 10404. The following is the implementation plan

 


Similarly, we first check whether the statistics are correct. We can see that the statistics are incorrect, but GATHER_STATS_JOB is successfully executed every day. ????? Why is that?

 












The solution is to define a single table collection JOB

This is why the big tables are defined separately to collect statistical information, the interview of a lot of students, basically tell the direct reason is not much, are said to do so according to the Internet.

 



 


You can also see the cost information for 10053 events, refer to the address below for the meaning of each type.

 




 

 

Finally, we created the execution plan after CN + C_date joint index during the service maintenance time. So far, there has been no similar SQL performance problem.