ORACLE执行计划和SQL调优

ORACLE ORACLE

执行计划和SQL 调优

内容安排

?第一部分：背景知识

第二部分

?第二部分：SQL调整

?第三部分：执行计划

?第四部分：常见问题与分析

第一部分背景知识

1：什么是性能优化？目的是什么？2：优化的主要依据和目标是什么?

优化的主据标什

3：优化中需要了解的几个重要的概念

什么是性能优化，目的是什么

?简而言之，就是在不影响系统运行正确性的前提下，使之运行地更快，完成特定功能所需的提下使之运行地更快完成特定功能所需的

时间更短

?影响性能变化的因素？

硬件版本程序变更运行环境的硬件，OS，DB版本，程序变更，运行环境的调整变更等。

?性能优化都优化什么？

?目的是什么?

性能优化是一项长期工作。

性能优化是项长期工作

优化的主要依据和方法

?依据

SQL的执行计划以及性能报告

?方法

充分利用索引

尽量的减少I/O

尽量的缩小数据（业务上支持）

几个重要的概念

Oracle的优化方式

?RBO特性

?总是使用索引（不识别位图索引或基于函数的索引）

?总是从驱动表开始

?只有在不可避免的情况下，才使用全表扫描

?索引选择的随机特性

?CBO 特性

?前提条件：存在表和索引的统计资料；使用analyze table 和analyze

index 命令从表或索引中收集统计资料（表的记录平均长度，记录数等）；

sql

如果没有现存的统计资料，将在q运行时收集资料，会大大降低性能；

?影响CBO执行计划成本评估的初始化参数

SQL 执行的步骤

?解析：安全性检查，语法检查；

?创建：评估多个执行计划，并选择一个最优的执行计划；

?执行：捆绑变量，执行已经创建的执行计划；

划

?获取：获取结果集，进行转换，排序等；

oracle访问数据的存取方法

?全表扫描（Full Table Scans, FTS）

?通过ROWID的表存取(Table Access by

ROWID )

ROWID)

?索引扫描（Index Scan）

1.索引唯一扫描(index unique scan)

2.索引范围扫描(index range scan)

3(index full scan)

3.索引全扫描(index full scan)

4.索引快速扫描(index fast full scan)

表访问方式

?全表扫描：读取表中每一条记录，顺序读取；

?散列获取：使用符号散列主键来为带有匹配散列值表中的记录创建ROWID ；

访过的的式?ROWID访问：通过指定的ROWID的方式

在表中选定一个单独的记录；是访问记在表中选定个单独的录；是访问

录的最快方式；

Recursive SQL概念

?Recursive SQL概念

?Driving Table(驱动表)

