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

I would like to share with you a case of sql optimization encountered by our group. The case is not the database I am responsible for. I am just a porter.

This case occurred last year, and it was found that the reason was the rise of nets host cpu, and the development operation found DA, then DA analyzed the situation, and finally located an inefficient SQL for analysis. (about DA, it is a subdivision of DBA by the database technology department of Ping an Group.)

The following is the located sql:

SELECT / * + index (c IDX_CALLINFO_UPDATED_DATE) * / COUNT (1) FROM T_SOFTPHONE_CALLINFO CWHERE C.updated_date > = sysdate-1 / 48 and (C.ANI like'%'| |: 1 or C.DANI like'%'| |: 2) and C.CREATED_BY =: 3

First of all, learn the following information from the related table (T_SOFTPHONE_CALLINFO) of this SQL.

1. This is a list of incoming phone calls (COUNT statistics are done here)

The variables passed in by 2.ANI and DANI are the phone number, one is the landline number, and the other is the mobile phone number

3. The HINT index used is the time field (updated_date) regular B-TREE index, and the execution plan also follows the RANGE mode of this index, which is no problem.

After having some knowledge of the appeal SQL, DA first collected some information about the historical execution, and the conclusions are as follows:

By comparing the growth in recent months, it is found that although the execution plan has not changed, the execution frequency has increased from 1000 times per 15 minutes to about 60000 times, and the consumption of a single logical read has also increased several times. with the increase of business and data volume, this time field index method is becoming more and more inefficient and becomes a hidden danger sql. At a certain time, the problem is suddenly highlighted and is in urgent need of optimization and improvement.

Ps: the mass of nets is close to 30TB at this time

In this regard, DA raised some comments and questions:

1. The first and second parameters are both mobile phone numbers, and historically they are all complete mobile phone numbers, so why should I use like? can I directly change it to an equal sign?

2. Both phone fields have separate indexes. If you do not use like, you can optimize the above SQL statement and follow the corresponding phone number index. The rewriting format is similar to the following:

SELECT (SELECT / * + index (c IDX_SOFTPHONE_CALLINFO_ANI) * / COUNT (1) FROM T_SOFTPHONE_CALLINFO C WHERE C.updated_date > = sysdate-1 / 48 and (C.ANI = '159x22') and C.CREATED_BY =' * 880') + (SELECT / * + index (c IDX_SOFTPHONE_CALLINFO_DANI) ) * / COUNT (1) FROM T_SOFTPHONE_CALLINFO C WHERE C.updated_date > = sysdate-1 / 48 and (C.DANI = '159m22') and C.CREATED_BY =' * 880') FROM DUAL

Through this rewriting method, the original average logical reading can be reduced from 30,000 to about 200, which is a great improvement in efficiency.

But after that, developers and operations gave new business feedback and found that things were not that simple.

Development and operation: the phone number in T_SOFTPHONE_CALLINFO is obtained from random data and may contain a prefix such as 0. If you want to count all the information, you cannot directly use the equal sign, plus 0 and do not add 0, which is related to the location of the incoming call. If you add 0 in the field and do not add 0 locally, you may not add 95511 in place A; if you use the same mobile phone number in place B to call 95511, you may add 0. This is indeed the case after DA investigation, and there are even special substitution symbols for mobile phone numbers.

Think about:

In that case, it seems that the way of LIKE can not be changed, using% in front of the field will suppress the use of the index, so that the corresponding index can not be used, how to avoid this problem and use an efficient index? Through my own thinking and colleagues' suggestions, combined with the current business scenario, a reliable scheme is given, that is, to create a functional index, reverse functional index!

The test and analysis phase was carried out as soon as the method was thought of.

1. First, the functional indexes of two corresponding fields are created.

Createindex NETS2DATA.IDX_SOFTPHONE_CI_ANI_REV on NETS2DATA.T_SOFTPHONE_CALLINFO (reverse (ani)) tablespace NETS2DATA parallel 8; Createindex NETS2DATA.IDX_SOFTPHONE_CI_DANI_REV on NETS2DATA.T_SOFTPHONE_CALLINFO (reverse (dani)) tablespace NETS2DATA parallel 8

two。 Rewrote sql

