Re: Big Complex MV creation and Refresh

Hello

A couple of years ago I have tried to solve a performance issue that occurs during the Materialised view refresh and not during its creation. I have summarised this issue, which seems identical to your current problem, in the following blog post:

https://hourim.wordpress.com/2015/04/18/parallel-refreshing-a-materialized-view

As per the parallel run during the MV refresh you can try the suggestion in the last comment of the post which I haven't tested.

Best regards
Mohamed Houri

2018-04-14 2:25 GMT+01:00 Sanjay Mishra <dmarc-noreply_at_freelists.org>:

> Sayan
>
> This view is created for some reporting data and so simple select was done
> to hide the actual details. Plan with MV will be to use for lots more
> purpose required by business
>
> HEre is the Plan and I just hide the name of the table
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------
>
> | Id | Operation | Name
> | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ
> Distrib |
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------
>
> | 0 | SELECT STATEMENT |
> | 618M| 601G| | 38M (1)| 00:25:00 | | |
> |
>
> | 1 | PX COORDINATOR |
> | | | | | | | |
> |
>
> | 2 | PX SEND QC (RANDOM) | :TQ10003
> | 618M| 601G| | 38M (1)| 00:25:00 | Q1,03 | P->S | QC
> (RAND) |
>
> | 3 | BUFFER SORT |
> | 618M| 601G| | | | Q1,03 | PCWP |
> |
>
> | 4 | NESTED LOOPS OUTER |
> | 618M| 601G| | 38M (1)| 00:25:00 | Q1,03 | PCWP |
> |
>
> | 5 | NESTED LOOPS OUTER |
> | 231M| 215G| | 33M (1)| 00:21:39 | Q1,03 | PCWP |
> |
>
> | 6 | NESTED LOOPS OUTER |
> | 192M| 174G| | 30M (1)| 00:19:34 | Q1,03 | PCWP |
> |
>
> | 7 | NESTED LOOPS OUTER |
> | 187M| 165G| | 26M (1)| 00:17:31 | Q1,03 | PCWP |
> |
>
> | 8 | NESTED LOOPS OUTER |
> | 183M| 157G| | 23M (1)| 00:15:32 | Q1,03 | PCWP |
> |
>
> | 9 | NESTED LOOPS OUTER |
> | 182M| 152G| | 19M (1)| 00:12:54 | Q1,03 | PCWP |
> |
>
> | 10 | NESTED LOOPS OUTER |
> | 180M| 148G| | 16M (1)| 00:10:56 | Q1,03 | PCWP |
> |
>
> |* 11 | HASH JOIN OUTER |
> | 170M| 136G| 2115M| 12M (1)| 00:08:28 | Q1,03 | PCWP |
> |
>
> | 12 | JOIN FILTER CREATE | :BF0000
> | 170M| 18G| | 7584K (1)| 00:04:57 | Q1,03 | PCWP |
> |
>
> | 13 | PX RECEIVE |
> | 170M| 18G| | 7584K (1)| 00:04:57 | Q1,03 | PCWP |
> |
>
> | 14 | PX SEND HASH | :TQ10001
> | 170M| 18G| | 7584K (1)| 00:04:57 | Q1,01 | P->P |
> HASH |
>
> | 15 | NESTED LOOPS OUTER |
> | 170M| 18G| | 7584K (1)| 00:04:57 | Q1,01 | PCWP |
> |
>
> | 16 | NESTED LOOPS OUTER |
> | 160M| 14G| | 4018K (1)| 00:02:37 | Q1,01 | PCWP |
> |
>
> | 17 | NESTED LOOPS |
> | 159M| 10G| | 464K (1)| 00:00:19 | Q1,01 | PCWP |
> |
>
> | 18 | PX BLOCK ITERATOR |
> | | | | | | Q1,01 | PCWC |
> |
>
> |* 19 | TABLE ACCESS STORAGE FULL FIRST ROWS| TABLE1
> | 10 | 340 | | 2 (0)| 00:00:01 | Q1,01 | PCWP |
> |
>
> | 20 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE2
> | 15M| 579M| | 46450 (1)| 00:00:02 | Q1,01 | PCWP |
> |
>
> |* 21 | INDEX RANGE SCAN | TABLE20
> | 15M| | | 465 (1)| 00:00:01 | Q1,01 | PCWP |
> |
>
> |* 22 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE3
> | 1 | 23 | | 0 (0)| 00:00:01 | Q1,01 | PCWP |
> |
>
> |* 23 | INDEX RANGE SCAN | TABLE31
> | 1 | | | 0 (0)| 00:00:01 | Q1,01 | PCWP |
> |
>
> |* 24 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE4
> | 1 | 23 | | 0 (0)| 00:00:01 | Q1,01 | PCWP |
> |
>
> |* 25 | INDEX RANGE SCAN | TABLE41
> | 1 | | | 0 (0)| 00:00:01 | Q1,01 | PCWP |
> |
>
> | 26 | PX RECEIVE |
> | 554M| 381G| | 3327K (3)| 00:02:10 | Q1,03 | PCWP |
> |
>
> | 27 | PX SEND HASH | :TQ10002
> | 554M| 381G| | 3327K (3)| 00:02:10 | Q1,02 | P->P |
> HASH |
>
> | 28 | JOIN FILTER USE | :BF0000
> | 554M| 381G| | 3327K (3)| 00:02:10 | Q1,02 | PCWP |
> |
>
> | 29 | VIEW |
> | 554M| 381G| | 3327K (3)| 00:02:10 | Q1,02 | PCWP |
> |
>
> | 30 | HASH GROUP BY |
> | 554M| 10G| 175G| 3327K (3)| 00:02:10 | Q1,02 | PCWP |
> |
>
> | 31 | PX RECEIVE |
> | 554M| 10G| | 3327K (3)| 00:02:10 | Q1,02 | PCWP |
> |
>
> | 32 | PX SEND HASH | :TQ10000
> | 554M| 10G| | 3327K (3)| 00:02:10 | Q1,00 | P->P |
> HASH |
>
> | 33 | HASH GROUP BY |
> | 554M| 10G| 175G| 3327K (3)| 00:02:10 | Q1,00 | PCWP |
> |
>
> | 34 | PX BLOCK ITERATOR |
> | 5212M| 101G| | 147K (17)| 00:00:06 | Q1,00 | PCWC |
> |
>
> |* 35 | TABLE ACCESS STORAGE FULL | TABLE5
> | 5212M| 101G| | 147K (17)| 00:00:06 | Q1,00 | PCWP |
> |
>
> |* 36 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE6
> | 1 | 26 | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 37 | INDEX RANGE SCAN | TABLE61
> | 1 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 38 | INDEX RANGE SCAN |
> TABLE71_CM | 1 | 14 | | 0 (0)| 00:00:01 | Q1,03 |
> PCWP | |
>
> |* 39 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE8
> | 1 | 26 | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 40 | INDEX RANGE SCAN | TABLE81
> | 2 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 41 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE8
> | 1 | 26 | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 42 | INDEX RANGE SCAN | TABLE81
> | 2 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 43 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE8
> | 1 | 26 | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 44 | INDEX RANGE SCAN | TABLE81
> | 2 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 45 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE8
> | 1 | 26 | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 46 | INDEX RANGE SCAN | TABLE81
> | 2 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 47 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE9
> | 3 | 126 | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> | 48 | SORT CLUSTER BY ROWID BATCHED |
> | 3 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> |* 49 | INDEX RANGE SCAN | TABLE91
> | 3 | | | 0 (0)| 00:00:01 | Q1,03 | PCWP |
> |
>
> ------------------------------------------------------------
> ------------------------------------------------------------
> ---------------------------------------
>
> Tx for your time and any suggestion.
>
> Sanjay
>
>
> On Friday, April 13, 2018, 7:53:05 PM EDT, Sayan Malakshinov <
> xt.and.r_at_gmail.com> wrote:
>
>
> Hi Sanjay,
>
> How is this view used usually?
> Could you show typical queries with their execution plans and explain plan
> for "select/*+ dynamic_sampling(8) */ * from View"?
>
>
>
> On Sat, Apr 14, 2018 at 2:35 AM, Sanjay Mishra <
> dmarc-noreply_at_freelists.org> wrote:
>
>
> Select Multiple Columns
> from
> (select * from table where condition) table1
> (select * from table where condition) table2
> (select * from table where condition) table3
>
> ....
> (select * from table where condition) tablenN
> where
> multiple Join Condition
>
>
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>


--
http://www.freelists.org/webpage/oracle-l