oracle执行占资源语句和程序,Oracle执行计划SQL语句执行效率问题查找与解决方法...

《Oracle执行计划SQL语句执行效率问题查找与解决方法》由会员分享,可在线阅读,更多相关《Oracle执行计划SQL语句执行效率问题查找与解决方法(9页珍藏版)》请在人人文库网上搜索。

1、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:读取缓冲区的。

2、次数 用途1、帮忙找出性能较差的SQL语句2、帮忙找出最高频率的SQL 3、帮忙分析是否需要索引或改善联接3. 监控当前Oracle的session,如出现时钟的标志,表示此进程中的sql运行时间较长。4. Trace工具:a) 查看数据库服务的初始参数:timed_statistics、user_dump_dest和max_dump_file_sizeb) Step 1: alter session set sql_trace=truec) Step 2: run sql;d) Step 3: alter session set sql_trace=falsee) Step 4:使用 “TK。

3、PROF”转换跟踪文件f) Parse,解析数量大通常表明需要增加数据库服务器的共享池大小,query或current提取数量大表明如果没有索引,语句可能会运行得更有效,disk提取数量表明索引有可能改进性能,library cache中多于一次的错过表明需要一个更大的共享池大小二、 如何管理语句处理和选项 基于成本(Cost Based) 和基于规则(Rule Based) 两种优化器, 简称为CBO 和RBO Optimizer Mode参数值:Choose:如果存在访问过的任何表的统计数据 ,则使用基于成本的Optimizer,目标是获得最优的通过量。如果一些表没有统计数据,则使用估计值。

4、。如果没有可用的统计数据,则将使用基于规则的OptimizerAll_rows:总是使用基于成本的Optimizer,目标是获得最优的通过量First_rows_n:总是使用基于成本的Optimizer,目标是对返回前N行(“n”可以是1,10,100或者1000)获得最优的响应时间First_rows:用于向后兼容。使用成本与试探性方法的结合,以便快速传递前几行RULE:总是使用基于规则的Optimizer三、 使用数据库特性来获得有助于查看性能的处理统计信息(解释计划和AUTOTRACE)No1: Explain PlanA) 使用Explain工具需要创建Explain_plan表,这必。

5、须先进入相关应用表、视图和索引的所有者的帐户内. (D:oracleora92rdbmsadminutlxplan)B) 表结构:STATEMENT_ID:为一条指定的SQL语句确定特定的执行计划名称。如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID,那么此值会被设为NULL。 OPERATION:在计划的某一步骤执行的操作名称,例如:Filters,Index,Table,Marge Joins and Table等。 OPTION:对OPERATION操作的补充,例如:对一个表的操作,OPERATION可能是TABLE ACCESS,但OPTION可能为by RO。

6、WID或FULL。 Object_Owner:拥有此database Object的Schema名或Oracle帐户名。 Object_name:Database Object名 Object_type:类型,例如:表、视图、索引等等 ID:指明某一步骤在执行计划中的位置。 PARENT_ID:指明从某一操作中取得信息的前一个操作。通过对与ID和PARENT_ID使用Connect By操作,我们可以查询整个执行计划树。C) EXPLAIN搜索路径解释 全表扫描(Full Table Scans)(无可用索引,大量数据,小表 ,全表扫描hints,HWM(High Water Mark), R。

7、owid扫描) 索引扫描 索引唯一扫描(Index Unique Scans)索引范围扫描(Index Range Scans)索引降序范围扫描(Index Range Scans Descending)索引跳跃扫描(Index Skip Scans)全索引扫描(Full Scans)快速全索引扫描(Fast Full Index Scans)索引连接(Index Joins)位图连接(Bitmap Joins) 如何选择访问路径: CBO首先检查WHERE子句中的条件以及FROM子句,确定有哪些访问路径是可用的。然后CBO使用这个访问路径产生一组可能的执行计划,再通过索引、表的统计信息评估每。

8、个计划的成本,最后优化器选择成本最低的一个。 表的连接方式:Nested Loops会循环外表(驱动表),逐个比对和内表的连接是否符合条件。在驱动表比较小,内表比较大,而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候,Oracle也会选择使用NL。基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。(优点:嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。缺点:如果内部行源表(读取的第二张表(内表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中。

9、提取的记录)非常庞大时,其他的连接方法可能更加有效。)SORT- merge JOIN,将两表的连接列各自排序然后合并,只能用于连接列相等的情况,适合两表大小相若的情况(在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。但是,排列合并连接只能用于等价连接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno=E.deptno)。排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。)HASH JOIN在其中一表。

10、的连接列上作散列,因此只有另外一个表做排序合并,理论上比SORT JOIN会快些,需要有足够的内存,而且打开了SORT_JOIN_ENABLE参数。(当缺少有用的索引时,哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快,因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快,因为处理内存中的哈希表比检索B_树索引更加迅速。和排序合并连接、群集连接一样,哈希连接只能用于等价连接。和排序合并连接一样,哈希连接使用内存资源,并且当用于排序内存不足时,会增加临时表空间的I/O(这将使这种连接方法速度变得极慢)。最后,只有基于代价的优化器才可以使用哈希连接。) 索引连接:N。

11、o2: AUTOTRACE set autotrace 使用步骤:1、以system登录2、创建plustrace角色; sqlplusadminplustrce.sql3、向常规用户授予权限:grant plustrace to 4、如果没有plan_table也要创建: rdbmsadminutlxplan.sql set autotrace 选项on 显示查询结果,执行计划,统计数据on statistics 显示查询结果,统计数据,不显示执行计划on explain 显示查询结果,执行计划,不显示统计数据traceonly 显示执行计划和统计结果,但不包括查询结果traceonly s。

12、tatistics 仅显示统计数据recursive calls在用户级别和系统级别上生成的递归调用的数量。Oracle维护了一些用于内部处理的表。当oracle需要对这些表进行更改时,它就会在内部生成一个SQL语句,然后这个语句再生成一个递归调用。db block gets请求一个CURRENT块的次数consistent gets为一块请求consistent read的次数physical reads从磁盘读取得数据块总数。这个数量等于“直接物理读取”的值加上读入缓冲区的所有数据块redo size生成的重做的总数量(以字节为单位)bytes sent via SQL * Net to 。

13、client从前台进程发送给客户的总字节数bytes received via SQL * Net from client通过Oracle Net从客户接收的总字节数SQL*Net roundtrips to/from client发送给客户和从客户接收的Oracle Net消息的总数sorts (memory)完全在内存中执行并且不需要任何磁盘写入的排序操作的数量sorts (disk)至少需要一个磁盘写入的排序操作的数量rows processed在操作过程中处理的行数四、最后,使用计时特性来测量和比较处理时间Set timing onV$session_event应观注一下event这列。

14、,这是我们调优的关键一列,下面对常出现的event做以简要的说明: a、buffer busy waits,free buffer waits这两个参数所标识是dbwr是否够用的问题,与IO很大相关的,当v$session_wait中的free buffer wait的条目很小或没有的时侯,说明你的系统的dbwr进程决对够用,不用调整;free buffer wait的条目很多,你的系统感觉起来一定很慢,这时说明你的dbwr已经不够用了,它产生的wio已经成为你的数据库性能的瓶颈,这时的解决办法如下: a.1增加写进程,同时要调整db_block_lru_latches参数 示例:修改或添加如。

15、下两个参数 db_writer_processes=4 db_block_lru_latches=8 a、2开异步IO,IBM这方面简单得多,hp则麻烦一些,可以与Hp工程师联系。 b、db file sequential read,指的是顺序读,即全表扫描,这也是我们应该尽量减少的部分,解决方法就是使用索引、sql调优,同时可以增大db_file_multiblock_read_count这个参数。 c、db file scattered read,这个参数指的是通过索引来读取,同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。 d、latch f。

16、ree,与栓相关的了,需要专门调节。 e、其他参数可以不特别观注。本文的目的: 1、说一说Oracle的Optimizer及其相关的一些知识。 2、回答一下为什么有时一个表的某个字段明明有索引,当观察一些SQL的执行计划时,发现确不走索引的问题。 3、如果你对 FIRST_ROWS、 ALL_ROWS这两种模式有疑惑时也可以看一下这篇文章。 开始吧: Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时。

17、间是最少的。相信你一定会用Pl/sql Developer、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、First rows、All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了? 1、优化器的优化方式 Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。 A、RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当。

18、一个where子句中的一列有索引时去走索引。 B、CBO方式:依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。 我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全。

19、表扫描(full table scan)是最好的。 2、优化器的优化模式(Optermizer Mode) 优化模式包括Rule,Choose,First rows,All rows这四种方式,也就是我们以上所提及的。如下我解释一下: Rule:不用多说,即走基于规则的方式。 Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。 First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的。

20、方式返回查询的最先的几行,从总体上减少了响应时间。 All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。 3、如何设定选用哪种优化模式 a、Instance级别 我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。 。

