ORACLE将执行过的SQL语句存放在内存的共享池

sunage001

　　Oracle SQL性能优化深入浅出
　　ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,
　　ORACLE就能很快获得已经被解析的语句以及最好的执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用。
　　为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池(shared buffer poo1)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。
　　可惜的是，Oracle只对简单的表提供高速缓冲(cache bufferiIlg)，这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。当向Oracle提交一个SQL语句时，Oracle会首先在这块内存中查找相同的语句。
　　SQL共享的三个条件：
　　1，当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等）
　　2，两个语句所指的对象必须完全相同 （同义词与表是不同的对象）
　　3，两个SQL语句中必须使用相同的名字的绑定变量(bind variables)
　　Oracle对两者采取的是一种严格匹配策略，要达成共享。SQL语句必须完全相同(包括空格、换行等)。能够使用共享的语句必须满足三个条件：
　　① 字符级的比较。当前被执行的语句和共享池中的语句必须完全相同。
　　例如： SELECT * FROM ATABLE；和下面每一个SQL语句都不同：
　　SELECT *from ATABLE
　　Select * From Atable；
　　② 语句所指对象必须完全相同。即两条SQL语句操作的数据库对象必须同一。
　　③语句中必须使用相同命名的绑定变量。如：第一组的两个SQL语句是相同的，可以共享；而第二组中两个语句不同，即使在运行时赋予不同的绑定变量以相同的值：
　　● 第一组 select pin，name from people where pin = ：blk1.pin；
　　select pin，name from people where pin =：blk1.pin；
　　●第二组 select pin，name from people where pin =：blk1.ot_jnd；
　　select pin，name from people where pin = ：blk1.ov_jnd；

http://img.educity.cn/img_10/263/2014010505/21786054626.png

http://img.educity.cn/img_10/263/2014010505/21789054626.png

　　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 Any Variables 绑定变量
　　6、Parallelize the Statement 并行执行语句
　　7、Run the Statement 运行语句
　　8、Fetch Rows of a Query 取查询出来的行
　　9、Close the Cursor 关闭游标
　　下面这个语句每执行一次就需要在SHARE POOL 硬解析一次，一百万用户就是一百万次，消耗CPU和内存，如果业务量大，很可能导致宕库……
　　如果绑定变量，则只需要硬解析一次，重复调用即可
　　select * from dConMsg
　　where contract_no = 32013484095139
　　ORACLE 优化器模式：
　　Oracle的优化器共有3种模式：RULE (基于规则)、COST(基于成本)、CHOOSE(基于选择)。
　　设置缺省的优化器的方法，是在启动参数文件中针对OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语句级别或是会话级别对其进行覆盖。
　　为了使用基于成本的优化器(CBO，Cost—Based Optimizer)，必须经常运行analyze命令，以增加数据库中的对象统计信息(object statistics)的准确性。如果数据库的优化器模式设置为基于选择，那么实际的优化器模式将和是否运行过analyze命令有关。如果数据表已经被analyze过，优化器模式将自动切换成CBO，反之，数据库将采用RULE形式的优化器。在缺省情况下，Oracle采用CHOOSE优化器。为避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。
　　影响数据库系统性能的要素：
　　1，主机CPU,RAM,存储系统;
　　2，OS参数配置,ORACLE参数配置;
　　3，应用方面：数据库设计及SQL编程的质量
　　一个性能优秀的应用系统需要：
　　1，良好的硬件配置；
　　2，正确合理的数据库及中间件参数配置；
　　3，合理的数据库设计；
　　4，良好的sql编程；
　　5，运行期的性能优化
　　SQL Tunning 的重点：
　　SQL: insert, update, delete, select（主要关注的是select）
　　关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置
　　SQL优化的一般性原则：
　　1，目标：
　　减少服务器资源消耗（主要是磁盘IO）；
　　2，设计方面：
　　尽量依赖oracle的优化器，并为其提供条件；
　　合适的索引，索引的双重效应，列的选择性；
　　3，编码方面：
　　利用索引，避免大表FULL TABLE SCAN；
　　合理使用临时表；
　　避免写过于复杂的sql，不一定非要一个sql解决问题；
　　在不影响业务的前提下减小事务的粒度；
　　优化概括：
　　● 创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。
　　● 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。
　　● 查询尽量用确定的列名，少用*号。select count(key)from tab where key> 0性能优于select count(*)from tab；
　　当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间；
　　尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；多表查询的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从右到左，所以记录少的表放在右边。
　　● 尽量多用commit语句提交事务，可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：alter table．．．cache；
　　● 在Oracle中动态执行SQL，尽量用execute方式，不用dbms_sql包。