查看某个select语句的实际执行计划（actual execution plan）

> conn hr/hr

> select xplan.*
  2  from
  3     (
  4     select max(sql_id) keep
  5            (dense_rank last order by last_active_time) sql_id,
  6            max(child_number) keep
  7            (dense_rank last order by last_active_time) child_number
  8     from v$sql
  9     where upper(sql_text) like '%&1%'
 10     and upper(sql_text) not like '%FROM V$SQL WHERE UPPER(SQL_TEXT) LIKE%'
 11     ) sqlinfo,
 12*    table(dbms_xplan.display_cursor(sqlinfo.sql_id,sqlinfo.child_number,'ALLSTATS LAST'))xplan
> save pln.sql

>

> select /* opt1 */ * from test;

结果省略

 

> @pln opt1
old   9:    where upper(sql_text) like '%&1%'
new   9:    where upper(sql_text) like '%opt1%'

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  db7cthr67ka6g, child number 0
-------------------------------------
select /* opt1 */ * from test

Plan hash value: 1357081020

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.03 |       8 |      6 |
|   1 |  TABLE ACCESS FULL| TEST |      1 |      1 |      1 |00:00:00.03 |       8 |      6 |

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

13 rows selected.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27795363/viewspace-743766/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27795363/viewspace-743766/