Core technical sentiment of Oracle ACS senior consultant Luo minoluo: Automatic SQL statement scanning tool

Author :? SHOUG Member-Luo Min, ORACLEACS Senior Consultant, asked questions and demands: "Does your Oracle company have such an automatic SQL statement scanning tool? Using this tool, we can import our application software to eliminate most of the SQL statements. In this way, we can reduce the workload of testing and performance optimization, and avoid exposing performance questions after production.

Author :? SHOUG Member-oracle acs senior consultant Luo Min asked questions and requirements "Does your Oracle company have such an automatic SQL statement scanning tool? Using this tool, we can import our application software to eliminate most of the SQL statements. In this way, we can reduce the workload of testing and performance optimization, and avoid exposing performance questions after production.

Author :?

SHOUG Member-oracle acs senior consultant Luo Min

1. Problems and requirements

"Does your Oracle company have such an automatic SQL statement scanning tool? Using this tool, we can import our application software to eliminate most of the SQL statements. This will reduce our testing and performance optimization workload and avoid exposing performance problems after production ." -Requirements from a mobile customer.

"Oracle, XX mobile wants Oracle to provide automatic SQL scanning tools. Do we have SQL scanning tools? I heard that a third-party company has such a product and has already tried it out with the customer ." -Concerns from Oracle service sales colleagues.

Yes, the customer's requirements are not reasonable. But as far as I know, Oracle does not seem to have such a magic tool to cure all diseases. A third-party company has such a tool, which is too eye-catching to customers. On the one hand, it is challenging and on the other hand it is also a competitive pressure.

2. First look at Lu Shan's true face

As a result, my sales colleagues and I took the opportunity to visit and investigate the customer at the customer's site to find out the customer's above requirements and the automation tools of third-party companies. We don't need to talk about the customer's requirements. The key is to be curious about the so-called automated tools. Due to business factors, the customer did not directly display the use process and interface of the tool, but told us the general principle: the tool first defines a set of scoring rules, for example: whether to bind variables to SQL statements; whether there are functions before condition fields; whether there are more than 4 tables connected to multiple tables... ..., Then, the entered SQL statement is judged. If these rules are violated, the score is deducted! Finally, score the SQL statement and the entire application module.

So it turns out! In most cases, these rules are unreasonable. For example, the condition field is used to add a function, especially the to_char function before the date field:

To_char (DJ_SZ.JDRQ, 'yyyy. MM. dd') BETWEEN '2017. 123' AND '2017. 123'

It is a very basic, amateur, and wrong programming method. The correct method should be:

DJ_SZ.JDRQ BETWEEN to_date ('1970. 100', 'yyyy. MM. dd') AND to_date ('1970. 100', 'yyyy. MM. dd ')

However, more rules are debatable. For example, in the programming specification recommended by Oracle, not all SQL statements should use variable binding, but only for small transaction SQL statements with large concurrency should use variable binding, for large transaction SQL statements with a small amount of concurrency, especially complicated SQL statements, Oracle recommends that you do not bind variables. Can a third-party automatic tool analyze whether SQL statements are high-concurrency or low-concurrency access, as well as large transactions and small transactions? Suspicious.

A more typical example is that Oracle has never officially suggested that a single SQL statement cannot be connected to more than four tables. Indeed, multi-table join may lead to poor performance, but the problem is not that the number of connected tables is large or small, whether programmers understand the principles and applicable scenarios of Oracle's Nested Loop, Hash Join, and other table Join technologies, as well as the design principles of indexes in table Join. The following is a typical SQL statement of a well-known financial software in China:

Select *

