1. Introduction

On SQL optimization, predecessors, technical masters, each technology forum has long had a lot of excellent articles, today I mentioned again, the mood uneasy, it is some teach a fish to suck and overdo oneself.


Under everyone’s encouragement, I want to write about it, just write the things around us, with the case around to deduce SQL optimization, with the image language SQL optimization said into the things around us, I hope to make contact with SQL students can experience the fun of SQL speed up!


2. Understand some nouns


When it comes to SQL optimization, we have to learn a few nouns, which is like practicing kung fu in martial arts novels. We don’t know a few acupoints, and we don’t know a few valves are embarrassed to mention that we practice kung fu. A lot of nouns, in addition to the mentioned here there are a lot of, such as internal and external connection, nested loop, cursor sharing, binding variables, soft and hard parsing, etc., too much martial arts, practice can not come over, then we first put the horse step tied up again. Kungfu not mentioned is available in the Sutra Pavilion, please baidu on demand.

 

2.1 Execution Plan

What is the execution plan? This is the path and step of SQL execution. How do you understand that? You can choose to drive to the highway from home to work, or you can choose to take the bus to the city streets, so which means of transportation you choose, which route you take, is your execution plan. But there can only be one solution at a time.

 

When an SQL database generates an execution plan, it is fixed to the shared pool. The SQL database regenerates the execution plan only when the table changes, the statistics are old, the shared pool is refreshed, or the database is restarted. Whether it’s from home to work, cities are building roads, and bus routes are changing to affect how you get to work.

 

2.2 the index

An index is a single, physical database structure that is a collection of values in one or more columns of a table and the corresponding logical pointer list to the data pages in the table that physically identify those values. How do you understand that? A table is a book and an index is the table of contents of the book.

 

2.3 Statistics

Statistics mainly describe the size, scale, and data distribution of tables and indexes in a database. For example, the number of rows in a table, number of blocks, average row size, leaf blocks of an index, number of rows in an index field, and size of different values are all statistics.

 

Oracle’s cost-based optimizer (or rule-based optimizer) uses these statistics to calculate the cost of various plans for different access paths and connection modes, and then selects the least expensive plan. What if there are no statistics? Dynamic sampling occurs, where data is sampled from tables and indexes prior to production execution plans.

 

How do you understand that? Or from home to work, with maps and bus information, high-speed fares and other information, people can choose the most appropriate way to get to work. Without this readily available information you need to actually do your research, which can be cumbersome and can lead to inappropriate choices.


3. Can a CERTAIN SQL be optimized?


How do you know if a SQL can be optimized? This is not an easy answer, dbAs often say “analyze first.” So what is the analysis: how efficient is it now? Carry out the plan? Waiting for events while running? The table? Is there an index on the selection column? How selective? Have statistics? Connection mode? . Don’t worry, I’m not selling wild medicine, I’m about to perform!

 

Well, it doesn’t have to be that complicated. Whether or not a very poor SQL (tens of minutes or even hours of SQL running) can be substantially faster depends on how much data the business needs to access by nature. The less data the business needs to access by nature, the more speed there is in general. Simple, so as a business expert, if you encounter a bad SQL, you should know if there is room for optimization without DBA analysis.

 

How do you understand that? A big playground there is a red glass ball, let you go to get, you did not see, carpet look for a long time, someone give you a coordinate map, you ran in 10 seconds to find, this is to speed up the room, if the playground is full of red glass balls to let you take, no matter how to take, to take a long time, It just can’t make the leap, ie it can’t get things done in seconds or minutes.


Let’s talk about some cases step by step.

The first sword

Shaoshang Sword: Rational use of index

1.1 Use indexes to improve efficiency

IDX_INS_USER1_571; IDX_INS_USER2_571; IDX_INS_USER2_571;

select * from SO1.INS_USER_571 M where M.BILL_ID = ‘13905710000’ and M.EXPIRE_DATE > sysdate;


There is only one data item that matches BILL_ID, but tens of thousands of data items that match EXPIRE_DATE. If BILL_ID is selected, IDX_INS_USER1_571 can be used to find the data item more quickly.


