Oracle SQL性能优化深入浅出 1

ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,
ORACLE就能很快获得已经被解析的语句以及最好的执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用。

为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(systemglobal area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果它和之前执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行方案。Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用。
可惜的是，Oracle只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。数据库管理员必须在启动参数文件中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。当向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；






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

检查共享SQL语句：
select * from v$db_object_cache
where name like 'select * from user_files%'；

SQL语句的四个处理阶段：



解析(PARSE)：
1，在共享池中查找SQL语句
2，检查语法
3，检查语义和相关的权限
4，合并(MERGE)视图定义和子查询
5，确定执行计划

绑定(BIND)：
1，在语句中查找绑定变量
2，赋值(或重新赋值)

执行(EXECUTE)：
1，应用执行计划
2，执行必要的I/O和排序操作

提取(FETCH)：
1，从查询结果中返回记录
2，必要时进行排序
3，使用ARRAY FETCH机制

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优化器，而直接采用基于规则或者基于成本的优化器。

访问数据表的方式：
① 全表扫描
全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。

② 通过ROWID访问表
ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。

影响数据库系统性能的要素：
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包。