bad Execution plan choose in Pro*C, why?
From: <kerbiquet_at_hotmail.com>
Date: 2000/04/19
Message-ID: <8dk0qk$2tp$1_at_nnrp1.deja.com>
1st maner :
EXEC SQL SELECT SUM(DECODE(B.repfault,'C',TO_NUMBER(NVL(D.repfault,'0')) *
2nd manner :
sprintf(buf_requete," SELECT SUM(DECODE(B.repfault,'C',TO_NUMBER(NVL (D.repfault,'0'))*\n\
...
3rd manner :
STRVRCPY(vrequete,buf_requete);
EXEC SQL PREPARE S FROM :Select_ordsql;
EXEC SQL DECLARE C2 CURSOR FOR S;
EXEC SQL OPEN C2 USING :vcba, :datindprec1....; EXEC SQL FETCH C2 INTO :vindcomm,:vindtrans,:vindenerg; EXEC SQL CLOSE C2;
SQL TRACE 1st manner
select sum(DECODE(B.repfault,'C',(TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0'))),0)) ,sum(DECODE(B.repfault,'T', (TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0'))),0)) , [Quoted] sum(DECODE(B.repfault,'E',(TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0'))),0)) into :b0,:b1,:b2 from
CHEM A ,CHEMCONS B ,FAULT C ,CHEMCONS D ,FAULT E ,CHEMCONS F ,FAULT
TO_DATE(:b4,'DD-MON-YYYY') and TO_DATE(:b5,'DD-MON-YYYY')) and A.validee=
'O') and (A.aaind is null or (A.aaind=:b6 and A.mmind=:b7))) and H.CODESYS=:b8) and (((A.pov||A.pow)||A.pox)||A.poy)= (((H.pov||H.pow)||H.pox)||H.poy)) and H.indindispo='O') and
------- ------ -------- ---------- ---------- ---------- ----------
------- ------ -------- ---------- ---------- ---------- ----------
[Quoted] SQL TRACE 2nd manner
SELECT SUM(DECODE(B.repfault,'C',TO_NUMBER(NVL(D.repfault,'0'))*
AND (A.aaind IS null OR (A.aaind = 0 AND A.mmind = 3))
------- ------ -------- ---------- ---------- ---------- ----------
------- ------ -------- ---------- ---------- ---------- ----------
Date: 2000/04/19
Message-ID: <8dk0qk$2tp$1_at_nnrp1.deja.com>
[Quoted] In a Pro*C (8.0.6) program, we tried different manner to select data.
The Optimizer goal is CHOOSE. For each maner, the execution plan is
different and execution time is from 3s to 10min. Could someone explain
me what's going on?
1st manner is VERY SLOW
3rd manner is SLOW
2nd manner is FAST
[Quoted] The same is SQL*Plus (with the values directly in the sequence) is VERY
FAST
Thanks,
Pierre
1st maner :
EXEC SQL SELECT SUM(DECODE(B.repfault,'C',TO_NUMBER(NVL(D.repfault,'0')) *
TO_NUMBER(NVL(F.repfault,'0')),0)), SUM(DECODE(B.repfault,'T',TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0')),0)), SUM(DECODE(B.repfault,'E',TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0')),0)) INTO :vindcomm,:vindtrans,:vindenerg FROM CHEM A,CHEMCONS B,FAULT C,CHEMCONS D, FAULT E,CHEMCONS F,FAULT G,TYPEVT H [Quoted] WHERE A.CBA = :vcba AND (A.drefdos between TO_DATE(:datindprec1,'DD-MON-YYYY') AND TO_DATE(:vdfinind1,'DD-MON-YYYY')) AND A.validee = 'O' AND (A.aaind IS null OR (A.aaind = :vaaind AND A.mmind = :vmmind)) AND H.CODESYS = :codesys AND A.pov||A.pow||A.pox||A.poy = H.pov||H.pow||H.pox||H.poy AND H.indindispo = 'O' AND B.CBA = :vcba AND A.drefdos = B.drefdos AND A.nuqchem = B.nuqchem AND C.CODESYS = :codesys AND C.coclasse = 'ORI' AND B.cofault = C.cofault AND D.CBA = :vcba AND A.drefdos = D.drefdos AND A.nuqchem = D.nuqchem AND E.CODESYS = :codesys AND E.coclasse = 'NE' AND D.cofault = E.cofault AND F.CBA = :vcba AND A.drefdos = F.drefdos AND A.nuqchem = F.nuqchem AND G.CODESYS = :codesys AND G.coclasse = 'DUREE' AND F.cofault = G.cofault;
2nd manner :
sprintf(buf_requete," SELECT SUM(DECODE(B.repfault,'C',TO_NUMBER(NVL (D.repfault,'0'))*\n\
TO_NUMBER(NVL(F.repfault,'0')),0)),\n\
SUM(DECODE(B.repfault,'T',TO_NUMBER(NVL(D.repfault,'0'))*\n\
TO_NUMBER(NVL(F.repfault,'0')),0)),\n\
SUM(DECODE(B.repfault,'E',TO_NUMBER(NVL(D.repfault,'0'))*\n\
TO_NUMBER(NVL(F.repfault,'0')),0))\n\
FROM CHEM A,CHEMCONS B,FAULT C,CHEMCONS D, \n\
FAULT E,CHEMCONS F,FAULT G,TYPEVT H \n\
WHERE A.CBA = \'%s\' \n\
...
vcba.arr, datindprec1.arr,\
vdfinind1.arr, vaaind, vmmind, codesys, vcba.arr, codesys, \
vcba.arr, codesys , vcba.arr, codesys );
STRVRCPY(vrequete,buf_requete);
EXEC SQL PREPARE S FROM :vrequete;
EXEC SQL DECLARE C2 CURSOR FOR S;
EXEC SQL OPEN C2;
EXEC SQL FETCH C2 INTO :vindcomm,:vindtrans,:vindenerg;
EXEC SQL CLOSE C2;
3rd manner :
STRVRCPY(vrequete,buf_requete);
EXEC SQL PREPARE S FROM :Select_ordsql;
EXEC SQL DECLARE C2 CURSOR FOR S;
EXEC SQL OPEN C2 USING :vcba, :datindprec1....; EXEC SQL FETCH C2 INTO :vindcomm,:vindtrans,:vindenerg; EXEC SQL CLOSE C2;
SQL TRACE 1st manner
select sum(DECODE(B.repfault,'C',(TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0'))),0)) ,sum(DECODE(B.repfault,'T', (TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0'))),0)) , [Quoted] sum(DECODE(B.repfault,'E',(TO_NUMBER(NVL(D.repfault,'0'))* TO_NUMBER(NVL(F.repfault,'0'))),0)) into :b0,:b1,:b2 from
CHEM A ,CHEMCONS B ,FAULT C ,CHEMCONS D ,FAULT E ,CHEMCONS F ,FAULT
G ,TYPEVT H where ((((((((((((((((((((((((A.CBA=:b3 and A.drefdosbetween
TO_DATE(:b4,'DD-MON-YYYY') and TO_DATE(:b5,'DD-MON-YYYY')) and A.validee=
'O') and (A.aaind is null or (A.aaind=:b6 and A.mmind=:b7))) and H.CODESYS=:b8) and (((A.pov||A.pow)||A.pox)||A.poy)= (((H.pov||H.pow)||H.pox)||H.poy)) and H.indindispo='O') and
B.CBA=:b3) and A.drefdos=B.drefdos) and A.nuqchem=B.nuqchem) and C.CODESYS=:b8) and C.coclasse='ORI') and B.cofault=C.cofault) and D.CBA=:b3) and A.drefdos=D.drefdos) and A.nuqchem=D.nuqchem) and E.CODESYS=:b8) and E.coclasse='NE') and D.cofault=E.cofault) and F.CBA=:b3) and A.drefdos=F.drefdos) and A.nuqchem=F.nuqchem) and G.CODESYS=:b8) and G.coclasse='DUREE') and F.cofault=G.cofault) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 6002 779047 0 1
------- ------ -------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 6002 779047 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (OPS$ICB2G)
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT AGGREGATE
4 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
0 PARTITION CONCATENATED
4092 NESTED LOOPS
190489 TABLE ACCESS BY LOCAL INDEX ROWID CHEMCONS
190492 INDEX RANGE SCAN (object id 5417)
190489 TABLE ACCESS BY LOCAL INDEX ROWID CHEM
190489 INDEX UNIQUE SCAN (object id 6480)
0 PARTITION SINGLE
4092 TABLE ACCESS BY LOCAL INDEX ROWID FAULT
4092 INDEX UNIQUE SCAN (object id 3224)
0 PARTITION SINGLE
161 TABLE ACCESS BY LOCAL INDEX ROWID CHEMCONS
165 INDEX RANGE SCAN (object id 5417)
0 PARTITION SINGLE
161 TABLE ACCESS BY LOCAL INDEX ROWID FAULT
161 INDEX UNIQUE SCAN (object id 3224)
0 PARTITION SINGLE
161 TABLE ACCESS BY LOCAL INDEX ROWID CHEMCONS
165 INDEX RANGE SCAN (object id 5417)
0 PARTITION SINGLE
161 TABLE ACCESS BY LOCAL INDEX ROWID FAULT
161 INDEX UNIQUE SCAN (object id 3224)
0 PARTITION SINGLE
4 TABLE ACCESS BY LOCAL INDEX ROWID TYPEVT
864 INDEX RANGE SCAN (object id 3272)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 SORT (AGGREGATE)
4 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
0 PARTITION (CONCATENATED)
4092 NESTED LOOPS
190489 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
'CHEMCONS'
190492 INDEX (RANGE SCAN) OF 'IU_CHEMCONS_1'
(UNIQUE)
190489 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX
ROWID) OF 'CHEM'
190489 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'IU_CHEM_1' (UNIQUE)
0 PARTITION (SINGLE)
4092 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FAULT'
4092 INDEX (UNIQUE SCAN) OF 'IU_FAULT_1' (UNIQUE)
0 PARTITION (SINGLE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
'CHEMCONS'
165 INDEX (RANGE SCAN) OF 'IU_CHEMCONS_1' (UNIQUE)
0 PARTITION (SINGLE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FAULT'
161 INDEX (UNIQUE SCAN) OF 'IU_FAULT_1' (UNIQUE)
0 PARTITION (SINGLE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CHEMCONS'
165 INDEX (RANGE SCAN) OF 'IU_CHEMCONS_1' (UNIQUE)
0 PARTITION (SINGLE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FAULT'
161 INDEX (UNIQUE SCAN) OF 'IU_FAULT_1' (UNIQUE)
0 PARTITION (SINGLE)
4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TYPEVT'
864 INDEX (RANGE SCAN) OF 'IU_TYPEVT_1' (UNIQUE)
************************************************************************
[Quoted] SQL TRACE 2nd manner
SELECT SUM(DECODE(B.repfault,'C',TO_NUMBER(NVL(D.repfault,'0'))*
TO_NUMBER(NVL(F.repfault,'0')),0)),
SUM(DECODE(B.repfault,'T',TO_NUMBER(NVL(D.repfault,'0'))*
TO_NUMBER(NVL(F.repfault,'0')),0)),
SUM(DECODE(B.repfault,'E',TO_NUMBER(NVL(D.repfault,'0'))*
TO_NUMBER(NVL(F.repfault,'0')),0))
FROM CHEM A,CHEMCONS B,FAULT C,CHEMCONS D,
FAULT E,CHEMCONS F,FAULT G,TYPEVT H
WHERE A.CBA = 'AZA'
AND (A.drefdos between TO_DATE('20-JAN-2000','DD-MON-YYYY')
AND TO_DATE('19-MAR-2000','DD-MON-YYYY'))
AND A.validee = 'O'
AND (A.aaind IS null OR (A.aaind = 0 AND A.mmind = 3))
AND H.CODESYS = 1
AND A.pov||A.pow||A.pox||A.poy =
H.pov||H.pow||H.pox||H.poy
AND H.indindispo = 'O'
AND B.CBA = 'AZA'
AND A.drefdos = B.drefdos
AND A.nuqchem = B.nuqchem
AND C.CODESYS = 1
AND C.coclasse = 'ORI'
AND B.cofault = C.cofault
AND D.CBA = 'AZA'
AND A.drefdos = D.drefdos
AND A.nuqchem = D.nuqchem
AND E.CODESYS = 1
AND E.coclasse = 'NE'
AND D.cofault = E.cofault
AND F.CBA = 'AZA'
AND A.drefdos = F.drefdos
AND A.nuqchem = F.nuqchem
AND G.CODESYS = 1
AND G.coclasse = 'DUREE'
AND F.cofault = G.cofault
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 9569 11555 0 1
------- ------ -------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 9569 11555 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (OPS$ICB2G)
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT AGGREGATE
4 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
23 NESTED LOOPS
307 TABLE ACCESS BY LOCAL INDEX ROWID FAULT
308 INDEX RANGE SCAN (object id 3224)
0 PARTITION CONCATENATED
23 TABLE ACCESS BY LOCAL INDEX ROWID CHEMCONS
571476 INDEX RANGE SCAN (object id 5417)
0 PARTITION SINGLE
23 TABLE ACCESS BY LOCAL INDEX ROWID CHEM
23 INDEX UNIQUE SCAN (object id 6480)
0 PARTITION SINGLE
161 TABLE ACCESS BY LOCAL INDEX ROWID CHEMCONS
165 INDEX RANGE SCAN (object id 5417)
161 TABLE ACCESS BY LOCAL INDEX ROWID FAULT
161 INDEX UNIQUE SCAN (object id 3224)
0 PARTITION SINGLE
161 TABLE ACCESS BY LOCAL INDEX ROWID CHEMCONS
165 INDEX RANGE SCAN (object id 5417)
161 TABLE ACCESS BY LOCAL INDEX ROWID FAULT
161 INDEX UNIQUE SCAN (object id 3224)
4 TABLE ACCESS BY LOCAL INDEX ROWID TYPEVT
864 INDEX RANGE SCAN (object id 3272)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4 SORT (AGGREGATE)
4 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
161 NESTED LOOPS
4 NESTED LOOPS
23 NESTED LOOPS
307 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FAULT'
308 INDEX (RANGE SCAN) OF 'IU_FAULT_1' (UNIQUE)
0 PARTITION (CONCATENATED)
23 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
'CHEMCONS'
571476 INDEX (RANGE SCAN) OF 'IU_CHEMCONS_1'
(UNIQUE)
0 PARTITION (SINGLE)
23 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX
ROWID) OF 'CHEM'
23 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'IU_CHEM_1' (UNIQUE)
0 PARTITION (SINGLE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF
'CHEMCONS'
165 INDEX (RANGE SCAN) OF 'IU_CHEMCONS_1' (UNIQUE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FAULT'
161 INDEX (UNIQUE SCAN) OF 'IU_FAULT_1' (UNIQUE)
0 PARTITION (SINGLE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CHEMCONS'
165 INDEX (RANGE SCAN) OF 'IU_CHEMCONS_1' (UNIQUE)
161 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FAULT'
161 INDEX (UNIQUE SCAN) OF 'IU_FAULT_1' (UNIQUE)
4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TYPEVT'
864 INDEX (RANGE SCAN) OF 'IU_TYPEVT_1' (UNIQUE)
************************************************************************
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Apr 19 2000 - 00:00:00 CEST