Oracle UPDATE performance issue and optimization

Let's take a query I had a lot of problems with.

The table *CONF_ELEMENTI_FATTURABILI* is 160 Gb large and is partitioned on the edw_partition field in 2587 partitions (but parallelism does not work in the second query). *BCK_MEM_ANIMALI_F_ROPT_ELF* and *INT_TEMP51* are very similar. They have indexes and contains 90k record (the second is the GROUP BY of the first one). The update is based on the PK. So exactly 90k records are updated.

In 30' the following update query does not end. How can I understand if it's working or not. The parallel execution doesn't work (all thread except one were idle). Why? Should I force some FULL table / index scan?

Other details for CONF_ELEMENTI_FATTURABILI:
Size: 161419,19 MB
Number Extents: 140964
NUM_ROWS: 262000000 (260 million)
BLOCKS: 10241238
PARTITIONED: YES - 2587 partitions

FIRST FORMULATION

alter session enable parallel dml;

UPDATE  
    CONF_ELEMENTI_FATTURABILI elf
   SET   ELF_ELEMENTO_CHIUSO = 'C',
         ELF_DATA_VER_FIN =
            (  SELECT   TELE_DATA_LETTURA
                 FROM   NETATEMP.int_temp51 t
                WHERE   t.ELF_STORICO_ID = elf.ELF_STORICO_ID
                AND t.edw_partition = elf.edw_partition)
WHERE   exists (SELECT  1 FROM netatemp.bck_mem_animali_f_ropt_elf kk WHERE   kk.ELF_STORICO_ID = elf.ELF_STORICO_ID
                AND kk.edw_partition = elf.edw_partition);

Explain plan:

Plan
UPDATE STATEMENT  ALL_ROWSCost: 87,99  Bytes: 6.269.021  Cardinality: 85,877                
    8 UPDATE SIUCONTRATTI.CONF_ELEMENTI_FATTURABILI             
        5 NESTED LOOPS  Cost: 87,99  Bytes: 6.269.021  Cardinality: 85,877          
            2 SORT UNIQUE  Cost: 67  Bytes: 1.975.171  Cardinality: 85,877      
                1 INDEX FAST FULL SCAN INDEX SIUINTEGRA.MEMBR_4 Cost: 67  Bytes: 1.975.171  Cardinality: 85,877  
            4 PARTITION LIST ITERATOR  Cost: 2  Bytes: 50  Cardinality: 1  Partition #: 5   
                3 INDEX RANGE SCAN INDEX (UNIQUE) SIUCONTRATTI.CONF_ELF_UK_IDX1 Access Predicates: "KK"."ELF_STORICO_ID"="ELF"."ELF_STORICO_ID" AND "KK"."EDW_PARTITION"="ELF"."EDW_PARTITION"  Cost: 2  Bytes: 50  Cardinality: 1  Partition #: 5  
        7 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.INT_TEMP51 Cost: 69  Bytes: 256  Cardinality: 8          
            6 INDEX RANGE SCAN INDEX SIUINTEGRA.MEMBR_6 Access Predicates: "T"."ELF_STORICO_ID"=:B1  Cost: 2  Cardinality: 333

SECOND FORMULATION

UPDATE  
    CONF_ELEMENTI_FATTURABILI elf
   SET   ELF_ELEMENTO_CHIUSO = 'C',
         ELF_DATA_VER_FIN =
            (  SELECT   TELE_DATA_LETTURA
                 FROM   int_temp51 t
                WHERE   t.ELF_STORICO_ID = elf.ELF_STORICO_ID
                AND t.edw_partition = elf.edw_partition)
 WHERE   (ELF_STORICO_ID, edw_partition) IN (SELECT /*+ full(kk) parallel(kk, 20) */  ELF_STORICO_ID, edw_partition FROM bck_mem_animali_f_ropt_elf kk);

Explain plan:

