10gR2里执行计划显示的一些增强
确实方便了好多！如下所示：
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里可以清晰的看到执行计划的顺序，它的源码在这里：
E-Rows 表示Estimated Rows
A-Rows 表示Actual Rows
A-Time 表示Actual Time