oracle执行计划结果分析,pl sql developer oracle如何分析执行计划-知识分享-234导航

oracle执行计划结果分析,pl sql developer oracle如何分析执行计划详细介绍

本文目录一览：如何看懂ORACLE执行计划

如何看懂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.一个简单的例子：SQL> select/*+parallel (e 4)*/*fromempe;Execution Plan----------------------------------------------------------0SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)10TABLE ACCESS* (FULL) OF ‘EMP‘ (Cost=1 Card=82 Bytes=7134):Q5000--[:Q5000]表示是并行方式1 PARALLEL_TO_SERIALSELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO",A1."ENAME",A1."JOB",A1."MGR",A1."HI优化模式是CHOOSE的情况下，看Cost参数是否有值来决定采用CBO还是RBO：SELECT STATEMENT [CHOOSE] Cost=1234--Cost有值，采用CBOSELECT STATEMENT [CHOOSE]--Cost为空，采用RBO(9I是如此显示的)2.层次的父子关系的例子：PARENT1**FIRST CHILD****FIRST GRANDCHILD**SECOND CHILDHere the same principles apply, the FIRST GRANDCHILD is the initial operation then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT collates the output.四、例子解说Execution Plan----------------------------------------------------------0 **SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)1 0 **HASH JOIN (Cost=3 Card=8 Bytes=248)2 1 ****TABLE ACCESS (FULL) OF ‘DEPT‘ (Cost=1 Card=3 Bytes=36)3 1 ****TABLE ACCESS (FULL) OF ‘EMP‘ (Cost=1 Card=16 Bytes=304)左侧的两排数据，前面的是序列号ID，后面的是对应的PID（父ID）。A shortened summary of this is:Execution starts with ID=0: SELECT STATEMENT but this is dependand on it‘s child objectsSo it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it‘s child objectsSo it executes its first child step: ID=2 PID=1 TABLE ACCESS (FULL) OF ‘DEPT‘Then the second child step: ID=3 PID=2 TABLE ACCESS (FULL) OF ‘EMP‘Rows are returned to the parent step(s) until finished五、表访问方式1.Full Table Scan (FTS)全表扫描In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk.--全表扫描模式下会读数据到表的高水位线（HWM即表示表曾经扩展的最后一个数据块），读取速度依赖于Oracle初始化参数db_block_multiblock_read_count(我觉得应该这样翻译:FTS扫描会使表使用上升到高水位(HWM),HWM标识了表最后写入数据的块,如果你用DELETE删除了所有的数据表仍然处于高水位(HWM),只有用TRUNCATE才能使表回归,FTS使用多IO从磁盘读取数据块).Query Plan------------------------------------SELECT STATEMENT [CHOOSE] Cost=1**INDEX UNIQUE SCAN EMP_I1--如果索引里就找到了所要的数据，就不会再去访问表2.Index Lookup索引扫描There are 5 methods of index lookup:index unique scan--索引唯一扫描Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.eg:SQL> explain plan for select empno,ename from emp where empno=10;index range scan--索引局部扫描Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .eg:SQL> explain plan for select mgr from emp where mgr = 5;index full scan--索引全局扫描Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.eg: SQL> explain plan for select empno,ename from big_emp order by empno,ename;index fast full scan--索引快速全局扫描，不带order by情况下常发生Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.eg: SQL> explain plan for select empno,ename from big_emp;index skip scan--索引跳跃扫描，where条件列是非索引的前导列情况下常发生Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.eg:SQL> create index i_emp on emp(empno, ename);SQL> select /*+ index_ss(emp i_emp)*/ job from emp where ename=‘SMITH‘;3.Rowid物理ID扫描This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in.--Rowid扫描是最快的访问数据方式六、表连接方式七、运算符1.sort--排序，很消耗资源There are a number of different operations that promote sorts:(1)order by clauses(2)group by(3)sort merge join_-这三个会产生排序运算2.filter--过滤，如not in、min函数等容易产生Has a number of different meanings, used to indicate partition elimination, may also indicate an actual filter step where one row source is filtering, another, functions such as min may introduce filter steps into query plans.3.view--视图，大都由内联视图产生(可能深入到视图基表)When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the ‘view‘ will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable.eg: SQL> explain plan forselect ename,totfrom emp,(select empno,sum(empno) tot from big_emp group by empno) tmpwhere emp.empno = tmp.empno;Query Plan------------------------SELECT STATEMENT [CHOOSE]**HASH JOIN**TABLE ACCESS FULL EMP [ANALYZED]**VIEW****SORT GROUP BY******INDEX FULL SCAN BE_IX4.partition view--分区视图Partition views are a legacy technology that were superceded by the partitioning option. This section of the article is provided as reference for such legacy systems.示例:假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.col1,a.col2)，注意a.col1列为索引的引导列。考虑下面的查询：selectA.col4fromA , B , CwhereB.col3 = 10andA.col1 = B.col1andA.col2 = C.col2andC.col3 = 5;Execution Plan------------------------------------0SELECT STATEMENT Optimizer=CHOOSE10MERGE JOIN21SORT (JOIN)32NESTED LOOPS43TABLE ACCESS (FULL) OF ‘B‘53TABLE ACCESS (BY INDEX ROWID) OF ‘A‘65INDEX (RANGE SCAN) OF ‘INX_COL12A‘ (NON-UNIQUE)71SORT (JOIN)87TABLE ACCESS (FULL) OF ‘C‘Statistics(统计信息参数,参见另外个转载的文章)--------------------------------------0recursive calls（归调用次数）8db block gets(从磁盘上读取的块数,即通过update/delete/select for update读的次数)6consistent gets(从内存里读取的块数,即通过不带for update的select读的次数)0physical reads(物理读—从磁盘读到数据块数量,一般来说是‘consistent gets‘ + ‘db block gets‘)0redo size(重做数——执行SQL的过程中，产生的重做日志的大小)551bytes sent via SQL*Net to client430bytes received via SQL*Net from client2SQL*Net roundtrips to/from client2sorts (memory)(在内存中发生的排序)0sorts (disk)(在硬盘中发生的排序)6rows processed在表做连接时，只能2个表先做连接，然后将连接后的结果作为一个row source，与剩下的表做连接，在上面的例子中，连接顺序为B与A先连接，然后再与C连接：B<---> A <--->Ccol3=10col3=5如果没有执行计划，分析一下，上面的3个表应该拿哪一个作为第一个驱动表？从SQL语句看来，只有B表与C表上有限制条件，所以第一个驱动表应该为这2个表中的一个，到底是哪一个呢？B表有谓词B.col3 = 10，这样在对B表做全表扫描的时候就将where子句中的限制条件(B.col3 = 10)用上，从而得到一个较小的row source,所以B表应该作为第一个驱动表。而且这样的话，如果再与A表做关联，可以有效利用A表的索引(因为A表的col1列为leading column)。上面的查询中C表上也有谓词(C.col3 = 5)，有人可能认为C表作为第一个驱动表也能获得较好的性能。让我们再来分析一下：如果C表作为第一个驱动表，则能保证驱动表生成很小的row source，但是看看连接条件A.col2 = C.col2，此时就没有机会利用A表的索引，因为A表的col2列不为leading column，这样nested loop的效率很差，从而导致查询的效率很差。所以对于NL连接选择正确的驱动表很重要。因此上面查询比较好的连接顺序为(B - - > A) - - > C。如果数据库是基于代价的优化器，它会利用计算出的代价来决定合适的驱动表与合适的连接顺序。一般来说，CBO都会选择正确的连接顺序，如果CBO选择了比较差的连接顺序，我们还可以使用ORACLE提供的hints来让CBO采用正确的连接顺序。如下所示select /*+ ordered */ A.col4fromB,A,CwhereB.col3 = 10andA.col1 = B.col1andA.col2 = C.col2andC.col3 = 5既然选择正确的驱动表这么重要，那么让我们来看一下执行计划，到底各个表之间是如何关联的，从而得到执行计划中哪个表应该为驱动表：在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。具体解释如下：得到去除妨碍判断的索引扫描后的执行计划：Execution Plan-------------------------------------0SELECT STATEMENT Optimizer=CHOOSE10 MERGE JOIN21 SORT (JOIN)32NESTED LOOPS43TABLE ACCESS (FULL) OF ‘B‘53TABLE ACCESS (BY INDEX ROWID) OF ‘A‘65INDEX (RANGE SCAN) OF ‘INX_COL12A‘ (NON-UNIQUE)71 SORT (JOIN)87TABLE ACCESS (FULL) OF ‘C‘看执行计划的第3列，即字母部分，每列值的左面有空格作为缩进字符。在该列值左边的空格越多，说明该列值的缩进越多，该列值也越靠右。如上面的执行计划所示：第一列值为6的行的缩进最多，即该行最靠右；第一列值为4、5的行的缩进一样，其靠右的程度也一样，但是第