Probed Table(

?Probed Table(被探查表)

导致排序的操作

?Order by 子句

?Group by 子句

Select distinct

?Select distinct 子句

?Union 或minus 操作

?优化器调用sort merge join 操作?创建索引

SQL

第二部分SQL 调整

?调整的目标

?常用的优化方法

SQL 调整的目标

?尽量的减少全表扫描；

?尽量减少I/O

SQL优化的方法

?建立有效的索引

?针对RBO和CBO不同的调整原则?适当的调整表的连接方式和顺序?适当的使用Hint提示（尽量少）?合理的使用分析函数

?合理的使用合并语句

?使用数组减少I/O。

?定期分析表或索引

常见可能导致全表扫描的操作

?使用null条件的查询：where xxx is null;

?对没有索引的字段查询；

带有like条件的查询：where xxx like %x；

?where xxx like‘’

?带有not equals条件的查询：<> , !=, not in等

（除非字段分布不平衡，而且存在字段矩形图）（除非字段分布不平衡而且存在字段矩形图）?内置函数使索引无效：substr()，to_char()等

ll l提示

?使用parallel 提示；

调整索引

?常见的索引类型

1.普通索引：最常见到的索引，create index idx1 on table1 (f1);

2.复合索引：索引建立在多个字段上；create idx2 on table2 (f1,f3);

3.位图索引：使用位图的方式存储索引；create bitmap index idx3

3create bitmap index idx3 on table3(f1);

4.基于函数的索引：create idx4 on table4(upper(f2))；

?索引调整

索引无效

?内置函数使索引无效：…from table1 a where

substr(a.f1,2,4) …，f1上的索引无效；

substr(a f124)

?执行一个数学函数也会使索引无效：…from table1 a

,

where a.f1*3 > 1000, f1上的索引无效；

?不相等运算符有时也会使索引无效：…from table1 a

where a.f1<> 1000, 导致全表扫描，f1上的索引无效；

子句中使用复合数的非第字段将无法使用?Where 子句中使用复合函数的非第一字段将无法使用

索引；

where xxx like‘’

?带有like条件的查询：where xxx like %x；

?Null字符串与字段相连接使索引无效；where f1||’’=

‘…’…；

基于RBO 、CBO 的调整原则

?

驱动表的设置：在RBO 中，驱动表是from 子句的最后一个表；驱动表应该是返回记录最少的那个表；?

Where 子句设置：限制性最强的布尔表达式放在最底层达式放在最底层；?CBO 中，表和相关索引有足够的分析统中表关索引有够分析计数据。

调整表连接

?表连接方法

1.嵌套循环连接（nested loop join）：一个小的内部表和一

个外部表。比较内部表的每一条记录和外部表的每一条记录，

返回满足条件的记录；

2.散列连接（hash join）：为较小的表在RAM创建散列表（可

以用来从较大的那个表读取记录）；

3.排序合并连接（sort merge join）：使用连接字段将两个

记录集排序然后合并；

?表连接调整原则

ORACLE 执行计划介绍与测试

ORACLE 执行计划介绍与测试 (沈克勤) 2005-3-3

1.目的： 本文档的目的是通过介绍常用的HINT来了解ORACLE的优化器的工作原理及执行计划，以期望起到抛砖引玉的作用。在实际开发中有意识地控制SQL的执行计划，以达到SQL 执行性能的最优以及执行计划稳定。 为了减少枯燥的文档描述，使用了较多的图示。 2.如何查看执行计划 首先创建EXPLAIN_PLAN表 不同版本的ORACLE，该表结构可能会不同。请使用的ORACLE中 $ORACLE_HOME/rdbms/admin/utlxplan.sql去创建该表。 方法1：使用SQL*PLUS 的SET AUTOTRACE ： SQL>SET AUTOTRACE ON EXPLAIN 执行SQL，且仅显示执行计划 SQL>SET AUTOTRACE ON STATISTICS 执行SQL，且仅显示执行统计信息 SQL>SET AUTOTRACE ON 执行SQL，且显示执行计划与执行统计信息SQL>SET AUTOTRACE TRACEONLY 仅显示执行计划与统计信息，无执行结果SQL>SET AUTOTRACE OFF 关闭跟踪显示计划与统计

方法2：使用PL/SQL Developer工具

方法3：使用DBMS_XPLAN.DISPLAY() 方法4：直接查看表：EXPLAIN_TABLE SELECT lpad(' ',level-1)||operation||' '||options||' '|| object_name "Plan" FROM plan_table CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0AND statement_id = '&1' ORDER BY id; 3.如何控制与改变执行计划 我并没有见过单独介绍ORACLE SQL优化器原理方面的资料。但可以从ORACLE的HINT这个侧面来了解ORACLE的优化器的原理，从而最有效地书写SQL。

利用Oracle执行计划机制提高查询性能

利用Oracle执行计划机制提高查询性能消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制，你能够控制Oracle花费在评估连接顺序的时间数量，并且能在大体上提高查询性能。 准备执行SQL语句 当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤： 1) 语法检查：检查SQL语句拼写是否正确和词序。 2) 语义分析：核实所有的与数据字典不一致的表和列的名字。 3) 轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在。 4) 生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。 5) 建立二进制代码：基于执行计划，Oracle生成二进制执行代码。 一旦为执行准备好了SQL语句，以后的执行将很快发生，因为Oracle认可同一个SQL语句，并且重用那些语句的执行。然而，对于生成特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要了，并

且前一个执行计划通常不能够被重用。对那些连接了很多表的查询，Oracle需要花费大量的时间来检测连接这些表的适当顺序。 评估表的连接顺序 在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时，它必须考虑到表之间所有可能的连接。例如：六个表的之间连接有720（6的阶乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）种可能的连接线路。当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。对于15个表之间的连接，需要评估的可能查询排列将超过1万亿（准确的数字是1,307,674,368,000）种。 使用optimizer_search_limit参数来设定限制 通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的连接组合。 例如：有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合，因此如果optimizer_search_limit等于5（默认值），则优化器将评