How to understand? Find a glass ball on the playground and give you two coordinates. One directly tells you the specific position of the glass ball, and the other tells you which one to choose within a circle of 10 meters. Definitely pick the first one.


1.2 Index is slow?


Create index IDX_INS_USER4_571 on CREATE_DATE;

SQL:

SELECT COUNT(*) FROM SO1.INS_USER_571 M WHERE  M.CREATE_DATE >= TO_DATE(‘20110101′,’YYYYMMDD’) AND M.CUST_TYPE=’1′;

PLAN: No index? !



Is there something wrong with the optimizer? Just test it out. To make it clear add Hints test:

SELECT /*+full(M)*/COUNT(*) FROM SO1.INS_USER_571 M WHERE  M.CREATE_DATE >= TO_DATE(‘20110101′,’YYYYMMDD’) AND M.CUST_TYPE=’1′;

SELECT /*+INDEX(M,IND_INS_USRE4_571)*/COUNT(*) FROM SO1.INS_USER_571 M WHERE  M.CREATE_DATE >= TO_DATE(‘20110101′,’YYYYMMDD’) AND M.CUST_TYPE=’1′;

It seems the optimizer is not wrong!

When a large amount of data needs to be scanned, the cost of index running is higher than that of full table scanning.


How to understand? A book has 1,000 pages and 800 of them are exactly what you need. You won’t have to turn back to the table of contents every time you turn to a page. Just go ahead and read it in that order.


1.3 Tips of this trick

  • The select column in the WHERE condition has a function added to it, making no use of the index


For example, where to_char(create_date, ‘YYYY-MM-DD HH24:MI:SS’)>= ‘2015-04-08 00:00:00’


This does not take advantage of the index create_date


Correct way to write it:

Where create_date>=to_date(‘ 2015-04-08 00:00:00 ‘, ‘YYYY-MM-DD HH24:MI:SS’)

Example: Select count(t.visitor_id) as pv

from t_stat_logbase t

where instr(t.visit_url, :””SYS_B_0″”)>:””SYS_B_1″” and

    to_char(t.visit_date,:””SYS_B_2″”)=:””SYS_B_3″”

Visit_url and visit_date both have indexes, and visit_URL column has very good selectivity, but because the function of INSTr is added to this column, only the full table scan can be caused. Because of the high execution frequency around zero o ‘clock every day, the database often appears performance alarm, after the application transformation, the index is effectively used. The database is restored.


ø Variable types should be correct to avoid implicit conversions that make it impossible to use indexes

Bill_Id is the type of Varchar2

Where bill_id=1, the index cannot be used.


ø The Where condition must eventually use the leading column to enter the composite index.

Index IND_ins_USER5_571 (create_date, CUST_type): WHERE CUST_type =1

Ind_ins_user5_571 cannot be accessed without cust_type and create_date


ø In the case of multi-table association, adding indexes to associated fields is often very effective even if the table is small, which may affect the join mode of the table and save costs.


Do not try to add indexes to every field. The more indexes there are, the greater the impact on DML of the table will be. It is not recommended to add indexes to interface tables that have high REQUIREMENTS on DML.


For example, if there is a very selective field in the Where condition, such as BILL_ID/ACC_ID/USER_ID, other selection conditions, such as STATE/TYPE, do not need to combine indexes.


The boss doesn’t have to worry about my data backlog anymore


A report library has such a SQL, each city started 10 threads, to carry out data processing, each city execution frequency up to tens of thousands of times per hour, causing a huge pressure on the database CPU, and reduce threads and data backlog, how to do? Dbas are also anxious!

select ROWID, t.*

from CHK_ORD_INTERFACE_574 t

where mod(INTERFACE_ID, :”SYS_B_0″) = :”SYS_B_1″

 and SCAN_STATE = :”SYS_B_2″

 and rownum <= :”SYS_B_3″

SQL features: execute plan full table scan, filter condition SCAN_STATE field only 2 minutes (0,1 is audited)


