Execution plan

1)  Query recent executed SQL for user from V$SQL

SQL>col sql_text format a50 truncate

SQL>select /* recentsql */ sql_id, child_number, hash_value, address, executions, sql_text
  2  from v$sql
  3  where parsing_user_id = (
  4  select user id from all_users where username = 'testuser')
  5  and command_type in (2,3,6,7,189)
  6  and UPPER(sql_text) not like UPPER('%recentsql%');

SQL_ID        CHILD_NUMBER HASH_VALUE ADDRESS          EXECUTIONS SQL_TEXT
------------- ------------ ---------- ---------------- ---------- --------------------------------------------------
0af82jbk8n805            0 3834257413 000007FFBE35D878          2 select /* DataTypeTest */ * from regions2 where to
dyk4dprp70d74            0 3933222116 000007FFBE6FE450          8 SELECT DECODE('A','A','1','2') FROM DUAL
2kwv6k885cf9a            0  274086186 000007FFB5BE8340          1 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER
atcxc7b9jsjtg                0 3541845807 000007FFB5BBD438          1 SELECT USERENV('SESSIONID') FROM DUAL
...

2) Query the plan for SQL statement  already executed and cached in the memory.

SQL> select / *+ gather_plan_statistics */ * from regions2; -- Tell the Oracle to collect statistics while executing the statement.

REGION_ID  REGION_NAME
---------- -------------------------
1          a
2          b

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

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

SQL_ID  96fchstu6ysrs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from regions2

Plan hash value: 670750275

----------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| REGIONS2 |      1 |      2 |      2 |00:00:00.01 |       8 |
----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


16 rows selected.

4) Statistics views/table: V$SQL_PLAN, V$SQL_PLAN_STATISTICS, V$SQL_PLAN_STATISTICS_ALL, PLAN_TABLE

5) Display functions for DBMS_XPLAN: DISPLAY, DISPLAY_CURSOR, DISPLAY_SQLSET, DISPLAY_SQL_PLAN_BASELINE

6) Identify SQL Statement for later plan retrival.

SQL> select /* MARK_STATEMENT */ * from regions2;

REGION_ID  REGION_NAME
---------- -------------------------
1          a
2          b

SQL> select sql_id, child_number, sql_text
  2  from v$sql
  3  where sql_text like '%MARK_STATEMENT%'
  4  /

SQL_ID          CHILD_NUMBER SQL_TEXT
-------------      ------------                   --------------------------------------------------
1znp4apfaxx8g          0                   select /* MARK_STATEMENT */ * from regions2
1nrj9fvvcqc4u            0                   select sql_id, child_number, sql_text from v$sql w

SQL> select * from table(dbms_xplan.display_cursor('1znp4apfaxx8g', 0, 'ALLSTATS LAST'));

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

SQL_ID  1znp4apfaxx8g, child number 0
-------------------------------------
select /* MARK_STATEMENT */ * from regions2

Plan hash value: 670750275

-----------------------------------------------
| Id  | Operation         | Name     | E-Rows |
-----------------------------------------------
|   1 |  TABLE ACCESS FULL| REGIONS2 |      2 |
-----------------------------------------------

Note
-----
   - dynamic sampling used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


19 rows selected.