sql> set serveroutput off --一定要關
sql> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
plan_table_output
------------------------------------------------------------------------------------------------------------------------------------------------------
sql_id 5qgz1p0cut7mx, child number 0
begin dbms_output.disable; end;
note: cannot fetch plan for sql_id: 5qgz1p0cut7mx, child_number: 0
please verify value of sql_id and child_number;
it could also be that the plan is no longer in cursor cache (check v$sql_plan)
已選擇8行。
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
sql_id 7xdg8akqchn1t, child number 1
-------------------------------------
select * from table(dbms_xplan.display_cursor(null,null,'advanced'))
plan hash value: 3713220770
---------------------------------------------------------------------
| id | operation | name | e-rows |
| 0 | select statement | | |
| 1 | collection iterator pickler fetch| display_cursor | 16 |
note
-----
- cardinality feedback used for this statement
- 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
已選擇20行。
sql> select count(*) from t1 where object_id between :x and :y;
count(*)
----------
145152
sql> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
sql_id 1ynfbm8a0p98u, child number 0
select count(*) from t1 where object_id between :x and :y
plan hash value: 1410530761
---------------------------------------------------------------------------------
| id | operation | name | rows | bytes | cost (%cpu)| time |
| 0 | select statement | | | | 91 (100)| |
| 1 | sort aggregate | | 1 | 5 | | |
|* 2 | filter | | | | | |
|* 3 | index fast full scan| idx_t1 | 145k| 708k| 91 (3)| 00:00:02 |
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - sel$1
3 - sel$1 / t1@sel$1
outline data
-------------
/*+
begin_outline_data
ignore_optim_embedded_hints
optimizer_features_enable('11.2.0.1')
db_version('11.2.0.1')
all_rows
outline_leaf(@"sel$1")
index_ffs(@"sel$1" "t1"@"sel$1" ("t1"."object_id"))
end_outline_data
*/
peeked binds (identified by position):
--------------------------------------
1 - :x (number): 0
2 - :y (number): 100000
predicate information (identified by operation id):
---------------------------------------------------
2 - filter(:x<=:y)
3 - filter(("object_id">=:x and "object_id"<=:y))
column projection information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) count(*)[22]
已選擇52行。