Business Characteristics: The CHK_ORD_INTERFACE_NN table is synchronized from the business end to the report end through DSG, and the data is audited at the report end. The audited data is deleted. The table is partitioned on a daily basis, that is, the fragment reuse rate is very low, resulting in extremely serious fragmentation of the table, with the fragment rate over 99%. Since DSG synchronization is performed through ROWID, this table cannot be cleared by move,shrink, and other means. Can not go on the partition, low selectivity filtering conditions! Can’t defragment! Doesn’t it seem impossible? !


Solution: it is fragment rate is as high as 99%, illustrates the actual data quantity is less, can create a global index (this form of life cycle is a permanent, global indexes under this business features can improve the reuse rate of index fragments, effectively control index size), although many of the blocks of data, the index is small, establish and effective! After creating the index, the thread is adjusted to 1, and the task is still completed. The boss no longer needs to worry about my data backlog.

The second sword

Zhong Chong Jian: Reasonable logic

1.1 Use connections efficiently and avoid nesting


The external join of a table is generally more efficient than the semi-join. IN order to ensure the business effect, we recommend that the table association replace the IN/EXISTS sub-query, which has many advantages, related to the driver table, join mode, here is not detailed table. Interested, please practice, in doubt, call DBA!


In the special optimization of a report system, we caught an unusually complex SQL involving 13 tables, 5 layers of nesting…. After analyzing the business requirements, we found that multi-table association can replace In word query (the business effect remains unchanged). We modified the three small paragraphs In SQL as follows, and created an index on the association key, which increased the running speed from 2 hours to less than 10 seconds:



Is amended as:


1.2 Big difference between inside and outside parentheses


Serve straight:

The original SQL


What’s the problem? The root cause of this SQL slowness is the parenthesis. What is the crime of the parenthesis? He creates a full table scan for RP_BUSI_DETAIL_NNN (571 180G), The business characteristic is to read distinct Customer_ORDER_ID, SALER_ID and associate it with the USTOMer_ORDER_ID column of RPT.INSX_OFFER_PRG_577.


Solution: Create the combined index customer_ORDER_ID, SALER_ID (both columns are not empty), and put the additional filter criteria of the RPT.INSX_OFFER_PRG_NNN table into the subquery, so that the SQL execution plan scans the index and does not return to the table.


Result: This SQL was not completed in the 1-hour snapshot cycle, but it is fine after optimization and can be completed in 1-30 seconds under various conditions.


The new SQL:


1.3 Deeply understand the business and make good use of partitions


Unfortunately, the last story is not over, after a week, the business side reflects that some business is very fast, but some are very slow, especially one-time statistics of 3 months of statements simply can not run…… Some faster? Some slow? This is the key point, immediately contact the business side and the development side to confirm the fast and slow scenarios, and trace, found that the fast scenario has entered the ORG_ID condition, while the slow scenario does not have the ORG_ID condition, then the key point is here!


Without the condition ORG_ID = :ORG_ID, take the data of a certain month as an example, then there are 140,000 INSX_OFFER_PRG_571 data that meet the conditions. When the table is associated with RP_BUSI_DETAIL_NNN, the CBO optimizer only selects hash jion connection. The selected index must be done_date (hash from range). Two tables of this size cannot be completed in 50 seconds (timeout limit). IOP.ORG_ID = :ORG_ID then meet the conditions of INSX_OFFER_PRG_571 data within 100, SQL can go nested loop, and both tables can use efficient index, speed naturally greatly improved.


ORG_ID = :ORG_ID can be fast? RP_BUSI_DETAIL_NNN is a monthly partition table. Each partition is only 1-6G, which is much smaller than 180G. In addition, the partition field is also DONE_DATE. Unfortunately, the partition is not used in SQL. Contact the demand side and the development side immediately to confirm whether the DONE_DATE of the INSX_OFFER_PRG_NNN and RP_BUSI_DETAIL_NNN tables are consistent in the business sense or there is little difference. Is it possible to include DONE_DATE from the RP_BUSI_DETAIL_NNN table in the condition? Fortunately, my proposal received a positive response immediately. After subsequent communication and testing, I re-customized the business rules, which can only be query by natural month at most (considering that the table is partitioned by natural month). The business timeout was changed from the original 50 seconds to 3 minutes, and finally the SQL was rewritten as:






