oraclesql性能优化优质PPT.ppt

1、,调优领域,调优领域：应用程序级调优：*SQL语句调优*管理变化调优2.实例级调优*内存*数据结构*实例配置3.操作系统交互*I/O*SWAP*Parameters本课程内容只讲解讨论应用程序级：Oracle SQL语句调优及管理变化调优,调整的方法,调整业务功能调整数据设计调整流程设计调整SQL语句调整物理结构调整内存分配调整I/O调整内存竞争调整操作系统,不同调整产生相应性能收益,调整的角色,SQL语句优化是提高性能的重要环节,开发人员不能只注重功能的实现，不管性能如何开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法必需遵守既定的开发规范未经过SQL语句优化

2、的模块不要上线,SQL语句优化的过程,定位有问题的语句检查执行计划检查执行过程中优化器的统计信息分析相关表的记录数、索引情况改写SQL语句、使用HINT、调整索引、表分析有些SQL语句不具备优化的可能，需要优化处理方式达到最佳执行计划,什么是好的SQL语句？,尽量简单，模块化易读、易维护节省资源内存CPU扫描的数据块要少少排序不造成死锁,SQL共享原理,ORACLE将执行过的SQL语句存放在内存的共享池（shared buffer pool）中，可以被所有的数据库用户共享。当你执行一个SQL语句（有时被称为一个游标）时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语

3、句以及最好的 执行路径.这个功能大大地提高了SQL的执行性能并节省了内存的使用。,SQL共享原理,为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA（systemglobal area）的共享池（shared buffer poo1）中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句（有时被称为一个游标）时，如果它和之前执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。可惜的是，Oracle只对简单的表提供高速缓冲（

4、cache bufferiIlg），这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle会首先在这块内存中查找相同的语句。,SQL共享的三个条件,当前被执行的语句和共享池中的语句必须完全相同（包括大小写、空格、换行等）两个语句所指的对象必须完全相同（同义词与表是不同的对象）两个SQL语句中必须使用相同的名字的绑定变量（bind variables）,共享SQL语句,注意：Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句

5、必须完全相同（包括空格、换行等）。能够使用共享的语句必须满足三个条件：字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。例如：SELECT*FROM ATABLE；和下面每一个SQL语句都不同：SELECT*from ATABLESelect*From Atable；语句所指对象必须完全相同 即两条SQL语句操作的数据库对象必须同一。语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量以相同的值：第一组 select pin，name from people where pin=：blk1.pin；se

6、lect pin，name from people where pin=：第二组 select pin，name from people where pin=：blk1.ot_jnd；blk1.ov_jnd；,SQL语句的处理过程,共享SQL区域,Sql 处理过程,SQL PARSE与共享SQL语句,当一个Oracle实例接收一条sql后1、Create a Cursor 创建游标2、Parse the Statement 分析语句3、Describe Results of a Query 描述查询的结果集4、Define Output of a Query 定义查询的输出数据5、Bind A

7、ny Variables 绑定变量6、Parallelize the Statement 并行执行语句7、Run the Statement 运行语句8、Fetch Rows of a Query 取查询出来的行9、Close the Cursor 关闭游标,为什么要bind variables?,字符级的比较:SELECT*FROM USER_FILES WHERE USER_NO=10001234;与SELECT*FROM USER_FILES WHERE USER_NO=10004321;检查：select name,executionsfrom v$db_object_cachewhe

8、re name like select*from user_files%,什么叫做重编译问题,什么叫做重编译？下面这个语句每执行一次就需要在SHARE POOL 硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库如果绑定变量，则只需要硬解析一次，重复调用即可select*from dConMsg where contract_no=32013484095139,绑定变量解决重编译问题,未使用绑定变量的语句sprintf（sqlstr,insert into scott.test1（num1,num2）values（%d,%d）,n_var1,n_var2）;EX

9、EC SQL EXECUTE IMMEDIATE:sqlstr;EXEC SQL COMMIT;使用绑定变量的语句 strcpy（sqlstr,insert into test（num1,num2）values（:v1,:v2）;EXEC SQL PREPARE sql_stmt FROM:EXEC SQL EXECUTE sql_stmt USING:n_var1,:n_var2;,绑定变量的注意事项,注意：1、不要使用数据库级的变量绑定参数cursor_sharing来强制绑定，无论其值为 force 还是similar2、有些带 的语句绑定变量后可能导致优化器无法正确使用索引,SQL语句

10、的四个处理阶段,SQL语句的处理过程,解析（PARSE）：,SQL语句的处理过程,在共享池中查找SQL语句检查语法检查语义和相关的权限合并（MERGE）视图定义和子查询确定执行计划,绑定（BIND）：,SQL语句的处理过程,在语句中查找绑定变量 赋值（或重新赋值）,执行（EXECUTE）：,SQL语句的处理过程,应用执行计划 执行必要的I/O和排序操作,提取（FETCH）：,从查询结果中返回记录 必要时进行排序 使用ARRAY FETCH机制,共享游标：好处,减少解析 动态内存调整 提高内存使用率,书写可共享的SQL,绑定变量和共享游标,ORACLE 优化器模式 概述,Oracle的优化器共有

11、3种模式：RULE（基于规则）、COST（基于成本）、CHOOSE（基于选择）。设置缺省的优化器的方法，是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。为了使用基于成本的优化器（CBO，CostBased Optimizer），必须经常运行analyze命令，以增加数据库中的对象统计信息（object statistics）的准确性。如果数据库的优化器模式设置为基于选择，那么实际的优化器模式将和是否运行过analyze命令有关。如果数据

12、表已经被analyze过，优化器模式将自动切换成CBO，反之，数据库将采用RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化器。为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。,访问数据表的方式,全表扫描全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块（database block）的方式优化全表扫描。通过ROWID访问表ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置（ROWID）之间的联系 通常索引提

13、供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。,数据库性能,影响数据库系统性能的要素：主机CPU,RAM,存储系统;OS参数配置,ORACLE参数配置;应用方面：数据库设计及SQL编程的质量一个性能优秀的应用系统需要：良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化,SQL Tunning 的重点,SQL:insert,update,delete,select;主要关注的是select关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置,SQL优化的一般性原则,目标：减少服务器资源消耗（主要是磁盘IO

14、）；设计方面：尽量依赖oracle的优化器，并为其提供条件；合适的索引，索引的双重效应，列的选择性；编码方面：利用索引，避免大表FULL TABLE SCAN；合理使用临时表；避免写过于复杂的sql，不一定非要一个sql解决问题；在不影响业务的前提下减小事务的粒度；,优化概括,课程Oracle数据库SQL语句优化的总体策略。以这些优化策略为指导，通过经验总结，我们可以不断地丰富优化方案，进而指导我们进行应用系统的数据库性能优化。以下枚举几则被证明行之有效的优化方案：创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失