|
PTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--执行计划3：real执行计划
SQL> set serveroutput off
SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;
Session altered.
Elapsed: 00:00:00.00
SQL> select a.*,(select name from b where b.id=a.id) from a;
ID NAME (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
5 rows selected.
Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'
Enter value for plan type:starts --这里输入starts
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID 8rv825dykpx1m, child number 0
-------------------------------------
select a.*,(select name from b where b.id=a.id) from a
Plan hash value: 3653839899
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| B | 5 | 1 | 2 |00:00:00.01 | 35 |
| 2 | TABLE ACCESS FULL| A | 1 | 5 | 5 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ID"=:B1)
Note
-----
- dynamic sampling used for this statement
--注意：
--第3种执行计划不能多次获取，只能执行1次，获取一次，否则会获取不到
下面再次获取一下试试：
SQL> @getplan
'general,outline,starts'
Enter value for plan type:starts
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID dvp8nn63wuhs8, child number 0
-------------------------------------
select * from table(dbms_xplan.display(null, null,'advanced -projection'))
where 'starts'='outline'
Plan hash value: 3440229843
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | 0 |00:00:00.01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 0 | 0 |00:00:00.01 | --第二次无法获取真实的执行计划
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
Note
-----
- rule based optimizer used (consider using cbo)
--第3种执行计划要关掉set serveroutput off，否则也不能获取执行计划。
测试如下：
这里和上面的测试是同一个会话，所以没有再执行ALTER SESSION SET STATISTICS_LEVEL=ALL;了。
SQL> set serveroutput on
SQL> select a.*,(select name from b where b.id=a.id) from a;
ID NAME (SELECTNAM
---------- ---------- ----------
1 a1 b1
2 a2 b2
3 a3
4 a4
5 a5
5 rows selected.
Elapsed: 00:00:00.03
SQL> @getplan
'general,outline,starts'
Enter value for plan type:starts
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 9babjv8yq8ru3, child number 0
BEGIN DBMS_OUTP |