Checking progress of a large parallel operation

From: Maris Elsins <elmaris@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Wed, 12 Mar 2014 15:07:27 +0200
Hi,

I'm running a large parallelized MV refresh (complete).
The refresh full-scans a 325G sized table and uses DOP=12.

If it was not a parallelized operation I'd be able to query
v$session_longops to assess the progress of the operation, but in this case
I see that each parallel slave reads a "chunk" from the table sized around
35200 blocks and then starts reading a new chunk.
The table consists of few partitions, and the total number of blocks is
~13.2 million. Based on this there's total of ~380 chunks to be processed,
so each parallel slave will process ~31 chunks.

Is there a way to tell how many chunks have been processed already, how
many chunks there will be and how to assess the progress of the whole
parallel operation.

I've had some success by monitoring the session statistics for the QC and
Slaves, but that can be done only if the task is isolated and not in a
middle of a larger sequence of tasks, I'm looking for something more
reliable.

Some more details about the DB and table (but I don't think it's relevant
as the question is generic.
Version : 11.2.0.3.0

The MV definition:
CREATE MATERIALIZED VIEW MV_TAB01
    ON PREBUILT TABLE
    refresh fast on demand with primary key
    AS SELECT /*+ parallel (a,12) */ ID, COL1 from TAB01 a;

Execution plan is like this:
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)|
Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |       |       |   616K(100)|
         |       |       |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL |          |       |       |            |
         |       |       |        |      |            |
|   2 |   PX COORDINATOR         |          |       |       |            |
         |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |   356M|   325G|   616K  (1)|
01:12:50 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |          |   356M|   325G|   616K  (1)|
01:12:50 |     1 |1048575|  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL   | TAB01    |   356M|   325G|   616K  (1)|
01:12:50 |     1 |1048575|  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------

thanks in advance,
---
Maris Elsins
@MarisElsins <https://twitter.com/MarisElsins>
www.facebook.com/maris.elsins
Checking progress of a large parallel operation

Other related posts:
