sql tuning---complex sql tuning思路与技巧-part1

  • wisdomone1

    2015-06-08 16:24:52
  • Oracle
  • 原创

本文附件

sql tuning---complex sql tuning思路与技巧.pdf

前言

      相信大多网友对于简单点的SQL，基本可以识别清晰，通过构建索引或者调整索引，可以快速优化问题SQL，如果SQL处理逻辑比较复杂，评估是否性能最佳就不是哪么容易的，
下面我们讲解一点优化思路，供大家参考。
      本文案例摘自网上：http://blog.csdn.net/robinson1988/article/details/18046759,主要分析SQL改写使用场景.

测试结论

1,建议一般上了4或5个表以上，绘制多表关联图，可以快速梳理关系，建体整体概念
2,通过多表关联图发现重复表扫描，可以通过WITH AS避免不必要的表扫描
3,重复的表扫描，必须是关联的表是相同，且WHERE条件也是一样
4,基于相同的表，但WHERE条件不同，是2个不同的结果集
5,再次梳理下思路：
      A,复制你要优化的SQL到一个文本编辑器，建议使用ULTRAEDIT或者SUBLIME TEXT，因为有漂亮美观的格式
      B,直接把SELECT中的查询列进移除，目的在于减少SQL篇幅，有助于你识别SQL整体框架
          当然如果有标量子查询，要保留下来，在后绪的多表关联图体现出来
      C,找到FROM子名，把不同的表及表别名记录在EXCEL中，因为要在绘制多表关联图使用
      D,定位到WHERE条件，根据EXCEL记录的表及表别名，绘制多表关联图，具体绘制多表关联图采用何种工具，
          因人而异，本人采用  omnigraffle pro
             一定要在多表关联图中体现左或右外关联（我采用表后跟+号表现左或右外关联）

测试明细


1,SQL的文本
SELECT "VOUCHER".FID "ID",

"ENTRIES".FID "ENTRIES.ID",

"ENTRIES".FEntryDC "ENTRIES.ENTRYDC",

"ACCOUNT".FID "ACCOUNT.ID",

"ENTRIES".FCurrencyID "CURRENCY.ID",

"PERIOD".FNumber "PERIOD.NUMBER",

"ENTRIES".FSeq "ENTRIES.SEQ",

"ENTRIES".FLocalExchangeRate "LOCALEXCHANGERATE",

"ENTRIES".FReportingExchangeRate "REPORTINGEXCHANGERATE",

"ENTRIES".FMeasureUnitID "ENTRYMEASUREUNIT.ID",

"ASSISTRECORDS".FID "ASSISTRECORDS.ID",

"ASSISTRECORDS".FSeq "ASSISTRECORDS.SEQ",

CASE

WHEN (("ACCOUNT".FCAA IS NULL) AND

("ACCOUNT".FhasUserProperty <> 1)) THEN

"ENTRIES".FOriginalAmount

ELSE

"ASSISTRECORDS".FOriginalAmount

END "ASSISTRECORDS.ORIGINALAMOUNT",

CASE

WHEN (("ACCOUNT".FCAA IS NULL) AND

("ACCOUNT".FhasUserProperty <> 1)) THEN

"ENTRIES".FLocalAmount

ELSE

"ASSISTRECORDS".FLocalAmount

END "ASSISTRECORDS.LOCALAMOUNT",

CASE

WHEN (("ACCOUNT".FCAA IS NULL) AND

("ACCOUNT".FhasUserProperty <> 1)) THEN

"ENTRIES".FReportingAmount

ELSE

"ASSISTRECORDS".FReportingAmount

END "ASSISTRECORDS.REPORTINGAMOUNT",

CASE

WHEN (("ACCOUNT".FCAA IS NULL) AND

("ACCOUNT".FhasUserProperty <> 1)) THEN

"ENTRIES".FQuantity

ELSE

"ASSISTRECORDS".FQuantity

END "ASSISTRECORDS.QUANTITY",

CASE

WHEN (("ACCOUNT".FCAA IS NULL) AND

("ACCOUNT".FhasUserProperty <> 1)) THEN

"ENTRIES".FStandardQuantity

ELSE

"ASSISTRECORDS".FStandardQuantity

END "ASSISTRECORDS.STANDARDQTY",

CASE

WHEN (("ACCOUNT".FCAA IS NULL) AND

("ACCOUNT".FhasUserProperty <> 1)) THEN

"ENTRIES".FPrice

ELSE

"ASSISTRECORDS".FPrice