查看ORACLE执行计划的几种常用方法

SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤，作为调优，只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划，才能够知道下一步往什么方向。执行计划的定义：执行目标SQL的所有步骤的组合。我们首先列出查看执行计划的一些常用方法：1.explain plan命令PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了。但其实按下F5后，实际后台调用的就是explain plan命令，相当于封装了该命令。explain plan使用方法：(1) 执行explain plan for + SQL(2) 执行select * from table(dbms_xplan.display);实验表准备：SQL> desc test1;Name NullType----------------------------------------- -------- ----------------------------T1IDNOT NULL NUMBER(38)T1VVARCHAR2(10)SQL> desc test2;Name NullType----------------------------------------- -------- ----------------------------T2IDNOT NULL NUMBER(38)T2VVARCHAR2(10)实验：SQL> set linesize 100SQL> explain plan for select t1id, t1v, t2id, t2v from test1, test2 where test1.t1id = test2.t2id;Explained.第一步使用explain plan对目标SQL进行了explain，第二步使用select * from table(dbms_xplan.display)语句展示出该SQL的执行计划。这里test2作为驱动表，进行了全表扫描，test1作为被驱动表，由于其包含主键，所以用的是索引全扫描。左侧ID带*号的第四步操作，表示有谓词条件，这里可以看到既使用了主键索引(access)，又使用了过滤条件(filter)。2.DBMS_XPLAN包(1) select * from table(dbms_xplan.display);--上面以说明。(2)select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));(3)select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));(4)select * from table(dbms_xplan.display_awr(‘sql_id‘));(2)select * from table(dbms_xplan.display_cursor(null, null, ‘advanced‘));主要用于SQLPLUS中查看刚执行过SQL的执行计划。首先第三个参数可以选择‘advanced‘：接下来，第三个参数使用‘all‘：可以看出‘advanced‘记录的信息要比‘all’多，主要就是多一个Outline Data。Outline Data主要是执行SQL时用于固定执行计划的内部HINT组合，可以将这部分内容摘出来加到目标SQL中以固定其执行计划。(3)select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘, child_cursor_number, ‘advanced‘));其中第一个参数可以输入SQL的sql_id或hash value，方法就是如果执行的SQL仍在库缓存中，则可以使用V$SQL查询：其中，使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：隐藏问题1：这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。使用：SQL>select * from table(dbms_xplan.display_cursor(‘1p2fk2v00c865‘, 0, ‘advanced‘));或select * from table(dbms_xplan.display_cursor(‘3221627077‘, 0, ‘advanced‘));就可以查出对应这条SQL的执行计划，内容同(2)中的‘advanced‘，这就不展示了。注意这还有第二个参数child_cursor_number，指的是子游标编号，如果未生成新的子游标，则此处写的是0。(2)和(3)的结论相近，区别就是(2)只是针对最近一次执行SQL查看执行计划，(3)可以针对仍在库缓存中的任意一次SQL查看执行计划。(4)select * from table(dbms_xplan.display_awr(‘sql_id‘));(1)是使用explain plan for +SQL作为前提，(2)和(3)的前提则是SQL的执行计划还在共享池中，具体讲是在库缓存中。如果已经被age out交换出共享池，则不能用这两种方法了。若该SQL的执行计划被采集到AWR库中，则可以用(4)来查询历史执行计划。隐藏问题2：实验这部分内容发现使用select * from table(dbms_xplan.display_awr(‘sql_id‘));并没有结果，@黄玮老师说有可能是AWR收集的是top的SQL，有可能测试用的SQL不是most intensive SQL，但我是用alter system flush shared_pool后执行的手工采集快照，还是未被AWR抓到，比较奇怪的问题，这个也会在另一篇博文中仔细说明。查看ORACLE执行计划的几种常用方法标签：