Plan
UPDATE STATEMENT  ALL_ROWSCost: 7,168  Bytes: 6.269.021  Cardinality: 85,877                                    
    13 UPDATE SIUCONTRATTI.CONF_ELEMENTI_FATTURABILI                                
        10 PX COORDINATOR                           
            9 PX SEND QC (RANDOM) SYS.:TQ10001 Cost: 7,168  Bytes: 6.269.021  Cardinality: 85,877                       
                8 NESTED LOOPS  Cost: 7,168  Bytes: 6.269.021  Cardinality: 85,877                      
                    5 SORT UNIQUE  Cost: 7  Bytes: 1.975.171  Cardinality: 85,877               
                        4 PX RECEIVE  Cost: 7  Bytes: 1.975.171  Cardinality: 85,877            
                            3 PX SEND HASH SYS.:TQ10000 Cost: 7  Bytes: 1.975.171  Cardinality: 85,877          
                                2 PX BLOCK ITERATOR  Cost: 7  Bytes: 1.975.171  Cardinality: 85,877     
                                    1 TABLE ACCESS FULL TABLE SIUINTEGRA.BCK_MEM_ANIMALI_F_ROPT_ELF Cost: 7  Bytes: 1.975.171  Cardinality: 85,877  
                    7 PARTITION LIST ITERATOR  Cost: 2  Bytes: 50  Cardinality: 1  Partition #: 10                  
                        6 INDEX RANGE SCAN INDEX (UNIQUE) SIUCONTRATTI.CONF_ELF_UK_IDX1 Access Predicates: "ELF_STORICO_ID"="ELF_STORICO_ID" AND "EDW_PARTITION"="EDW_PARTITION"  Cost: 2  Bytes: 50  Cardinality: 1  Partition #: 10           
        12 TABLE ACCESS BY INDEX ROWID TABLE SIUINTEGRA.INT_TEMP51 Cost: 69  Bytes: 256  Cardinality: 8                             
            11 INDEX RANGE SCAN INDEX SIUINTEGRA.MEMBR_6 Access Predicates: "T"."ELF_STORICO_ID"=:B1  Cost: 2  Cardinality: 333

Just ONE of the thread works!!!

The waits:

ROWNUM   SID    EVENT                         TOTAL_WAITS  TOTAL_TIMEOUTS  TIME_WAITED  AVERAGE_WAIT  MAX_WAIT  TIME_WAITED_MICRO                      
1        1012   latch: cache buffers chains             8               0            0          0            0                270    
2        1012   read by other session                 366               0          141          0,38         3            1407428
3        1012   db file sequential read              1287               0          772          0,6          4            7718838
7        1012   events in waitclass Other              40              38         7613        190,33       195           76130586                               
5        1012   PX Deq: Execution Msg                   3               0            8          2,61         4              78312  
6        1012   PX Deq: Table Q Normal                  2               0            1          0,34         1               6763   
4        1012   cursor: pin S wait on X                 1               1            1          0,98         1               9829   
8        1114   latch: cache buffers chains             3               0            0          0            0                 43     
9        1114   read by other session                 381               0          131          0,34         4            1308282
10       1114   db file sequential read              1416               0          841          0,59         4            8410582
11       1114   cursor: pin S wait on X                 1               1            1          0,98         1               9793   
12       1114   PX Deq: Execution Msg                   3               0            8          2,65         4              79609  
13       1114   PX Deq: Table Q Normal                  2               0            1          0,37         1               7441   
14       1114   events in waitclass Other              41              38         7576        184,78       195           75758618                               
15       1154   latch: cache buffers chains             8               0            0          0            0                163    
16       1154   read by other session                 368               0          141          0,38         3            1412020
17       1154   db file sequential read              1819               0         1156          0,64         3           11555685                               
18       1154   cursor: pin S wait on X                 1               1            1          0,98         1               9833   
19       1154   PX Deq: Execution Msg                   3               0            8          2,66         4              79754  
20       1154   PX Deq: Table Q Normal                  2               0            1          0,36         1               7147   
21       1154   events in waitclass Other              38              35         7113        187,19       195           71130875                               
22       1398   latch: cache buffers chains             4               0            0

