首次Oracle SQL Tuning经历

原创 2011年01月07日 18:06:00

这篇文章跟我一样的菜鸟可以看看。事情的缘由在上一篇Blog:如何判断程序处于I/O等待里面已经介绍过了。这件事情我也得到一个教训,QA环境和Production环境还是很不同的,特别是DB 操作,QA环境数据库数据量比较小,SQL执行计划在Production上可能得出完全不同的结果,而我的遭遇就是一条SQL在Production上一跑就再也回不来了,最后只好让DBA kill掉那个任务。

原来的SQL是这样的:一共牵涉5张表,表名和 每张表的索引情况如下:

Table Index Name Index Infomation
Table_Item Item_PK PRIMARY KEY (ID)
Table_Item Item_LM_IDX Last_Modified Asc
Table_Attr Attr_PK PRIMARY KEY (ID)
Table_Desc Desc_PK PRIMARY KEY (ID)
Table_Delay Delay_PK PRIMARY KEY (ID, PARTITION_KEY)
Table_Product Product_P_PK PRIMARY KEY(Product_ID)

select … from , Table_Desc , Table_Product , Table_Attr , Table_Delayed
where
(Table_Item.id = Table_Desc.id)
and (Table_Item.product_id = Table_Product.product_id(+))
and (Table_Item.id = Table_Attr.id(+))
and (Table_Item.id = Table_Delay.id(+))
and (Table_Item.last_modified between to_date( :start_time, ‘YYYY-MM-DD HH24:MI:SS’ )
                                                and to_date( :end_time , ‘YYYY-MM-DD HH24:MI:SS’ ) 
      )
order by Table_Item.last_modified;

使用的DB Hint 是:

/*+ index(table_item item_lm_idx) use_nl(table_delay) index(table_delay delay_pk) */

SQL>  explain plan for
select …

SQL>  @?/rdbms/admin/utlxpls

由于打印格式不同,我就不把执行计划贴出来了,执行情况良好:

  1. 所有的表都使用的了索引,Index Range Scan或者Index Unique Scan
  2. 执行Join的时候使用了Nested Loops或者Nested Loops Outer
  3. 没有发现Sort Join和Merge Join
  4. 没有发现Table Access Full –> Full Table Scan

我们发现即使在SQL中有order by table_item.last_modified,在SQL执行计划中也没有出现任何Sort有关的信息,一般在SQL中尽量不要使用order by,因为在DB Server端进行Sort是很耗费资源的。但是有一个例外,就是表中的数据已经排好序的情况下,我们这里就是这样的哦情况,执行计划中没有Sort是因为当从Table_Item表中以 Table_Item.last_modified between :start_time and :end_time取数据时,走的是Item_LM_IDX –>Last_Modified Asc, 所以取出来的数据本身已经按照last_modified 排好序了。

但是我更改过的SQL是这样的:

where Table_Item.last_modified=:start_time and Table_Item.ID>:last_id order by Table_Item.ID

让我悲哀的是这条修改过的SQL在有些数据库服务器上的执行计划是好的,有些不好。DBA给我的解释是并不是所有的表的统计信息都是更新过的,于是在那些统计信息没有更新的表上的执行计划很有可能就不对了。不如说这个:

00 SELECT STATEMENT
01     MERGE JOIN OUTER
02         NESTED LOOPS OUTER(将Item,Product,Desc的结果和Table_Delay进行Nested Join)
03             MERGE JOIN(由两个SORT JOIN的结果组成,得到Item,Product,Desc3张表的Join结果)
04                 SORT JOIN(将Table_Item和Table_Product的结果按照ID进行排序)
05                    NESTED LOOPS OUTER(将Table_Item和Table_Product进行Nested Outer Join)
06                         TABLE ACCESS BY INDEX ROWID   –> Table_Item
07                             INDEX RANGE SCAN                 –> Item_LM_IDX(使用Item_LM_IDX索引)
08                         TABLE ACCESS BY INDEX ROWID   –> Table_Product
09                             INDEX UNIQUE SCAN               –>  Product_P_PK
10                 SORT JOIN(将Table_Desc满足条件的记录按照ID进行排序)
11                    TABLE ACCESS BY INDEX ROWID       –> Table_Desc
12                          INDEX RANGE SCAN                   –>  RTable_DESC_PK
13             PARTITION RANGE ALL
14                 TABLE ACCESS BY LOCAL INDEX ROWID  –> Table_Delay
15                     INDEX RANGE SCAN                         –> Delay_PK
16         SORT JOIN
17             TABLE ACCESS FULL                                –> Table_Attr

注意到使用了很多Merge Sort,而处理Table_Attr的时候更是出现了Full Table Scan,更本就不使用索引。这里说句老实话,我不知道是不是应该DBA更新统计信息,重新给相关的表更新一下统计信息,还是就赶紧改一下DB Hint把自己的事情做完就算。不过我也理解的,Ebay的数据库太大了,不是随便想做什么就行的,最后我还是选择了修改DB Hint.