oracle如何查看执行计划

1.在PL/SQL Developer中得到一个SQL的执行计划输入想要查看执行计划的目标SQL，再按一下快捷键F5就可以了。2.explain plan 命令 explain plan for + 目标SQL select * from table(dbms_xplan.display)3. DBMS_XPLAN 包 1) select * from table(dbms_xplan.display_cursor(null,null,‘advanced‘)) 它用于在SQLPLUS中查看刚刚执行过的SQL的执行计划。这里所传入的第一个和第二个参数的值均为null，第三个参数的值是 advanced，第三个输入参数的值也可以是 all，只不过用 advanced 后的显示结果会比用 all 的显示结果更详细一些。 2) select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value‘,child_cursor_number,‘advanced‘)) 它用于查看指定SQL的执行计划。这里所传入的第一个参数的值是指定SQL的SQL ID 或者 SQL HASH VALUE，第二个参数的值是要查看的执行计划所在的Child Cursor Number，第三个参数和上边的介绍一样，我们一般都是用 advanced 只要目标SQL所对应的Child Cursor还在Library Cache中，我们就可以从 V$SQL 中查询到目标SQL的Child Cursor的详细信息，包括SQL ID、SQL HASH VALUE、Child Cursor Number等。 select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like ‘SQL目标语句‘; 只要目标SQL的执行计划所在的Child Cursor还没有被age out 出 Shared Pool，就可以使用该方法来查看该SQL的执行计划。 3) select * from table(dbms_xplan.display_awr(‘sql_id‘)) 它用于查看指定SQL的所有历史执行计划。使用方法1),2)能够显示目标SQL执行计划的前提是该SQL的执行计划还在Shared Pool中，而如果该SQL的执行计划已经被 age out 出 Shared Pool，那么只要该SQL的执行计划被Oracle采集到AWR Repository中，我们就可以使用该方法来查看该SQL的所有历史执行计划。手工采集AWR报告的方法：exec dbms_workload_repository.create_snapshot(); 查看目标SQL的执行计划是否被 age out 出 Shared Pool: select sql_text,sql_id,version_count,executions from v$sqlarea wheresql_text like ‘目标SQL‘; 清空Shared Pool：alter system flush shared_pool;(请勿随意在生产环境执行此语句)4. AUTOTRACE 开关 set autotrace [off | on | traceonly] 1) 在SQLPLUS的当前Session中执行命令 set autotrace on, 可以在当前Session中将autotrace开关完全打开。这样，在这个Session中随后执行的所有SQL语句，除了显示SQL执行结果之外，还会额外显示这些SQL所对应的执行计划和资源消耗情况。 2) 在SQLPLUS的当前Session中执行命令 set autotrace off, 可以在当前Session中将autotrace开关关闭，这样，在这个Session中随后执行的所有SQL都只会显示SQL执行结果。autotrace开关的默认值是off. 3) 在SQLPLUS的当前Session中执行命令 set autotrace traceonly, 可以在当前Session中将autotrace开关以不显示SQL执行结果的具体内容的方式打开。适用于那些SQL执行结果的具体内容特别长，会连续刷屏的SQL, 这种情况下我们不关心SQL的执行结果，而只是关系SQL的执行计划和资源消耗量。5. 10046事件与 tkprof命令（得到的执行计划最准确）这种方法可以得到执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。 1) oradebug setmypid 表示准备对当前Session使用oradebug命令。 2) oradebug event 10046 trace name context forever,level 12 在当前Session中激活10046事件。 3) 执行目标SQL 4) oradebug tracefile_name 显示当前Session激活10046事件后所对应的trace文件的路径和名称。 5) oradebug event 10046 trace name context off 在当前Session中关闭10046事件。 6) tkprof trace文件绝对路径翻译后目标文件的绝对路径使用tkprof命令翻译trace文件，使结果更直观。6.（得到真实的、准确的执行计划）如果目标SQL的执行计划还在Shared Pool中，可以使用脚本display_cursor-9i.sql和存储过程printsql来得到其真实的执行计划和资源消耗情况。如果目标SQL的执行计划已经被age out 出 Shared Pool了，我们可以执行DBMS_XPLAN.DISPLAY_AWR 或者使用 AWR SQL 报告和 Statspack SQL 报告来得到其历史执行计划和资源消耗。 1) display_cursor_9i.sql的用法（适用于Oracle 9i 及其以后的数据库版本）在执行脚本时，传入待查看执行计划的目标SQL的SQL HASH VALUE 和 Child Cursor Number a. 执行目标SQL b. 查询HASH_VALUE 和 Child Number select sql_text,hash_value,child_number from v$sql where sql_text like ‘目标SQL‘; c. 执行脚本 @脚本 HASH_VALUE CHILD_NUMBER 2) printsql的用法（适用于9i/10g/11g）存储过程printsql是在脚本display_cursor_9i.sql上的封装，它可以把指定SPID或者Session ID的进程或者Session 正在执行的SQL以及其对应的真实执行计划、资源消耗情况打印出来。 a. 执行 topas 得到oracle的PID b. set serveroutput on size 1000000 c. exec printsql(PID,‘SPID‘) 注释：PID是a查询出来的PID，‘SPID‘原样输入。 3) 获取AWR SQL 报告（10g及其以后的版本）手工执行脚本 $ORACLE_HOME/rdbms/admin/awrsqrpt.sql,并依此输入报告类型(text/html)、要查看的快照范围(最近几天内的快照)、目标SQL ID和所要生产的AWR SQL 报告的名称。 4) 获取Statspack SQL 报告（9i）手工执行脚本 $ORACLE_HOME/rdbms/admin/sprepsql,并依此输入要查看的快照的范围、目标SQL HASH VALUE 和所要生成的Statspack SQL报告的名称。注释：事先已经部署了Statspack报告，并且采集Statspack报告的level值大于等于6（Statspack 报告level的默认值是5） 7.如何查看执行计划的执行顺序先从最开头一直连续往右看，直到看到最右边的并列的地方；对于不并列的，靠右的先执行；如果见到并列的，从上往下看，对于并列的部分，靠上的先执行。oracle如何查看执行计划标签：divflush情况语句部署putwherecount关闭

