Re: bad Execution plan choose in Pro*C, why?
Date: Thu, 20 Apr 2000 12:48:39 GMT
Message-ID: <8dmub1$ajd$1_at_nnrp1.deja.com>
Make sure your variables are defined correctly matching the columns.
Things like :codesys if c.codesys is a number then so should :codesys be.
ProC does not do the explain plan, the db does, so what you see in ProC should be the same as SQL*PLUS, except for the variable substitution problem just noted above.
Another thing to try -- if you cant change the instance OPTIMIZER_MODE, then atleast try to change it on the query for testing to /*+ FIRST_ROWS +*/
Never use CHOOSE, it will most likely do full table scans.
Have you analyzed the tables? All the tables?
In article <8dk0qk$2tp$1_at_nnrp1.deja.com>,
kerbiquet_at_hotmail.com wrote:
> 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
> 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
> 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)) ,
> 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.drefdos
> between
> 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)
>
>
> ********
> ===========
> 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.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Apr 20 2000 - 14:48:39 CEST