Ticket resolve的结果是,修改Db Hint :

/*+
index_asc(Table_Attr ATTR_PK)
index(Table_Item Item_LM_IDX)
use_nl(Table_Item Table_Attr Table_Desc Table_Delayed Table_Product) index(Table_Delay DELAY_PK)
index(Table_Product PRODUCT_P_PK)
index(Table_Desc RTable_DESC_PK)
*/

给所有的表都制定Index,建议Oracle对所有的表都使用Nested Loops Join。重新看一下执行计划:

00  SELECT STATEMENT
01      SORT ORDER BY
02          NESTED LOOPS OUTER
03              NESTED LOOPS OUTER
…(以下省略)

可以看到,修改DB Hint以后,只有对最后的结果进行SORT,这也是我们的初衷。没有SORT JOIN和Merge Join了。速度也快了很多。因为Ebay的DB是Split的,DBA为了保险,帮我把这条修改过的SQL对该数据库所有的Splits都运行了一次,才把Ticket标识成Resolved.

我刚把代码改了,添加了判断,如果运行新的SQL就使用修改的Hint。待会给QA测试,IDC的Sunil还不肯发BUG给我,因为他在QA环境没有观察到这个问题,甚至的Production StandBy DB也不能重现。这一点只能用楼上DBA的话来解释了:虽然是同一个DB的不同的Split,但是不同的Splits之间数据还是有差异,统计数据也不同,结果是影响Oracle做出判断,所以他们做SQL Review的时候都是要运行一下的,而不是光看看执行计划。

谢谢COC DBA Team的amao和alex给我上了一课。          

 

SQL Tuning Advisor 使用11G的自动调优建议

ORACLE 提供了自动SQL优化的工具,当对DBA来说 可以节约下思考的时间,或许提供更多的参考价值...
  • ZengMuAnSha
  • ZengMuAnSha
  • 2016年06月03日 11:40
  • 6077

Oracle Tuning (Oracle 性能调整)的一些总结

Oracle Tuning (Oracle 性能调整)的一些总结 关于Oracle的性能调整,一般包括两个方面,一是指Oracle数据库本身的调整,比如SGA、PGA的优化设置,二是连接Oracle的...
  • jojojojo2002
  • jojojojo2002
  • 2014年11月28日 11:11
  • 728

初次使用SQL调优建议工具--SQL Tuning Advisor

在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor :STA),它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。...
  • guogang83
  • guogang83
  • 2014年05月23日 09:29
  • 2100

Oracle Performance Tuning Overview 翻译(Oracle性能优化概述 自己的中英文比对翻译)

Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) B14211-03 Home Book List ...
  • xiaohai798
  • xiaohai798
  • 2014年06月23日 23:26
  • 2719

ORACLE调优概述以及ADDM以及STA辅助生成示例

整理了笔者日常工作中的经验,概述了常见的ORACLE数据库优化的方法,并展示了如何通过ADDM以及STA辅助生成优化意见...
  • zccgood002
  • zccgood002
  • 2017年03月12日 14:52
  • 269

快速SQL TUNING——1分钟搞定超慢SQL

本文通过真实的示例,展示了一次解决大SQL性能问题的过程,面对如此繁杂而冗长的SQL和执行计划,我们必须掌握一定的经验和方法,才可能在短时间内解决问题,这里主要是提供给大家一个真实的示例,希望大家能从...
  • LHDZ_BJ
  • LHDZ_BJ
  • 2013年09月17日 16:39
  • 1421

Camera tuning 基础知识点

相关知识点总结: 一、模组及工艺相关知识      ①模组的组成           1)模组的基本组成。(AF&FF)           2)镜头----镜头的发展历史,镜头相关参数解析,...
  • suiyiwuhui
  • suiyiwuhui
  • 2017年06月05日 16:45
  • 1165

第一次安装oracle遇到的问题

写这篇文章是想把我在学习中遇到的问题记录下来,以后再遇到这种问题可以不费力气的解决,另外一个目的就是想记录自己学习的过程,来发现自己有哪些不足和需要改进的地方。...
  • c913647576
  • c913647576
  • 2015年11月29日 10:23
  • 661

数据库性能优化(database tuning)性能优化绝不仅仅只是索引

本文从宏观上讨论了调优涉及的内容,也说明了调优不仅仅是索引的问题,这虽然对很多朋友来说很简单、很明显,但也许还有很多朋友不知道,希望通过这个短文普及下调优的常识。...
  • LHDZ_BJ
  • LHDZ_BJ
  • 2015年03月02日 15:32
  • 1350

2018年首次面试经历

17年底公司裁员,很幸运我被裁掉了,呵呵,休息了一段时间,今天去参加2018年的首次面试,我有面试恐惧症(无论大小面试都会紧张,即使面试上几十回还是紧张),面试的是一家P2P金融公司,高级开发工程师职...
  • keplerpig
  • keplerpig
  • 2018年01月08日 18:17
  • 14
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:首次Oracle SQL Tuning经历
举报原因:
原因补充:

(最多只允许输入30个字)