Oracle中利用10053事件来分析Oracle是如何做出最终的执行计划

我们都知道Oracle从10g开始SQL语句选择什么样的执行方式,是全表扫描,还是走索引的依据是执行代价.那么我们怎么可以去看执行代价的我们都知道Oracle从10g开始SQL语句选择什么样的执行方式,是全表扫描,还是走索引的依据是执行代价.那么我们怎么可以去看执行代价的信息呢?通过10053事件可以Oracle依据的执行代价和如何做出执行计划的.如果我们发现某一条SQL语句的执行计划和想像的不一样,我们就可以去看看Oracle所使用的统计分析数据是否准确,是不是统计信息太久没有分析了,重新分析有问题的对象,最终让Oracle做出正确的执行计划。我们来做一次10053事件的示例:SQL> create table t1 as select rownum rn from dba_objects;Table created.SQL> create index ind_t1 on t1(rn);Index created.SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);PL/SQL procedure successfully completed.SQL> create table t2 as select rn, 't2' name from t1 where rncreate index ind_t2 on t2(rn);Index created.SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true);PL/SQL procedure successfully completed.SQL> alter session set tracefile_identifier='mysession';Session altered.SQL> alter session set events '10053 trace name context forever,level 1';Session altered.SQL> explain plan for select t2.* from t1,t2 where t1.rnalter session set events '10053 trace name context off';Session altered.和上次讲SQL_TRACE的时候一样,生成的trace文件的路径是$ORACLE_BASE/admin/SID/udump目录.与SQL_TRACE和10046事件不同的是,生成的trace文件不能用tkprof处理,只能阅读原始的trace文件.对trace文件做一个大体的介绍:**************************Predicate Move-Around (PM)**************************SQL:******* UNPARSED QUERY IS *******SELECT "T2"."RN" "RN","T2"."NAME" "NAME" FROM "YORKER"."T1" "T1","YORKER"."T2" "T2" WHERE "T1"."RN"，

