Re: Big Complex MV creation and Refresh

  • From: "Sanjay Mishra" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "smishra_97" for DMARC)
  • To: dmarc-noreply@xxxxxxxxxxxxx, Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • Date: Mon, 16 Apr 2018 14:12:52 +0000 (UTC)

 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@xxxxxxxxx> 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@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) 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





  

Other related posts: