627
----执行计划
oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。
分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条
SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。
--oracle优化器
-------------优化器的优化方式--------------------
1、基于规则的优化方式RBO
遵循oracle内部预定的一些规则,如当一个where子句中的一列有索引时去走索引。
2、基于代价的优化方式CBO
代价主要指cpu和内存,优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。
--------------------------------------------------
--------------优化器的优化模式--------------------
优化模式包括Rule,Choose,First rows,All rows这四种方式
1、Rule:基于规则的方式
2、Choose:默认,指的是当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,
表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。
3、First Row:与Choose类似,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询
的最先的几行,从总体上减少了响应时间。
4、All Rows:基于代价的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从
总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。
------------------------------------------------------
----------------设定优化模式---------------------
1、instance级别
在init<SID>.ora文件中设置
2、session级别
SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;
3、语句级别
Hint???
SQL> SELECT /*+ RULE */ a.userid, b.name, b.depart_name FROM tf_f_yhda a,
tf_f_depart b WHERE a.userid=b.userid;
----------------------------------------------------
----------------不走索引的原因及解决方案-------------
1、原因
♀在Instance级别所用的是all_rows的方式
♀的表的统计信息让Oracle 认为在CBO方式下不走索引更合理(最可能的原因)
♀的表很小,上文提到过的,Oracle的优化器认为不值得走索引。
2、解决方案
♀可以修改init<SID>.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或
Choose,重起数据库。
♀删除统计信息
SQL>analyze table table_name delete statistics;
♀表小不走索引是对的,不用调的。
------------------------------------------------------
------------------相关知识补充-----------------------
1、查看表或索引是否是统计信息
SQL>SELECT * FROM user_tables WHERE table_name=<table_name> AND
num_rows is not null;
SQL>SELECT * FROM user_indexes WHERE table_name=<table_name> AND
num_rows is not null;
备注:user_tables 是个视图,自动过滤Schema.
2、用CBO方式,要及时更新表和索引的统计信息,以免生成不切合实际的执行计划
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS;
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;
-----------------------------------------------------
--执行计划解读
1、Oracle用来运行一个语句的步骤就叫做执行计划(execution plan),执行计划包
含了语句所涉及的每个表的访问路径和连接顺序。
2、查看执行计划:在Sql window中,输入要执行的sql语句,然后按 F5 可以得到执行计划
3、解读顺序:按照从里到外,从上到下的次序解读分析的结果
-------------------Full Table Scans(全表扫描)----------------
Oracle 的I/O 是针对数据块的,通常一个数据块中存储着多条记录,被请
求的记录要么聚集在少数几个块中,要么分散在大量的数据块中。而oracle 对某个表
进行全表扫描时,究竟应该读哪些数据块是根据全表扫描范围的标记-HWM(High
Water Mark) 进行的。
全表扫描将读取HWM 之下的所有数据块,访问表中的所有行,每一行都要经
WHERE 子句判断是否满足检索条件。当Oracle 执行全表扫描时会按顺序读取每个块
且只读一次,因此如果能够一次读取多个数据块,可以提高扫描效率,初始化参数
DB_FILE_MULTIBLOCK_READ_COUNT 用来设置在一次I/O 中可以读取数据块的
最大数量。
当一个表被大量删除记录之后,HWM 下面的大量数据块是空的,此时若对此表进
行全表扫描,Oracle 仍然会读到HWM 位置,会对全表扫描的性能产生极坏的影响。
1、无可用索引
SELECT last_name, first_name FROM employees WHERE UPPER(last_name)='TOM'
create index index_name on employees(UPPER(last_name));
2、大量数据
如果优化器认为查询将访问表中绝大多数的数据块,此时就算索引可用,也会全表扫描
3、小表
如果一个表HWM 之下的数据块比DB_FILE_MULTIBLOCK_READ_COUNT
要少,只需要一次I/O 就能扫完,则使用全表扫描要比使用索引的成本低,此时会使
用全表扫描。
4、并行
如果在表一级设置了较高的并行度,如alter table table_name
parallel(degree 10),通常会使CBO 错误的选择全表扫描。通常不建议在表级的设
置并行。
5、全表扫描hints
-------------------------------------------------------------------
------------------TABLE ACCESS BY INDEX ROWID (ROWID 扫描)----------------
Rowid 就是一个记录在数据块中的位置,由于指定了记录在数据库中的精确位
置,因此rowid 是检索单条记录的最快方式。如果通过rowid 来访问表,Oracle 首
先需要获得被检索记录的rowid,Oracle 可以在WHERE 子句中得到rowid,但更多
的是通过扫描索引来获得,然后Oracle 基于rowid 来定位被检索的每条记录。
select * from employees where last_name='King'
rowid组成(采用64位编码)
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
64位编码
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
查询rowid的详细信息
select dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) num
from employees
where rownum < 5;
----------------------------------------------------------------------------
--------------------INDEX FULL SCAN(索引全扫描)------------------------------
全索引扫描就是对整个索引进行一次逐条扫描,只需要一次I/O。进行全索引扫描
时因为有些查询条件必须对整个索引进行一次逐条扫描。
----------------------------------------------------------------------------
----------------INDEX UNIQUE SCAN(索引唯一扫描)-----------------------------
这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定相等条件时,只有
单行记录被访问。
----------------------------------------------------------------------------
------------------INDEX RANGE SCAN(索引范围扫描)----------------------------
索引范围扫描通常发生在对一个索引字段指定范围条件时,有多行记录被访问。是
检索数据的常用方式,返回的数据返照索引字段升序排列,字段值相同的则按照rowid
升序排列。如果在语句中指定了order by字句,而且排序字段是索引字段时Oracle将
忽略order by子句。
---------------------------------------------------------------------------
INDEX XXX SCAN DESCENDING(索引降序范围扫描)
INDEX SKIP SCAN(索引跳跃式扫描)
INDEX FAST FULL SCAN (索引快速全扫描)
FILTER:In (Sql 子查询) , Exists (Sql 子查询)
-----------------------PARTITION RANGE ALL------------------------------
如果表是分区表,则对这个表查询的Sql语句的执行计划可能会出现PATITION
RANGE ALL .
------------------------------------------------------------------------
-----------------------NESTED LOOP(嵌套连接)---------------------------
从执行计划的角度上看,表与表连接方法共有三种,嵌套循环是其中的一种,是执
行计划中看到的最常见的一种连接。在嵌套循环中,内表被外表驱动,外表返回的每
一行都要在内表中检索找到与它匹配的行。
嵌套循环在小表驱动大表,并且返回结果小的情况下是最快的一种连接方式。对于
嵌套循环来说,整个查询返回的结果集不能太大(大于1万不适合),要把返回子集
较小表的作为外表(CBO默认外表是驱动表),而且在内表的连接字段上一定要有索
引。
----------------------------------------------------------------------
-----------------------HASH JOIN(散列连接)------------------------------
散列连接,又称哈希连接,是CBO做大数据集连接时的常用方式,优化器使用两个表中较
小的表(或数据源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列
表,找出与散列表匹配的行。
哈希表又称散列表,其定义是这样的:根据设定的哈希函数H(key)和所选中的处理
冲突的方法,将一组关键字映象到一个有限的、地址连续的地址集(区间)上,并以关键
字在地址集中的“象”作为相应记录在表中的存储位置,这种表被称为哈希表。
-------------------------------------------------------------------------
----------------------MERGE JOIN & SORT JOIN (排序合并)------------------
从执行计划的角度上看,表与表连接方法共有三种,排序合并是其中的一种。一般
我们称排序合并为 SORT MERGE,在执行计划中表现为两个表分别作Sort Join 然后
再在一起做个Merge Jion。
sort merge join的操作通常分三步:对连接的每个表做table access full;对table
access full的结果进行排序;进行merge join对排序结果进行合并。sort merge join性
能开销几乎都在前两步。一般是在没有索引的情况下,9i开始已经很少出现了,因为
其排序成本高,大多为hash join替代了。
归并排序是一种排序方法,把一组需要排序元素分成两组,先分别排序,然后再归
并。这种排序方法又称为二路归并排序。
----------------------------------------------------------------------------
发表评论
-
plsql一些补充
2012-07-05 14:33 817704 一些补充 1、存储过程的参数 in:输入参数, ... -
plsql包、触发器和大对象操作
2012-07-05 14:32 1837703 ------------------------- ... -
plsql存储过程、函数
2012-07-05 14:30 6089629 ------------------------- ... -
plsql高级操作
2012-07-04 17:31 1263627 ---group by 子句的增强 --roll ... -
plsql权限、数据连接和集合操作
2012-07-04 17:30 1350627 ---控制用户权限 Oracle的权限主要包括角色 ... -
plsql查询和数据对象
2012-07-04 17:01 3661626 ---多表查询 --Oracle ... -
plsql简单操作
2012-07-04 16:59 2897626 1、pl/sql --NULL表示不可用、未赋值、不 ... -
oracle与jdbc的结合
2012-06-13 22:17 1243import java.sql.Connectio ... -
oracle 触发器、视图、自定义类型和数组
2012-06-13 22:11 31561:视图(view) 视图的作用: A:控制数据访问 ... -
oracle 存储过程和函数、程序包
2012-06-13 22:07 15361:存储过程 什么是存 ... -
oracle 游标管理
2012-06-13 22:04 873游标: 游标:逐行处理查询结果,以编程的方式访问数据。 游 ... -
oracle pl/sql编程
2012-06-13 22:01 9721:什么是PL/SQL 编程式的SQL语句 2:PL/SQ ... -
oracle单表查询与多表查询
2012-06-11 19:21 6138一、单表查询 1 基本查询 查询格式: ... -
oracle DDL、DML与TCL
2012-06-11 19:07 1436一、DDL 1、建表 建表语 ... -
oracle基础
2012-06-10 12:40 9651 oracle数据库基础 Oracle数据库简介 ...
相关推荐
介绍:sql_trace命令会将执行的整个过程输出到一个trace文件,我通过阅读这个trace文件来了解这个sql在执行过程中Oracle究竟做了哪些事情
执行计划:一条查询语句在ORACLE中的执行过程或...2:在SQL*PLUS(PL/SQL的命令窗口和SQL窗口均可)下执行下面步骤3:在SQL*PLUS下(有些命令在PL/SQL下无效)执行如下命令: SETAUTOT[RACE]{ON|OFF|TRACE[ONLY]}[EXP[LAIN]]
如何使用oracle提供的SQL_TRACE来跟踪sql的执行情况?Sql性能非常差的时候,oracle提供了SQL_TRACE来跟踪sql的执行情况。注:分析sql的方式比较多,还有根据优化器、sql执行计划来分析。SQL_TRACE能够将sql执行的过程...
得到执行计划的方式有两种: 1、一种是在指令的前面打开一些开关...可以用快捷键:Ctrl+L 小写L 他会执行你的语句并显示执行计划,但是不会返回结果集 2、另一种方法是使用SQL Trace里的事件跟踪来跟踪语句的执行计划。
怎么进行autotrace进行查看执行计划
SQLTracker,非常好用的抓取sql语句的工具,可以再没有源码的情况下抓取出执行的SQL语句。
第3章 循规蹈矩——如何读懂SQL执行计划 34 3.1 执行计划分析概述 35 3.1.1 SQL执行计划是什么 35 3.1.2 统计信息用来做什么 36 3.1.3 数据库统计信息的收集 37 3.1.4 数据库的动态采样 37 3.1.5 获取执行...
第3章 循规蹈矩——如何读懂SQL执行计划 34 3.1 执行计划分析概述 35 3.1.1 SQL执行计划是什么 35 3.1.2 统计信息用来做什么 36 3.1.3 数据库统计信息的收集 37 3.1.4 数据库的动态采样 37 3.1.5 获取执行...
在企业管理器界面(已连接到相关数据库)依次打开:tool —> SQLServer Profiler —> New Trace(新建跟踪器)—> 链接到相应数据库服务器(connect)—> 运行跟踪(run)—> Clear Trace Window()清除跟踪窗口—>。...
Oracle中的trace跟踪不仅可以跟踪当前的运行情况,还可以对SQl语句的执行进行跟踪
SQL*PlusAUTOTRACE可以用来替代SQLTrace使用,AUTOTRACE的好处是不必设置跟踪文件的格式,并且它将自动为SQL语句显示执行计划。AUTOTRACE与执行计划的区别是AUTOTRACE分析和执行语句;而EXPLAINPLAN仅分析语句,而不...
4、SQL语句性能诊断、执行计划 5、用索引提高效率 6、全表扫描及索引扫描的实例比较 7、诊断有问题的SQL 8、使用sql_trace/10046事件进行数据库诊断 9、当前会话的所有SQL语句生成执行计划 10、如何干预执行计划 - -...
使用性能监视器、SQL Trace以及动态管理视图和函数建立性能基线 理解一般系统中发生瓶颈的地方。以及解决瓶颈的方法 识别常见性能问题以及对其快速处理的方法 实施修复甚至预防性能问题的T-SQL最佳实践 《SQL ...
使用性能监视器、SQL Trace以及动态管理视图和函数建立性能基线 理解一般系统中发生瓶颈的地方。以及解决瓶颈的方法 识别常见性能问题以及对其快速处理的方法 实施修复甚至预防性能问题的T-SQL最佳实践 《SQL ...
Oracle 8i PL SQL高级程序设计(PDF) ...6) 使用动态SQL语言在运行时创建并执行SQL和PL/SQL语句。 由于本书内容较新,篇幅较多,再加上译者的时间和水平有限,在翻译过程中难免有疏漏和错误,敬请读者给予批评指正。
orcale sqltrace sql语句追踪 session 基于dbms_system.set_sql_trace_in_session 通过sid追踪sql语句,用于web等后台sql语句执行追踪,调试 已经做成工具,操作简单,支持根据uid筛选sql语句
全面介绍了PL/SQL语言的包、触发器、动态SQL、外部例程、对象、集合等基本概念,而且还集中讨论了Oracle8...6) 使用动态SQL语言在运行时创建并执行SQL和PL/SQL语句。 7) 允许数据库管道与警告之间进行交互会话,等等。