21、B、Sessions级别 通过SQL ALTER SESSION SET OPTIMIZER_MODE=;来设定。 C、语句级别 这些需要用到Hint,比如: SQL SELECT /*+ RULE */ a.userid, 2 b.name, 3 b.depart_name 4 FROM tf_f_yhda a, 5 tf_f_depart b 6 WHERE a.userid=b.userid; 4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢?如何解决呢 ? A、不走索引大体有以下几个原因 你在Instance级别所用的是all_rows的方式 你的表的统计信。

22、息(最可能的原因) 你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。 B、解决方法 可以修改init.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint. 删除统计信息 SQLanalyze table table_name delete statistics; 表小不走索引是对的,不用调的。 5、其它相关 A、如何看一个表或索引是否是统计信息 SQLSELECT * FROM user_tables 2 WHERE table_name= 3 AND num_rows is not null; SQLSEL。

23、ECT * FROM user_indexes 2 WHERE table_name= 3 AND num_rows is not null; b、如果我们先用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。 SQL ANALYZE TABLE table_name COMPUTE STATISTICS; SQL ANALYZE INDEX index_name ESTIMATE STATISTICS; 具体的ANALYZE语句请参照Oracle8i/9i 的refrence文档。. 确保最优的索引使用 :对于改善查询的速度,这是特别重要的。有时 Oracle 可。

24、以选择多个索引来进行查询,调优专家必须检查每个索引并且确保 Oracle 使用正确的索引。它还包括 bitmap 和基于函数的索引的使用。 . 确保最优的 JOIN 操作:有些查询使用 NESTED LOOP join 快一些,有些则是 HASH join 快一些,另外一些则是 sort-merge join 更快。 这些规则看来简单,不过它们占 SQL 调优任务的 90% ,并且它们也无需完全懂得 Oracle SQL 的内部运作。以下我们来简单概览以下 Oracle SQL 的优化。 我们首先简要查看 Oracle 的排序,并且看一看排序操作是如何影响性能的。 调整 Oracle 的排序操。

25、作 排序是 SQL 语法中一个小的方面,但很重要,在 Oracle 的调整中,它常常被忽略。当使用 create index 、 ORDER BY 或者 GROUP BY 的语句时, Oracle 数据库将会自动执行排序的操作。通常,在以下的情况下 Oracle 会进行排序的操作: 使用 Order by 的 SQL 语句 使用 Group by 的 SQL 语句 在创建索引的时候 进行 table join 时,由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT 当与 Oracle 建立起一个 session 时,在内存中就会为该 session 分配一个私有的排序区域。如果。

26、该连接是一个专用的连接 (dedicated connection) ,那么就会根据 init.ora 中 sort_area_size 参数的大小在内存中分配一个 Program Global Area (PGA) 。如果连接是通过多线程服务器建立的,那么排序的空间就在 large_pool 中分配。不幸的是,对于所有的 session ,用做排序的内存量都必须是一样的,我们不能为需要更大排序的操作分配额外的排序区域。因此,设计者必须作出一个平衡,在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序( disk sorts )的同时,对于那些并不需要进行很大排序的任务,就会出现一些浪费。。

27、当然,当排序的空间需求超出了 sort_area_size 的大小时,这时将会在 TEMP 表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢 14,000 倍。 上面我们已经提到,私有排序区域的大小是有 init.ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init.ora 中的 sort_area_retained_size 参数决定。当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在 Oracle 实例中的临时表空间中进行。 磁盘排序的开销是很大的,有几个方面的原因。首先,和内存排序相比较,它们特别慢;而且磁盘排序会消耗临时表空间中的资源。。

28、 Oracle 还必须分配缓冲池块来保持临时表空间中的块。无论什么时候,内存排序都比磁盘排序好,磁盘排序将会令任务变慢,并且会影响 Oracle 实例的当前任务的执行。还有,过多的磁盘排序将会令 free buffer waits 的值变高,从而令其它任务的数据块由缓冲中移走。 接着,让我们看一下 Oracle 的竞争,并且看一下表的存储参数的设置是如何影响 SQL UPDATE 和 INSERT 语句的性能的。 调整 Oracle 的竞争 Oracle 的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle 负责处理表和索引的空间管理,这样就可以让我们无需懂得 Oracle 的表和。