oracle执行计划解释

oracle执行计划解释 一.相关概念 1·rowid，伪列：就是系统自己给加上的，每个表都有一个伪列，并不是物理存在。它不能被修改，删除，和添加，rowid在该行的生命周期是唯一的，如果向数据库插入一列，只会引起行的变化，但是rowid并不会变。 2·recursive sql概念：当用户执行一些SQL语句时，会自动执行一些额外的语句，我们把这些额外的SQL语句称为“recursive calls” 或者是“recursive sql statement”,当在执行一个DDL语句时，Oracle总会隐含的发出一些Recursiv sql语句，用于修改数据字典，如果数据字典没有在共享内存中，则就执行“resursive calls”，它会把数据字典从物理读取到共享内存。当然DML和select语句都可能引起recursive SQL。 3·row source 行源：在查询中，由上一操作返回的符合条件的数据集，它可能是整个表，也可能是部分，当然也可以对2个表进行连接操作（join）最后得到的数据集4·predicate:一个查询中的where限制条件 5·driving table 驱动表：该表又成为外层表，这个感念用于内嵌和HASH连接中，如果返回数据较大，会有负面影响，返回行数据较小的适合做驱动表 6·probed table 被探查表：该表又称为内层表，我们在外层表中取得一条数据，在该表中寻找符合连接的条件的行。 7·组合索引（concatenated index）由多个列组成的索引，在组合索引中有一个重要的概念，就是引导索引， create index idx_tab on tab(col1,col2,col3), indx_tab则称为组合索引， col1则称为引导列 在查询条件where后，必须使用引导索引，才会使用该组合索引 8.可选择性（selectivity）比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。 二.Oracle访问数据的存取方法 1.全表扫描(Full tabel scans,FTS) 为了实现全表扫描，Oracle读取数据库中的每一行，并检查每一行是否满足语句的where 限制条件一个多块读操作，可以使io能读取多块数据块。减少了IO次数，提高了系统的吞吐量。在多块读的方法的使用下，可以高效的实现数据库全表扫描，而且，中有在全表扫描的情况下，在可以使用多块读的方法。在这个种访问模式下，数据块只读一次。 【注意】 使用FTS的前提是，在较大的表中，不建议使用FTS,除非取出的数据较多，超过总量的5%-10%，或者使用并行查询时 2.通过rowid的表存取 行的ROWID指向了该行的数据文件，数据块，以及在数据块中的位置，使用rowid能快速的定位到要取得数据的行上，在Oracle中，这是取得单行最快的方式。 【注意】 该存取方法，不会用到多块读操作，一次IO只能读取一个数据块。 3.索引扫描(index scan 和index lookup) 索引扫描时通过index查找到对应行的rowid，然后通过rowid从数据库中得到具体的数据。该方法分为两个步骤，

sqlplus中查看执行计划分析

sqlplus中查看执行计划分析 对于oracle9i，需要手工设置plustrace角色，步骤如下： 1、在SQL>connect sys/密码as sysdba （密码为：数据库所在的那台服务器的密码） 在sys用户下运行$ORACLE_HOME/sqlplus/admin/plustrce.sql SQL>@$ORACLE_HOME/sqlplus/admin/plustrce.sql 这段sql的实际内容如下： set echo on drop role plustrace; create role plustrace; grant select on v_$sesstat to plustrace; grant select on v_$statname to plustrace; grant select on v_$mystat to plustrace; grant plustrace to dba with admin option; set echo off 以上产生plustrace角色 2、在sys用户下把此角色赋予一般用户 SQL> grant PLUSTRACE to 用户名; （用户名为：当前你登陆数据库的用户名，如：bbass） 3、然后在当前用户下运行$ORACLE_HOME/rdbms/admin/utlxplan.sql SQL>@$ORACLE_HOME/rdbms/admin/utlxplan.sql 它会创建一个plan_table，用来存储分析SQL语句的结果。 4、SQL> set timing on 可查看SQL语句执行的用时 SQL> set autotrace on; 可查看SQL执行计划分析。 关于Autotrace几个常用选项的说明: SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式

oracle执行计划学习文档

