We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN
create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user, 'T1')
explain plan for
select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y
where x.object_name = y.object_name and x.owner != y.owner
Explain plan writes in the PLAN_TABLE and could be displayed with
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
--------------------------------
Plan hash value: 2344570521
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18287 | 3500K|
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 18287 | 3500K|
|* 3 | HASH JOIN | | 18287 | 3500K|
| 4 | PX RECEIVE | | 19219 | 1839K|
| 5 | PX SEND HYBRID HASH | :TQ10001 | 19219 | 1839K|
| 6 | STATISTICS COLLECTOR | | | |
| 7 | PX BLOCK ITERATOR | | 19219 | 1839K|
| 8 | TABLE ACCESS FULL | T1 | 19219 | 1839K|
| 9 | BUFFER SORT | | | |
| 10 | PX RECEIVE | | 19219 | 1839K|
| 11 | PX SEND HYBRID HASH | :TQ10000 | 19219 | 1839K|
| 12 | PARTITION HASH ALL | | 19219 | 1839K|
| 13 | TABLE ACCESS FULL | T1 | 19219 | 1839K|
---------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"."OBJECT_NAME"="Y"."OBJECT_NAME")
filter("X"."OWNER"<>"Y"."OWNER")
But if you want to display progress on a currently running query, use DBMS_SQLTUNE (or Oracle Enterprise Manager SQL Monitoring):
SQL> set lin 150 longc 150 long 1000000;
SQL> select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_plan_hash_value
=>2344570521) from dual;
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521)
----------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ PARALLEL(x, 4) */ * from t1 x, t1 y where
x.object_name = y.object_name and x.owner != y.owner
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SCOTT (25:10369)
SQL ID : 0dpj0fxm2gf81
SQL Execution ID : 16777216
Execution Started : 08/12/2013 14:48:26
First Refresh Time : 08/12/2013 14:48:26
Last Refresh Time : 08/12/2013 14:48:59
Duration : 34s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus.exe
Fetch Calls : 19
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_PLAN_HASH_VALUE=>2344570521)
----------------------------------------------------------------
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.25 | 0.13 | 0.12 | 19 | 752 |
=================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
========================================================
| Name | Type | Server# | Elapsed | Cpu |
| | | | Time(s) | Time(s) |
========================================================
| PX Coordinator | QC | | 0.08 | 0.03 |
| p000 | Set 1 | 1 | 0.03 | 0.02 |
| p001 | Set 1 | 2 | 0.03 | 0.02 |
| p002 | Set 1 | 3 | | |
| p003 | Set 1 | 4 | 0.03 | 0.02 |
| p004 | Set 2 | 1 | 0.02 | 0.01 |
| p005 | Set 2 | 2 | 0.03 | 0.02 |
| p006 | Set 2 | 3 | 0.03 | 0.00 |
| p007 | Set 2 | 4 | 0.02 | 0.00 |
========================================================
SQL Plan Monitoring Details (Plan Hash Value=2344570521)
==================================================================
| Id | Operation | Rows | Cost | Rows |
| | | (Estim) | | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT | | | 271 |
| -> 1 | PX COORDINATOR | | | 271 |
| 2 | PX SEND QC (RANDOM) | 18287 | 202 | 615 |
| 3 | HASH JOIN | 18287 | 202 | 538 |
| 4 | PX RECEIVE | 19219 | 44 | 14481 |
| 5 | PX SEND HYBRID HASH | 19219 | 44 | 19219 |
| 6 | STATISTICS COLLECTOR | | | 19219 |
| 7 | PX BLOCK ITERATOR | 19219 | 44 | 19219 |
| 8 | TABLE ACCESS FULL | 19219 | 44 | 19219 |
| 9 | BUFFER SORT | | | 1316 |
| 10 | PX RECEIVE | 19219 | 158 | 14481 |
| 11 | PX SEND HYBRID HASH | 19219 | 158 | 19219 |
| 12 | PARTITION HASH ALL | 19219 | 158 | 19219 |
| 13 | TABLE ACCESS FULL | 19219 | 158 | 19219 |
==================================================================
The small -> sign shows you where it is and display some actual (not estimates) info.
If I run it again :
==================================================================
| Id | Operation | Rows | Cost | Rows |
| | | (Estim) | | (Actual) |
==================================================================
| -> 0 | SELECT STATEMENT | | | 6451 |
For the same query, we see some progress (6451 rows now).
Check you have licensed the appropriate tuning options before using DBMS_SQLTUNE