Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Howto improve an update with correlated subquery

697829Jan 15 2010 — edited Jan 21 2010
Hi all,

Could somebody give some help or hint, please ?
I try to do the following update with the correlated subquery to get all updatedl records in the table PSI (700k records)
update /*+ parallel(psi 4) */ psi psi
set PREMIERE_VENTE =
       (select min(eve.dateve) 
        from eve, evp 
        where eve.codsoc = evp.codsoc and eve.achvte = evp.achvte and eve.typeve = evp.typeve and eve.numeve = evp.numeve 
        and eve.codsoc = psi.num_mag and eve.achvte = 'V' and eve.typeve in('TIC','FAV') 
        and eve.dateve between '20090101' and '20091231' and evp.codpro = psi.ref_pro)
It takes of course a long time to do the update. I tried to do it by num_mag (I have 73 distinct values, and for each distinct value I have around 9k rows)
and it took around 8min to update the 9000 rows. Ok, it is in total around 10 hours. But I need to execute 15 similar updates and that's a lot time.
Not only that, but during the night we have a lot batches running, so I am not able to let it run for 24/24 .

Here is the explain plan:
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1857481200                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                     
| Id  | Operation                               | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                     
|   0 | UPDATE STATEMENT                        |              |   704K|    15M|   583   (5)| 00:00:02 |       |       |        |      |            |                                                                                                     
|   1 |  UPDATE                                 | PSI          |       |       |            |          |       |       |        |      |            |                                                                                                     
|   2 |   PX COORDINATOR                        |              |       |       |            |          |       |       |        |      |            |                                                                                                     
|   3 |    PX SEND QC (RANDOM)                  | :TQ10000     |   704K|    15M|   583   (5)| 00:00:02 |       |       |  Q1,00 | P->S | QC (RAND)  |                                                                                                     
|   4 |     PX BLOCK ITERATOR                   |              |   704K|    15M|   583   (5)| 00:00:02 |       |       |  Q1,00 | PCWC |            |                                                                                                     
|   5 |      TABLE ACCESS FULL                  | PSI          |   704K|    15M|   583   (5)| 00:00:02 |       |       |  Q1,00 | PCWP |            |                                                                                                     
|   6 |   SORT AGGREGATE                        |              |     1 |    50 |            |          |       |       |        |      |            |                                                                                                     
|*  7 |    HASH JOIN                            |              |     1 |    50 |    34   (3)| 00:00:01 |       |       |        |      |            |                                                                                                     
|   8 |     PARTITION RANGE SINGLE              |              |    22 |   550 |    23   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|   9 |      PARTITION LIST SINGLE              |              |    22 |   550 |    23   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|  10 |       INLIST ITERATOR                   |              |       |       |            |          |       |       |        |      |            |                                                                                                     
|  11 |        TABLE ACCESS BY LOCAL INDEX ROWID| EVP          |    22 |   550 |    23   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|* 12 |         INDEX RANGE SCAN                | PPP_EVP_IDX1 |     1 |       |     5   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|  13 |     PARTITION RANGE SINGLE              |              |    57 |  1425 |    10   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|  14 |      PARTITION LIST SINGLE              |              |    57 |  1425 |    10   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|  15 |       INLIST ITERATOR                   |              |       |       |            |          |       |       |        |      |            |                                                                                                     
|  16 |        TABLE ACCESS BY LOCAL INDEX ROWID| EVE          |    57 |  1425 |    10   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
|* 17 |         INDEX RANGE SCAN                | EVE_M_IDX1   |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |        |      |            |                                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                     
                                                                                                                                                                                                                                                          
Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                             
-------------------------------------------------------------                                                                                                                                                                                             
                                                                                                                                                                                                                                                          
   1 - UPD$1                                                                                                                                                                                                                                              
   5 - UPD$1 / PSI@UPD$1                                                                                                                                                                                                                                  
   6 - SEL$1                                                                                                                                                                                                                                              
  11 - SEL$1 / EVP@SEL$1                                                                                                                                                                                                                                  
  12 - SEL$1 / EVP@SEL$1                                                                                                                                                                                                                                  
  16 - SEL$1 / EVE@SEL$1                                                                                                                                                                                                                                  
  17 - SEL$1 / EVE@SEL$1                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                          