From (select rownum num, temp .*

From (select a. fid,

... ...

A. playdeptname as playdeptNameCode

From t_claim_remittancerecord

Left join t_pay_remittype B on a. remittype = B. fid

Left join t_pay_fundtype c on c. fid = a. amountscategory

Left join t_org_department d on a. remitdepart =

D. finasyscode

Left join t_org_department y on a. playdeptname =

Y. finasyscode

And y. status = 1

Left join t_org_employee f on f. empcode = a. addperson

Left join t_org_department k on f. deptid = k. id

Left join t_org_employee g on g. empcode = a. updateperson

Left join t_org_employee h on h. empcode = a. claimman

Left join t_bd_customer cus on cus. fnumber = a. customer

Left join V_LMS_SUPPLIER s on s. snumber = a. supplier

Left join t_deposit_printer I on I. codenum = a. codenum

Left join t_org_employee j on I. createuser = j. empcode

WHERE 1 = 1

And a. accountName like '%' |: 1 | '%'

And a. claimState like '%' |: 2 | '%'

And a. writeOffState like '%' |: 3 | '%'

And a. reachAmountDate between: 4 and: 5

And a. repealstate! = 1

Order by addTime desc, codeNum) temp) t

WHERE t. num <=: 6

And t. num>: 7

Wow! This statement is complex. There are more than 10 connected tables. If you use a third-party company SQL automatic scanning tool. This statement must have been deducted or even completely shot. However, what is the actual running status of this statement? The execution plan of the statement is as follows:

------------------------------- | Id? | Operation ???????????????????????????????????? | Name ????????????????????? | Cost (% CPU) |

------------------------------- | ?? 0 | select statement ????????????????????????????? | ?????????????????????????? | ??? 14 (100) |

| ?? 1 |? FILTER ?????????????????????????????????????? | ?????????? ???????????????? | ??????????? |

| ?? 2 | ?? VIEW ??????????????????????????????????????? | ?????????????????????????? | ??? 14 ?? (8) |

| ?? 3 | ??? COUNT ????????????????????????????????????? | ?????????????????????????? | ??????????? |

| ?? 4 | ???? VIEW ?????????????? ??????????????????????? | ?????????????????????????? | ??? 14 ?? (8) |

| ?? 5 | ????? Sort order ??????????????????????????? | ?????????????????????????? | ??? 14 ?? (8) |

| ?? 6 | ?????? FILTER ????????????????????????????????? | ?????????????????????????? | ????????? ?? |

| ?? 7 | ??????? Nested loops outer ???????????????????? | ?????????????????????????? | ??? 13 ?? (0) |

| ?? 8 | ???????? Nested loops outer ??????????????????? | ?????????????????????????? | ??? 12 ?? (0) |

| ?? 9 | ????????? Nested loops outer ?????????????????? | ?? ???????????????????????? | ??? 11 ?? (0) |

|? 10 | ?????????? Nested loops outer ????????????????? | ?????????????????????????? | ??? 10 ?? (0) |

|? 11 | ??????????? Nested loops outer ???????????????? | ?????????????????????????? | ???? 9 ?? (0) |

|? 12 | ???????????? Nested loops outer ??????????????? | ?????????????????????????? | ???? 8 ?? (0) |

|? 13 | ????????????? Nested loops outer ?????????????? | ?????????????????????????? | ???? 7 ?? (0) |

|? 14 | ?????????????? Nested loops outer ????????????? | ?????????????????????????? |? ??? 6 ?? (0) |

|? 15 | ??????????????? Nested loops outer ???????????? | ?????????????????????????? | ???? 5 ?? (0) |

|? 16 | ???????????????? Nested loops outer ??????????? | ?????????????????????????? | ???? 4 ?? (0) |

|? 17 | ????????????????? Nested loops outer ?????? ???? | ?????????????????????????? | ???? 3 ?? (0) |

|? 18 | ?????????????????? Nested loops outer ????????? | ?????????????????????????? | ???? 2 ?? (0) |

|? 19 | ??????????????????? Table access by index rowid | T_CLAIM_REMITTANCERECORD? | ???? 1 ?? (0) |

|? 20 | ??????? ????????????? Index range scan ????????? | Idx_ctl ?????????????????? | ???? 1 ?? (0) |

|? 21 | ??????????????????? Table access by index rowid | T_PAY_REMITTYPE ?????????? | ???? 1 ?? (0) |

|? 22 | ???????????????????? Index unique scan ???????? | PK_REMITTYPE_FID ???? ????? | ???? 1 ?? (0) |

