Viewing execution plans with Oracle scripts

Disclaimer: Script from Pro Oracle SQL, Pln.sql

Here's just one way to view the execution plan, which is to represent a unique SQL statement by adding a note:

[Oracle@maa3 ~]$ Cat Pln.sql

SELECT xplan.*

From

(

Select Max (sql_id) keep

(Dense_rank last order by Last_active_time) sql_id

, Max (Child_number) keep

(Dense_rank last order by Last_active_time) Child_number

From V$sql

where Upper (Sql_text) like '%&1% '

and upper (Sql_text) Not like "%from v$sql WHERE Upper (sql_text) like% '

) Sqlinfo,

Table (Dbms_xplan. Display_cursor (sqlinfo.sql_id, Sqlinfo.child_number, ' Allstats last ') Xplan

/

Luocs@maa> @pln AAA

Plan_table_output

----------------------------------------------------------------------------------------------------

sql_id A151A11P17S10, child number 0

-------------------------------------

Select/* AAA */COUNT (OWNER) from T1 where owner= ' SYS '

Plan Hash value:1245464496

----------------------------------------------------

| Id | Operation | Name | E-rows |

----------------------------------------------------

| 0 |           SELECT STATEMENT |        | |

|  1 |           SORT AGGREGATE |      | 1 |

|* 2 | INDEX FAST Full scan|  Inx_owner | 19134 |

----------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter ("OWNER" = ' SYS ')

Note

-----

-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 ', in session or system level

Sys@maa> alter system flush Shared_pool;

System altered.

Luocs@maa> alter session set Statistics_level=all;

Session altered.

Luocs@maa> Select/* AAA */COUNT (OWNER) from T1 where owner= ' SYS ';

COUNT (OWNER)

---------------

19134

Luocs@maa> Select/* www.luocs.com */COUNT (OWNER) from T1 where owner= ' Luocs ';

COUNT (OWNER)

---------------

2

Luocs@maa> @pln AAA

Plan_table_output

----------------------------------------------------------------------------------------------------

sql_id A151A11P17S10, child number 0

-------------------------------------

Select/* AAA */COUNT (OWNER) from T1 where owner= ' SYS '

Plan Hash value:1245464496

---------------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers |

---------------------------------------------------------------------------------------------

| 0 |           SELECT STATEMENT |      |        1 |      |     1 |00:00:00.01 | 116 |

|  1 |           SORT AGGREGATE |      |      1 |      1 |     1 |00:00:00.01 | 116 |

|* 2 | INDEX FAST Full scan|      Inx_owner |  1 |  19134 |     19134 |00:00:00.01 | 116 |

---------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

2-filter ("OWNER" = ' SYS ')

Luocs@maa> @pln WWW.LUOCS.COM

Plan_table_output

----------------------------------------------------------------------------------------------------

sql_id FCZADM51F27C4, child number 0

-------------------------------------

Select/* www.luocs.com */COUNT (OWNER) from T1 where owner= ' Luocs '

Plan Hash value:3047016978

-----------------------------------------------------------------------------------------

| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers |

----------------------------------------------------------