声明：脚本来自《Pro Oracle SQL》一书，pln.sql


下面只是一个查看执行计划一种方法，就是通过加上备注表示唯一SQL语句：


[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', at 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 |

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

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

|   1 |  SORT AGGREGATE   |           |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX RANGE SCAN| INX_OWNER |      1 |      2 |      2 |00:00:00.01 |       2 |

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


Predicate Information (identified by operation id):

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


2 - access("OWNER"='LUOCS')



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html