当前位置:Gxlcms > mysql > OracleAcs资深顾问罗敏老罗技术核心感悟:自动扫描SQL语句工具

OracleAcs资深顾问罗敏老罗技术核心感悟:自动扫描SQL语句工具

时间:2021-07-01 10:21:17 帮助过:17人阅读

作者为:? SHOUG成员 – ORACLE ACS高级顾问罗敏 问题和需求 “你们Oracle公司有这样的自动扫描SQL语句工具吗?通过这个工具,把我们的应用软件输进去,就能扫出SQL语句的大部分问题。这样就可以减少我们测试和性能优化工作量,更能避免投产之后才暴露性能问

作者为:?

SHOUG成员 – ORACLE ACS高级顾问罗敏

  1. 问题和需求

“你们Oracle公司有这样的自动扫描SQL语句工具吗?通过这个工具,把我们的应用软件输进去,就能扫出SQL语句的大部分问题。这样就可以减少我们测试和性能优化工作量,更能避免投产之后才暴露性能问题。” — 来自某移动客户的需求。

“老罗,XX移动公司希望我们Oracle公司提供自动扫描SQL工具,我们有吗?听说第三方公司有这样的产品,已经在客户那儿试用了。” — 来自Oracle服务销售同事的担忧。

是啊,客户的需求再合理不过。但据我所知,Oracle公司好像没有这样包治百病的神奇工具。第三方公司居然有这样的工具,太吸引客户眼球了,一方面让人感到质疑,另一方面也令人感到一种竞争压力。

  1. 初识庐山真面目

于是,我和销售同事趁去该客户现场拜访、调研的机会,对该客户的上述需求和第三方公司的自动化工具一探究竟了。客户的需求不必多言了,我们关键是对所谓自动化工具充满好奇。因商务因素,客户并没有给我们直接展示该工具的使用过程和界面,但告诉我们大致原理:原来该工具首先通过定义一组评分规则,例如:SQL语句是否使用绑定变量;条件字段前是否有函数;多表连接是否超过4个表… …,然后将输入的SQL语句进行评判,若违反这些规则,扣分!最后给该SQL语句和整个应用模块打分。

原来如此!这些规则在大部分情况下不无道理,例如,条件字段加函数,特别是在日期字段前加to_char函数:

to_char(DJ_SZ.JDRQ, ‘YYYY.MM.DD’) BETWEEN ‘2014.04.01’ AND ‘2014.04.17’

就是一种非常初级、业余、错误的编程方式。正确方式应该是:

DJ_SZ.JDRQ BETWEEN to_date(‘2014.04.01’,’YYYY.MM.DD’) AND to_date(‘2014.04.17’,’YYYY.MM.DD’)

但是,更多的规则值得商榷。例如,在Oracle公司推荐的编程规范中,并不是所有SQL语句都应该使用绑定变量的,而只是针对并发量大的小事务SQL语句才应该使用绑定变量,而针对并发量小的大事务SQL语句,特别是非常复杂SQL语句,Oracle公司建议是不要使用绑定变量。第三方的自动工具能分析出SQL语句是高并发量还是低并发量访问,以及大事务和小事务吗?值得怀疑。

更为典型的例子是,其实Oracle公司从来没有官方正式建议:一个SQL语句不能超过4个表的连接。的确,多表连接可能导致性能不佳,但问题不在于连接表的多和少,而在于编程人员是否理解了Oracle的Nested Loop、Hash Join等多种表连接技术原理和适应场景,以及在表连接中索引的设计原理。以下就是一个国内著名财务软件的典型SQL语句:

select *

