The Top Sessions monitor included in the Oracle Enterprise Manager (OEM) Diagnostic Pack also allows you to see the EXPLAIN PLAN information. Click on the session you wish to view, go to the Cursors tab, and choose the EXPLAIN PLAN button.

The Top Sessions monitor has two limitations, but both can be easily handled. The first is that Top Sessions explicitly looks for SYS.PLAN_TABLE. You must execute the utlxplan.sql script while connected as SYS or INTERNAL. The other limitation is that Top Sessions can only process true DML statements. For example, it cannot process a CREATE TABLE ... AS SELECT statement because it is a DDL (Data Definition Language) statement. You must first create the table, then populate it with an INSERT statement.

For example, the following will not be processed:

CREATE TABLE emp AS SELECT * FROM scott.emp;

But it can be replaced with:

CREATE TABLE emp AS SELECT * FROM scott.emp WHERE 1 = 2;
INSERT INTO emp SELECT * FROM scott.emp;

In this example, note that the WHERE 1 = 2 clause is recognized by the optimizer as ensuring that no rows will match. Thus, it automatically creates the table with no rows and does not perform the query. An alternative to this clause might be WHERE ROWNUM < 0.