|? 23 | ?????????????????? Table access by index rowid | T_PAY_FUNDTYPE ??????????? | ???? 1 ?? (0) |

|? 24 | ??????????????????? Index unique scan ????????? | PK_FUNDTYPE_FID ?????????? | ???? 1 ?? (0) |

|? 25 | ????????????????? Table access by index rowid? | T_DEPOSIT_PRINTER ???????? | ???? 1 ?? (0) |

|? 26 | ?????????????????? Index unique scan ?????????? | PK_T_DEPOSIT_PRINTER ????? | ???? 1 ?? (0) |

|? 27 | ???????????????? Table access by index rowid ?? | T_BD_SUPPLIER ???????????? | ???? 1 ?? (0) |

|? 28 | ????????????????? Index range scan ???????????? | IDX_BD_SUPPLIER_NUM ?????? | ???? 1 ?? (0) |

|? 29 | ??????????????? Table access by index rowid ??? | T_ORG_DEPARTMENT ????????? | ???? 1 ?? (0) |

|? 30 | ???????????????? Index range scan ????????????? | IDX_T_ORG_DPT_FINASYSCODE | ???? 1 ?? (0) |

|? 31 | ?????????????? Table access by index rowid ???? | T_ORG_DEPARTMENT ????????? | ???? 1 ?? (0) |

|? 32 | ??????????????? Index range scan ?????????????? | IDX_T_ORG_DPT_FINASYSCODE | ???? 1 ?? (0) |

|? 33 | ????????????? Table access by index rowid ????? | T_BD_CUSTOMER ???????????? | ???? 1 ?? (0) |

|? 34 | ?????????????? Index range scan ??????????????? | IDX_BD_CUSTOMER_NUM ?????? | ???? 1 ?? (0) |

|? 35 | ???????????? Table access by index rowid ?????? | T_ORG_EMPLOYEE ??????????? | ???? 1 ?? (0) |

|? 36 | ????????????? Index unique scan ??????????????? | UK_EMPLOYEE_EMPCODE ?????? | ???? 1 ?? (0) |

|? 37 | ??????????? Table access by index rowid ??????? | T_ORG_DEPARTMENT ????????? | ???? 1 ?? (0) |

|? 38 | ???????????? Index unique scan ???????????????? | SYS_C00797036 ???????????? | ???? 1 ?? (0) |

|? 39 | ?????????? Table access by index rowid ???????? | T_ORG_EMPLOYEE ??????????? | ???? 1 ?? (0) |

|? 40 | ??????????? Index unique scan ????????????????? | UK_EMPLOYEE_EMPCODE ?????? | ?? ?? 1 ?? (0) |

|? 41 | ????????? Table access by index rowid ????????? | T_ORG_EMPLOYEE ??????????? | ???? 1 ?? (0) |

|? 42 | ?????????? Index unique scan ?????????????????? | UK_EMPLOYEE_EMPCODE ?????? | ???? 1 ?? (0) |

|? 43 | ???????? Table access by index rowid ??????? ??? | T_ORG_EMPLOYEE ??????????? | ???? 1 ?? (0) |

|? 44 | ????????? Index unique scan ??????????????????? | UK_EMPLOYEE_EMPCODE ?????? | ???? 1 ?? (0) |

We should not be afraid of the above execution plan, but feel it in appearance, that is, the beauty of the database! We can see how symmetrical and rhythmic the execution plan is. It is also like an open fan. Secondly, you must believe that the appearance of beautiful things should also be good in essence. Indeed, back to the technical nature, we found that although the statement involves more than 10 tables, the actual running efficiency is very high, for example, Cost is only 14, of course, Cost is inaccurate. More importantly, this statement uses the Nested Loop connection technology very beautifully for each table connection and uses the index of the joined field reasonably. It is precisely because the design and development staff are very familiar with the Oracle table connection principle and index design specifications that they have designed such an "delicious and nice-looking" SQL statement.

