Checking progress of a large parallel operation
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-lReceived on Wed Mar 12 2014 - 14:07:27 CET