Although the INSX_OFFER_PRG_NNN and RP_BUSI_DETAIL_NNN tables are still connected by hash jion in the current execution plan, and the data volume is still large, we have embarked on partition filtering. In particular, the large table RP_BUSI_DETAIL_NNN can only scan single partition, so we successfully optimized the report under the new rules and timeout mechanism.


1.4 Optimization before database migration


Background: A somewhat year database migration is needed in a certain project reform and upgrade to the new machine, because the library business online and is a bit before migration to upgrade the hardware configuration of the reasons for low host CPU utilization rate is very high, and migration requires using a third-party tools, the tool’s initialization process need to have enough CPU resources, However, the cutover time window and business reasons can only be quasi-online cutover (the time for stopping business is very short), that is to say, although the database can not run for a few days on the current low-configuration machine, DBAs are still trying to optimize in order to ensure the smooth cutover.


A case study in the optimization process:

The original SQL:

The SQL execution time is around 25 seconds, which seems a bit complicated logically, but we found the first breakthrough through our patient analysis: changing the way to write the following query





The above is equivalent to:


The execution time of the following script can be reduced from 15 seconds to 10 seconds. Some progress! To continue.


Considering the table join that the subquery performs, we can further simplify the logic by placing join criteria in the WHERE clause of the subquery:





The execution time of this subquery was further reduced to 1.7 seconds, and the execution time of the original SQL was reduced from 25 seconds to 4.6 seconds using this new subquery, a performance improvement of more than five times. Ok, understanding contacted the developers to test and verify, and two days later they reported that the business was exactly the same, and it was significantly faster!


See! We didn’t do anything, we didn’t index the table, we didn’t change the physical model of the table, we just did an optimization by combing out the logic, simplifying the steps of SQL execution, and avoiding duplicate data scans, so should we think about it more in the future as we write code? ! After two weeks of efforts, we finally reduced the CPU usage of the host to nearly 10%, which ensured the cutover smoothly.

Even the three swords

1. Guan Chongjian: Bind execution plan

1.1 Rational use of Hints


Customized execution plans are widely used in cutover, sub, audit and other scenarios. Reasonable use can effectively utilize resources and improve execution efficiency.


Parallel: Parallel can effectively utilize resources and improve execution efficiency. But the core library is not allowed during daily production! Parallel (a,64) /*+ Parallel (a,64)*/ Our logical CPU is only 32!

Select /*+parallel(a,6)*/count(*) from ins_offer_571 a where …..


Append nologging: Append nologging High watermark direct path writing to reduce log writing. This mode is widely used in clipping scenarios

INSERT /*+ APPEND NOLOGGING PARALLEL(TMP_H_MD_PAR_EXT_USER_D16,3) */

INTO TMP_H_MD_PAR_EXT_USER_D16

(…)

SELECT /*+PARALLEL(R,3)*/*

from H_MD_BLL_TMN_BUSN_D_574 PARTITION(P20150406) R


  • /*+index(a,index_name)*/ Specify index scan

  • /*+full(a)*/ specifies full table scan

. Select * from v$SQL_hint


1.2 SQL PROFILE, fortunately have you!

A report library has a foreground multi-check box operation report, SQL condition combination diversification, table RP_BUSI_DETAIL_NNN there are 12 indexes, and most of them are composite indexes, index key repetition is more, one day broke out, a large number of execution plans go wrong, statistics were collected, cursor brush out, no effect. Daytime index risk is too high, fortunately this SQL has a feature, DONE_DATE field must be used…… This is an emergency! Dbas have another option: SQL profiles.



1 for the outline

select* fromtable(dbms_xplan.display_cursor(‘0bbt69m5yhf3p’,null,’outline’));


2 Create an SQL Profile binding execution plan





