Using dbms_xplan.display_cursor one can see the execution plan of a SQL statement. The function takes following 3 parameters.

SQL_ID (VARCHAR2) – If passed NULL, then it would assume SQL_ID of the last SQL statement
CURSOR_CHILD_NO – If passed NULL, then it would assume child_number of last SQL Statement
FORMAT – Possible values are BASIC, TYPICAL, ALL. The default value in TYPICAL

SQL> select count(1) from table1;

COUNT(1)
———-
32

Show execution plan of last SQL statement
SQL> SELECT *FROM TABLE (DBMS_XPLAN.display_cursor());

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.

Using sql_id, sql_child_number of a SQL statement that was last executed
SQL> select sql_id, sql_child_number from v$session where sid = 130;

SQL_ID SQL_CHILD_NUMBER
————- —————-
5mf7bd0jnctjv 0

SQL> SELECT * FROM TABLE (DBMS_XPLAN.display_cursor(‘dtzn8k4hzc88d’, 0));
SQL_ID dtzn8k4hzc88d, child number 0
————————————-
select count(1) from table1

Plan hash value: 1869552569

——————————————————————–
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
——————————————————————–
| 0 | SELECT STATEMENT | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| I_TABLE1 | 32 | 1 (0)| 00:00:01 |
——————————————————————–

14 rows selected.