Why I cannot get execution plan from AWR Repository?
SQL "select * from table(dbms_xplan.display_awr('sql_id')); can query the execution plan in AWR Repository.
My command is:
SQL> select count(*) from dual;
COUNT(*)
----------
1
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select count(*) from dual%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
------------------------------------ ----------------------------------- --------------------------- --------------------------------
select count(*) from dual 4m94ckmu16f9k 1 1
create snapshot for AWR:
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
flush cache:
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select count(*) from dual%';