Predicate Information (identified by operation id):                                                                                                                                                                                                       
---------------------------------------------------                                                                                                                                                                                                       
                                                                                                                                                                                                                                                          
   7 - access("EVE"."CODSOC"="EVP"."CODSOC" AND "EVE"."ACHVTE"="EVP"."ACHVTE" AND "EVE"."TYPEVE"="EVP"."TYPEVE" AND                                                                                                                                       
              "EVE"."NUMEVE"="EVP"."NUMEVE")                                                                                                                                                                                                              
  12 - access("EVP"."CODSOC"=TO_NUMBER(:B1) AND "EVP"."ACHVTE"='V' AND ("EVP"."TYPEVE"='FAV' OR "EVP"."TYPEVE"='TIC') AND "EVP"."CODPRO"=:B2)                                                                                                             
  17 - access("EVE"."CODSOC"=TO_NUMBER(:B1) AND "EVE"."ACHVTE"='V' AND ("EVE"."TYPEVE"='FAV' OR "EVE"."TYPEVE"='TIC') AND                                                                                                                                 
              "EVE"."DATEVE">='20090101' AND "EVE"."DATEVE"<='20091231')                                                                                                                                                                                  
       filter(SUBSTR("EVE"."DATEVE",1,6)>='200901' AND SUBSTR("EVE"."DATEVE",1,6)<='200912')                                                                                                                                                              
                                                                                                                                                                                                                                                          
