基础01-生成执行计划
--对于11G平时最常用的是table函数+dbms_xplan包,其它方式如autot traceonly也偶尔用,下面就来介绍dbms_xplan
--explan plan for...,这个只是预估计划,除非明显连接方式错误,否则有时帮忙并不大,所以最直接还是用dbms_xplan
方法(1) alter session set statistics_level=all;
exec sql;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
方法(2)
exec sql(加入hint /*+ gather_plan_statistics */)
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--最常用的几个参数,操作下便知
allstats last --最后一次统计,不用漏了last,否则就是累加值了;
all --一般看计划,SQL_ID,PLAN_HASH_VALUE
advanced --一般拿来看outline_data信息和绑定变量
--方法(1)示例
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> alter session set statistics_level=all;
Session altered.
SQL> select t1.status,t2.status
2 from t1,t2
3 where t1.object_id=t2.object_id
4 and t1.object_id=99;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 581vna1k8tkk7, child number 0
-------------------------------------
select t1.status,t2.status from t1,t2 where t1.object_id=t2.object_id
and t1.object_id=99
Plan hash value: 2959412835
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 3 | 963K| 963K| 150K (0)|
|* 2 | TABLE ACCESS FULL| T2 | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 3 | TABLE ACCESS FULL| T1 | 0 | 12 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T2"."OBJECT_ID"=99)
3 - filter("T1"."OBJECT_ID"=99)
Note
-----
- dynamic sampling used for this statement (level=2)
27 rows selected.
--方法(2)示例
SQL> select /*+ gather_plan_statistics */t1.status,t2.status
2 from t1,t2
3 where t1.object_id=t2.object_id
4 and t1.object_id=99;
no rows selected
SQL> @allstat--(放在文件中:select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 27qqjb8bsq4vc, child number 0
-------------------------------------
select /*+ gather_plan_statistics */t1.status,t2.status from t1,t2
where t1.object_id=t2.object_id and t1.object_id=99
Plan hash value: 2959412835
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 3 | 963K| 963K| 154K (0)|
|* 2 | TABLE ACCESS FULL| T2 | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 3 | TABLE ACCESS FULL| T1 | 0 | 12 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T2"."OBJECT_ID"=99)
3 - filter("T1"."OBJECT_ID"=99)
Note
-----
- dynamic sampling used for this statement (level=2)
27 rows selected.
--接下来看下执行计划这些参数的含义,直接上图看了;