10gR2里执行计划显示的一些增强

By cui hua on February 26, 2009 3:47 PM | No Comments

确实方便了好多！如下所示：

SQL> conn scott/tiger@ipradev;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;

 

     EMPNO ENAME      DNAME

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

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

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

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  ch8faj4xqm1j3, child number 0

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

select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

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

| Id  | Order | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 |     5 | SELECT STATEMENT             |         |       |       |     5 (100)|          |

|   1 |     4 |  NESTED LOOPS                |         |    14 |   350 |     5   (0)| 00:00:01 |

 

PLAN_TABLE_OUTPUT

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

|   2 |     1 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |

|   3 |     3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    12 |     1   (0)| 00:00:01 |

|*  4 |     2 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

 

已选择21行。

 

SQL> select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.deptno=t2.deptno;

 

     EMPNO ENAME      DNAME

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

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

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

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  ch8faj4xqm1j3, child number 0

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

select t1.empno,t1.ename,t2.dname from emp t1,dept t2 where

t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

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

| Id  | Operation                    | Name    | E-Rows |

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

|   1 |  NESTED LOOPS                |         |     14 |

 

PLAN_TABLE_OUTPUT

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

|   2 |   TABLE ACCESS FULL          | EMP     |     14 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

Note

 

PLAN_TABLE_OUTPUT

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

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

 

 

已选择27行。

 

SQL> select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.de

ptno=t2.deptno;

 

     EMPNO ENAME      DNAME

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

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

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

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  4m81jub7yju91, child number 0

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

select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where

t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

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

| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   1 |  NESTED LOOPS                |         |      1 |     14 |     14 |00:00:00.01 |      24 |

 

PLAN_TABLE_OUTPUT

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

|   2 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |     14 |00:00:00.01 |      16 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     14 |      1 |     14 |00:00:00.01 |       2 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

 

已选择21行。

 

SQL> select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where t1.de

ptno=t2.deptno;

 

     EMPNO ENAME      DNAME

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

      7369 SMITH      RESEARCH

      7499 ALLEN      SALES

      7521 WARD       SALES

      7566 JONES      RESEARCH

      7654 MARTIN     SALES

      7698 BLAKE      SALES

      7782 CLARK      ACCOUNTING

      7788 SCOTT      RESEARCH

      7839 KING       ACCOUNTING

      7844 TURNER     SALES

      7876 ADAMS      RESEARCH

 

     EMPNO ENAME      DNAME

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

      7900 JAMES      SALES

      7902 FORD       RESEARCH

      7934 MILLER     ACCOUNTING

 

已选择14行。

 

SQL> select * from table(xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  4m81jub7yju91, child number 0

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

select /*+ gather_plan_statistics */ t1.empno,t1.ename,t2.dname from emp t1,dept t2 where

t1.deptno=t2.deptno

 

Plan hash value: 210866379

 

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

| Id  | Order | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   1 |     4 |  NESTED LOOPS                |         |      1 |     14 |     14 |00:00:00.01 |      24 |

 

PLAN_TABLE_OUTPUT

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

|   2 |     1 |   TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8

|   3 |     3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |     14 |00:00:00.01 |   

|*  4 |     2 |    INDEX UNIQUE SCAN         | PK_DEPT |     14 |      1 |     14 |00:00:00.01 |      

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

 

Predicate Information (identified by operation id):

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

 

   4 - access("T1"."DEPTNO"="T2"."DEPTNO")

 

 

已选择21行。

 

xplan是对dbms_xplan的增强，从xplan里可以清晰的看到执行计划的顺序，它的源码在这里：

xplan.sql 

 

E-Rows 表示Estimated Rows

A-Rows 表示Actual Rows

A-Time 表示Actual Time

Categories:

Leave a comment