Oracle的SQL语句执行效率问题查找与解决方法

一、识别占用资源较多的语句的方法（4种方法）

1. 测试组和最终用户反馈的与反应缓慢有关的问题。

2. 利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）

? 数据列

EXECUTIONS：执行次数

DISK_READS：读盘次数

COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序单元）

OPTIMIZER_MODE：优化方式

SQL_TEXT：Sql语句

SHARABLE_MEM：占用shared pool的内存多少

BUFFER_GETS：读取缓冲区的次数

? 用途

1、帮忙找出性能较差的SQL语句

2、帮忙找出最高频率的SQL

3、帮忙分析是否需要索引或改善联接

3. 监控当前Oracle的session，如出现时钟的标志，表示此进程中的sql运行时间较长。

4. Trace工具：

a) 查看数据库服务的初始参数：timed_statistics、user_dump_dest和

max_dump_file_size

b) Step 1: alter session set sql_trace=true

c) Step 2: run sql；

d) Step 3: alter session set sql_trace=false

e) Step 4:使用 “TKPROF”转换跟踪文件

f) Parse，解析数量大通常表明需要增加数据库服务器的共享池大小，

query或current提取数量大表明如果没有索引，语句可能会运行得更有效，

disk提取数量表明索引有可能改进性能，

library cache中多于一次的错过表明需要一个更大的共享池大小

二、如何管理语句处理和选项

…… …… 余下全文

Oracle执行计划详解

目录

---

一．相关的概念

Rowid的概念

Recursive Sql概念

Predicate(谓词)

DRiving Table(驱动表)

Probed Table(被探查表)

组合索引(concatenated index)

可选择性(selectivity)

二．oracle访问数据的存取方法

1） 全表扫描（Full Table Scans， FTS）

2） 通过ROWID的表存取（Table Access by ROWID或rowid lookup）

3）索引扫描（Index Scan或index lookup）有4种类型的索引扫描：

（1） 索引唯一扫描（index unique scan）

（2） 索引范围扫描（index range scan）

在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况： （a） 在唯一索引列上使用了range操作符（> < <> >= <= between） （b） 在组合索引上，只使用部分列进行查询，导致查询出多行 （c） 对非唯一索引列上进行的任何查询。

（3） 索引全扫描（index full scan）

（4） 索引快速扫描（index fast full scan）

三、表之间的连接

1，排序 - - 合并连接（Sort Merge Join， SMJ）

2，嵌套循环（Nested Loops， NL）

3，哈希连接（Hash Join， HJ）

另外，笛卡儿乘积（Cartesian Product）

总结Oracle连接方法

Oracle执行计划总结概述

+++

一．相关的概念

Rowid的概念：rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。 对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行 修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。

…… …… 余下全文

执行计划：

首先在分析的用户下执行rdbms\admin\utlxplan.sql

用sys用户登录：sqlplus\admin\plustrace.sql

grant sqlplus to user_name;

1. 找出耗费资源比较多的语句

SELECT ADDRESS,

substr(SQL_TEXT,1,20) Text,

buffer_gets,

executions,

buffer_gets/executions AVG

FROM v$sqlarea

WHERE executions>0

AND buffer_gets > 100000

ORDER BY 5;

2. 如何分析执行计划：

SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’;

Query Plan

-----------------------------------------

SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)

TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]

TABLE ACCESS FULL large_table:在large_table上做全表扫描

[:Q65001] 表明该部分查询是以并行方式运行的。

[ANALYZED] 表明操作中引用的对象被分析过了，在数据字典中有该对象的统计信息可以供CBO使用。

3. 各个表之间是如何关联的

在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。

…… …… 余下全文

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 64 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| DAVE | 8 | 64 | 2 (0)| 00:00:01 |

--------------------------------------------------------------------------

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

609 bytes sent via SQL*Net to client

416 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

SQL>

1.2 使用SQL

SQL>EXPLAIN PLAN FOR sql语句;

SQL>SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 示例：