如何解析oracle执行计划

1. 预估执行计划 - Explain Plan
Explain plan以SQL语句作为输入，得到这条SQL语句的执行计划，并将执行计划输出存储到计划表中。
首先，在你要执行的SQL语句前加explain plan for，此时将生成的执行计划存储到计划表中，语句如下：
explain plan for SQL语句
然后，在计划表中查询刚刚生成的执行计划，语句如下：
select * from table(dbms_xplan.display);
注意：Explain plan只生成执行计划，并不会真正执行SQL语句，因此产生的执行计划有可能不准，因为：
1）当前的环境可能和执行计划生成时的环境不同；
2）不会考虑绑定变量的数据类型；
3）不进行变量窥视。
2. 查询内存中缓存的执行计划（dbms_xplan.display_cursor）
如果你想获取正在执行的或刚执行结束的SQL语句真实的执行计划（即获取library cache中的执行计划），可以到动态性能视图里查询。方法如下：
1）获取SQL语句的游标
游标分为父游标和子游标，父游标由sql_id(或联合address和hash_value)字段表示，子游标由child_number字段表示。
如果SQL语句正在运行，可以从v$session中获得它的游标信息，如：
select status, sql_id, sql_child_number from v$session where status='ACTIVE' and ....
如果知道SQL语句包含某些关键字，可以从v$sql视图中获得它的游标信息，如：
select sql_id, child_number, sql_text from v$sql where sql_text like '%关键字%‘
2）获取库缓存中的执行计划
为了获取缓存库中的执行计划，可以直接查询动态性能视图v$sql_plan和v$sql_plan_statistics_all等，但更方便的方法是以sql_id和子游标为参数，执行如下语句：
select * from table(dbms_xplan.display_cursor('sql_id',child_number));
3）获取前一次的执行计划：
set serveroutput off
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
3. 查询历史执行计划（dbms_xplan.display_awr）
AWR会定时把动态性能视图中的执行计划保存到dba_hist_sql_plan视图中，如果你想要查看历史执行计划，可以采用如下方法查询：
select * from table(dbms_xplan.display_awr('sql_id');
4. 在用sqlplus做SQL开发是（Autotrace）
set autotrace是sqlplus工具的一个功能，只能在通过sqlplus连接的session中使用，它非常适合在开发时测试SQL语句的性能，有以下几种参数可供选择：
SET AUTOTRACE OFF ---------------- 不显示执行计划和统计信息，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ 只显示优化器执行计划
SET AUTOTRACE ON STATISTICS -- 只显示统计信息
SET AUTOTRACE ON ----------------- 执行计划和统计信息同时显示
SET AUTOTRACE TRACEONLY ------ 不真正执行，只显示预期的执行计划，同explain plan
5. 生成Trace文件查询详细的执行计划（SQL_Trace, 10046）
SQL_TRACE作为初始化参数可以在实例级别启用，也可以只在会话级别启用，在实例级别启用SQL_TRACE会导致所有进程的活动被跟踪，包括后台进程及所有用户进程，这通常会导致比较严重的性能问题，所以在一般情况下，我们使用sql_trace跟踪当前进程，方法如下：
SQL>alter session set sql_trace=true;
...被跟踪的SQL语句...
SQL>alter session set sql_trace=false;
如果要跟踪其它进程，可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION来实现，例如：
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true) --开始跟踪
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false) --结束跟踪
生成trace文件后，再用tkprof 工具将sql trace 生成的跟踪文件转换成易读的格式，语法如下：
tkprof inputfile outputfile
10046事件是SQL_TRACE的一个升级版，它也是追踪会话，生成Trace文件，只是它里面的内容更详细
要执行任何SQL语句，Oracle 必须推导出一个“执行计划”。查询的执行计划是 Oracle 将如何实现数据的检索，以满足给定 SQL 语句的描述。它只不过是其中包含的步骤及它们之间关系的顺序树。
执行计划树的基本规则如下：
执行计划将包含一个根，没有父（操作）
父（操作）可以有一个或更多的子（操作），其ID将小于子（操作）ID
一子（操作）可只有一个父（操作），显示时右缩进；含许多子（操作）时，缩进相同
下面是一个执行计划示例。
SQL> explain plan for
2 select e.empno, e.ename, d.dname
3 from emp e, dept d
4 where e.deptno = d.deptno
5 and e.deptno = 10;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------
Plan hash value: 568005898