29、索引的内部运作。不过,对于有经验的 Oracle 调优专家来说,他需要懂得 Oracle 是如何管理表的 extent 和空闲的数据块。对于调整拥有高的 insert 或者 update 的系统来说,这是非常重要的。 要精通对象的调整,你需要懂得 freelists 和 freelist 组的行为,它们和 pctfree 及 pctused 参数的值有关。这些知识对于企业资源计划( ERP )的应用是特别重要的,因为在这些应用中,不正确的表设置通常是 DML 语句执行慢的原因。 对于初学者来说,最常见的错误是认为默认的 Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题,否。

30、则在设置表的 pctfree 和 pctused 参数时,就必须考虑平均的行长和数据库的块大小,这样空的块才会被有效地放到 freelists 中。当这些设置不正确时,那些得到的 freelists 也是 dead 块,因为它们没有足够的空间来存储一行,这样将会导致明显的处理延迟。 Freelists 对于有效地重新使用 Oracle 表空间中的空间是很重要的,它和 pctfree 及 pctused 这两个存储参数的设置直接相关。通过将 pctused 设置为一个高的值,这时数据库就会尽快地重新使用块。不过,高性能和有效地重新使用表的块是对立的。在调整 Oracle 的表格和索引时,需要认真。

31、考虑究竟需要高性能还是有效的空间重用,并且据此来设置表的参数。以下我们来看一下这些 freelists 是如何影响 Oracle 的性能的。 当有一个请求需要插入一行到表格中时, Oracle 就会到 freelist 中寻找一个有足够的空间来容纳一行的块。你也许知道, freelist 串是放在表格或者索引的第一个块中,这个块也被称为段头( segment header )。 pctfree 和 pctused 参数的唯一目的就是为了控制块如何在 freelists 中进出。虽然 freelist link 和 unlink 是简单的 Oracle 功能,不过设置 freelist link。

32、 (pctused) 和 unlink (pctfree) 对 Oracle 的性能确实有影响。 由 DBA 的基本知识知道, pctfree 参数是控制 freelist un-links 的(即将块由 freelists 中移除)。设置 pctfree=10 意味着每个块都保留 10% 的空间用作行扩展。 pctused 参数是控制 freelist re-links 的。设置 pctused=40 意味着只有在块的使用低于 40% 时才会回到表格的 freelists 中。 许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实,一旦由于一个删除的操作而令块被重新加入到。

33、 freelist 中,它将会一直保留在 freelist 中即使空间的使用超过了 60% ,只有在到达 pctfree 时才会将数据块由 freelist 中移走。 表格和索引存储参数设置的要求总结 以下的一些规则是用来设置 freelists, freelist groups, pctfree 和 pctused 存储参数的。你也知道, pctused 和 pctfree 的值是可以很容易地通过 alter table 命令修改的,一个好的 DBA 应该知道如何设置这些参数的最佳值。 有效地使用空间和高性能之间是有矛盾的,而表格的存储参数就是控制这个方面的矛盾: . 对于需要有效地重新使用。

34、空间,可以设置一个高的 pctused 值,不过副作用是需要额外的 I/O 。一个高的 pctused 值意味着相对满的块都会放到 freelist 中。因此,这些块在再次满之前只可以接受几行记录,从而导致更多的 I/O 。 . 追求高性能的话,可以将 pctused 设置为一个低的值,这意味着 Oracle 不会将数据块放到 freelists 中直到它几乎是空的。那么块将可以在满之前接收更多的行,因此可以减少插入操作的 I/O 。要记住 Oracle 扩展新块的性能要比重新使用现有的块高。对于 Oracle 来说,扩展一个表比管理 freelists 消耗更少的资源。 让我们来回顾一下设置。

35、对象存储参数的一些常见规则: 经常将 pctused 设置为可以接收一条新行。对于不能接受一行的 free blocks 对于我们来说是没有用的。如果这样做,将会令 Oracle 的性能变慢,因为 Oracle 将在扩展表来得到一个空的块之前,企图读取 5 个 dead 的 free block 。 表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下, RAW 和 LONG RAW 列都很巨大,以至超过了 Oracle 的最大块的大小,这时 chained rows 是不可以避免的。 如果一个表有同时插入的 SQL 语句,。

36、那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个 freelist 中,而没有其它包含有任何空闲块的 freelists 出现。 freelist 参数应该设置为表格同时更新的最大值。例如,如果在任何时候,某个表最多有 20 个用户执行插入的操作,那么该表的参数应该设置为 freelists=20 。 应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的。对于这类 Oracle , freelist groups 应该设置为访问该表格的 Oracle Parallel Server 实例的数目。

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值