from (select rownum num, temp.*

from (select a.fid,

… …

a.playdeptname as playdeptNameCode

from t_claim_remittancerecord a

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

哇!该语句好复杂哦,连接的表多达10多个。若采用第三方公司的SQL自动扫描工具。该语句一定被扣分甚至彻底枪毙了。可是,该语句实际运行情况如何呢?以下就是该语句的执行计划:

———————————————————————————————| 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 BY??????????????????????????? |?????????????????????????? |??? 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_TCR?????????????????? |???? 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)|

大家看到上述执行计划,首先不应感到畏惧,而应该从外观上感慨一下,那就是数据库的美感!大家看这个执行计划的形状多么对称和富有韵律感,也多像一把打开的美丽扇子。其次,大家一定要相信,外观充满美感的东西,本质上也应该不错,呵呵。的确,回到技术本质,我们发现虽然该语句涉及10多张表的连接,但实际运行效率效果非常高,例如Cost才14,当然Cost有不准确的时候。更重要的是,该语句每次表连接都非常漂亮地采用了Nested Loop连接技术,并且都合理地采用了被连接字段的索引。正是因为设计开发人员非常了解Oracle表连接原理以及索引设计规范,所以才设计出了这样“又好吃、又好看”的SQL语句。

可是,第三方公司的SQL自动扫描工具却很可能滥杀无辜了。大家一定能相信一个原理:世界上一件事物的好坏不在于多和少,而在于其本身的对和错。若将此原理运用在多表连接技术方面,那就是:多表连接的好坏不在于连接表的多和少,而在于每次表连接的对和错。因为Oracle表连接每次都是两个表进行连接,然后再进行第三个、第四个表的连接。若充分理解了Oracle各种表连接技术、索引设计规范等,每次表连接都是高效的,再多的表连接也是合理的。反过来,若不了解Oracle表连接技术和适应场景,即便是两个表的连接都会出问题。

  1. 少一点噱头,多一点务实

此标题有点刺耳,甚至刻薄,但的确是本人有感而发。国内IT市场也的确存在这种不太正常现象:面对客户某些看似合理,实则很难实现的需求,某些公司不是去合理引导客户,反而是一味迎合客户,甚至是推波助澜,更实质的目的还是出于商业考虑。但是,大家不知这是一种非常短视的行为吗?难道客户不会很快就验证出这种所谓SQL自动扫描工具的有效性,甚至真伪性吗?既然如此,大家何必去费尽心机,去讨客户这种“好”?实际上很可能是既让客户失望,也毁自己声誉的事情。

性能分析和优化,特别是SQL语句性能分析和优化,怎么可能只做静态的形式分析?而不做与实际系统和数据相关联的动态神式分析?记得有一年参加一个数据库技术大会,一位国外性能优化大师的演讲曾经让我非常震撼,他的演讲主题是性能优化与应用数据的关联性,整个演讲中,他未展现一个SQL语句优化技术,而是大谈数据分布对SQL语句访问性能的重要性,诸如按字段分析最大值、最小值、分组统计等,以及何时需要按Bucket方式收集统计信息等。所谓的SQL语句自动扫描工具,可能连客户实际系统都不连接,执行计划也不分析,客户数据更不了解,就能扫描出SQL语句质量?的确有点像个乌托邦的东西。

再回到本文开头一个问题:“你们Oracle公司有这样的自动扫描SQL语句工具吗?”准确地回答是:Oracle的确没有这种不看数据、不看执行计划的所谓自动扫描SQL语句工具,但Oracle公司自10g开始就提供了大量内置的SQL优化工具,例如:ADDM、SQL Access Advisor、SQL Tuning Advisor、Automatic SQL Tuning、SQL Profile、SPA(SQL Performance Analyzer)、SPM(SQL Plan Management)… …这些工具一个共同特点是不仅分析SQL语句执行计划,而且分析统计信息,分析数据分布情况,分析索引设计情况等,综合各方面情况,给出一些更合理的SQL语句优化建议。例如,11g的Automatic SQL Tuning就是分析SQL语句所访问表的统计信息是否过期、是否缺乏索引、是否可产生有效的SQL Profile信息、语句编写是否合理等。

再者,虽然Oracle自动优化工具能有效分析和解决很多SQL性能问题,但更多基础性,特别是与应用数据紧密相关的问题,还是需要应用设计开发人员从数据库模型规范化设计、 基础技术掌握、SQL设计开发规范、应用软件质量控制、加强设计开发管理等层面和角度去加以解决。

总之,性能优化工作,特别是应用性能分析和优化工作,还是需要大家踏踏实实、一点一滴地做起,即便需要所谓自动化的工具,也建议大家优先考虑Oracle公司本身自带的工具,毕竟这些工具是Oracle产品的一部分,经过了严格测试,也为全球广大客户的大量实践所验证,是具有普遍适用性的东西。

还是以本节标题作为本文结尾:

少一点噱头,多一点务实!

Related posts:

  1. Oracle Acs资深顾问罗敏 老罗技术核心感悟:分表还是分区?
  2. Oracle Acs资深顾问罗敏 老罗技术核心感悟:牛! 11g的自动调优和SQL Profile
  3. 【Oracle CBO优化器】视图合并View Merging技术 _complex_view_merging & _simple_view_merging
  4. Gather more plan statistics by gather_plan_statistics hint
  5. latch free:cache buffer handles造成的SQL性能问题
  6. DBA_HIST_PLAN_OPERATION_NAME
  7. ORA-00600:[15570]内部错误一例
  8. Oracle中可被并行化执行的SQL操作
  9. unique index vs non-unique index
  10. UNION ALL returning wrong results?

人气教程排行