The IO

SID    BLOCK_GETS  CONSISTENT_GETS  PHYSICAL_READS  BLOCK_CHANGES   CONSISTENT_CHANGES                     
1012            0             9006            1805              0                    0      
1114            0             9999            2393              0                    0      
1154            0            10056            2668              0                    0      
1398            0            10007            1917              0                    0      
1597            0            10058            2046              0                    0      
1649            0            10074            2261              0                    0      
2279        92557         21996787        11908995         170829                  206    
2322            0            10026            1863              0                    0      
2460            0            10082            1927              0                    0      
2479            0             9978            2006              0                    0      
2694            0             9996            2462              0                    0      
2800            0             8641            1767              0                    0      
2829            0            10003            1855              0                    0      
2840            0            10049            2280              0                    0      
2888            0            10038            2574              0                    0      
2998            0             9993            1995              0                    0      
3147            0             8607            1569              0                    0      
3186            0            10021            2478              0                    0      
3219            0            10055            1773              0                    0      
3227            0             9998            2678              0                    0      
3228            0            10003            3083              0                    0

Despite providing a lot of information, I'm not sure that there is really enough useful information here to help with answering this question but I'll give it a go. It would be useful to have some information on keys of the tables and also how many rows you expect to be updated here.

If the table to be updated has 262M rows and the table containing the data to update from has 90k records, then you could be trying to update anywhere between 90k and 262M rows depending on keys.

In my experience, writing updates is a little difficult. They are never treated quite how you want by the optimizer. They are useful for updating a handful of rows or updating columns to a constant value (SET ELF_ELEMENTO_CHIUSO = 'C') but not so good wen you need to look these values up in another table. The subquery in the SET clause seems to often get called for every row in the table.

For this reason, I would normally try to either write a MERGE statement or UPDATE a view. Both approaches have slight drawbacks, especially updating a view because there are quite a number of restrictions to look out for, the most important one here being that that it needs to be a key-preserved table. A key preserved table is defined here http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#sthref3058

I think that in this case that when creating a view then your table CONF_ELEMENTI_FATTURABILI should be key preserved. So an example of updating a view using your tables would be

UPDATE (
  select
    elf.ELF_STORICO_ID,
    elf.edw_partition,
    elf.ELF_ELEMENTO_CHIUSO,
    elf.ELF_DATA_VER_FIN,
    t.TELE_DATA_LETTURA
  from 
    CONF_ELEMENTI_FATTURABILI elf
    join NETATEMP.int_temp51 t on (t.ELF_STORICO_ID = elf.ELF_STORICO_ID and
                                   t.edw_partition = elf.edw_partition)
  ) a
set 
  a.ELF_ELEMENTO_CHIUSO = 'C',
  a.ELF_DATA_VER_FIN = a.TELE_DATA_LETTURA;

I can't really guarantee that this will work because of the key preservation issue. It might, or it might not. Even if it were my own data I'd have trouble working it out.

The other option is a MERGE statement. This is more likely to work as it doesn't rely on key preservation but I've also had weird problems with that too.

An example of the merge would be:

MERGE INTO CONF_ELEMENTI_FATTURABILI elf
USING NETATEMP.int_temp51 t on (t.ELF_STORICO_ID = elf.ELF_STORICO_ID and
                                t.edw_partition = elf.edw_partition)
WHEN MATCHED THEN UPDATE SET elf.ELF_ELEMENTO_CHIUSO = 'C',
                             elf.ELF_DATA_VER_FIN = t.TELE_DATA_LETTURA

As I say above, I'm sure that either of the statements shown above will actually work because they are both a little fiddly and there are reasons why either should fail. Hopefully you can get something working though.