END "ASSISTRECORDS.PRICE",

CASE

WHEN ("ACCOUNT".FCAA IS NULL) THEN

NULL

ELSE

"ASSISTRECORDS".FAssGrpID

END "ASSGRP.ID"

FROM T_GL_Voucher "VOUCHER"

LEFT OUTER JOIN T_BD_Period "PERIOD"

ON "VOUCHER".FPeriodID = "PERIOD".FID

INNER JOIN T_GL_VoucherEntry "ENTRIES"

ON "VOUCHER".FID = "ENTRIES".FBillID

INNER JOIN T_BD_AccountView "ACCOUNT"

ON "ENTRIES".FAccountID = "ACCOUNT".FID

LEFT OUTER JOIN T_GL_VoucherAssistRecord "ASSISTRECORDS"

ON "ENTRIES".FID = "ASSISTRECORDS".FEntryID

WHERE "VOUCHER".FID IN

(SELECT "VOUCHER".FID "ID"

FROM T_GL_Voucher "VOUCHER"

INNER JOIN T_GL_VoucherEntry "ENTRIES"

ON "VOUCHER".FID = "ENTRIES".FBillID

INNER JOIN T_BD_AccountView "ACCOUNT"

ON "ENTRIES".FAccountID = "ACCOUNT".FID

INNER JOIN t_bd_accountview PAV

ON ((INSTR("ACCOUNT".flongnumber, pav.flongnumber) = 1 AND

pav.faccounttableid = "ACCOUNT".faccounttableid) AND

pav.fcompanyid = "ACCOUNT".fcompanyid)

WHERE (("VOUCHER".FCompanyID IN ('fSSF82rRSKexM3KKN1d0tMznrtQ=')) AND

(("VOUCHER".FBizStatus IN (5)) AND

((("VOUCHER".FPeriodID IN ('+wQxkBFVRiKnV7OniceMDoI4jEw=')) AND

"ENTRIES".FCurrencyID =

'dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC') AND

(pav.FID IN ('vyPiKexLRXiyMb41VSVVzJ2pmCY='))))))

ORDER BY "ID" ASC, "ENTRIES.SEQ" ASC, "ASSISTRECORDS.SEQ" ASC

1.1,单从执行计划看，没有发现明显的全表扫描或索引跳扫，可见执行计划没有明显的问题

2，SQL涉及的表及表别名


3，SQL涉及之间的关联关系图


4，从上述的关联关系图，可知：
    A,T_GL_VOUCHER表与T_GL_VOUCHERENTRY表存在重复扫描；
        T_GL_VOUCHER表与T_GL_BD_ACCOUNTVIEW表存在重复扫描
    B,T_BD_ACCOUNTVIEW因为WHERE条件不同，是作为2个不同的结果集进行关联

    C,存在重复扫描现象，就要消除这种问题，避免一个表同时扫描2次，也是说避免上述A结论涉及表仅扫描1次即可

5，直接采用WITH AS写法，避免重复扫描表问题


6，注意，因为我不知道这些表的具体列到底是什么，所以WITH AS里面的select后面的列用*代替，请根据真实SQL用各个对应表的列替换，
      避免提取不必要的列数据

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
   
   服务过的客户：
          中国电信
          中国移动
          中国联通
          中国电通
          国家电网
          四川达州商业银行
          湖南老百姓大药房
          山西省公安厅
          中国邮政
          北京302医院     
          河北廊坊新奥集团公司
  
 项目经验:
           中国电信3G项目AAA系统数据库部署及优化
           中国联通CRM数据库性能优化
           中国移动10086电商平台数据库部署及优化
           湖南老百姓大药房ERR数据库sql优化项目
           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
           北京高铁信号监控系统RAC数据库部署及优化
           河南宇通客车数据库性能优化
           中国电信电商平台核心采购模块表模型设计及优化
           中国邮政储蓄系统数据库性能优化及sql优化
           北京302医院数据库迁移实施
           河北廊坊新奥data guard部署及优化
           山西公安厅身份证审计数据库系统故障评估
           国家电网上海灾备项目4 node rac+adg 
          贵州移动crm及客服数据库性能优化项目
          贵州移动crm及客服务数据库sql审核项目
          深圳穆迪软件有限公司数据库性能优化项目
 联系方式：
          手机：18201115468
          qq   :   305076427
          qq微博: wisdomone1
          新浪微博：wisdomone9
          qq群：275813900    
          itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/
请使用浏览器的分享功能分享到微信等