oracle执行计划学习文档 一、O racl e 执行SQL的步骤 1.1、SQL 语句的两种类型 DDL语句，不共享，每次执行硬解析； DML语句，会共享，硬解析或者软解析。 1.2、SQL执行步骤 1、语法检测。判断一条SQL语句的语法是否符合SQL的规范； 2、语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？用户是否有权限访问或更改相应的表或列？ 3、检查共享池中是否有相同的语句存在。假如执行的SQL语句已经在共享池中存在同样的副本，那么该SQL语句将会被软解析，也就是可以重用已解析过的语句的执行计划和优化方案，可以忽略语句解析过程中最耗费资源的步骤，这也是我们为什么一直强调避免硬解析的原因。这个步骤又可以分为两个步骤： （1）验证SQL语句是否完全一致。 （2）验证SQL语句执行环境是否相同。比如同样一条SQL语句，一个查询会话加了/*+ first_rows */的HINT，另外一个用户加/*+ all_rows */的HINT，他们就会产生不同的执行计划，尽管他们是查询同样的数据。 通过如上三个步骤检查以后，如果SQL语句是一致的，那么就会重用原有SQL语句的执行计划和优化方案，也就是我们通常所说的软解析。如果SQL语句没有找到同样的副本，那么就需要进行硬解析了。 4、Oracle根据提交的SQL语句再查询相应的数据对象是否有统计信息。如果有统计信息的话，那么CBO将会使用这些统计信息产生所有可能的执行计划（可能多达成千上万个）和相应的Cost，最终选择Cost最低的那个执行计划。如果查询的数据对象无统计信息，则按RBO的默认规则选择相应的执行计划。这个步骤也是解析中最耗费资源的，因此我们应该极力避免硬解析的产生。至此，解析的步骤已经全部完成，Oracle将会根据解析产生的执行计划执行SQL语句和提取相应的数据。

oracle-SQL语句执行原理和完整过程详解

SQL语句执行过程详解 一条sql，plsql的执行到底是怎样执行的呢？ 一、SQL语句执行原理： 第一步:客户端把语句发给服务器端执行 当我们在客户端执行select 语句时,客户端会把这条SQL 语句发送给服务器端,让服务器端的进程来处理这语句。也就是说,Oracle 客户端是不会做任何的操作,他的主要任务就是把客户端产生的一些SQL 语句发送给服务器端。虽然在客户端也有一个数据库进程,但是,这个进程的作用跟服务器上的进程作用事不相同的。服务器上的数据库进程才会对SQL 语句进行相关的处理。不过,有个问题需要说明,就是客户端的进程跟服务器的进程是一一对应的。也就是说,在客户端连接上服务器后,在客户端与服务器端都会形成一个进程,客户端上的我们叫做客户端进程;而服务器上的我们叫做服务器进程。 第二步:语句解析 当客户端把SQL 语句传送到服务器后,服务器进程会对该语句进行解析。同理,这个解析的工作, 其会做很多小动作。 也是在服务器端所进行的。虽然这只是一个解析的动作,但是,“” 1. 查询高速缓存(library cache)。服务器进程在接到客户端传送过来的SQL 语句时,不 会直接去数据库查询。而是会先在数据库的高速缓存中去查找,是否存在相同语句的执行计划。如果在数据高速缓存中,则服务器进程就会直接执行这个SQL 语句,省去后续的工作。所以,采用高速数据缓存的话,可以提高SQL 语句的查询效率。一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高,另一方面,也是因为这个语句解析的原因。 不过这里要注意一点,这个数据缓存跟有些客户端软件的数据缓存是两码事。有些客户端软件为了提高查询效率,会在应用软件的客户端设置数据缓存。由于这些数据缓存的存在,可以提高客户端应用软件的查询效率。但是,若其他人在服务器进行了相关的修改,由于应用软件数据缓存的存在,导致修改的数据不能及时反映到客户端上。从这也可以看出,应用软件的数据缓存跟数据库服务器的高速数据缓存不是一码事。 2. 语句合法性检查(data dict cache)。当在高速缓存中找不到对应的SQL 语句时,则服 务器进程就会开始检查这条语句的合法性。这里主要是对SQL 语句的语法进行检查,看看其是否合乎语法规则。如果服务器进程认为这条SQL 语句不符合语法规则的时候,就会把这个错误信息,反馈给客户端。在这个语法检查的过程中,不会对SQL 语句中所包含的表名、列名等等进行SQL 他只是语法上的检查。 3. 语言含义检查(data dict cache)。若SQL 语句符合语法上的定义的话,则服务器进程 接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。如果表名与列名不准确的话,则数据库会就会反馈错误信息给客户端。所以,有时候我们写select 语句的时候,若语法与表名或者列名同时写错的话,则系统是先提示说语法错误,等到语法完全正确后,再提示说列名或表名错误。 4. 获得对象解析锁(control structer)。当语法、语义都正确后,系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性,防止我们在查询的过程中,其他用户对这个对象的结构发生改变。 5. 数据访问权限的核对(data dict cache)。当语法、语义通过检查之后,客户端还不一定 能够取得数据。服务器进程还会检查,你所连接的用户是否有这个数据访问的权限。若你连接上服务器

