How Does Oracle view the execution plan of an SQL statement based on the dynamic performance view?

1. Authorize a user to view sys. v $ SQL _plan

2. Create a plan_table-like View

SQL> create or replace view dynamic_plan_table
2
3 select rawtohex (address) | '_' | child_number statement_id,
4 sysdate timestamp, operation, options, object_node,
5 object_owner, object_name, 0 object_instance,
6 optimizer, search_columns, id, parent_id, position,
7 cost, cardinality, bytes, other_tag, partition_start,
8 partition_stop, partition_id, other, distribution,
9 cpu_cost, io_cost, temp_space, access_predicates,
10 filter_predicates
11 from v $ SQL _plan;

The view has been created.

3. view the execution plan

SQL> select plan_table_output
2 from table (dbms_xplan.display
3 ('dynamic _ plan_table ',
4 (select rawtohex (address) | '_' | child_number x
5 from v $ SQL where SQL _text =
6 'select * from user_tables '),
7 'serial '))
8/

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |
------------------------------------------------------------------------------
| 0 | select statement | 703 (100) |
| * 1 | hash join | 2513 | 5700K | 703 (7) |
| 2 | fixed table full | X $ KSPPCV | 100 | 196K | 0 (0) |
| 3 | merge join cartesian | 2513 | 755K | 703 (7) |
| * 4 | hash join outer | 2513 | 620K | 661 (1) |
| * 5 | hash join right outer | 2513 | 547K | 473 (2) |
| 6 | table access full | USER $ | 84 | 1428 | 3 (0) |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| * 7 | hash join outer | 2513 | 505K | 470 (2) |
| * 8 | hash join | 2513 | 485K | 415 (1) |
| 9 | table access full | TS $ | 7 | 133 | 4 (0) |
| * 10 | hash join outer | 2513 | 439K | 410 (1) |
| 11 | nested loops | 2485 | 352 | (1) |
| * 12 | table access full | OBJ $ | 3142 | 110K | 236 (2) |
| * 13 | table access cluster | TAB $ | 1 | 98 | 1 (0) |
| * 14 | index unique scan | I _OBJ # | 1 | 0 (0) |
| 15 | table access full | SEG $ | 7092 | 311K | 58 (0) |
| 16 | index fast full scan | I _OBJ1 | 69116 | 539K | 54 (0) |
| 17 | index fast full scan | I _OBJ2 | 69116 | 2024K | 187 (0) |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 18 | buffer sort | 1 | 55 | 516 (10) |
| * 19 | fixed table full | X $ KSPPI | 1 | 55 | 0 (0) |
------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

1-access ("KSPPI". "INDX" = "KSPPCV". "INDX ")
4-access ("T". "BOBJ #" = "CO". "OBJ #")
5-access ("CX". "OWNER #" = "CU". "USER #")
7-access ("T". "DATAOBJ #" = "CX". "OBJ #")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
8-access ("T". "TS #" = "TS". "TS #")
10-access ("T". "FILE #" = "S". "FILE #" AND "T". "BLOCK #" = "S". "BLOCK #" AND
"T". "TS #" = "S". "TS #")
12-filter ("O". "OWNER #" = USERENV ('schemaid') AND
BITAND ("O". "FLAGS", 128) = 0 ))
13-filter (BITAND ("T". "PROPERTY", 1) = 0)
14-access ("O". "OBJ #" = "T". "OBJ #")
19-filter ("KSPPI". "KSPPINM" = '_ dml_monitoring_enabled ')

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-'Dynamic _ PLAN_TABLE 'is old version

45 rows have been selected.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12