SHOUG卢巍分享：Oracle SQL性能优化实战指南

SQL
需积分: 25 2 下载量 172 浏览量 更新于2024-07-24 收藏 1.89MB PDF 举报
本篇文章是Oracle SQL性能优化的专题分享，由SHOUG.卢巍在上海Oracle用户组SHOUG（ShangHaiOracleUsersGroup）的活动上进行讲解。内容覆盖了广泛的SQL优化策略，旨在帮助用户提升Oracle数据库的性能和可扩展性。 首先，文章详细探讨了索引在SQL优化中的关键作用，包括如何选择和创建有效的索引，以及索引对查询速度的影响。接着，作者介绍了关联查询(join)的优化技巧，强调了在处理大规模数据集时合理设计联接操作的重要性。 针对并发性能，作者讲解了Oracle数据库的并发控制机制，并分享了如何通过优化锁定策略减少资源争用。Oracle优化器(optimizer)的介绍也占据了重要篇幅，阐述了如何理解并利用optimizer的功能来提高查询效率。 "Hint"（提示）作为辅助优化器的一种手段，被深入解析，包括何时使用和如何编写合适的hint来引导查询执行。此外，软解析(hardparse)和绑定变量(bound variables)的概念也被讨论，特别是它们在程序可扩展性和避免数据重复扫描方面的应用。 关于程序的可扩展性，文章涉及了进程间的争用，如insert操作可能导致的问题，以及短连接管理对于性能的影响。序列(sequence)上的竞争问题也得到了关注，以确保资源的有效利用。此外，如何通过WITH AS子句和避免数据的重复扫描来进一步优化查询性能。 在分布式环境中，优化策略着重于数据流设计和驱动节点的选择，以及如何利用分区表(partitioning)来分解大表的查询负担。数组技术的使用也被提及，以及在不同编程语言中如何设置数组以提高性能。 文章还介绍了常用的调优工具，如SET Autotrace和Execute Plan Table用于监控和调整SQL性能，而SQL Trace则提供更深入的执行计划分析。绑定变量的正确使用和检测未使用绑定变量的语句方法也是重要内容。 此外，自动化调优工具如SQL Tuning Advisor和SQL Profile的应用，以及如何稳定SQL执行计划，都在文中给出了实用的指导。这些策略不仅限于单个数据库实例，还包括了系统级别的优化考虑。 本文是一份全面的Oracle SQL性能优化指南，无论对初学者还是经验丰富的DBA来说，都能从中获取宝贵的知识和实践建议，以提升数据库性能和系统的整体稳定性。

SQL性能优化经验

151 浏览量
该文档基于Teradata数据库为案例，详细讲解一些sql语句优化原理和技巧。同样也适用于其它关系型数据库优化的参考

SQLServer性能优化篇

290 浏览量
资源名称：SQLServer性能优化篇内容简介：       本文档主要讲述的是SQLServer性能优化;在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。

SQL性能优化