TOAD中查看SQL的执行计划

TOAD中查看SQL的执行计划 一、TOAD中查看SQL的执行计划： 1、点击工具栏上120救护车图标按钮 2、快捷键Ctrl+E 3、菜单View-Explain plan 二、如果是默认安装TOAD，在查看执行计划时会报一个错： ORA-02404: 未找到指定的计划表 稍微研究了一下，解决这个问题基本上有3个方案： 1、最直接的解决方案：直接创建TOAD所需要的计划表，该脚本在%oracle_home%\rdbms\admin\utlxplan.sql 中，不过该脚本是创建PLAN_TABLE表，表结构一样，改名为TOAD_PLAN_TABLE 即可。如下： CREATE TABLE TOAD_PLAN_TABLE ( STATEMENT_ID VARCHAR2 (32), TIMESTAMP DATE, REMARKS VARCHAR2 (80), OPERATION VARCHAR2 (30), OPTIONS VARCHAR2 (30), OBJECT_NODE VARCHAR2 (128), OBJECT_OWNER VARCHAR2 (30), OBJECT_NAME VARCHAR2 (30), OBJECT_INSTANCE NUMBER, OBJECT_TYPE VARCHAR2 (30), SEARCH_COLUMNS NUMBER, ID NUMBER, COST NUMBER, PARENT_ID NUMBER, POSITION NUMBER, CARDINALITY NUMBER, OPTIMIZER VARCHAR2 (255), BYTES NUMBER, OTHER_TAG VARCHAR2 (255), OTHER LONG, PARTITION NUMBER, PARTITION_START VARCHAR2 (255), PARTITION_STOP VARCHAR2 (255), DISTRIBUTION VARCHAR2 (30) ) ; 2、偷机取巧的处理方案：修改TOAD参数 将菜单View -> Options ->Oracle -> General -> Explain Plan Table name中的参数修改为PLAN_TABLE即可使用。 3、通过TOAD自带功能创建表结构： 在菜单tools -> server side objects wizard下运行，不过要建立一些对象，最好建议一个单独的表空间放这些对象。 在10g中带有plan_table这张表，但是将名字改为了plan_table$ 只需$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色 grant plustrace to public

Oracle定时执行计划任务.