SQL>EXPLAIN PLAN FOR SELECT * FROM DAVE;

已解释。

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE')); 或者：

…… …… 余下全文

一、什么是执行计划

An explain plan is a representation of the access path that is taken when a query is executed within Oracle. 二、如何访问数据

At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:

Full Table Scan (FTS) --全表扫描

Index Lookup (unique & non-unique) --索引扫描（唯一和非唯一）

Rowid --物理行id 三、执行计划层次关系

When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. --采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行 1、看一个简单的例子：

Query Plan

-----------------------------------------

SELECT STATEMENT [CHOOSE] Cost=1234

**TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED]--[:Q65001]表示是并行方式，

…… …… 余下全文

Oracle的执行计划查看方法

Oracle在执行一个SQL之前，首先需要看一下SQL的执行计划，然后在按照执行计划执行SQL，分析执行计划的工作是由优化器来执行的，在不同的条件下，一个SQL可能存在多条执行计划，但是在某个特定的时间点，特定的环境下，一定只有一种执行计划是最优的。

1、 Oracle的优化方式

Oracle有两种优化方式：RBO(Rule-Based Optimization)和CBO(Cost-Based Optimization)

RBO: 优化器在分析SQL语句的时候所遵循的优化规则是基于Oracle内部预定的一些规则。

CBO: 优化器字分析SQL语句的时候主要是看语句执行的开销(Cost)，这里的代价主要指CPU和内存，优化器在判断是否使用这种方式的时候，主要参照的是表及索引的统计信息。统计信息给出表的大小，有多少行，每行的长度等信息。

2、 优化器的优化模式

优化模式包括：Rule, Choose, First Rows, All Rows四种模式。

Rule：走基于规则的模式。

Choose：默认情况下Oracle使用这种优化模式，当一个表或索引有统计信息的时候，走CBO的方式；如果表或索引没有统计信息，表又不是特别的小，且相应的字段有索引，则走RBO的方式。在缺省情况下，ORACLE采取Choose的优化模式，为了避免不必要的Full Table Scan，尽量避免采取此种优化模式，而直接采取CBO或者RBO的优化模式。

First Rows：它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

All Rows：也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

…… …… 余下全文

Oracle索引使用和执行计划

索引( Index )是常见的数据库对象，它的设置好坏、使用是否得当，极大地影响数据库应用程序和Database 的性能。

当你运用SQL语言，向数据库发布一条查询语句时，ORACLE将伴随产生一个“执行计划”，也就是该语句将通过何种数据搜索方案执行，是通过全表扫描、还是通过索引搜寻等其它方式。搜索方案的选用与ORACLE的优化器息息相关。

一、SQL语句的执行步骤和ORACLE的优化器 一条SQL语句的处理过程要经过以下几个步骤:

1 语法分析 分析语句的语法是否符合规范，衡量语句中各表达式的意义。 2 语义分析 检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。 3 视图转换 将涉及视图的查询语句转换为相应的对基表查询语句。

4 表达式转换 将复杂的SQL表达式转换为较简单的等效连接表达式。

5 选择优化器 不同的优化器一般产生不同的“执行计划”

6 选择连接方式 ORACLE有三种连接方式，对多表连接ORACLE可选择适当的连接方式。 7 选择连接顺序 对多表连接ORACLE选择哪一对表先连接，选择这两表中哪个表做为源数据表。 8 选择数据的搜索路径 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。

9 运行“执行计划”

ORACLE的优化器

ORACLE有两种优化器：基于规则的优化器（RBO， Rule Based Optimizer），和基于代价的优化器（CBO， Cost Based Optimizer）。

RBO自ORACLE 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感”，ORACLE公司已经不再发展这种技术了。

CBO自ORACLE 7版被引入，ORACLE自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，和并行查询等。CBO计算各种可能“执行计划”的“代价”，即cost，从中选用cost最低的方案，作为实际运行方案。各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布并不清楚，须要分析表和相关的索引，才能搜集到CBO所需的数据。

…… …… 余下全文