However, the SQL automatic scanning tools of third-party companies are likely to have no harm. Everyone can believe in the principle that the quality of a thing in the world lies in its own right and wrong, rather than in the quantity and quantity of things. If this principle is applied to the multi-Table connection technology, it means that the multi-Table connection is not due to the many or fewer connected tables, but to the right and error of each table connection. Because the Oracle table is connected to two tables each time, and then connected to the third and fourth tables. If you fully understand the various Oracle table connection technologies and index design specifications, each table connection is efficient, and it is reasonable to connect multiple tables. In turn, if you do not understand the Oracle table connection technology and adaptation scenarios, even the connection between the two tables will fail.

3. More pragmatic

This title is harsh and even mean, but it is true that I feel it. The domestic IT market does not have such a normal phenomenon: in the face of some seemingly reasonable needs of customers, IT is difficult to achieve, some companies not to guide customers, but blindly cater to customers, it is even a boost for commercial purposes. But do you know this is a very short-sighted behavior? Won't the customer quickly verify the effectiveness or even authenticity of the so-called SQL automatic scanning tool? In this case, why bother asking customers for such "good "? In fact, it is likely to disappoint customers and ruin their reputation.

Performance analysis and optimization, especially SQL statement performance analysis and optimization, how can we only analyze the performance in static form? Instead of dynamic god analysis associated with the actual system and data? I remember one year I attended a database technology conference. I was shocked by the speech of a foreign performance optimization master. His speech focused on the correlation between performance optimization and application data. Throughout the speech, he did not show an SQL statement optimization technique, but talked about the importance of data distribution on SQL statement access performance, such as analyzing maximum value, minimum value, and grouping statistics by field, and when to collect statistics by Bucket. The so-called automatic SQL statement scanning tool may not connect to the customer's actual system, and does not analyze the execution plan or the customer's data, so the quality of the SQL statement can be scanned? It is indeed something of a utopia.

Return to the question at the beginning of this article: "Does your Oracle company have such an automatic SQL statement scanning tool ?" The correct answer is: Oracle does not have this so-called automatic SQL statement scanning tool that does not read data or execute plans, however, Oracle has provided a large number of built-in SQL optimization tools since 10 Gb. For example: ADDM, SQL Access Advisor, SQL Tuning Advisor, Automatic SQL Tuning, SQL Profile, SPA (SQL Performance Analyzer), SPM (SQL Plan Management )... ... These tools not only analyze SQL statement execution plans, but also analyze statistical information, data distribution, index design, and so on, some more reasonable optimization suggestions for SQL statements are provided. For example, the 11g Automatic SQL Tuning is used to analyze whether the statistical information of the tables accessed by the SQL statement has expired, whether the index is missing, whether valid SQL Profile information can be generated, and whether the statement writing is reasonable.

Furthermore, although the Oracle automatic optimization tool can effectively analyze and solve many SQL Performance problems, more fundamental issues, especially those closely related to application data, it is still necessary for application design developers to solve problems from the perspectives of standardized database model design, basic technology mastery, SQL design and development specifications, application software quality control, and enhanced design and development management.

In short, performance optimization work, especially application performance analysis and optimization work, still needs to be done in a down-to-earth manner, even if we need so-called automated tools, we also recommend that you give priority to Oracle's built-in tools. After all, these tools are part of Oracle products and have been rigorously tested and verified by a large number of global customers, it is something that has universal applicability.

Or end with the title of this section:

A little less, more pragmatic!

Related posts:

1. Oracle Acs senior consultant Luo minoluo core technical sentiment: Table sharding or partition?
2. Oracle Acs senior consultant Luo minoluo core technical sentiment: Niu! Automatic Optimization of 11 GB and SQL Profile
3. [Oracle CBO optimizer] View Merging technology _ complex_view_merging & _ simple_view_merging
4. Gather more plan statistics by gather_plan_statistics hint
5. Latch free: SQL Performance problems caused by cache buffer handles
6. DBA_HIST_PLAN_OPERATION_NAME
7. ORA-00600: [15570] internal error example
8. SQL operations that can be executed in parallel in Oracle
9. Unique index vs non-unique index
10. Union all returning wrong results?

Original article address: Oracle Acs senior consultant Luo minoluo core technology sentiment: automatic scanning of SQL statement tools ?, Thanks to the original author.