Re: Big Complex MV creation and Refresh
From: Sanjay Mishra <"Sanjay>
Date: Sat, 14 Apr 2018 01:25:13 +0000 (UTC)
Message-ID: <1086774103.41358.1523669113386_at_mail.yahoo.com>
| 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
Date: Sat, 14 Apr 2018 01:25:13 +0000 (UTC)
Message-ID: <1086774103.41358.1523669113386_at_mail.yahoo.com>
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) tablenNwhere multiple Join Condition
-- Best regards, Sayan MalakshinovOracle performance tuning engineer Oracle ACE Associate http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 14 2018 - 03:25:13 CEST