1062 浏览量
1 性能优化 1.1 避免频繁 commit，尤其是把 commit 写在循环体中每次循环都进行commit。 1.2 使用绑定变量，避免常量的直接引用。 示例：以下书写不符合本规范。 INSERT INTO sm_users (user_id, user_name, created_by, creation_date) VALUES (1, 'Tang', -1, SYSDATE); 建议用如下方式操作： DECLARE v_user_id sm_users.user_id%TYPE; v_user_name sm_users_user_name%TYPE; v_created_by sm_users.created_by%TYPE; v_creation_date sm_users.creation_date%TYPE; BEGIN ... INSERT INTO sm_users (user_id, user_name, created_by, creation_date) VALUES (v_user_id, v_user_name, v_created_by, v_creation_date); END; 1.3 Operator 的使用规范  IN  比较容易写及清晰易懂  但效能是比较低的  ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。  NOT IN  此操作是强列推荐不使用的，因为不能应用表的索引。  推荐方案：用NOT EXISTS 或（Outer-Join+判断为空）方案代替 例如： SELECT deptno FROM dept WHERE deptno NOT IN(SELECT deptno FROM emp) 建议写成： SELECT deptno FROM dept, emp WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL  <>  永远不会用到索引的  推荐方案：用其它相同功能的操作运算代替，如: a<>0 改为 a>0 or a<0 a<>’’ 改为 a>’’  IS NULL 或IS NOT NULL  一般是不会应用索引的，因为B-tree索引是不索引空值的。  推荐方案：用其它相同功能的操作运算代替，如: a is not null 改为 a>0 或a>’’  不允许字段为空，而用一个default代替空值，如业扩申请中状态区位不允许为空， default为申请。  > 及 <  有索引就会采用索引查找  但有的情况下可以对它进行优化  如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。  LIKE  LIKE可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。性能肯定大大提高。  UNION  SQL在运行时先取出数个查询的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。  实际大部分应用中是不会产生重复的记录，推荐采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。  Exists 示例：当有 A、B 两个结果集，当结果集 B 很大时，A 较小时，适用 exists，如： SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE a.COLUMN = b.COLUMN); 当结果集 A 很大时，B 很小时，适用 in,如： SELECT * FROM a WHERE a.COLUMN IN(SELECT b.COLUMN FROM b) 1.4 SQL书写的影响  同一功能同一性能不同写法SQL的影响  Select * from zl_yhjbqk  Select * from dlyx.zl_yhjbqk（带表所有者的前缀）  Select * from DLYX.ZL_YHJBQK（大写表名）  Select * from DLYX.ZL_YHJBQK（中间多了空格）  以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。  WHERE后面的条件顺序影响  Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1  Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'  以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。  查询表顺序的影响  在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）  对条件字段的一些优化  采用函数处理的字段不能利用索引，如： substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’ trunc(sk_rq)=trunc(sysdate)， 优化处理： sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)  进行了显式或隐式的运算的字段不能进行索引，如： ss_df+20>50，优化处理：ss_df>30 ‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’ sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5 hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’ 注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型  条件内包括了多个本表的字段运算时不能进行索引，如： ys_df>cx_df，无法进行优化 qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’  HINT  是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示  目标方面的提示：  COST（按成本优化）  RULE（按规则优化）  CHOOSE（缺省）（ORACLE自动选择成本或规则进行优化）  ALL_ROWS（所有的行尽快返回）  FIRST_ROWS（第一行资料尽快返回）  执行方法的提示：  USE_NL（使用NESTED LOOPS方式联合）  USE_MERGE（使用MERGE JOIN方式联合）  USE_HASH（使用HASH JOIN方式联合）  索引提示：  INDEX（TABLE INDEX）（使用提示的表索引进行查询）  其它高级提示(如并行处理等等) 1.5 索引的规则： 建立索引常用的原则如下： 1. 表的主键、外键必须有索引 2. 数据量超过 1000 行的表应该有索引 3. 经常与其它表进行连接的表，在边接字段上应建立索引 4. 经常出现在 where 子句中的字段且过滤性极强的，特别是大表的字段，应该建立索引 5. 索引字段，尽量避免值为 null 6. 复合索引的建立需要仔细分析；尽量考虑用单字段索引代替：  正确选择复合索引中的第一个字段，一般是选择性较好的且在 where 子句中常的字段上。  复合索引的几个字段是否经常同时以and方式出现在where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引。  如果复合索引中包含的字段经常单独出现在 where 子句中，则分解为多个单字段索引。  如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段。  如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 7. 频繁 DDL 的表，不要建立太多的索引 8. 删除无用的索引，避免对执行计划造成负面影响 9. 让 SQL 语句用上合理的索引，合理让 SQL 语句使用索引的原则如下：  首先，看是否用上了索引，对于该使用索引而没有用上索引的 SQL 语句，应该想办法用上索引。  其次，看是否用上正确的索引了，特别复杂的 SQL 语句，当其中 where 子句包含多个带有索引的字段时，更应该注意索引的选择是否合理。错误的索引不仅不会带来性能的提高，相反往往导致性能的降低。  针对如何用上合理的索引，以 Oracle 数据中的例子进行说明：  任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。  避免不必要的类型转换，要了解“隐藏”的类型转换。  增加查询的范围，限制全范围的搜索。  索引选择性低，但资料分布差异很大时，仍然可以利用索引提高效率。  Oracle 优化器无法用上合理索引的情况下，利用 hint 强制指定索引。  使用复合索引且第一个索引字段没有出现在 where 中时，建议使用 hint 强制。 1.6 索引使用优化  建立Plan_Table CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE, REMARKS VARCHAR2(80), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_INSTANCE NUMBER(38), OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER(38), ID NUMBER(38), PARENT_ID NUMBER(38), POSITION NUMBER(38), OTHER LONG )  Syntax 说明： explain plan set statement_id = user_define for select ... 将结果显示 SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name, POSITION FROM plan_table START WITH ID = 0 AND STATEMENT_ID = user_define CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = user_define  示例 如要测试下面SQL： SELECT c.short, a.cday, a.card_no, a.qty FROM sales.stockiohis a, sales.product_info b, sales.vendor c WHERE a.card_no = b.card_no AND b.vendorid = c.vendorid AND a.produce_no = '2007090001' AND a.CATEGORY = '10' AND a.iotype = '1' 新增文件：例 d:\mydoc\plan.sql '0001'为user_define为使用者自定义编号 EXPLAIN PLAN SET STATEMENT_ID = '0001' FOR SELECT 'X' FROM sales.stockiohis a ,sales.product_info b ,sales.vendor c WHERE a.card_no = b.card_no AND b.vendorid = c.vendorid AND a.produce_no = '2007090001' AND a.CATEGORY = '10' AND a.iotype = '1' / SET arraysize 1 SET line 100 COLUMN op format a40 COLUMN object_name format a20 COLUMN options format a20 SELECT LPAD(' ', 2 *(LEVEL - 1)) || operation op, options, object_name, POSITION FROM plan_table START WITH ID = 0 AND STATEMENT_ID = '0001' CONNECT BY PRIOR ID = parent_id AND STATEMENT_ID = '0001' / DELETE FROM plan_table WHERE STATEMENT_ID = '0001' / COMMIT / 结果 1.7 避免不必要的排序 说明：对查询结果进行排序会大大的降低系统的性能，group与union都会对数据作排序，要耗费较多的内存，视状况用union all既可，不然有时数据太大又要进行union的排序，会导致Oracle数据库SORT_AREA_SIZE不足发生系统错误。 1.8 对于数字型的Primary Key，建议用序列 sequence 产生。 说明：除非是单据的单号，要求必须是唯一，并且依据流水号不可以跳号，不然在大量交易的表格中，不在乎跳耗时，要取得唯一的Primary Key 建议使用Oracle Sequence这样速度会较快，而且不会有锁定(Lock)的问题。