After the binding is complete, kill executes the SQL with the problem plan, and the performance of the database gradually recovers. The index was reformed after the completion of the follow-up business sorting, and all kinds of SQL execution plans were stable.


2. Less blunt sword: Why are you suddenly slow? It’s you!


The maintenance of a core system is looking for, and one of the operations that I run every day has slowed down these days. I used to run for 1.5-2 hours, but now I have not run out for 6 hours. The boss said take care of him!


Sql is coming:


Execute the plan first



This is dynamic sampling! Or level=7, did you run for 6 hours or in dynamic sampling? Open 10046 and see what the session is up to. Not only is there a problem with dynamic sampling, there is also GC!



Check back quickly, sure enough, this job has been running on node 1. DR_GGPRS_XX_YYYYMMDD is stored on node 2! There is a problem with executing this job on node 1!


What about dynamic sampling? SQL > select * from DR_GGPRS_XX_YYYYMMDD; SQL > select * from DR_GGPRS_XX_YYYYMMDD; SQL > select * from DR_GGPRS_XX_YYYYMMDD;

1590 s!

Hints /*+ PARALLEL (a,4) dynamic_sampling(a,0)*/

Avoid dynamic sampling and subsequent feedback tasks can be completed within 30 minutes.


3. Shaozawa Sword: Efficient update, can’t do anything!

The Cpu usage of a certain core system is close to 100%, the monitoring response speed is very slow, and the main business data storage backlog is serious! After analysis, we found the culprit, which turned out to be several circular UPDATE statements draining the CPU!

Let’s take a look at his power:

There is a problem with the statement variables alone: the statement executes in a loop! And there are so many cycles! Find the app, call back, sure enough! And this homework has run for a day.


Matching condition table DR_GGPRS_XX_YYYYMMDD according to different cities between 70-200g, the amount of data are hundreds of millions of levels, the execution plan is not needed to see, there is no index is not important, just with so cycle down, I can only ha ha!


Table user_fenleijx_temp_YYYYMMDD is a few hundred MB. What did I say? ! Can a SQL be substantially speeded up by looking at the data it ends up scanning? It must be speeded up!


Let’s get started!

1.create tmp_table

create table jf.tmp_tab1 parallel 10 nologging as select distinct user_number from jf.dr_ggprs_jx_20150203 where charging_characteristics=’04’;

create table jf.tmp_tab2 parallel 10 nologging as select distinct user_number from jf.dr_ggprs_jx_20150203 where charging_characteristics=’0400′;

2.Create unique index

create unique index jf.ind_user_number_1 on jf.tmp_tab1(user_number) nologging;

create unique index jf.ind_user_number_2 on jf.tmp_tab2(user_number) nologging;

3. Efficient update

update (select a.status

from bossmdy.user_fenleijx_temp_20150203 a,

jf.tmp_tab1 b

where a.user_number = b.user_number

and a.status = ‘3’)

set status = ‘0’;

update (select a.status

from bossmdy.user_fenleijx_temp_20150203 a,

jf.tmp_tab2 b

where a.user_number = b.user_number

and a.status = ‘3’)

set status = ‘0’;


You only need to create a temporary table with unique matching conditions and create a unique index to update the target table at one time. The whole work takes 10 minutes. Not only is it efficient, the high CONSUMPTION of CPU resources is gone, and the database is back to normal.

Efficient UPDATE mode

Update (select A.col_a,b.col_b from T_tab_A a, T_tab_b B where A.id = B.id and b.col_x= ‘x’) c

set c.col_a=c.col_b;


To ensure that there is a unique index on the matching condition B.ID, if there is no unique index, the unique data can be extracted by creating an intermediate table and then the unique index can be created, for example:

Create table t_TAB_c as select * from T_TAB_B WHERE b.col_x= ‘x’;

Create unique index ind_t_tab_c on t_tab_c(id);



The sixth sword

Shang Yang Sword: Cut these three axe!

