Checking progress of a large parallel operation

From: Maris Elsins <elmaris_at_gmail.com>
Date: Wed, 12 Mar 2014 15:07:27 +0200
Message-ID: <CABQhObumw-Oc-Jdv7U1zkAWs0KUFx-R190MXpLVkdvxtYbze1g_at_mail.gmail.com>



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
_at_MarisElsins <https://twitter.com/MarisElsins>
www.facebook.com/maris.elsins

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 12 2014 - 14:07:27 CET

Original text of this message