------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 3 | INDEX UNIQUE SCAN | PK_DEPT |
| 4 | TABLE ACCESS FULL | EMP |
------------------------------------------------
使用上述的规则，可以说：
操作0是树的根，它有一个子操作，即操作1
操作1有两个子操作，即操作2和4
操作2有一子操作，即操作3
下面是执行计划的图形表示。如果解读该树：为执行操作1，需执行操作2和4；操作2先完成；为执行2，需执行其子操作3；为执行操作4，需执行操作2。
Operation 0
(SELECT STATEMENT)
|
|
|
Operation 1
(NESTED LOOPS)
/\
/ \
/ \
/ \
/ \
/ \
/ \
/ \
Operation 2 Operation 4
(TABLE ACCESS (TABLE ACCESS FULL)
BY INDEX ROWID)
|
|
|
Operation 3
(INDEX UNIQUE SCAN)
操作3访问DEPT表，使用 INDEX UNIQUE SCAN，并传递ROWID给操作2
操作2从DEPT表返回所有行给操作1
操作1对操作2返回的每一行执行操作4
操作4执行全表扫描（TABLE ACCESS FULL），应用过滤器E. DEPTNO = 10，返回结果行给操作1
操作1返回最后结果给操作0
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from dual;
　　执行完语句后，会显示explain plan 与统计信息。
　　这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
　2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
　　上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
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;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
　　从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL> alter session set SQL_TRACE=true;
SQL> select * from dual;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL> alter session set events '10046 trace name context forever, level 8';
关闭:SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_dump_dest' ) d;-- 其它用户的 session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p, ( select t.instance from v$thread t,v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from v$parameter where name = 'user_dump_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL> $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Production on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