Speaking of cutover, is to let DBA, development, testing and other people are terrified of things, because the cutover time window is tight, cutover resource consumption is serious, cutover to all kinds of backup, cutover generates a large number of logs, run the wrong to roll back…….. It’s a little high risk anyway.


1.1 Backup and Temporary Tables Do not Write Logs

Cutover requires a lot of backup and photography, so reducing log writing can greatly improve efficiency and reduce archiving pressure on the database, since many of our libraries also have DSG synchronization, and reducing log volume can also avoid DSG analysis delays.



3. DDL replaces DML to reduce undo pressure

Insert all data from table A into table B

create table B tablespace tbs_data nologging as select * from B;

Rename and CREATE Table as SELECT (CTAS) table creation is a popular practice in cutover scenarios


1.2 Batch Submit to Reduce UNDO/REDO pressure

In addition, the undo tablespace is under heavy pressure. If the undo tablespace is cancelled, the rollback of large objects consumes performance and may affect the subsequent cutover. If the cursor commits one file after another, it may cause I/O problems. It can also cause a large number of log file sync events. Therefore, we recommend batch submission for full or near-full DML of large tables.



1.3 To improve the clustering factor, seize the day!

A monthly maintenance job like the script above is essentially a conditional batch update of the large table I_USER_STATUS_CENTER, which has about 1.6 billion data on it and indexes on the MSISDN column, Distinct numbers: 1.6 billion / 11,000 = 150,000.


According to the steps to do, but ran 2 hours also did not run……..


I’ve read the execution plan. Go to the index. No problem! The table property is now nologging. Enough is enough? ! But the speed is still not good, an analysis, estimated 72 hours above……… Where is it acceptable to perform such a job on a core library? ! We’ve done everything we need to do. Isn’t there any way to speed things up?


There is an example in front of the playground, pick up the glass ball, now is not a pick up one, but a group of a group, each group according to the different color to pick up, pick up 15,000, if this each group of balls are scattered in different places, with the index how, pick up so many is not tiring? ! Wouldn’t it be faster if the balls in each group were stacked in one place instead of scattered around? !


This is to increase the clustering factor.


(1) Sort and rebuild the target table according to MSISDN.

Alter table I_USER_STATUS_CENTER rename to I_USER_STATUS_CENTER_bak;

Create table I_USER_STATUS_CENTER tablespace tbs_data parallel 10 nologging as

Select /*+parallel(a,10)*/* from I_USER_STATUS_CENTER_bak a  order by MSISDN;

Create index ind_ USER_STATUS_CENTER1 on I_USER_STATUS_CENTER(MSISDN) tablespace tbs_data parallel 10 nologging;

Alter table I_USER_STATUS_CENTER noparallel;

Alter index ind_ USER_STATUS_CENTER1 noparallel;


(2) Establish indexes on MSISDN columns

(3) Execute the above script for 1.5 hours to complete

(4) Modify the log attributes of the table and index

Alter table I_USER_STATUS_CENTER logging;

Alter index ind_ USER_STATUS_CENTER1 logging;

(5) Can it be faster?

Can!!!! You can split the cursor scope by business field net_id, for example:


SELECT NET_ID||HLR_SEGMENT BILL_ID,REGION_ID  FROM  RES_NUMBER_HLR where net_id >=1 and net_id<100;

SELECT NET_ID||HLR_SEGMENT BILL_ID,REGION_ID  FROM  RES_NUMBER_HLR where net_id >=100 and net_id<200;

.


Divide it into 8 cursors and put the program in different Windows at the same time. Then we will be finished in 15 minutes.


2. To summarize

This issue of ramble SQL optimization on the temporary stop here, here through a few examples hope to give you a little inspiration. In the SQL optimization process is often not so smooth, most of the cases are closely related to system problems, business requirements, physical models, etc., need to analyze and consider a lot of places. Soldiers are fickle, water is fickle. There is no universal formula here, but we IT people have a serious and responsible, flexible application, study to the end of the attitude, so we can solve many problems in the end!

(This article is from the subscription number of SANDun IT people, and is merged by DBA+ community with the consent of the author)