Oracle 定时执行计划任务 Oracle 在 10g 版本以前, 计划任务用的是 DBMS_JOB包, 10g 版本引入 DBMS_SCHEDULER来替代先前的 DBMS_JOB,在功能方面 , 它比 DBMS_JOB提供了更强大的功能和更灵活的机制管理, 但 DBMS_JOB包的使用相对比较简单, 也基本能够满足定时执行计划任务的需求, 故接下来就先看看 DBMS_JOB包的使用方法。 1. DBMS_JOB 我们可以在命令窗口输入 show parameter job_queue_processes查看数据库中定时任务的最多并发数,一般设置为 10(设置方法:alter system set job_queue_processes=10 ,如果设为 0,那么数据库定时作业是不会运行的。 oracle 定时执行 job queue 的后台进程是 SNP , 要启动 snp, 首先看系统模式是否支持 sql> alter system enable restricted session;或 sql> alter system disenable restricted session; 利用上面的命令更改系统的会话方式为 disenable restricted,为 snp 的启动创建条件 . 接下来我们尝试实现以下功能:每隔一分钟自动向 job_test表中插入当前的系统时间。 1、创测试表 create table job_test(updatetime date; 2、创建 JOB variable v_job_no number; begin dbms_job.submit(:v_job_no, 'insert into job_test values(sysdate;', sysdate, 'sysdate+1/1440';

执行计划详解

简介： 本文全面详细介绍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执行计划总结概述 +++ 一．相关的概念

Oracle定时执行计划任务

Oracle定时执行计划任务 Oracle在10g版本以前，计划任务用的是DBMS_JOB包，10g版本引入DBMS_SCHEDULER 来替代先前的DBMS_JOB,在功能方面,它比DBMS_JOB提供了更强大的功能和更灵活的机制管理，但DBMS_JOB包的使用相对比较简单，也基本能够满足定时执行计划任务的需求，故接下来就先看看DBMS_JOB包的使用方法。 1.DBMS_JOB 我们可以在命令窗口输入show parameter job_queue_processes查看数据库中定时任务的最多并发数，一般设置为10（设置方法：alter system set job_queue_processes=10），如果设为0，那么数据库定时作业是不会运行的。 oracle定时执行job queue 的后台进程是SNP,要启动snp,首先看系统模式是否支持sql> alter system enable restricted session;或sql> alter system disenable restricted session; 利用上面的命令更改系统的会话方式为disenable restricted,为snp的启动创建条件. 接下来我们尝试实现以下功能：每隔一分钟自动向job_test表中插入当前的系统时间。 1、创测试表 create table job_test(updatetime date); 2、创建JOB variable v_job_no number; begin dbms_job.submit(:v_job_no, 'insert into job_test values(sysdate);', sysdate, 'sysdate+1/1440'); end; / 其中最后一个参数'sysdate+1/1440'表示时间间隔为每分钟。其它常用的时间间隔的设置如下： （1）如果想每天凌晨1点执行，则此参数可设置为'trunc(sysdate)+25/24'； （2）如果想每周一凌晨1点执行，则此参数可设置为 'trunc(next_day(sysdate,1))+25/24'； （3）如果想每月1号凌晨1点执行，则此参数可设置为 'trunc(last_day(sysdate))+25/24'； （4）如果想每季度执行一次，则此参数可设置为 'trunc(add_months(sysdate,3),'Q')+1/24'；

半小时看懂Oracle的执行计划

一、什么是执行计划 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]表示是并行方式，[ANALYZED]表示 该对象已经分析过了 优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO： SELECT STATEMENT [CHOOSE] Cost=1234 --Cost有值，采用CBO SELECT STATEMENT [CHOOSE] Cost= --Cost为空，采用RBO

Oracle 执行计划中的几种关联说明

Oracle 执行计划中的几种关联说明 NESTED LOOP 对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是nested loops。 一般在nested loop中，驱动表满足条件结果集不大，被驱动表的连接字段要有索引，这样就走nstedloop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。 可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。 oradered 表示根据from 后面表的顺序，从左到右join，左表做驱动表，3个或3个以上最有用 oracle 并没有指出use_nl(a b) 中哪个是驱动表，所以有时我们习惯使用ordered 或者full() 或者index() 来强化我们的目标 " HASH JOIN hash join是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。 当小表可以全部放入内存中，其成本接近全表扫描两个表的成本之和。如果表很大不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。临时段中的分区都需要换进内存做hash join。这时候成本接近于全表扫描小表+分区数*全表扫描大表的代价和。至于两个表都进行分区，其好处是可以使用parallel query，就是多个进程同时对不同的分区进行join，然后再合并。但是复杂。 使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。 以下条件下hash join可能有优势： 两个巨大的表之间的连接。

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

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中多于一次的错过表明需要一个更大的共享池大小 二、如何管理语句处理和选项 ?基于成本（Cost Based）和基于规则（Rule Based）两种优化器，简称为CBO 和RBO ?Optimizer Mode参数值: Choose：如果存在访问过的任何表的统计数据，则使用基于成本的Optimizer,目标是获得最优的通过量。如果一些表没有统计数据，则使用估计值。如果没有可用的统计数据，则将使用基于规则的Optimizer All_rows：总是使用基于成本的Optimizer，目标是获得最优的通过量 First_rows_n：总是使用基于成本的Optimizer，目标是对返回前N行（“n”可以是1，10，100或者1000）获得最优的响应时间 First_rows：用于向后兼容。使用成本与试探性方法的结合，以便快速传递前几行 RULE：总是使用基于规则的Optimizer 三、使用数据库特性来获得有助于查看性能的处理统计信息（解释计划和AUTOTRACE） No1: Explain Plan A）使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引

Oracle事务的完整流程的分析(精)

Oracle 事务的完整流程的分析 oracle 客户端与服务端的连接 要想登录数据库并在数据库中真正做事情, 一定要先连接数据库, 根据连接的原理不同, 分为专用服务器连接(dedicated server 和共享服务器连接(shared server 。TCP/IP是网络上连接 Oracle 所用的主要网络协议 什么是专用服务器? 在登录 oracle 时, Oracle 总会为我创建一个新的进程。这通常称为专用服务器配置 , 因为这个服务器进程会在我的会话生存期中专门为我服务。对于每个会话,都会出现一个新的专用服务器,会话与专用服务器之间存在一对一的映射。按照定义,这个专用服务器不是实例的一部分。我的客户进程 (也就是想要连接数据库的程序会通过某种网络通道 (如 TCP/IP socket 与这个专用服务器直接通信,并由这个服务器进程接收和执行我的 SQL 。如果必要, 它会读取数据文件,并在数据库的缓存中查找我要的数据。也许它会完成我的更新语句,也可能会运行我的 PL/SQL代码。这个服务器进程的主要目标就是对我提交的 SQL 调用做出响应。 什么是共享服务器?

Oracle 还可以接受另一种方式的连接,这称为共享服务器(shared server ,正式的说法是多线程服务器(Multi-ThreadedServer 或 MTS 。如果采用这种方式,就不会对每条用户连接创建另外的线程或新的 UNIX 进程。在共享服务器中, Oracle 使用一个“共享进程”池为大量用户提供服务。共享服务器实际上就是一种连接池机制 (例如程序的连接池。利用共享服务器,我们不必为 10000个数据库会话创建 10000个专用服务器(这样进程或线程就太多了,难于管理 ,而只需建立很少的一部分进程 /线程,顾名思义,这些进程 /线程将由所有会话共享。这样 Oracle 就能让更多的用户与数据库建立连接,否则很难连接更多用户。如果让我的机器管理 10000个进程,这个负载肯定会把它压垮,但是管理 100个或者 1 000个进程还是可以的。采用共享服务器模式, 共享进程通常与数据库一同启动, 使用 ps 命令可以看到这个进程。 共享服务器连接和专用服务器连接之间有一个重大区别, 与数据库连接的客户进程不会与共享服务器直接通信,但专用服务器则不然,客户进程会与专用服务器直接通信。之所以不能与共享服务器直接对话,原因就在于这个服务器进程是共享的。为了共享这些进程,还需要另外一种机制,通过这种机制才能与共享服务器进程“对话” 。为此, Oracle 使用了一个或一组称为调度器(dispatcher ,也称分派器的进程。客户进程通过网络与一个调度器进程通信。这个调度器进程将客户的请求放入 SGA 中 UGA 中的请求队列(这也是 SGA 的用途之一。第一个空闲的共享服务器会得到这个请求, 并进行处理 (例如, 请求可能是 UPDATE T SET X =X+5 WHERE Y = 2 。完成这个命令后,共享服务器会把响应放在原调度器(即接收请求的调度器的响应队列中。调度器进程一直在监听这个队列,发现有结果后,就会把结果传给客户。

ORACLE执行计划

1，什么是执行计划 所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以 选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究 的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息 的，是由数据库来决定的。 我们先简单的看一个执行计划的对比： SQL> set autotrace traceonly 执行计划一： SQL> select count(*) from t; COUNT(*) ---------- 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE Access (FULL) OF 'T' 执行计划二： SQL> select count(*) from t; COUNT(*) 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 C ard=28180) 这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加;第二个表示根据表中索引，把 整个索引读进内存来逐条累加，而不用去读表中的数据。但是这两种方式到底哪种快呢?通常来说可能二比一快，但也不是绝对的。这是一 个很简单的例子演示执行计划的差异。对于复杂的SQL(表连接、嵌套子查询等)，执行计划可能几十种甚至上百种，但是到底那种最好呢?

ORACLE中的执行计划

Oracle 执行计划 1，什么是执行计划 所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以 选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究 的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息 的，是由数据库来决定的。 我们先简单的看一个执行计划的对比： SQL> set autotrace traceonly 执行计划一： SQL> select count(*) from t; COUNT(*) ---------- 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE Access (FULL) OF 'T' 执行计划二： SQL> select count(*) from t; COUNT(*) 24815 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 C ard=28180) 这两个执行计划中，第一个表示求和是通过进行全表扫描来做的，把整个表中数据读入内存来逐条累加;第二个表示根据表中索引，把 整个索引读进内存来逐条累加，而不用去读表中的数据。但是这两种方式到底哪种快呢?通常来说可能二比一快，但也不是绝对的。这是一

Oracle查看执行计划的几种方法

Oracle查看执行计划的几种方法 原创作者：lhrbest时间：2017-04-09 14:42:0123930 Oracle查看执行计划的几种方法 一般来说，有如下几种获取执行计划的方式： 1、AUTOTRACE方式 AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后，SQL*Plus会自动收集执行过的SQL语句的执行计划、性能统计数据等，并在语句执行结束后显示在SQL*Plus中。 DBA用户可以直接使用AUTOTRACE功能，但是如果用户没有DBA权限，那么需要在SYS用户下执行plustrce.sql脚本，自动创建PLUSTRACE角色，再把PLUSTRACE权限赋给普通用户即可。 $ORACLE_HOME/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO USER_LHR; 另外，若启用AUTOTRACE报“SP2-0611”的错误，则可以执行utlxplan.sql脚本来创建表PLAN_TABLE，如下所示： SQL> set autot on SP2-0613: 无法验证 PLAN_TABLE 格式或实体 SP2-0611: 启用EXPLAIN报告时出错 SQL> @?/rdbms/admin/utlxplan.sql

@?/rdbms/admin/utlxplan.sql CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; GRANT ALL ON PLAN_TABLE TO PUBLIC; @?/sqlplus/admin/plustrce.sql GRANT PLUSTRACE TO PUBLIC; AUTOTRACE的语法如下所示： SET AUTOTRACE {OFF|ON|TRACEONLY} [EXPLAIN] [STATISTICS] 其中，AUTOTRACE可简写为AUTOT，TRACEONLY可简写为TRACE，EXPLAIN可简写为EXP，STATISTICS可简写为STAT。SQL> SET AUTOT ON SQL> SELECT COUNT(*) FROM PLAN_TABLE; COUNT(*) ---------- 68 Execution Plan ---------------------------------------------------------- Plan hash value: 1751138260 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |

ORACLE性能AWR报告的使用和分析报告

ORACLE性能诊断AWR报告的使用和分析 为满足业务的运行要求，高性能要目前IT系统普遍面临的最棘手问题，尤其是客户面对着目前越来越庞大系统和数据，系统整合、数据大集中似乎成了趋势。针对系统性能优化的诊断和分析，数据库方向又是其中的重要一环，本文将针对ORACLE中常用的性能诊断工具AWR报告，进行分析说明。 一、ORACLE性能诊断工具 ORACLE数据库的性能的诊断工具有很多种，在9i之前主要通过手工进行采集分析，例如使用动态视图和Statspack报告来获取数据库性能状态信息，10g以后ORACLE数据库的性能诊断和改进建议越来越自动化，不过能够熟悉并掌握ORACLE的相关性能诊断工具的使用，仍对性能问题的准确和有效处理提供有利的帮助。以下是ORACLE中常用的一些分析工具。 ●动态性能视图 动态性能视图是ORACLE中最常用，也是最简单的一种工具。无论何种性能问题，都能在动态性能视图中找到线索，不过仅10g中动态性能视图就高达几百个，每个视图都包括很多诊断信息，想在众多的视图中找到问题的根源，也是一件费力的事情。一般常用的动态性能视图有：v$session、v$session_wait、v$process、v$sql、v$lock、v$latch、v$sysstat、v$system_event、v$sgastat。 ●Statspack报告 statspack 是Oracle 9i 之前使用的一个数据库收集工具，收集了数据库全面信息，包括负载概览、前五个等待事件、高速缓存的大小、共享池中SQL语句、表空间和文件I/O、库高速缓存、SGA统计等。 ●AWR和ADDM报告 AWR是10g以后提供的一个新工具，Oracle 建议用户用这个取代Statspack，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题，并自动生成ADDM （自动数据库诊断监控）报告，为用户提供数据库性能诊断分析建议。 ●SQL执行计划和建议 数据库中SQL的执行效率可能是对系统影响最大的一个因素，利用ORACLE执行计划的分析，可以准确知道SQL执行的代价，并提供多个方面的调整建议，来进行SQL代码的优化分析。