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@xxxxxxxxxxxxx>:
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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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
