ORACLE执行计划和SQL调优

索引的物理表现形式
其实就是一张物理表 ROWID，索引列 因为如上原因尽量索引表空间与数据表
空间分开存放,减少资源竞争
索引优点
可以大大加快数据的检索速度，这也是创建索 引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每 一行数据的唯一性。
可以加速表和表之间的连接，特别是在实现数 据的参考完整性方面特别有意义
变量绑定必须满足的条件
字符级的比较
两个SQL语句中必须使用相同的名字的绑定变量
两个语句所指的对象必须完全相同
用户 对象名
Jack sal_limit Work_city Plant_detail
Jill sal_limit Work_city Plant_detail
如何访问
SQL
private synonym
2 优化数据设计
7 优化内存分配
3 优化应用程序设计 8优化I/O 和物理结构
4 优化数据库逻辑设计 9 优化资源争用
5 优化数据库操作 10优化所采用的平台
* 越靠前越重要
第一部分 背景知识
SQL处理流程 优化器 缓冲池 执行计划 变量绑定 索引 表连接
DBMS_STATUS 从10g以后可以自动分析表, 有个系统job(GATHER_STATS_JOB )。
SQL优化器处理体系结构
解析程序 优化程序 行源产生程序 SQL执行
SQL优化器处理流程图-1
SQL优化器处理流程图-2
SQL优化器处理流程图-3
缓冲池
PGA Program Global Area是为每个连接 到Oracle database的用户进程保留的内 存
在使用分组和排序子句进行数据检索时，同样 可以显著减少查询中分组和排序的时间
块缓冲区高速缓存(Database Buffer Cache) 重做日志缓冲区(Redo log buffer) Java程序缓冲区(Java Pool) 大池(Large Pool)
执行计划
概念
就是对一个查询任务，做出一份怎样去完成任务的详 细方案.
工具
变量绑定
为什么DBA要求我们变量绑定 预编译概念 怎样处理(?,? Procedure等)
总是从驱动表开始 只有在不可避免的情况下，才使用全表扫
描 索引选择的随机特性
*从ORACLE 10G开始，开始废弃RBO优化器。这句话并不是指在ORACLE 10G中不 能使用RBO，而是从ORACLE 10G开始开始，不再为RBO的BUG提供修补服务
CBO 特性
前提条件：存在表和索引的统计资料；使用 analyze table 和 analyze index 命令从表或索 引中收集统计资料（表的记录平均长度，记录 数等）；如果没有现存的统计资料，将在sql运 行时收集资料，会大大降低性能；
preparedstatement是预编译 preparedstatement支持批处理
SQL注入，安全,强制类型转换 不同数据库不同
IBATIS预编译证据
package com.ibatis.sqlmap.engine.execution;-->SqlExecutor public int executeUpdate(RequestScope request, Connection conn, String sql, Object[] parameters) throws SQLException { ErrorContext errorContext = request.getErrorContext(); errorContext.setActivity("executing update"); errorContext.setObjectId(sql); PreparedStatement ps = null; setupResultObjectFactory(request); int rows = 0; try { errorContext.setMoreInfo("Check the SQL Statement (preparation failed)."); ps = prepareStatement(request.getSession(), conn, sql); setStatementTimeout(request.getStatement(), ps); errorContext.setMoreInfo("Check the parameters (set parameters failed)."); request.getParameterMap().setParameters(request, ps, parameters); errorContext.setMoreInfo("Check the statement (update failed)."); ps.execute(); rows = ps.getUpdateCount(); } finally { closeStatement(request.getSession(), ps); } return rows
ORACLE 调优
吴志忠 2009.09
面向对象
懂基本数据库常识 有ORACLE开发使用经验 乐于接受性能优化探讨
内容安排
第一部分：背景知识 第二部分：SQL调优 第三部分：工具介绍 第四部分 ROWID高级应用 第五部分 附录
调优方面
1 商业逻辑
6 优化访问路径
SGA System Global Area是Oracle Instance的基本组成部分，在实例启动时 分配; 系统全局域SGA主要由三部分构成： 共享池、数据缓冲区、日志缓冲区。
缓冲池-SGA
共享池(Shared pool)
SQL语句缓冲(Library Cache)也叫库缓冲区 数据字典缓冲区(Data Dictionary Cache)
ORACLE 的访问路径排序
通过rowid 单行访问 通过 cluster 连接的单行访问 通过 散列键或主键的单行访问 通过主键字的单行访问 cluster 连接 散列簇键 索引簇键 复合键 单列索引 在索引列上的有界搜索 在索引列上的无界搜索 排序（order by ）,合并（union）连接 索引列的最大(max)到最小(min) 通过索引列排序 全表扫描 了解SQL语句这些特性后，就应该少用order by 等之类的语句
影响CBO执行计划成本评估的初始化参数较多 （optimizer_search_limit,optimizer_max_per mutations,optimizer_index_caching,hash_are a_size,hash_join_enable,hash_multiblock_io_ count,star_transformation_enable,optimizer_i ndex_cost_adj等）
}
何时Oracle使用绑定变量性能更差
http://database.ctocio.com.cn/tips/408/ 7062908.shtml
索引
访问路径 ROWID
索引及优缺点 其它概念 索引分类 索引扫描方式 索引的创建
Oracle访问数据库的存取方式
全表扫描（Full Table Scans） 通过ROWID的表存取（Table Access by ROWID） 索引扫描（Index Scan）
SQL优化器(Optimizer)
概念：是一个为所有的sql语句创建执行 计划的工具。
目的：生成最快的，消耗资源最少的执 行计划。
两种优化器 a. RBO, Rule-Based Optimizer b. CBO, Cost-Based Optimizer
RBO特性
总是使用索引（不识别位图索引或基于函 数的索引）
public synonym public synonym
private synonym
select max(sal_cap) from sal_limit;
public synonym
table owner
select count(*0 from work_city
where sdesc like 'NEW%';
为什么使用ROWID
rowid对访问一个表中的给定的行提供了最快 的访问方法，通过ROWID可以直接定位到相应 的数据块上，然后将其读到内存。我们创建一 个索引时，该索引不但存储索引列的值，而且 也存储索引值所对应的行的ROWID，这样我们 通过索引快速找到相应行的ROWID后，通过该 ROWID，就可以迅速将数据查询出来。这也就 是我们使用索引查询时，速度比较快的原因
Rowid的概念
rowid是一个伪列，既然是伪列，那么这个列 就不是用户定义，而是系统自己给加上的。对 每个表都有一个rowid的伪列，但是表中并不 物理存储ROWID列的值。不过你可以像使用其 它列那样使用它，但是不能删除改列，也不能 对该列的值进行修改、插入。一旦一行数据插 入数据库，则rowid在该行的生命周期内是唯 一的，即即使该行产生行迁移，行的rowid也 不会改变。
SQL 执行的步骤
解析：安全性检查，语法检查； 创建：评估多个执行计划，并选择一个
最优的执行计划； 执行：捆绑变量，执行已经创建的执行
计划； 获取：获取结果集，进行转换，排序等；
SQL处理流程
· 第1步: Create a Cursor 创建游标 · 第2步: Parse the Statement 分析语句 · 第5步: Bind Any Variables 绑定变量 · 第7步: Run the Statement 运行语句 · 第9步: Close the Cursor 关闭游标 如果使用了并行功能，还会包含下面这个阶段： · 第6步: Parallelize the Statement 并行执行语句 如果是查询语句，则需要以下几个额外的步骤 · 第3步: Describe Results of a Query 描述查询的结果集 · 第4步: Define Output of a Query 定义查询的输出数据 · 第8步: Fetch Rows of a Query 取查询出来的行
能否共 享 不能
能
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id
不能
原因
每个用户都有一个private synonym - sal_limit , 它
们是不同的对象 两个用户访问相同的对象
publ源自文库c synonym work_city
用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象
不同.
Statement和preparedStatement
PreparedStatement 对象的开销比Statement大，对 于一次性操作并不会带来额外的好处
SQL优化器模式
实例级通过对init.ora中OPTIMIZER_MODE参数,2会话级 ALTER SESSION SET OPTIMIZER_MODE=;3语句别 hint
CHOOSE 不是优化器,决定用什么优化器的参数: 如果表有分 析资料,便用CBO,否则用RBO
在缺省情况下,ORACLE采用CHOOSE优化器模式
CHOOSE
RBO CBO
ALL_ROWS(DSS 系统) FIRST_ROWS_n (OLTP系统)
FIRST_ROWS
表分析
为了使用CBO,必须经常运行analyze 命令,以 增加数据库中的对象统计信息
多表连接时只要有一个表分析过，就用CBO 7i: ANALYZE和DBMS_UTILITY 从815开始有