Re: Big Complex MV creation and Refresh

  • From: "Sanjay Mishra" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "smishra_97" for DMARC)
  • To: dmarc-noreply@xxxxxxxxxxxxx, Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • Date: Sat, 14 Apr 2018 01:25:13 +0000 (UTC)

 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) tablenNwhere    multiple Join 
Condition






-- 
Best regards,
Sayan MalakshinovOracle performance tuning engineer
Oracle ACE Associate
http://orasql.org  ;

Other related posts: