Oracle如何查看SQL实际执行计划

1、 查看最近执行的SQL语句

select /*recentsql*/s.SQL_ID,s.CHILD_NUMBER,s.HASH_VALUE,s.ADDRESS,s.EXECUTIONS,s.SQL_TEXT
    from v$sql s
where s.PARSING_USER_ID = (
    select u.user_id from all_users u
        where u.username = 'YH_TEST'
) and s.COMMAND_TYPE in (2 ,3, 6,7 ,189)
and upper(s.SQL_TEXT) not like upper( '%recentsql%')

2、使用dbms_xplan.display_cursor查看执行计划，它的用法见笔记 《dbms_xplan.display_cursor的用法》,

注意了：若dbms_xplan.display_cursor要以ALLSTATS LAST格式输出的话，/*+gather_plan_statistics*/这个提示信息放到查询语句中是必须的。

select /*+gather_plan_statistics*/ /*plan_statistics1*/ name ,salary from test where name = 't1' ;

select s.SQL_ID,s.CHILD_NUMBER,s.HASH_VALUE,s.ADDRESS,s.EXECUTIONS,s.SQL_TEXT
    from v$sql s
where upper(s.SQL_TEXT) like upper('%plan_statistics1%' )
      and upper(s.SQL_TEXT) not like upper( '%v$sql%');

select * from table (dbms_xplan.display_cursor('4wktu80k1xy5k' , 0, 'ALLSTATS LAST cost' ));