SELECT COUNT (1) FROM (SELECT / * + index (c IDX_SOFTPHONE_CI_ANI_REV) * / C.CALLINFO_ID FROM T_SOFTPHONE_CALLINFO C WHERE C.updated_date > = sysdate-1 / 48 and (reverse (C.ANI) like: 1 | |'%') and C.CREATED_BY =: 2 UNION ALL SELECT / * + index (c IDX_SOFTPHONE_CI_DANI_REV) * / C.CALLINFO_ID FROM T_SOFTPHONE_CALLINFO C WHERE C.updated_date > = sysdate-1 / 48 and (reverse (C.DANI) like: 3 | |%') and C.CREATED_BY =: 4)

After the performance test, the efficiency improvement is very obvious in most scenarios. The original average consumption of hundreds of thousands is maintained at several hundred. The original index is the time field index, and now it is the reverse function index of two phone number fields. So the developer immediately arranged the first round of rectification, looking forward to good results.

The attached implementation plan is similar to the following:

Execution Plan---Plan hash value: 1437385812 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 1 | 17 | 6 (0) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 17 | * 2 | TABLE ACCESS BY INDEX ROWID | T_SOFTPHONE_CALLINFO | 1 | 17 | 6 (0) | 00:00:01 | | * 3 | INDEX RANGE SCAN | IDX_CALLINFO_UPDATED_DATE | 2 | | 4 (0) | 00:00:01 | | -Predicate Information (identified byoperation id):- -2-filter ("ANI" LIKE '2*96'AND "ANI" IS NOT NULL) 3-access ("C". "UPDATED_DATE" > = SYSDATEFUR 1) Statistics-- -- 8 recursive calls 0 db block gets 291086 consistent gets 0 physical reads 0 redo size 515 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk ) rows processedExecution Plan---Plan hash value: 3534627589 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- -| 0 | SELECT STATEMENT | | 1 | 17 | 831K (1) | 02:46:18 | 1 | 1 | SORT AGGREGATE | | 1 | 17 | * 2 | TABLE ACCESS BY INDEX ROWID | T_SOFTPHONE_CALLINFO | 1 | 17 | 831K (1) | 02:46:18 | * 3 | INDEX RANGE SCAN | IDX_SOFTPHONE_ANI_ANT | 4989K | | 14254 (1) | 00:02:52 |-| -Predicate Information (identified byoperation id):- -2-filter ("C". "UPDATED_DATE" > = Sysdat (REVERSE ("ANI") LIKE'69*251%') filter (REVERSE ("ANI") LIKE'69*251%') Statistics-- -- 8 recursive calls 0 db block gets 137 consistent gets 0 physical reads 0 redo size 515 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) rows processed

However, after a few days, there were some minor episodes. Although most of the scenarios are extremely efficient, there are still a small number of scenarios with poor efficiency and high consumption after bringing in values, although there may not be many scenarios with those values. but it happens occasionally. DA analysis immediately thought about whether the tilt of the data would lead to the inefficiency of a small number of scores.

After guessing the situation, immediately log in to the system to check the tilt of the data in this picture. Sure enough, some values are very skewed, there is a 8 million, and there are many 100 to 2 million field values. When these extreme values are taken, relying on a single-valued index alone must be very inefficient, as shown in the figure:

So we entered a new round of thinking and analysis, how to rectify and reform to meet all the scenarios, and whether we can directly create a more efficient index? Suddenly found that this sql uses three conditions (updated_date,ani (dani), CREATED_BY), and the developer also provides ideas that the data filtered out under the three conditions will not be very much. At this time, there are new ideas. Can a composite index be created and arranged selectively? will it have amazing results?

Needless to say, immediately started a new round of performance test and analysis, through several combinations of composite index and single-valued index testing, needless to say the specific steps, please directly look at the following test data:

Presumably after repeated performance analysis tests and experiments, combined with the above test data, we already know which way is the best. Finally, we also adopted the transformation plan that is most suitable for this scene, carried out another round of rectification and reform, monitored the operation in the next few days, and achieved excellent results. Finally, we completely solved all the problems, and everyone was happy. I think it's a good case. Share it!

Finally, before we popularize it, some students may make a mistake about reverse indexing and reverse functional indexing, which is a different concept:

A reverse index is also a B-tree index, but it physically reverses each index key value saved in column order. For example, if the index key is 20, and the two bytes of the standard B-tree index key stored in hexadecimal are C1Grade15, then the byte stored in the reverse index is 15PowerC1, which is mainly designed to reduce the contention of breaking up index leaf blocks. It is more practical for large concurrent insertion scenarios, but the disadvantages are also obvious. When using range queries, the performance will also be degraded because the data is scattered in different blocks.

Function index is a kind of index based on function use. When using special functions for certain fields, you can build relevant indexes if you need to use indexes. In this case scenario, what I need to achieve is to completely reverse the number (not byte reversal). The concept is not used, more from the point of view of query efficiency and scenario use, so comprehensive consideration is more suitable for the use of reverse order functions. And establish the index of the relevant reverse function.

Experience:

The sql involved in this case is very simple, but requires DA to have solid basic skills and good business sense of smell. Today, when the database is becoming more intelligent and the daily operation and maintenance is becoming more and more simple, DBA needs to be deeply integrated with the business, and sql optimization and architecture design should be carried out according to business characteristics.

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.