oracle sql执行计划怎么分析

在发现一个语句的执行计划有异常的时候，通常会生成一个sqlrpt看看使用的执行计划是否正确，如何来判断执行计划是否正确，将通过以下几个步骤来判断：
1.先查看sql语句的结构，看语句中连接是union，还是等值连接还是左、右连接,再看连接中的表数量。
2.查看执行计划中出现异常的部分。
3.查看各表的索引情况及表是否是分区的，在where条件上使用的索引列是否正确，看统计分析表中对表的分析结果是否正确
4.分析表的用途，表的数据日增长量。
5.分析为什么会出现异常的执行计划。
跟踪执行计划的方法：
(1) set autotrace on explain 只显示查询结果和执行计划
set autotrace on statistic 只显示查询结果统计信息
set autotrace on 显示前两者
set autotrace traceonly 不显示查询结果，只显示执行计划和统计信息
set autotrace off 关闭跟踪
要使用autotrace，必须在sqlplus里面使用，且使用的是sys用户。
（2）可以使用explain plan for select * from c_cons 可以解析执行计划，然后通过select * from table(dbms_xplain.display(null,null,’outline’,null));来显示执行计划。
（3）使用工具Toad for oracle使用sql_id来生成执行计划

如何解析oracle执行计划

要执行任何SQL语句，Oracle 必须推导出一个“执行计划”。查询的执行计划是 Oracle 将如何实现数据的检索，以满足给定 SQL 语句的描述。它只不过是其中包含的步骤及它们之间关系的顺序树。
执行计划树的基本规则如下：
执行计划将包含一个根，没有父（操作）
父（操作）可以有一个或更多的子（操作），其ID将小于子（操作）ID
一子（操作）可只有一个父（操作），显示时右缩进；含许多子（操作）时，缩进相同
下面是一个执行计划示例。

SQL> explain plan for
2 select e.empno, e.ename, d.dname
3 from emp e, dept d
4 where e.deptno = d.deptno
5 and e.deptno = 10;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT
------------------------------------------------
Plan hash value: 568005898

