Oracle 10g SQL 优化再学习
Posted onOracle 10g SQL 优化再学习 - joe --专注java,开源,架构,项目管理 - BlogJava
joe --专注java,开源,架构,项目管理
STANDING ON THE SHOULDERS OF GIANTS
posts - 339, comments - 292, trackbacks - 0, articles - 1 BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合 :: 管理 ## Oracle 10g SQL 优化再学习
Posted on 2010-10-10 23:52 @joe 阅读(107) 评论(0) 编辑 收藏 所属分类: java性能 、方法论 、架构
从8i到10g,Oracle不断进化自己的SQL Tuning智能,一些秘籍级的优化口诀已经失效。 但我喜欢失效,不用记口诀,操个Toad for Oracle Xpert ,按照大方向舒舒服服的调优才是爱做的事情。
1.Excution Plan Excution Plan是最基本的调优概念,不管你的调优吹得如何天花乱堕,结果还是要由Excution plan来显示Oracle 最终用什么索引、按什么顺序连接各表,Full Table Scan还是Access by Rowid Index,瓶颈在什么地方。如果没有它的指导,一切调优都是蒙的。
2.Toad for Oracle Xpert 用它来调优在真的好舒服。Quest 吞并了Lecco后,将它整合到了Toad 的SQL Tunning里面:最清晰的执行计划显示,自动生成N条等价SQL、给出优化建议,不同SQL执行计划的对比,还有实际执行的逻辑读、物理读数据等等一目了然。
3.索引 大部分的性能问题其实都是索引应用的问题,Where子句、Order By、Group By 都要用到索引。 一般开发人员认为将索引建全了就可以下班回家了,实则还有颇多的思量和陷阱。
3.1 索引列上不要进行计算 这是最最普遍的失效陷阱,比如where trunc(order_date)=trunc(sysdate), i+2>4。索引失效的原因也简单,索引是针对原值建的二叉树,你将列值/*3/4+2折腾一番后,原来的二叉树当然就用不上了。解决的方法: 1. 换成等价语法,比如trunc(order_date) 换成
where order_date>trunc(sysdate)-1 and order_date<trunc(sysdate)+1 2. 特别为计算建立函数索引
create index I_XXXX on shop_order(trunc(order_date)) 3. 将计算从等号左边移到右边 这是针对某些无心之失的纠正,把a/*2>4 改为a>4/2;把TO_CHAR(zip) = '94002' 改为zip = TO_NUMBER('94002');
3.2 CBO与索引选择性 建了索引也不一定会被Oracle用的,就像个挑食的孩子。基于成本的优化器(CBO, Cost-Based Optimizer),会先看看表的大小,还有索引的重复度,再决定用还是不用。表中有100 条记录而其中有80 个不重复的索引键值. 这个索引的选择性就是80/100 = 0.8,留意Toad里显示索引的Selective和Cardinailty。实在不听话时,就要用hints来调教。 另外,where语句存在多条索引可用时,只会选择其中一条。所以索引也不是越多越好:)
3.3 索引重建
传说中数据更新频繁导致有20%的碎片时,Oracle就会放弃这个索引。宁可信其有之下,应该时常alter index
3.4 其他要注意的地方 不要使用Not,如goods_no != 2,要改为
where goods_no>2 or goods_no<2 不要使用is null , 如WHERE DEPT_CODE IS NOT NULL 要改为
WHERE DEPT_CODE >=0;3.5 select 的列如果全是索引列时 又如果没有where 条件,或者where条件全部是索引列时,Oracle 将直接从索引里获取数据而不去读真实的数据表,这样子理论上会快很多,比如
select order_no,order_time from shop_order where shop_no=4当order_no,order_time,shop_no 这三列全为索引列时,你将看到一个和平时完全不同的执行计划。
3.6 位图索引 传说中当数据值较少,比如某些表示分类、状态的列,应该建位图索引而不是普通的二叉树索引,否则效率低下。不过看执行计划,这些位图索引鲜有被Oracle临幸的。
4.减少查询往返和查询的表 这也是很简单的大道理,程序与Oracle交互的成本极高,所以一个查询能完成的不要分开两次查,如果一个循环执行1万条查询的,怎么都快不到哪里去了。
4.1 封装PL/SQL存储过程 最高级的做法是把循环的操作封装到PL/SQL写的存储过程里,因为存储过程都在服务端执行,所以没有数据往返的消耗。
4.2 封装PL/SQL内部函数 有机会,将一些查询封装到函数里,而在普通SQL里使用这些函数,同样是很有效的优化。
4.3 Decode/Case 但存储过程也麻烦,所以有case/decode把几条条件基本相同的重复查询合并为一条的用法:
SELECT COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low, COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med, COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high FROM products;4.4 一种Where/Update语法
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)
UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY)FROM EMP_CATEGORIES) 5.其他优化 5.1RowID和ROWNUM 连Hibernate 新版也支持ROWID了,证明它非常有用。比如号称删除重复数据的最快写法:
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);6.终极秘技 - Hints 这是Oracle DBA的玩具,也是终极武器,比如Oracle在CBO,RBO中所做的选择总不合自己心水时,可以用它来强力调教一下Oracle,结果经常让人喜出望外。 如果开发人员没那么多时间来专门学习它,可以依靠Toad SQL opmitzer 来自动生成这些提示,然后对比一下各种提示的实际效果。不过随着10g智能的进化,hints的惊喜少了。
- 找出要优化的Top SQL 磨了这么久的枪,如果找不到敌人是件郁闷的事情。 幸亏10g这方面做得非常好。进入Web管理界面,就能看到当前或者任意一天的SQL列表,按性能排序。 有了它,SQL Trace和TKPROF都可以不用了。
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/calvinxiu/archive/2005/11/15/529756.aspx
找优秀程序员,就在博客园 IT新闻: · 婚恋网世纪佳缘乱象重生 成一夜情猎场 · 帝国时代 Online 已发布,可免费下载 · 小米发布会中的亮点与尿点:“狗日的 1999” · 315投诉网疑被关后改名重开张 新网站否认 · 团购网站融资冷却裁员求生 行业洗牌危机并存 博客园 博问 IT新闻 Java程序员招聘 标题 请输入标题 姓名 请输入你的姓名 主页 请输入验证码 验证码 /* 内容(请不要发表任何与政治相关的内容) 请输入评论内容 Remember Me? 登录 [使用Ctrl+Enter键可以直接提交] 推荐职位: · 广州ASP.NET程序员(广州丹霄信息技术) · 上海 .NET软件工程师(上海苏秦网络) · 上海.NET软件开发工程师(东方财富信息) · 北京 SQL数据库开发工程师(圣特尔科技) · 北京高新诚聘 ASP.NET 程序员(盈科融通软件) · 北京 .NET软件工程师(北京科胜永昌) · 北京.NET 研发工程师 (北京捷报数据) · 北京C/#开发工程师 B/S方向(圣特尔科技)
博客园首页随笔: · C/#设计模式——命令模式(Command Pattern) · 老系统维护 · 使用单例模式实现自己的HttpClient工具类 · Spread for Windows Forms高级主题(2)---理解单元格类型 · ERP/MIS开发 菜单设计器(Menu Designer)及其B/S,C/S双重实现(B/S开源) 知识库: · IT项目管理的六种错误思维 · 我的10个开发原则 · 大数据下的数据分析平台架构 · 为什么编程是独一无二的职业 · 分享8年开发经验,浅谈个人发展经历,明确自己发展方向 最简洁阅读版式: Oracle 10g SQL 优化再学习 网站导航:
博客园 IT新闻 知识库 博客生活 IT博客网 C++博客 博问 管理 相关文章:
- 转载一篇文章oracle的一些操作
- 过v$sqlarea,v$sql查询最占用资源的查询
- JVM内存模型以及垃圾回收
- 加速Javascript:DOM操作优化
- Tomcat内存溢出的三种情况及解决办法分析
- Tomcat启动分析server.xml
- Java虚拟机参数 -XX等相关参数应用
- Oracle 10g SQL 优化再学习
- google Analytics 初探
- 使用tomcat的compression来提高网页加载速度 Powered by: BlogJava Copyright © @joe
公告
留言簿(2)
随笔分类
- ")all 生活杂谈(14)
- ")android(18)
- ")apache项目(13)
- ")chart(1)
- ")concurrent(1)
- ")database(34)
- ")dwr(3)
- ")flex(1)
- ")hibernate(23)
- ")java (123)
- ")javafx(1)
- ")java安全(6)
- ")java性能(12)
- ")jbpm(1)
- ")jquery(3)
- ")linux(6)
- ")lucene(1)
- ")lucene(1)
- ")others(2)
- ")questions(7)
- ")questions_hander(5)
- ")spring(24)
- ")struts(8)
- swing
- ")UML(2)
- ")web(33)
- ")webservice(5)
- ")xml(2)
- ")敏捷(6)
- ")方法论(14)
- ")架构(10)
- ")网络通讯(5)
- ")项目管理(17)
相册
搜索
积分与排名
- 积分 - 249820
- 排名 - 69
最新评论
- 1. re: 一篇不错的讲解Java异常的文章(转载)----感觉很不错,读了以后很有启发[未登录]
- 受教了。非常感谢。 不过,后面罗列的那么多异常有点不必要了。
- --jake
- 2. re: strtuts2 异常之Could not create and/or set value back on to object [未登录]
- thank you very much!
- --fighting
- 3. re: Android屏幕元素层次结构[未登录]
顶一下
- --黑石
- 4. re: 不同技术团队的配合问题及DevOps(不错的文章,来自infoq)
- 做个记号。
- --何杨
- 5. re: other[未登录]
- 希望学习,谢谢! trust_myself@126.com
- --asa
阅读排行榜
- 1. 一篇不错的讲解Java异常的文章(转载)----感觉很不错,读了以后很有启发(17440)
- 2. 世界上最健康的作息时间表 大家对比下(16717)
- 3. ibatis学习(三)---ibatis与spring的整合(13592)
- 4. 如何用javascript控制checkbox,并进行批量删除(8527)
- 5. ibatis学习(二)--ibatis使用介绍(6368)
评论排行榜
- 1. other(41)
- 2. 一篇不错的讲解Java异常的文章(转载)----感觉很不错,读了以后很有启发(22)
- 3. ssh中利用pager-taglib和filter进行分页(14)
- 4. 团队内是有必要统一IDE(14)
- 5. 世界上最健康的作息时间表 大家对比下(13)