执行计划2：查看更多的信息

explain plan for {sql statement}
select * from table(dbms_xplan.display);


set serveroutput off
{sql statement}
select * from table(dbms_xplan.display_cursor);

alter session setworkarea_size_policy = manual;
alter session setsort_area_size = 10485760;

alter session setoptimizer_mode = first_rows_10;
alter session set"_hash_join_enabled" = false;

set serveroutput off

variable m_owner varchar2(32)
variable m_object varchar2(32)
execute :m_owner :='TEST_USER'
execute :m_object := 'T1'

select /*+ tracking */ * from t1 
where owner = :m_owner 
and segment_name =:m_object 
order by extent_id;

select * from table(
dbms_xplan.display_cursor(null,null,'outlinepeeked_binds')
);

SQL_ID  0wwbn4bhvrrxj, child number 0
-------------------------------------
select /*+ tracking */  * from t1 where owner = :m_owner and
segment_name = :m_object order by extent_id

Plan hash value: 3684778271

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT ORDER BY               |       |     1 |    65 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    65 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I2 |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."SEGMENT_NAME"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------
   1 - :M_OWNER (VARCHAR2(30), CSID=178): 'TEST_USER'
   2 - :M_OBJECT (VARCHAR2(30), CSID=178): 'T1'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=:M_OWNER)
   3 - access("SEGMENT_NAME"=:M_OBJECT)

column sql_id new_valuem_sql_id
column child_number new_valuem_child_no

select  sql_id,child_number, sql_text 
from   v$sql 
where   sql_textlike '%tracking%' 
and    sql_text not like '%v$sql%'
;

select * from table(
dbms_xplan.display_cursor('&m_sql_id',&m_child_no,'outlinepeeked_binds')
);

select 
name, value 
from 
v$sql_optimizer_env 
where 
sql_id      = '&m_sql_id' 
and child_number =&m_child_no 
and isdefault   = 'NO'
;

NAME                                    VALUE
-----------------------------------------------------------------
hash_area_size                          20971520
sort_area_size                          10485760
optimizer_mode                          first_rows_10
_hash_join_enabled                      false
workarea_size_policy                    manual
_smm_auto_cost_enabled                  false

select * from table(
dbms_xplan.display_awr('9yaqj3djhwxa2',null, null,'outline peeked_binds')
);

executedbms_workload_repository.add_colored_sql({sql_id})
--
-- 输出当前被标记的语句
--
select * fromwrm$_colored_sql;
--
-- 解除语句标记
--
executedbms_workload_repository.remove_colored_sql({sql_id})