核心sql
explain plan for (select * from v1);
select * from table(dbms_xplan.display());
SQL解析
dbms_xplan.display()相当于一个report,是由plan_table而来,
也就是说explain生成执行计划之后,数据存入plan_table,然后由plan_table生成dbms_xplan.display() report,我们在查看时,可以直接查看这个report
report由plan_table而来的依据:
dbms_xplan.display()是一个加密的,可以通过unwrap工具解密查看,发现是从plan_table而来
dbms_xplan是一个包,用来查看执行计划的包
dbms_xplan信息来自于db_source
Select text from db_source where XXX=’dbms_xplain’;
实验
-
scott用户下创建一个视图
Create view v1 as
Select empno,dname from emp,dept where emp.deptno=dept.deptno;
***没有grant权限的话切换sys用户赋予权限 -
生成执行计划并展示report
scott@ORCL>explain plan for (select * from v1);
scott@ORCL>select * from table(dbms_xplan.display());
结论显示DEPT,EMP这两张表构成了v1视图
scott@ORCL>explain plan for(select * from v1);
Explained.
scott@ORCL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 280 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 98 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
18 rows selected.
延伸
上面我们说dbms_xplan.display() report是由plan_table而来的,那么接下来就简单介绍一下plan_table
首先看看plan_table里都有哪些字段
scott@ORCL>desc plan_table;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_ALIAS VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
OTHER_XML CLOB
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(30)
scott@ORCL>
对比较重要的几个字段进行说明
scott@ORCL>Select object_owner,object_name,object_type from plan_table;
OBJECT_OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
SCOTT DEPT TABLE
SCOTT PK_DEPT INDEX (UNIQUE)
SCOTT EMP TABLE
OBJECT_OWNER: 对象用户
OBJECT_NAME:对象(例如table 名)
OBJECT_TYPE:是table类型还是其他类型
这里你也可以指定将OBJECT_TYPE=‘TABLE’条件来进行筛选,即可查询出你想要的table。