> 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
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%'
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
----------------------------------------------------------------------------------------------------
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 |
| 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博客 ” ，链接：https://blog.itpub.net/27795363/viewspace-743766/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
explain plan 解释计划语法
下一篇：
1-5. 当创建表时，避免区间分配延迟
请登录后发表评论
登录
全部评论
