Re: Is Parallelism happening at INSERT level?

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Wed, 6 May 2020 08:14:29 +0200
Message-ID: <36e8cc10-5b0d-3732-9613-95b7082d9694_at_bluewin.ch>



Hi Ram,

Yes, your insert is parallel. You can see this because the load as select is before the coordinator step.
P->S means parallel to serial. That is after the insert step, therefore no harm.
I always figured that the parallel slaves send the coordinator a message when they are finished writing, but I have never bothered to dig into it. It can just as well be something else.
If you want to know where you loose your time sql monitor (if licensed) would be much better than an execution plan. When I look at the row estimates I would figure parallel 4 is much too low. I would experiment with higher DOP provided you have the resources.

Regards

Lothar

Am 06.05.2020 um 00:33 schrieb Ram Raman:
> Hi,
> We are trying to tune a query to use parallelism at multiple levels.
> Here is the query along with the plan gotten by using dbms_xplan:
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> SQL_ID  1qrcwrva8jgba, child number 0
> -------------------------------------
> insert /*+ ENABLE_PARALLEL_DML APPEND PARALLEL(4) */ into abcd select
> /*+ parallel(4) */ * from user1.sce where esk in
> (select esk from efgh)
>
> Plan hash value: 1996703413
>
> ----------------------------------------------------------------------------------------------------------------------------------------------
> | Id  | Operation                          | Name       | Rows  |
> Bytes | Cost (%CPU)| Time     | Pstart| Pstop | TQ  |IN-OUT| PQ Distrib |
> ----------------------------------------------------------------------------------------------------------------------------------------------
> |   0 | INSERT STATEMENT                   | |       |       | 
> 2840K(100)|          |       |       |     |      |            |
> |   1 |  PX COORDINATOR                    | |       |       |       
>     |          |       |       |     |      |            |
> |   2 |   PX SEND QC (RANDOM)              | :TQ10000  |    30M|   
> 10G|  2840K  (1)| 00:01:51 |       |       | Q1,00 | P->S | QC (RAND)  |
> |   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| |       |       |       
>     |          |       |       | Q1,00 | PCWP |            |
> |   4 |     OPTIMIZER STATISTICS GATHERING | |    30M|    10G|  2840K 
> (1)| 00:01:51 |       |       | Q1,00 | PCWP |            |
> |*  5 |      HASH JOIN RIGHT SEMI          | |    30M|    10G|  2840K 
> (1)| 00:01:51 |       |       | Q1,00 | PCWP |            |
> |   6 |       TABLE ACCESS FULL            | efgh  |   193K|  1131K| 
>   36   (3)| 00:00:01 |       |       | Q1,00 | PCWP |            |
> |   7 |       PX BLOCK ITERATOR            | |  1547M|   536G|  2839K 
> (1)| 00:01:51 |     1 |    32 | Q1,00 | PCWC |            |
> |*  8 |        TABLE ACCESS FULL           | sce | 1547M|   536G| 
> 2839K  (1)| 00:01:51 |     1 |    32 | Q1,00 | PCWP |            |
> ----------------------------------------------------------------------------------------------------------------------------------------------
> ......
> Note
> -----
>    - dynamic statistics used: dynamic sampling (level=AUTO)
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>    - Degree of Parallelism is 4 because of hint
>
>
> Given that P->S happens in line id 2, is this statement using
> parallelism for the INSERT part of it? I can see a parallelism of 4
> reported at the v$px_session table for this SQL. Should I see 8 rows
> if there is parallelism of 4 for SELECT and INSERT each?
>
> Dont know why it is saying the statement will complete in 1:51. It has
> been running for an hour.  The waits are all on 'direct path
> read/writes'. we have some bad IO, but still finding ways to tune the
> statement. ver is 12c.
> Thanks
> Ram
>
> --
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 06 2020 - 08:14:29 CEST

Original text of this message