Re: Big Complex MV creation and Refresh
Date: Sat, 14 Apr 2018 01:25:13 +0000 (UTC)
Message-ID: <1086774103.41358.1523669113386_at_mail.yahoo.com>
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) 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