Column Projection Information (identified by operation id):                                                                                                                                                                                               
-----------------------------------------------------------                                                                                                                                                                                               
                                                                                                                                                                                                                                                          
   2 - (upd=4; cmp=2,3) "SYS_ALIAS_2".ROWID[ROWID,10], "PSI"."NUM_MAG"[VARCHAR2,12], "PSI"."REF_PRO"[VARCHAR2,16],                                                                                                                                        
       "PREMIERE_VENTE"[CHARACTER,8]                                                                                                                                                                                                                      
   3 - (#keys=0) "SYS_ALIAS_2".ROWID[ROWID,10], "PSI"."NUM_MAG"[VARCHAR2,12], "PSI"."REF_PRO"[VARCHAR2,16], "PREMIERE_VENTE"[CHARACTER,8]                                                                                                                 
   4 - "SYS_ALIAS_2".ROWID[ROWID,10], "PSI"."NUM_MAG"[VARCHAR2,12], "PSI"."REF_PRO"[VARCHAR2,16], "PREMIERE_VENTE"[CHARACTER,8]                                                                                                                           
   5 - "SYS_ALIAS_2".ROWID[ROWID,10], "PSI"."NUM_MAG"[VARCHAR2,12], "PSI"."REF_PRO"[VARCHAR2,16], "PREMIERE_VENTE"[CHARACTER,8]                                                                                                                           
   6 - (#keys=0) MIN("EVE"."DATEVE")[8]                                                                                                                                                                                                                   
   7 - (#keys=4) "EVP"."CODSOC"[NUMBER,22], "EVE"."CODSOC"[NUMBER,22], "EVP"."ACHVTE"[VARCHAR2,1], "EVE"."ACHVTE"[VARCHAR2,1],                                                                                                                            
       "EVP"."TYPEVE"[VARCHAR2,3], "EVE"."TYPEVE"[VARCHAR2,3], "EVP"."NUMEVE"[NUMBER,22], "EVE"."NUMEVE"[NUMBER,22], "EVP".ROWID[ROWID,10],                                                                                                               
       "EVP"."CODPRO"[VARCHAR2,16], "EVE".ROWID[ROWID,10], "EVE"."DATEVE"[VARCHAR2,8]                                                                                                                                                                     
   8 - "EVP".ROWID[ROWID,10], "EVP"."ACHVTE"[VARCHAR2,1], "EVP"."TYPEVE"[VARCHAR2,3], "EVP"."NUMEVE"[NUMBER,22],                                                                                                                                          
       "EVP"."CODPRO"[VARCHAR2,16], "EVP"."CODSOC"[NUMBER,22]                                                                                                                                                                                             
   9 - "EVP".ROWID[ROWID,10], "EVP"."ACHVTE"[VARCHAR2,1], "EVP"."TYPEVE"[VARCHAR2,3], "EVP"."NUMEVE"[NUMBER,22],                                                                                                                                          
       "EVP"."CODPRO"[VARCHAR2,16], "EVP"."CODSOC"[NUMBER,22]                                                                                                                                                                                             
  10 - "EVP".ROWID[ROWID,10], "EVP"."ACHVTE"[VARCHAR2,1], "EVP"."TYPEVE"[VARCHAR2,3], "EVP"."NUMEVE"[NUMBER,22],                                                                                                                                          
       "EVP"."CODPRO"[VARCHAR2,16], "EVP"."CODSOC"[NUMBER,22]                                                                                                                                                                                             
  11 - "EVP".ROWID[ROWID,10], "EVP"."ACHVTE"[VARCHAR2,1], "EVP"."TYPEVE"[VARCHAR2,3], "EVP"."NUMEVE"[NUMBER,22],                                                                                                                                          
       "EVP"."CODPRO"[VARCHAR2,16], "EVP"."CODSOC"[NUMBER,22]                                                                                                                                                                                             
  12 - "EVP".ROWID[ROWID,10], "EVP"."CODSOC"[NUMBER,22], "EVP"."ACHVTE"[VARCHAR2,1], "EVP"."TYPEVE"[VARCHAR2,3], "EVP"."CODPRO"[VARCHAR2,16]                                                                                                              
  13 - "EVE".ROWID[ROWID,10], "EVE"."ACHVTE"[VARCHAR2,1], "EVE"."TYPEVE"[VARCHAR2,3], "EVE"."NUMEVE"[NUMBER,22], "EVE"."DATEVE"[VARCHAR2,8],                                                                                                              
       "EVE"."CODSOC"[NUMBER,22]                                                                                                                                                                                                                          
  14 - "EVE".ROWID[ROWID,10], "EVE"."ACHVTE"[VARCHAR2,1], "EVE"."TYPEVE"[VARCHAR2,3], "EVE"."NUMEVE"[NUMBER,22], "EVE"."DATEVE"[VARCHAR2,8],                                                                                                              
       "EVE"."CODSOC"[NUMBER,22]                                                                                                                                                                                                                          
  15 - "EVE".ROWID[ROWID,10], "EVE"."ACHVTE"[VARCHAR2,1], "EVE"."TYPEVE"[VARCHAR2,3], "EVE"."NUMEVE"[NUMBER,22], "EVE"."DATEVE"[VARCHAR2,8],                                                                                                              
       "EVE"."CODSOC"[NUMBER,22]                                                                                                                                                                                                                          
  16 - "EVE".ROWID[ROWID,10], "EVE"."ACHVTE"[VARCHAR2,1], "EVE"."TYPEVE"[VARCHAR2,3], "EVE"."NUMEVE"[NUMBER,22], "EVE"."DATEVE"[VARCHAR2,8],                                                                                                              
       "EVE"."CODSOC"[NUMBER,22]                                                                                                                                                                                                                          
  17 - "EVE".ROWID[ROWID,10], "EVE"."CODSOC"[NUMBER,22], "EVE"."ACHVTE"[VARCHAR2,1], "EVE"."TYPEVE"[VARCHAR2,3], "EVE"."DATEVE"[VARCHAR2,8]                                                                                                               
Oracle version is :
 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit 
...
And my Optimizer Parms are:

SQL> show parameter optimizer
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      10.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      FIRST_ROWS_100
optimizer_secure_view_merging        boolean     TRUE
SQL>
SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL>     select
  2  sname,pname,pval1,pval2
  3  from sys.aux_stats$;

SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          01-14-2010 21:05
SYSSTATS_INFO        DSTOP                           01-14-2010 21:05
SYSSTATS_INFO        FLAGS                         1
SYSSTATS_MAIN        CPUSPEEDNW             1247,559
SYSSTATS_MAIN        IOSEEKTIM                 5,053
SYSSTATS_MAIN        IOTFRSPEED            45469,627
SYSSTATS_MAIN        SREADTIM                  2,583
SYSSTATS_MAIN        MREADTIM                  1,851
SYSSTATS_MAIN        CPUSPEED                   1329
SYSSTATS_MAIN        MBRC                         14

SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN        MAXTHR                174532608
SYSSTATS_MAIN        SLAVETHR                  48128
If you think I missed something important in this sample post let me know, please.

Regards,
Marek
This post has been answered by B.Guillaumin on Jan 15 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 18 2010
Added on Jan 15 2010
22 comments
2,666 views