How Does Oracle view the execution plan of an SQL statement based on the dynamic performance view?
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.
,