sqlserver性能优化专题PPT和sql脚本

225 浏览量
大话sqlserver性能优化专题PPT和sql脚本，该压缩包里一共包含43课的PPT和sql脚本示例代码，欢迎下载使用

SQL Server性能优化专题之五：负载均衡

918 浏览量
在SQL Server性能优化的过程中，负载均衡是一个至关重要的概念，尤其对于处理大型数据库的场景。负载均衡旨在有效地分配系统资源，确保服务器性能的稳定性和高可用性，避免单一节点过载，提高整体系统的响应时间和...

SQL Server性能优化专题之一：磁盘缓存.pdf.rar

173 浏览量
在SQL Server性能优化的过程中，磁盘缓存是关键的一环，因为数据库的读写操作大量依赖于磁盘I/O效率。本专题将深入探讨如何利用SQL Server的内存管理和磁盘缓存策略来提升数据库的性能。 首先，了解SQL Server的...

ORACLE_SQL性能优化

2013-06-08 上传
【ORACLE SQL性能优化】是针对Oracle数据库中的SQL查询进行性能提升的一个专题。课程内容涵盖了SQL语句执行的全过程、Oracle优化器的工作机制、表之间的关联处理、如何获取和分析SQL执行计划等关键知识点，旨在帮助...

SQL优化面试专题.pdf

139 浏览量
在SQL优化面试中，了解MySQL的技术特点、存储引擎、表类型、事务隔离级别以及查询优化策略至关重要。 1. MySQL技术特点： - 开源：MySQL是免费且开放源代码的，允许用户自由分发和修改。 - 多线程：支持多线程，...

SQL优化面试专题及答案.pdf

2023-01-17 上传
在SQL优化面试中，掌握各种数据库特性和操作是至关重要的。...这些知识点涵盖了数据库设计、性能优化、存储引擎、事务处理、数据类型和连接MySQL的基本操作，对于面试和实际工作中解决SQL相关问题非常有帮助。

oracle性能优化专题[归类].pdf

2021-10-11 上传
Oracle性能优化是一个复杂而关键的领域，涉及到数据库的多个层面，包括查询优化、索引设计、表连接策略等。在优化Oracle系统时，首先要明确优化目标，通常是为了提高系统的吞吐量，即处理更多的事务，同时减少响应...
Geronimo
  • 粉丝: 0
  • 资源: 1
上传资源 快速赚钱

最新资源