使用上述的规则，可以说：
操作0是树的根，它有一个子操作，即操作1
操作1有两个子操作，即操作2和4
操作2有一子操作，即操作3
下面是执行计划的图形表示。如果解读该树：为执行操作1，需执行操作2和4；操作2先完成；为执行2，需执行其子操作3；为执行操作4，需执行操作2。

Operation 0
(SELECT STATEMENT)
|
|
|
Operation 1
(NESTED LOOPS)
/\
/ \
/ \
/ \
/ \
/ \
/ \
/ \
Operation 2 Operation 4
(TABLE ACCESS (TABLE ACCESS FULL)
BY INDEX ROWID)
|
|
|
Operation 3
(INDEX UNIQUE SCAN)

操作3访问DEPT表，使用 INDEX UNIQUE SCAN，并传递ROWID给操作2
操作2从DEPT表返回所有行给操作1
操作1对操作2返回的每一行执行操作4
操作4执行全表扫描（TABLE ACCESS FULL），应用过滤器E. DEPTNO = 10，返回结果行给操作1
操作1返回最后结果给操作0

pl sql developer oracle如何分析执行计划

选file-new-explain plan window，或者直接选中sql点F5，进入这个窗口后点F8执行sql就行了。
执行后下面有一些信息，看cost列，一般越小效率越高，还有看Description列，有没有table access full 这种全表扫描标志的，一般查询大表要避免全表扫描，再细致的我也不清楚了...

优化SQL的另一种思维5 如何分析ORACLE执行计划

　　七、整体实例分析　　　　客户端程序优化SQL步骤　　　　tkprof程序整体实例解说
　　【IT专家网独家】　　例1：　　假设LARGE_TABLE是一个较大的表，且username列上没有索引
，则运行下面的语句：
　　SQLSELECT*FROMLARGE_TABLEwhereUSERNAME=‘TEST’;
　　QueryPlan
　　-----------------------------------------
　　SELECTSTATEMENTOptimizer=CHOOSE(Cost=1234Card=1Bytes=14)
　　TABLEACCESSFULLLARGE_TABLE[:Q65001][ANALYZED]
　　在这个例子中，TABLE ACCESS FULL LARGE_TABLE是第一个操作，意思是在LARGE_TABLE表上做全表扫描。当这个操作完成之后，产生的row source中的数据被送往下一步骤进行处理，在此例中，SELECT STATEMENT操作是这个查询语句的最后一步。
　　Optimizer=CHOOSE 指明这个查询的optimizer_mode，即optimizer_mode初始化参数指定的值，它并不是指语句执行时真的使用了该优化器
。决定该语句使用何种优化器的唯一方法是看后面的cost部分。例如，如果给出的是下面的形式，则表明使用的是CBO优化器，此处的cost表示优化器认为该执行计划的代价:
　　SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14)
　　然而假如执行计划中给出的是类似下面的信息，则表明是使用RBO优化器，因为cost部分的值为空，或者压根就没有cost部分。
　　SELECT STATEMENT Optimizer=CHOOSE Cost=
　　SELECT STATEMENT Optimizer=CHOOSE
　　cost属性的值是一个在oracle内部用来比较各个执行计划所耗费的代价的值，从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性，只能对同一个语句的不同执行计划的cost值进行比较。
　　[:Q65001] 表明该部分查询是以并行方式运行的。里面的数据表示这个操作是由并行查询的一个slave进程处理的，以便该操作可以区别于串行执行的操作。
　　[ANALYZED] 表明操作中引用的对象被分析过了，在数据字典中有该对象的统计信息可以供CBO使用。
　　例2：　　假定A、B、C都是不是小表，且在A表上一个组合索引：A(a.col1,a.col2) ，注意a.col1列为索引的引导列。　　考虑下面的查询：

← 上一篇: excel表格怎么做,怎样用excel做表格下一篇: 学编程的孩子有什么好处,让孩子学习编程，对孩子有哪些好处？→

oracle执行计划结果分析,pl sql developer oracle如何分析执行计划

oracle执行计划结果分析,pl sql developer oracle如何分析执行计划详细介绍

知识分享相关文章：

网站数据信息