Re: Big Complex MV creation and Refresh
Date: Mon, 16 Apr 2018 14:12:52 +0000 (UTC)
Message-ID: <384108488.511529.1523887972027_at_mail.yahoo.com>
Mohamed
I read your article and it was very good. I had crated View with Main Select /*+Parallel hint and then MV with Parallel has been created MV in 1.5hr. Refresh is major issue as tested with explicitly parallel argument as well as without Parallel, it took same 5hr to refresh. I tried to have View on which MV is ceated with view definition also added paralle to all involved tables as I cannot explictily made changed to table degree. But Refresh is taking 3-4 times. Any suggestion as what can be the reason. Will create trace also today to check where it is taking time.
Sanjay
On Saturday, April 14, 2018, 10:44:44 AM EDT, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:
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 regardsMohamed 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 |
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------|* 11 | HASH JOIN OUTER | | 170M| 136G| 2115M| 12M (1)| 00:08:28 | Q1,03 | PCWP | |
| 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 | |
| 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) tablenNwhere multiple Join Condition
-- Best regards, Sayan MalakshinovOracle performance tuning engineer Oracle ACE Associate http://orasql.org -- Houri Mohamed OracleDBA-Developer-Performance & Tuning Memberof Oraworld-team Visit My - Blog Let'sConnect - LinkedinProfile My Twitter - MohamedHouri -- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 16 2018 - 16:12:52 CEST