Database Tuning (MOSC)

MOSC Banner

Why I cannot get execution plan from AWR Repository?

edited Sep 13, 2014 5:00AM in Database Tuning (MOSC) 2 commentsAnswered

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%';

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center