精华内容
下载资源
问答
  • RAID常见的LEVEL有0,1,3,5,组合方式常有0+1,1+0,各自的特点描述如下: RAID0:将多个物理盘组成一个大的逻辑盘,容量为各盘之和,RW性能最好,但问题如果一个盘如现损失,整个RAID的数据都将丢失。 RAID1:...
    RAID常见的LEVEL0135,组合方式常有0+11+0,各自的特点描述如下:
    RAID0:将多个物理盘组成一个大的逻辑盘,容量为各盘之和,RW性能最好,但问题是如果一个盘如现损失,整个RAID的数据都将丢失。
    RAID1:即镜像,组成RAID1的多个盘中的数据都是同样的,这样多个盘的容量为一个盘,实现了数据保护功能,且读写性能都不错,在一些情况下,读性能比单个物理硬盘读高出15-20%左右。
    RAID3:组成RAID的多个磁盘中有一个盘用来存储校验数据,当有一个非校验盘损坏时,数据能自动恢复到新盘。但当校验盘出现损毁时,整个RAID都失效了,即存在单点故障。写性能较差,但读性能不错,因为读数据时,为一个IO请求服务时,所有的磁盘磁头都位于同一位置(同一柱面,同一扇区)
    RAID5:与RAID3类似,不过校验数据是分布在整个RAID上的所有硬盘上的。所有无论丢失哪个盘,都不会整个毁坏RAID上的数据。这种方式写性能较差,读性能不错。并适用于随机读写频繁的情况,即多个硬盘磁头可各自服务于不同的IO请求,提高了读写性能。
    1+0的情况好于0+1,其原因是0+1时,如果0中一块盘损坏时,这一半逻辑盘都失效了,读性能理论上丢失了50%(请参考RAID1的描述);而1+0时,如果0中丢了一块盘,它的镜像还存在,能正常工作,整个0还是存在的,对性能影响不大。
    ORACLE使用RAID时,可作这样的考虑:
    两个盘作RAID1,用来存放在线日志,最好是开成三个卷,存三组,每组两个成员。
    两个盘作RAID1,用来存归档日志。
    如果不能为归档做专门的RAID1,要将其放在和在线日志同样的RAID1上,也应该为其单独开一个卷组
    其余盘做RAID3,或RAID5,具体用哪种要看应用的目的,即参考:如果DB应用为DATA MART/DATA WH ,则用RAID3较好;如果为一般的OLTP应用,则用RAID5较好。这一原则可参看RAID3RAID5的特点描述。
    另外,表空间的划分上,TEMP,RBS,SYSTEM表空间最好分布在同一卷组上,它们形成的竞争最少,另外数据表空间和索引表空间要分在不同的卷存放,通常它们都是形成竞争的主要原因。
    展开全文
  • ORACLE 执行计划分析

    2019-09-24 07:58:13
    一、什么是执行计划 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 ...
    一、什么是执行计划

    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

     

    2.层次的父子关系,看比较复杂的例子:

    PARENT1

    **FIRST CHILD
    ****FIRST GRANDCHILD
    **SECOND CHILD

    Here 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 objects
    So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects
    So 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

    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 Merge Join (SMJ)    --由于sort是非常耗资源的,所以这种连接方式要避免

    Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently.

    SQL> explain plan for
    select /*+ ordered */ e.deptno,d.deptno
    from emp e,dept d
    where e.deptno = d.deptno
    order by e.deptno,d.deptno;

    Query Plan
    -------------------------------------
    SELECT STATEMENT [CHOOSE] Cost=17
    **MERGE JOIN
    ****SORT JOIN
    ******TABLE ACCESS FULL EMP [ANALYZED]
    ****SORT JOIN
    ******TABLE ACCESS FULL DEPT [ANALYZED]

     

    Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method.

    2.Nested Loops (NL)    --比较高效的一种连接方式

    Fetches the first batch of rows from row source 1, Then we probe row source 2 once for each row returned from row source 1.
    For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1.

    SQL> explain plan for
    select a.dname,b.sql
    from dept a,emp b
    where a.deptno = b.deptno;

     

    Query Plan
    -------------------------
    SELECT STATEMENT [CHOOSE] Cost=5
    **NESTED LOOPS
    ****TABLE ACCESS FULL DEPT [ANALYZED]
    ****TABLE ACCESS FULL EMP [ANALYZED]

    3.Hash Join    --最为高效的一种连接方式

    New join type introduced in 7.3, More efficient in theory than NL & SMJ, Only accessible via the CBO. Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory.

     

    SQL> explain plan for
    select /*+ use_hash(emp) */ empno
    from emp,dept
    where emp.deptno = dept.deptno;

     

    Query Plan
    ----------------------------
    SELECT STATEMENT [CHOOSE] Cost=3
    **HASH JOIN
    ****TABLE ACCESS FULL DEPT
    ****TABLE ACCESS FULL EMP

     

    Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3.

    3.Cartesian Product    --卡迪尔积,不算真正的连接方式,sql肯定写的有问题

    A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data. Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out.
    It can be useful in some circumstances - Star joins uses cartesian products.Notice that there is no join between the 2 tables:

     

    SQL> explain plan for
    select emp.deptno,dept,deptno
    from emp,dept

     

    Query Plan
    ------------------------------
    SLECT STATEMENT [CHOOSE] Cost=5
    **MERGE JOIN CARTESIAN
    ****TABLE ACCESS FULL DEPT
    ****SORT JOIN
    ******TABLE ACCESS FULL EMP

     

    The CARTESIAN keyword indicate that we are doing a cartesian product.

    七、运算符

    1.sort    --排序,很消耗资源

    There are a number of different operations that promote sorts:
    order by clauses
    group by
    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 for
    select ename,tot
    from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp
    where emp.empno = tmp.empno;

     

    Query Plan
    ------------------------
    SELECT STATEMENT [CHOOSE]
    **HASH JOIN
    **TABLE ACCESS FULL EMP [ANALYZED]
    **VIEW
    ****SORT GROUP BY
    ******INDEX FULL SCAN BE_IX

    4.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.

    转载于:https://www.cnblogs.com/rootq/archive/2008/09/06/1285779.html

    展开全文
  • ORACLE执行计划

    2013-01-14 15:06:12
    ORACLE执行计划,在pl/...一、什么是执行计划 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执行计划,在pl/sql中点击F5

    下面是执行计划的解释,转载自互联网

    一、什么是执行计划

    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)*/  *  from  emp  e;

    Execution Plan

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

       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)

       1    0   TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=7134):Q5000

    --[:Q5000]表示是并行方式

       1 PARALLEL_TO_SERIAL            SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO"

                                       ,A1."ENAME",A1."JOB",A1."MGR",A1."HI

    优化模式是CHOOSE的情况下,看Cost参数是否有值来决定采用CBO还是RBO
    SELECT STATEMENT [CHOOSE] Cost=1234--Cost
    有值,采用CBO
    SELECT STATEMENT [CHOOSE]
               --Cost为空,采用RBO(9I是如此显示的)

    2.层次的父子关系的例子:
    PARENT1
    **FIRST CHILD
    ****FIRST GRANDCHILD
    **SECOND CHILD

    Here 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 objects

    So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects

    So 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 inmin函数等容易产生

    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 for

    select ename,totfrom emp,(select empno,sum(empno) tot from big_emp group by empno) tmp

    where emp.empno = tmp.empno;

    Query Plan

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

    SELECT STATEMENT [CHOOSE]

    **HASH JOIN

    **TABLE ACCESS FULL EMP [ANALYZED]

    **VIEW

    ****SORT GROUP BY

    ******INDEX FULL SCAN BE_IX

     

    4.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.

    示例:假定ABC都是不是小表,且在A表上一个组合索引:A(a.col1,a.col2) ,注意a.col1列为索引的引导列。考虑下面的查询:

    select A.col4   from  A , B , C

    where B.col3 = 10  and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5;

    Execution Plan

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

      0   SELECT STATEMENT Optimizer=CHOOSE

      1  0 MERGE JOIN

      2  1 SORT (JOIN)

      3  2 NESTED LOOPS

      4  3 TABLE ACCESS (FULL) OF 'B'

      5  3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

      6  5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

      7  1 SORT (JOIN)

      8  7 TABLE ACCESS (FULL) OF 'C'

    Statistics(统计信息参数,参见另外个转载的文章)

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

         0 recursive calls(归调用次数)

         8 db block gets(从磁盘上读取的块数,通过update/delete/select for update读的次数)

         6 consistent gets(从内存里读取的块数,通过不带for updateselect 读的次数)

         0 physical reads(物理读从磁盘读到数据块数量,一般来说是'consistent gets' + 'db block gets')

         0 redo size      (重做数——执行SQL的过程中,产生的重做日志的大小)

        551 bytes sent via SQL*Net to client

        430 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         2 sorts (memory) (在内存中发生的排序)

         0 sorts (disk)   (在硬盘中发生的排序)

         6 rows processed

      在表做连接时,只能2个表先做连接,然后将连接后的结果作为一个row source,与剩下的表做连接,在上面的例子中,连接顺序为BA先连接,然后再与C连接:

    B   <---> A <--->  C

    col3=10       col3=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.col4

    from  B,A,C

    where B.col3 = 10   and  A.col1 = B.col1   and  A.col2 = C.col2   and  C.col3 = 5

    既然选择正确的驱动表这么重要,那么让我们来看一下执行计划,到底各个表之间是如何关联的,从而得到执行计划中哪个表应该为驱动表:

     

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

     

    得到去除妨碍判断的索引扫描后的执行计划:Execution Plan

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

      0   SELECT STATEMENT Optimizer=CHOOSE

      1  0 MERGE JOIN

      2  1 SORT (JOIN)

      3  2    NESTED LOOPS

      4  3       TABLE ACCESS (FULL) OF 'B'

    5            3       TABLE ACCESS (BY INDEX ROWID) OF 'A'

    6  5         INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

      7  1 SORT (JOIN)

      8  7    TABLE ACCESS (FULL) OF 'C'

      看执行计划的第3列,即字母部分,每列值的左面有空格作为缩进字符。在该列值左边的空格越多,说明该列值的缩进越多,该列值也越靠右。如上面的执行计划所示:第一列值为6的行的缩进最多,即该行最靠右;第一列值为45的行的缩进一样,其靠右的程度也一样,但是第一列值为4的行比第一列值为5的行靠上;谈论上下关系时,只对连续的、缩进一致的行有效。

     

      从这个图中我们可以看到,对于NESTED LOOPS部分,最右、最上的操作是TABLE ACCESS (FULL) OF 'B',所以这一操作先执行,所以该操作对应的B表为第一个驱动表(外部表),自然,A表就为内部表了。从图中还可以看出,BA表做嵌套循环后生成了新的row source ,对该row source进行来排序后,与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行SMJ连接操作。所以从上面可以得出如下事实:B表先与A表做嵌套循环,然后将生成的row sourceC表做排序—合并连接。

     

           通过分析上面的执行计划,我们不能说C表一定在BA表之后才被读取,事实上,B表有可能与C表同时被读入内存,因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的,因为ORACLE读取数据时,如果就是需要一行数据也是将该行所在的整个数据块读入内存,而且有可能为多块读。

     

        看执行计划时,我们的关键不是看哪个操作先执行,哪个操作后执行,而是关键看表之间连接的顺序(如得知哪个为驱动表,这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引)

    在从执行计划中判断出哪个表为驱动表后,根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适,如果不合适,对SQL语句进行更改,使优化器可以选择正确的驱动表。

    展开全文
  • 一、什么是执行计划 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....

    如何看懂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)*/  *  from  emp  e;

    Execution Plan

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

       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)

       1    0   TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=7134):Q5000

    --[:Q5000]表示是并行方式

       1 PARALLEL_TO_SERIAL            SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO"

                                       ,A1."ENAME",A1."JOB",A1."MGR",A1."HI

    优化模式是CHOOSE的情况下,看Cost参数是否有值来决定采用CBO还是RBO:
    SELECT STATEMENT [CHOOSE] Cost=1234 --Cost有值,采用CBO
    SELECT STATEMENT [CHOOSE]           --Cost为空,采用RBO(9I是如此显示的)

    2.层次的父子关系的例子:
    PARENT1
    **FIRST CHILD
    ****FIRST GRANDCHILD
    **SECOND CHILD

    Here 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 objects

    So it executes its first child step: ID=1 PID=0 HASH JOIN but this is dependand on it's child objects

    So 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 inmin函数等容易产生

    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 for

    select ename,tot from emp,(select empno,sum(empno) tot from big_emp group by empno) tmp

    where emp.empno = tmp.empno;

    Query Plan

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

    SELECT STATEMENT [CHOOSE]

    **HASH JOIN

    **TABLE ACCESS FULL EMP [ANALYZED]

    **VIEW

    ****SORT GROUP BY

    ******INDEX FULL SCAN BE_IX

     

    4.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列为索引的引导列。考虑下面的查询:

    select A.col4   from  A , B , C

    where B.col3 = 10  and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5;

    Execution Plan

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

      0   SELECT STATEMENT Optimizer=CHOOSE

      1  0 MERGE JOIN

      2  1 SORT (JOIN)

      3  2 NESTED LOOPS

      4  3 TABLE ACCESS (FULL) OF 'B'

      5  3 TABLE ACCESS (BY INDEX ROWID) OF 'A'

      6  5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

      7  1 SORT (JOIN)

      8  7 TABLE ACCESS (FULL) OF 'C'

    Statistics(统计信息参数,参见另外个转载的文章)

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

         0 recursive calls(归调用次数)

         8 db block gets(从磁盘上读取的块数,通过update/delete/select for update读的次数)

         6 consistent gets(从内存里读取的块数,即通过不带for updateselect 读的次数)

         0 physical reads(物理读—从磁盘读到数据块数量,一般来说是'consistent gets' + 'db block gets')

         0 redo size      (重做数——执行SQL的过程中,产生的重做日志的大小)

        551 bytes sent via SQL*Net to client

        430 bytes received via SQL*Net from client

         2 SQL*Net roundtrips to/from client

         2 sorts (memory) (在内存中发生的排序)

         0 sorts (disk)   (在硬盘中发生的排序)

         6 rows processed

      在表做连接时,只能2个表先做连接,然后将连接后的结果作为一个row source,与剩下的表做连接,在上面的例子中,连接顺序为B与A先连接,然后再与C连接:

    B   <---> A <--->  C

    col3=10       col3=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.col4

    from  B,A,C

    where B.col3 = 10   and  A.col1 = B.col1   and  A.col2 = C.col2   and  C.col3 = 5

    既然选择正确的驱动表这么重要,那么让我们来看一下执行计划,到底各个表之间是如何关联的,从而得到执行计划中哪个表应该为驱动表:

     

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

     

    得到去除妨碍判断的索引扫描后的执行计划:Execution Plan

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

      0   SELECT STATEMENT Optimizer=CHOOSE

      1  0 MERGE JOIN

      2  1 SORT (JOIN)

      3  2    NESTED LOOPS

      4  3       TABLE ACCESS (FULL) OF 'B'

    1. 3       TABLE ACCESS (BY INDEX ROWID) OF 'A'

    6  5         INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)

      7  1 SORT (JOIN)

      8  7    TABLE ACCESS (FULL) OF 'C'

      看执行计划的第3列,即字母部分,每列值的左面有空格作为缩进字符。在该列值左边的空格越多,说明该列值的缩进越多,该列值也越靠右。如上面的执行计划所示:第一列值为6的行的缩进最多,即该行最靠右;第一列值为4、5的行的缩进一样,其靠右的程度也一样,但是第一列值为4的行比第一列值为5的行靠上;谈论上下关系时,只对连续的、缩进一致的行有效。

     

      从这个图中我们可以看到,对于NESTED LOOPS部分,最右、最上的操作是TABLE ACCESS (FULL) OF 'B',所以这一操作先执行,所以该操作对应的B表为第一个驱动表(外部表),自然,A表就为内部表了。从图中还可以看出,B与A表做嵌套循环后生成了新的row source ,对该row source进行来排序后,与C表对应的排序了的row source(应用了C.col3 = 5限制条件)进行SMJ连接操作。所以从上面可以得出如下事实:B表先与A表做嵌套循环,然后将生成的row source与C表做排序—合并连接。

     

           通过分析上面的执行计划,我们不能说C表一定在B、A表之后才被读取,事实上,B表有可能与C表同时被读入内存,因为将表中的数据读入内存的操作可能为并行的。事实上许多操作可能为交叉进行的,因为ORACLE读取数据时,如果就是需要一行数据也是将该行所在的整个数据块读入内存,而且有可能为多块读。

     

        看执行计划时,我们的关键不是看哪个操作先执行,哪个操作后执行,而是关键看表之间连接的顺序(如得知哪个为驱动表,这需要从操作的顺序进行判断)、使用了何种类型的关联及具体的存取路径(如判断是否利用了索引)

    在从执行计划中判断出哪个表为驱动表后,根据我们的知识判断该表作为驱动表(就像上面判断ABC表那样)是否合适,如果不合适,对SQL语句进行更改,使优化器可以选择正确的驱动表。

    展开全文
  • ORACLE执行计划学习总结

    千次阅读 2016-04-06 18:54:01
    一、什么是执行计划 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
  • SQL> @E:\drp\sql\drp-data2.sql SP2-0734: 未知的命令开头 "if exists(..." - 忽略了剩余的行。 delete foreign key FK_T_CLIENT_REFERENCE_T_CLIENT * ERROR 位于第 2 行: ...ORA-01735: 非法的 ALTER TABLE ...
  • 如何看懂Oracle执行计划

    千次阅读 2018-01-15 14:25:12
    一、什么是执行计划 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 ...
  • 一、什么是执行计划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 ...
  • Oracle AWR 阙值影响历史执行计划

    千次阅读 2013-03-28 17:21:27
    最近有网友提到为什么在dba_hist_sql_plan中无法查看到sql语句的历史执行计划,对于这个问题由于缺省情况下,Oracle 设定的阙值并非捕获所有的sql语句,所以无法看到某些sql历史执行计划乃正常现象。在Oracle 9i的...
  • 一、什么是执行计划 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 ...

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 150
精华内容 60
关键字:

level是什么oracle