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