查询路径—估算树
create table e
as select * from emp
create table d
as
select * from dept
Explain plan for
selectename,dname from d,e where e.deptno=d.deptno
select * from table(dbms_xplan.display());
Plan hash value: 1127375450
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 630 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 15 | 630 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| D | 4 | 88 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| E | 15 | 300 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Explain plan for
select ename,dnamefrom d, (select ename,deptno from e where rownum<2) e where e.deptno=d.deptno
select * from table(dbms_xplan.display());
Plan hash value: 1791846393
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 5 (0)| 00:00:01 |
| 1 | NESTEDLOOPS | | 1 | 42 | 5 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | COUNTSTOPKEY | | | | | |
| 4 | TABLE ACCESS FULL| E | 15 | 300 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | D | 1 | 22 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
3 - filter(ROWNUM<2)
5 -filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
驱动表概念
估算树
从左到右 从下到上
autotrace
oracle_home\sqlplus\admin\
conn sys/pwd@tiwen as sysdba;
drop role plustrace;
create role plustrace;
grant select on v_$sesstat to plustrace;
grant select on v_$statname to plustrace;
grant select on v_$mystat to plustrace;
grant plustrace to dba with admin option;
grant plustrace to public;
grant select on v_$sesstat to public;
grant select on v_$statname to public;
grant select on v_$mystat to public;
grant plustrace to dba with admin option;
grant plustrace to public;
grant alter session to public;
使用命令
set autotrace on
set autotrace off
set autotrace on explain
set autotrace on statistics
set autotrace traceonly
autotrace输出内容解释
recursive calls：执行语句时、调用的oracle内部语句(如分析所用的sql)和其他语句（如触发器）。
测试举例：
举例1
conn scott/tiger@tiwen
set autotrace on
alter system flush shared_pool
set autotrace on
select * from emp
select * from emp
举例2