《最新Orcle PL SQL程序开发与性能调优(共28张PPT课件).pptx》由会员分享，可在线阅读，更多相关《最新Orcle PL SQL程序开发与性能调优(共28张PPT课件).pptx（28页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、资源共享 欢迎(hunyng)交流 产品工程中心:吴志鹏 Email: QQ: 966846SQL语句语句(yj)性能优化性能优化第一页，共二十八页。什么什么(shn me)是是ROWID ROWNID是row在数据文件中的具体位置：64位编码(bin m)的数据，A-Z, a-z, 0-9, +, 和 / 。 如：SELECT ROWID, * FROM emp 第二页，共二十八页。什么什么(shn me)是索引是索引 索引是与表或视图关联的磁盘上结构，可以加快(ji kui)从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。 它与类似书的目录结构。Oracle 的“索引”
2、对象，与表关联的可选对象，提高SQL查询语句的速度。索引直接指向包含所查询值的行的位置，减少磁盘I/O。 与所索引的表是相互独立的物理结构第三页，共二十八页。创建(chungjin)索引：create index emp_nm_idx on emp(name); 索引索引(suyn)与表的与表的关系关系第四页，共二十八页。SQL语句语句(yj)性能调性能调优优用NOT EXISTS替换NOT IN 将更显著地提高效率，尽量用NOT EXISTS替代NOT IN。在子查询中，NOT IN子句(z j)将执行一个内部的排序和合并. 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表
3、执行了一个全表遍历). 为了避免使用NOT IN，我们可以把它改写成NOT EXISTS。例如，查询所有非正常状态客户下的用户： 用NOT IN： select * from serv_2 t1 where t1.cust_id not in (select t2.cust_id from cust_2 t2 where t2.state = 70A); 已选择126行。 已用时间: 00: 01: 15.12 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF SERV_2 3 1
4、TABLE ACCESS (FULL) OF CUST_2第五页，共二十八页。SQL语句语句(yj)性能调性能调优优用NOT EXISTS： select * from serv_2 t1 where not exists (select 1 from cust_2 t2 where t2.cust_id = t1.cust_id and t2.state = 70A); 已选择126行。 已用时间: 00: 00: 00.78 执行(zhxng)计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (FULL
5、) OF SERV_2 3 1 TABLE ACCESS (BY INDEX ROWID) OF CUST_2 4 3 INDEX (UNIQUE SCAN) OF SYS_C0017303 (UNIQUE)第六页，共二十八页。尽量避免在索引列上进行计算、避免在WHERE子条件对字段使用计算。 WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描(somio)。例如，cust_2表上有索引cust_id，现在要查询所有cust_id大于978000 - 1： 低效的写法： select * from cust_2 t where t.cust_id + 1 978000
6、; 已选择164行。 已用时间: 00: 00: 00.35 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF CUST_2第七页，共二十八页。高效的写法： select * from cust_2 twhere t.cust_id 978000 - 1;已选择164行。已用时间(shjin): 00: 00: 00.34执行计划- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF CUST_2 2 1 IND
7、EX (RANGE SCAN) OF SYS_C0017303 (UNIQUE)第八页，共二十八页。不管在什么情况下，总是使用(shyng)索引的第一个列（对于组合索引而言）。 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。 例如，表serv上建有组合索引“CUST_ID, SERV_TYPE, STATE”，现在要查询所有销户用户：没有使用组合索引： select * from serv t where t.state = F0X; 已选择1412行。 已用时间: 00: 00: 01.75 执行计划 - 0
8、SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF SERV第九页，共二十八页。使用组合索引： select * from serv t where t.cust_id 0 and t.state = F0X; 已选择1412行。 已用时间: 00: 00: 00.71 执行(zhxng)计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF SERV 2 1 INDEX (RANGE SCAN) OF IDX_STATE_C
9、USTID_SERVTYPE (NON-U NIQUE)第十页，共二十八页。在From子句中，选择有效的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM 子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序(pi x)及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表
10、中合适记录进行合并。 例如，对表cust_2（10000条记录）和serv（136540条记录），按字段cust_id进行相等连接：SQLSQL语句语句(yj)(yj)性能调优性能调优第十一页，共二十八页。 方法一： select * from cust_2 t1, serv t2 where t1.cust_id = t2.cust_id; 已选择(xunz)11976行。 已用时间: 00: 00: 04.09 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF SER
11、V 3 1 TABLE ACCESS (BY INDEX ROWID) OF CUST_2 4 3 INDEX (UNIQUE SCAN) OF SYS_C0017303 (UNIQUE)第十二页，共二十八页。 方法二： select * from serv t1, cust_2 t2 where t1.cust_id = t2.cust_id; 已选择(xunz)11976行。 已用时间: 00: 00: 03.20 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF SERV 2 1
12、 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF CUST_2 4 2 INDEX (RANGE SCAN) OF IDX_SERV_CUST_ID (NON-UNIQUE)第十三页，共二十八页。WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。例如，要查询所有(suyu)文圣区销户用户数： 方法一： select * from serv t where t.state = F0X and t.organ_code
13、 = LYWS; 已选择850行。 已用时间: 00: 00: 01.57 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF SERV第十四页，共二十八页。 方法二： select * from serv t where t.organ_code = LYWS and t.state = F0X; 已选择850行。 已用时间(shjin): 00: 00: 00.64 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF
14、 SERV第十五页，共二十八页。使用DECODE函数来减少(jinsho)处理时间。使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如，统计文圣区的数字电视用户数和宽带用户数： 方法一 select * from serv t where t.serv_type = 1 and t.organ_code = LYWS; 方法二，可以用DECODE函数高效地得到相同结果， select count(decode(t.serv_type, 1, 1, null), count(decode(t.serv_type, 2, 1, null) from serv t where t.
15、organ_code = LYWS; 类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。 第十六页，共二十八页。减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询。例如(lr)：低效的写法： select * from cust t1 where t1.cust_id = (select t2.cust_id from serv t2 where t2.serv_id = 969303) and t1.organ_code = (select t3.organ_code from serv t3 where t3.serv_id = 969303
16、); 已用时间: 00: 00: 00.34 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (BY INDEX ROWID) OF CUST 3 2 INDEX (UNIQUE SCAN) OF SYS_C0017300 (UNIQUE) 4 3 TABLE ACCESS (BY INDEX ROWID) OF SERV 5 4 INDEX (UNIQUE SCAN) OF SYS_C0017301 (UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF SERV
17、 7 6 INDEX (UNIQUE SCAN) OF SYS_C0017301 (UNIQUE)第十七页，共二十八页。 高效的写法(xif) select * from cust t1 where (t1.cust_id, t1.organ_code) = (select t2.cust_id, t2.organ_code from serv t2 where t2.serv_id = 969303); 已用时间: 00: 00: 00.32 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (BY
18、INDEX ROWID) OF CUST 3 2 INDEX (UNIQUE SCAN) OF SYS_C0017300 (UNIQUE) 4 3 TABLE ACCESS (BY INDEX ROWID) OF SERV 5 4 INDEX (UNIQUE SCAN) OF SYS_C0017301 (UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF SERV 7 6 INDEX (UNIQUE SCAN) OF SYS_C0017301 (UNIQUE)第十八页，共二十八页。用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况(qngk
19、ung)下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后 ,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML。第十九页，共二十八页。用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过
20、WHERE子句限制记录的数目,那就能减少这方面的开销.例如，要查询文圣区的所有用户数: 低效: select t1.organ_code, count(*) from serv t1 group by t1.organ_code having t1.organ_code = LYWS; 已用时间: 00: 00: 00.76 执行(zhxng)计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 TABLE ACCESS (FULL) OF SERV第二十页，共二十八页。高效(o xio) sel
21、ect t1.organ_code, count(*) from serv t1 where t1.organ_code = LYWS group by t1.organ_code; 已用时间: 00: 00: 00.57 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (FULL) OF SERV第二十一页，共二十八页。对于VARCHAR2类型，我们在内存使用和效率上需要(xyo)做出一个权衡。对于VARCHAR2(长度=2000)变量，PL/SQL动态分配内存来存放实际值，但对于
22、VARCHAR2(长度 (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);（6）用用TRUNCATE替代替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. 第二十四页，共二十八页。性能性能(xngnng)优
23、化、索引优化优化、索引优化（7） 适当的时候的使用COMMIT：只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息(xnx). b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 （8） 用Where子句替换HAVING子句：避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面
24、的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计
25、算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里第二十五页，共二十八页。如何如何(rh)设计超大数据量的表设计超大数据量的表设计原则： 1） 对经常查询到的字段做索引。 2） 避免对经常更新的字段做索引。
26、例 工作量累计表 （emp），有姓名（name），工作量（workCnt）这两个字段。 在这个(zh ge)表中，姓名是经常被查询且很少被修改，应该做索引。 工作量每天被修改，避免做索引。 3） 数据量超大时，建议做分拆为多个表，例每月一张表。 4） 数据量超大且不能被拆分时，将日期字段拆分为多个字段做索引。 例 全国身份证信息数据库，假设将所有人的数据放入到一张表中。这时，可以将出生年月日及办证年月日拆分为三个字段年、月、日，并做索引。这种索引比日期类型索引要快上很多。 第二十六页，共二十八页。谢谢谢谢(xi xie)！第二十七页，共二十八页。内容(nirng)总结资源共享 欢迎(hunyng)交流。1 0 FILTER。1 0 NESTED LOOPS。2 1 NESTED LOOPS。2 1 SORT (GROUP BY)。1 0 SORT (GROUP BY)第二十八页，共二十八页。