Odd behavior with IDS11 optimizer .....Is there an
A user migrating from IDS 7.31.UD8 to IDS 11.50.FC6 on AIX 5.3 found a large UNION ALL query (many outer joins, a NOT EXISTS subquery and a UDR) ran fine on 7.31 but hung on 11.50. The 11.50 query plan showed an absurd estimated cost/row count (max 64-bit value) and a sequential scan on the employee table in the second branch of the union. Suggestions were to fully drop and rebuild distributions/statistics (done, no change), check how their customised dostats script and statistics levels were set, and to use optimizer directives to force the old index path. The thread trails off into attachment/mailing-list issues with no resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I have a query that works fine with IDS7.31.UD8 but taking forever with=
IDS11.FC6.W2 on AIX5.3. Both structures are the same except the engine
version. Update statistics/oncheck(s) were also run after the conversio=
n to
IDS11. Is there any other settings that may influence the optimizer pat=
h or
make it behave differently. The problem seems to be in the second SQL i=
n
the union. See sql statement at the end of the email.
Values in onconfig for IDS11
OPTCOMPIND 0
OPT_GOAL -1
DIRECTIVES 1
EXT_DIRECTIVES 0
IFX_FOLDVIEW 0
VALUES IN ONCONFIG for IDS7
OPTCOMPIND 0
OPT_GOAL -1
DIRECTIVES 1
Look at the estimated cost below for IDS11.FC6.W2 versus IDS7.31.UD8
The first plan is from IDS11
Estimated Cost: 9223372036854775807
Estimated # of Rows Returned: 9223372036854775807
Temporary Files Required For: Order By
1) informix.b: INDEX PATH
(1) Index Name: sysadm.ix_org5
Index Keys: name
Lower Index Filter: informix.b.name LIKE 'AGT%'
2) informix.a: INDEX PATH
Filters: (informix.a.appstatus !=3D 40 AND NOT EXISTS <subquery=
> )
(1) Index Name: sysadm.ix_app3
Index Keys: apporgid date_eff lobcd
Lower Index Filter: informix.a.apporgid =3D informix.b.orgid
NESTED LOOP JOIN
3) informix.i: INDEX PATH
(1) Index Name: sysadm. 515_699
Index Keys: orgid (Key-Only)
Lower Index Filter: informix.a.prdcrorgid =3D informix.i.orgid
NESTED LOOP JOIN
4) informix.c: INDEX PATH
(1) Index Name: sysadm. 627_1011
Index Keys: orgid
Lower Index Filter: informix.c.orgid =3D informix.i.orgid
NESTED LOOP JOIN
5) informix.h: INDEX PATH
(1) Index Name: informix. 767_1502
Index Keys: prdctcd
Lower Index Filter: informix.a.prdctcd =3D informix.h.prdctcd
NESTED LOOP JOIN
6) informix.d: INDEX PATH
(1) Index Name: sysadm.stat01u
Index Keys: statcd (Serial, fragments: ALL)
Lower Index Filter: informix.a.appstatus =3D informix.d.statcd
NESTED LOOP JOIN
7) informix.f: INDEX PATH
(1) Index Name: sysadm.i01employee
Index Keys: perid (Serial, fragments: ALL)
Lower Index Filter: informix.a.uwperid =3D informix.f.perid
NESTED LOOP JOIN
8) informix.io: INDEX PATH
(1) Index Name: sysadm. 739_1382
Index Keys: orgid
Lower Index Filter: informix.b.orgid =3D informix.io.orgid
NESTED LOOP JOIN
9) informix.q: INDEX PATH
(1) Index Name: sysadm. 540_740
Index Keys: appid
Lower Index Filter: informix.a.appid =3D informix.q.appid
NESTED LOOP JOIN
10) informix.p: INDEX PATH
(1) Index Name: sysadm.i12pol
Index Keys: appid prdctcd (Serial, fragments: ALL)
Lower Index Filter: (informix.a.appid =3D informix.p.appid AND
informix.a.prdctcd =3D informix.p.prdctcd )
NESTED LOOP JOIN
11) informix.z: INDEX PATH
(1) Index Name: sysadm.i01employee
Index Keys: perid (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.p.uwcontperid =3D informix.z.perid=
NESTED LOOP JOIN
12) informix.bp: INDEX PATH
(1) Index Name: sysadm. 1218_6096
Index Keys: bp_gin (Key-Only)
Lower Index Filter: informix.b.bp_gin =3D informix.bp.bp_gin
NESTED LOOP JOIN
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.z: INDEX PATH
(1) Index Name: sysadm.i08_opt
Index Keys: appid status (Key-Only) (Serial, fragments: =
ALL)
Lower Index Filter: informix.z.appid =3D informix.a.appid
UDRs in query:
--------------
UDR id : 155
UDR name: sp_poltype
Union Query:
------------
1) informix.f: SEQUENTIAL SCAN
2) informix.e: INDEX PATH
Filters: informix.e.status !=3D 'COMBINED'
(1) Index Name: sysadm.i06opt
Index Keys: uwperid effdate
Lower Index Filter: informix.e.uwperid =3D informix.f.perid
NESTED LOOP JOIN
3) informix.a: INDEX PATH
(1) Index Name: sysadm. 408_493
Index Keys: appid
Lower Index Filter: informix.a.appid =3D informix.e.appid
NESTED LOOP JOIN
4) informix.h: INDEX PATH
(1) Index Name: informix. 767_1502
Index Keys: prdctcd
Lower Index Filter: informix.e.prdctcd =3D informix.h.prdctcd
NESTED LOOP JOIN
5) informix.b: INDEX PATH
Filters: informix.b.name LIKE 'AGT%'
(1) Index Name: sysadm. 627_1011
Index Keys: orgid
Lower Index Filter: informix.a.apporgid =3D informix.b.orgid
NESTED LOOP JOIN
6) informix.i: INDEX PATH
(1) Index Name: sysadm. 515_699
Index Keys: orgid (Key-Only)
Lower Index Filter: informix.a.prdcrorgid =3D informix.i.orgid
NESTED LOOP JOIN
7) informix.oi: INDEX PATH
(1) Index Name: sysadm. 739_1382
Index Keys: orgid
Lower Index Filter: informix.b.orgid =3D informix.oi.orgid
NESTED LOOP JOIN
8) informix.c: INDEX PATH
(1) Index Name: sysadm. 627_1011
Index Keys: orgid
Lower Index Filter: informix.c.orgid =3D informix.i.orgid
NESTED LOOP JOIN
9) informix.g: INDEX PATH
(1) Index Name: sysadm. 517_708
Index Keys: branchnum
Lower Index Filter: informix.g.branchnum =3D informix.e.servbra=
nchnum
NESTED LOOP JOIN
10) informix.s: INDEX PATH
(1) Index Name: sysadm.ix_op_statdesc
Index Keys: dsc
Lower Index Filter: informix.e.status =3D informix.s.dsc
NESTED LOOP JOIN
11) informix.bp: INDEX PATH
(1) Index Name: sysadm. 1218_6096
Index Keys: bp_gin (Key-Only)
Lower Index Filter: informix.b.bp_gin =3D informix.bp.bp_gin
NESTED LOOP JOIN
12) informix.p: INDEX PATH
(1) Index Name: sysadm.i12pol
Index Keys: appid prdctcd (Serial, fragments: ALL)
Lower Index Filter: (informix.e.appid =3D informix.p.appid AND
informix.e.prdctcd =3D informix.p.prdctcd )
NESTED LOOP JOIN
13) informix.z: INDEX PATH
(1) Index Name: sysadm.i01employee
Index Keys: perid (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: informix.p.uwcontperid =3D informix.z.perid=
NESTED LOOP JOIN
14) informix.or1: INDEX PATH
Filters: (informix.or1.opt_role_c =3D 'ASR' AND informix.or1.ex=
p_d >
TODAY )
(1) Index Name: sysadm.ix_optrole01
Index Keys: appid prdctcd
Lower Index Filter: (informix.e.appid =3D informix.or1.appid AN=
D
informix.e.prdctcd =3D informix.or1.prdctcd )
NESTED LOOP JOIN
15) informix.bp1: INDEX PATH
Filters: informix.bp1.exp_d > TODAY
(1) Index Name: informix. 697_2115
Index Keys: bp_gin
Lower Index Filter: informix.or1.bp_gin =3D informix.bp1.bp_gin=
NESTED LOOP JOIN
16) informix.or2: INDEX PATH
Filters: (informix.or2.opt_role_c =3D 'CSR' AND informix.or2.ex=
p_d >
TODAY )
(1) Index Name: sysadm.ix_optrole01
Index Keys: appid prdctcd
Lower Index Filter: (informix.e.appid =3D informix.or2.ap
jpierrot@chubb.com wrote: > I have a query that works fine with IDS7.31.UD8 but taking forever with= > > IDS11.FC6.W2 on AIX5.3. Both structures are the same except the engine > version. Update statistics/oncheck(s) were also run after the conversio= > n to > IDS11. Is there any other settings that may influence the optimizer pat= > h or > make it behave differently. The problem seems to be in the second SQL i= > n > the union. See sql statement at the end of the email. Did you completely drop the old statistics and completely recreate them under 11.50? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Yes, did drop distributions too. jp = From: "Obnoxio The Clown" <obnoxio@serendipita.com> = = To: ids@iiug.org = = Date: 09/01/2010 05:50 PM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2110= 5] = Sent by: ids-bounces@iiug.org = = jpierrot@chubb.com wrote: > I have a query that works fine with IDS7.31.UD8 but taking forever wi= th=3D > > IDS11.FC6.W2 on AIX5.3. Both structures are the same except the engin= e > version. Update statistics/oncheck(s) were also run after the convers= io=3D > n to > IDS11. Is there any other settings that may influence the optimizer p= at=3D > h or > make it behave differently. The problem seems to be in the second SQL= i=3D > n > the union. See sql statement at the end of the email. Did you completely drop the old statistics and completely recreate them= under 11.50? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
jpierrot@chubb.com wrote: > Yes, did drop distributions too. Is there any chance of seeing the SQL -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
The problem seems to be with the second piece of the query or the union=
.
QUERY:
------
SELECT distinct BP.BP_GIN SA_BP_GIN , B.BP_GIN ACCT_BP_GIN,A.APPORGID,A.PRDCRORGID,A.APPID
,A.PARNTAPPID,A.CLASSCD,B.ACCTNUM,B.NAME scAcctName, '' scOptSicCd,=
A.MKTCD scMarket,A.MRLINE
,A.POLTYPE,A.APPTYPE scAppType,TRIM(A.LOBCD) scLOB,
A.DATE_EFF,DATEEXPIRE,A.TOTAL_ASSETS
, B.MSTRACCT, A.EXTWIZSCORE,A.MEDMALWIZSCORE,
A.OBJWIZSCORE,A.OBJREASON, A.OBJOVERRIDE
,A.SUBWIZSCORE,TRIM(A.TRNWIZSCORE) scTrnWizScore,
A.APPSTATUS,A.BROKERCONTACTIND
,C.NAME scPrdName,D.DSC scStatus,A.RECVDATE,A.FILEDESTRUCTIND
scDestructInd1,B.NFPID
,A.REVSHEET, A.INDEXPRATE, A.ENVEXPRATE, A.ENVRESPRATE, A.LRCR,
A.EXTRATE,A.PRDCONTPERID
,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL
UWFullName,A.UWPERID ncUWId
,A.ACCTREVIEWED, A.CLAIMREVIEWED,A.FILEDESTRUCTIND scDestructInd2,
B.DOMSTATECD, A.SIZECAT
,A.BOXNO, A.MKTSEG, BI_TRACK, ADM_TRACK,'' NYFTZ, 0 NYFTZ_CLASS,''
TAXSTATUS, '' TAX_EX_STATCD
, '' EXEMPTION_TY_CD,A.PRDCTCD
, TRIM(A.LOBCD)|| '-'||TRIM(A.PRDCTCD)|| ' / '||TRIM(H.PRDCTSHRTNAM=
E)
scLobCdPrdctCd
,A.QUOTEBY,TRIM('') scOptPrdctCd,'' scOptStatus, '' scOptLOB, ''
scOptBrkrContInd
, DATE(NULL) EFFDATE, DATE(NULL) EXPDATE, '' scOptClassCd, 0
ncOptUWPerId, '' scOptMktSeg
, '' scOptFileLoc,DATE(NULL) dcOptQuoteBy, '' scOptMarket, A.PROGRA=
MID,
D.STATCD, D.SORTORD
,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL
sOptUW,TRIM(PREQUOTEQPC) Pre_Quote_Qpc
, PYAUDITQPC, PYAUDITCOMMENT, C.FAX, A.BOR_IND scBorInd, A.BOR_IND
scOptBorInd, A.SUBKIND
,0 SERVBRANCHNUM, TRIM('') BRANCHABBR,0 ncSLPrdcrOrgId, 0
SLPRDCONTPERID,A.RTLBRKRID,A.RTLBRKRCONTID
, '' ASRFullName, '' CSRFullName, 0 ncASRId,0 ncCSRId,0 ncOptStatus=
Cd,
0 dmy01, P.RENSTATCD, P.CARRINIT
, P.FORMPOLNUM, H.EMP_CNT_REQ,C.TOTALREVENUE, C.ESTABLISHEDDATE,
B.CMP_EMPL_CNT, TODAY dtSysServerDate
, B.OWNERSHIP,'' policy_types,' ' UWAFullName,0 PERID, '' scIndustr=
y,
IO.COMPUSTAT_GICS_KEY
FROM APP A
, ORG B
, ORG C
, OUTER (POL P
, OUTER EMPLOYEE Z)
, STAT D
, EMPLOYEE F
, OUTER QPC Q
, ORG_PRODUCER I
,PRDCT H
, OUTER ORG_INSURED IO
, outer allnc_bp bp
WHERE(A.APPORGID =3D B.ORGID)
AND A.PRDCRORGID=3DC.ORGID
AND A.APPID =3D P.APPID
AND A.PRDCTCD =3D P.PRDCTCD
AND A.APPSTATUS=3DD.STATCD
AND A.APPSTATUS !=3D 40
AND A.UWPERID=3DF.PERID
AND A.APPID=3DQ.APPID
AND C.ORGID =3D I.ORGID
AND A.PRDCTCD =3D H.PRDCTCD
AND P.UWCONTPERID=3DZ.PERID
AND NOT EXISTS (SELECT 1 FROM OPT Z WHERE A.APPID=3DZ.APPID)
AND B.NAME Like 'AGT%'
AND B.ORGID =3D IO.ORGID
and b.bp_gin =3D bp.bp_gin
UNION ALL
SELECT distinct BP.BP_GIN SA_BP_GIN , B.BP_GIN ACCT_BP_GIN, A.APPORGID=
,A.PRDCRORGID
, A.APPID, A.PARNTAPPID, A.CLASSCD, B.ACCTNUM, B.NAME scAcctName,
E.SICCD scOptSicCd
, A.MKTCD scMarket,A.MRLINE, A.POLTYPE,E.APPTYPCD scAppType,E.LOBCD=
scLOB, A.DATE_EFF
,A.DATEEXPIRE, A.TOTAL_ASSETS, B.MSTRACCT,A.EXTWIZSCORE,
A.MEDMALWIZSCORE, A.OBJWIZSCORE
, A.OBJREASON,A.OBJOVERRIDE,A.SUBWIZSCORE, TRIM(A.TRNWIZSCORE)
scTrnWizScore
, A.APPSTATUS,A.BROKERCONTACTIND,C.NAME scPrdName, E.STATUS scStatu=
s,
A.RECVDATE
, A.FILEDESTRUCTIND scDestructInd1,B.NFPID, A.REVSHEET, A.INDEXPRAT=
E,
A.ENVEXPRATE
,A.ENVRESPRATE,A.LRCR, A.EXTRATE, E.PRDCONTPERID
,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL
name01,E.UWPERID ncUWId, A.ACCTREVIEWED
, A.CLAIMREVIEWED,A.FILEDESTRUCTIND scDestructInd2,B.DOMSTATECD,
A.SIZECAT,A.BOXNO, A.MKTSEG
, BI_TRACK, ADM_TRACK,E.GEOCD NYFTZ, E.NYFTZ_CLASS,B.TAXSTATUS,
B.TAX_EX_STATCD, OI.EXEMPTION_TY_CD
,E.PRDCTCD, E.LOBCD||'-'||E.PRDCTCD||' / '||TRIM(H.PRDCTSHRTNAME)
scLobCdPrdctCd,A.QUOTEBY
, TRIM(E.PRDCTCD) scOptPrdctCd,E.STATUS scOptStatus, TRIM(E.LOBCD)
scOptLOB
, E.BROKERCONTACTIND scOptBrkrContInd,E.EFFDATE EFFDATE,E.EXPDATE
EXPDATE, TRIM(E.CLASSCD) scOptClassCd
, E.UWPERID ncOptUWPerId, TRIM(E.MKTSEG) scOptMktSeg,TRIM(E.FILELOC=
)
scOptFileLoc, E.QUOTEBY dcOptQuoteBy
, E.MKTCD scOptMarket, A.PROGRAMID, 0 ncOptStatusCd, 0 dmy01
,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL sOptU=
W
,TRIM(PREQUOTEQPC) Pre_Quote_Qpc, PYAUDITQPC, PYAUDITCOMMENT, C.FAX=
,
A.BOR_IND scBorInd
, A.BOR_IND scOptBorInd,A.SUBKIND,E.SERVBRANCHNUM, G.BRANCHABBR,
SLPRDCRORGID ncSLPrdcrOrgId
, SLPRDCONTPERID,A.RTLBRKRID,A.RTLBRKRCONTID,BP1.last_na || ', ' ||=
BP1.first_na || ' ' || BP1.mid_init ASRFullName
, BP2.last_na || ', ' || BP2.first_na || ' ' || BP2.mid_init
CSRFullName,F.PERID ncASRId
, F.PERID ncCSRId,S.STATCD xcOptStatusCd, S.SORTORD, P.RENSTATCD,
P.CARRINIT,P.FORMPOLNUM, H.EMP_CNT_REQ
,C.TOTALREVENUE, C.ESTABLISHEDDATE, B.CMP_EMPL_CNT,TODAY
dtSysServerDate, B.OWNERSHIP
, sp_poltype(E.appid, E.prdctcd) policy_types,BP3.last_na || ', ' |=
|
BP3.first_na || ' ' || BP3.mid_init UWAFullName
,F.PERID, TRIM(E.INDRYCD) scIndustry, OI.COMPUSTAT_GICS_KEY
FROM APP A
,ORG B
,ORG C
, ORG_INSURED OI
, OPT E
, OUTER (POL P
, OUTER EMPLOYEE Z)
, EMPLOYEE F
, OUTER QPC Q
,OUTER CHB_BRANCH G
, ORG_PRODUCER I
, PRDCT H
, OUTER STAT S
,OUTER (OPT_ROLE OR1
, BP_NAME BP1)
, OUTER (OPT_ROLE OR2
, BP_NAME BP2)
, OUTER (OPT_ROLE OR3
, BP_NAME BP3)
, outer allnc_bp bp
WHERE(A.APPORGID =3D B.ORGID)
AND B.ORGID =3D OI.ORGID
AND A.PRDCRORGID=3DC.ORGID
AND E.APPID =3D P.APPID
AND E.PRDCTCD =3D P.PRDCTCD
AND E.UWPERID =3DF.PERID
AND E.STATUS =3D S.DSC
AND E.STATUS!=3D 'COMBINED'
AND A.APPID =3D Q.APPID
AND A.APPID =3D E.APPID
AND E.PRDCTCD =3D H.PRDCTCD
AND P.UWCONTPERID=3DZ.PERID
AND E.APPID =3D OR1.APPID
AND E.PRDCTCD =3D OR1.PRDCTCD
AND E.APPID =3D OR2.APPID
AND E.PRDCTCD =3D OR2.PRDCTCD
AND OR1.BP_GIN =3D BP1.BP_GIN
AND OR1.OPT_ROLE_C =3D 'ASR'
AND OR1.EXP_D > TODAY
AND OR2.BP_GIN =3D BP2.BP_GIN
AND OR2.OPT_ROLE_C =3D'CSR'
AND OR2.EXP_D > TODAY
AND BP1.EXP_D > TODAY
AND BP2.EXP_D > TODAY
AND BP3.EXP_D > TODAY
AND C.ORGID =3D I.ORGID
AND G.BRANCHNUM =3D E.SERVBRANCHNUM
AND OR3.BP_GIN =3D BP3.BP_GIN
AND OR3.OPT_ROLE_C =3D 'UWA'
AND OR3.EXP_D > TODAY
AND B.NAME Like 'AGT%'
AND E.APPID =3D OR3.APPID
AND E.PRDCTCD =3D OR3.PRDCTCD
and b.bp_gin =3D bp.bp_gin
ORDER BY 9, 5
into temp xx_pbm with no log
=
From: "Obnoxio The Clown" <obnoxio@serendipita.com> =
=
To: ids@iiug.org =
=
Date: 09/01/2010 05:58 PM =
=
Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2110=
7]
=
Sent by: ids-bounces@iiug.org =
=
jpierrot@chubb.com wrote:
> Yes, did drop distributions too.
Is there any chance of seeing the
jpierrot@chubb.com wrote: > The problem seems to be with the second piece of the query or the union= Jesus. I'm sorry I asked. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
jpierrot@chubb.com wrote: > The problem seems to be with the second piece of the query or the union= It seems like 11.50 is doing a sequential scan of the employee table, rather than traversing an index. You absolutely sure the statistics are kosher? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
That's correct! But I will take a look at the 4gl app that runs update stats to see if the latest code/binary that was complied was used after= the migration. jp = From: "Obnoxio The Clown" <obnoxio@serendipita.com> = = To: ids@iiug.org = = Date: 09/01/2010 06:31 PM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111= 1] = Sent by: ids-bounces@iiug.org = = jpierrot@chubb.com wrote: > The problem seems to be with the second piece of the query or the uni= on=3D It seems like 11.50 is doing a sequential scan of the employee table, rather than traversing an index. You absolutely sure the statistics are= kosher? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
I dropped the distributions again last night and run update statistics = and the query still hangs. Are there any variables out there that can force= the optimizer to behave like in 7.31. Thanks! jp = From: "Obnoxio The Clown" <obnoxio@serendipita.com> = = To: ids@iiug.org = = Date: 09/01/2010 06:31 PM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111= 1] = Sent by: ids-bounces@iiug.org = = jpierrot@chubb.com wrote: > The problem seems to be with the second piece of the query or the uni= on=3D It seems like 11.50 is doing a sequential scan of the employee table, rather than traversing an index. You absolutely sure the statistics are= kosher? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
jpierrot@chubb.com wrote: > I dropped the distributions again last night and run update statistics = > and > the query still hangs. Are there any variables out there that can force= > the > optimizer to behave like in 7.31. My best guess would be to add an optimiser directive to try avoid sequential scans or encourage it to you the same index that 7.31 used. It's very strange. What update stats are you running? Dostats? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
I am using a customized version of dostats. Thanks! jp = From: "Obnoxio The Clown" <obnoxio@serendipita.com> = = To: ids@iiug.org = = Date: 09/02/2010 09:47 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111= 9] = Sent by: ids-bounces@iiug.org = = jpierrot@chubb.com wrote: > I dropped the distributions again last night and run update statistic= s =3D > and > the query still hangs. Are there any variables out there that can for= ce=3D > the > optimizer to behave like in 7.31. My best guess would be to add an optimiser directive to try avoid sequential scans or encourage it to you the same index that 7.31 used. It's very strange. What update stats are you running? Dostats? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
jpierrot@chubb.com wrote: > I am using a customized version of dostats. Customised how? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
What level of statistics are you using on these tables? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > I dropped the distributions again last night and run update statistics = > and > the query still hangs. Are there any variables out there that can force= > the > optimizer to behave like in 7.31. > > Thanks! > > jp > > = > > From: "Obnoxio The Clown" <obnoxio@serendipita.com> = > > = > > To: ids@iiug.org = > > = > > Date: 09/01/2010 06:31 PM = > > = > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111= > 1] > > = > > Sent by: ids-bounces@iiug.org = > > = > > jpierrot@chubb.com wrote: > > The problem seems to be with the second piece of the query or the uni= > on=3D > > It seems like 11.50 is doing a sequential scan of the employee table, > rather than traversing an index. You absolutely sure the statistics are= > > kosher? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0050450175faf1dbe6048f475e82
Everything that dostasts does in terms of 'update statistics', we follo= wed that same approach using our own tool. jp = From: "Obnoxio The Clown" <obnoxio@serendipita.com> = = To: ids@iiug.org = = Date: 09/02/2010 10:06 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112= 1] = Sent by: ids-bounces@iiug.org = = jpierrot@chubb.com wrote: > I am using a customized version of dostats. Customised how? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Here is a sample output! (See attached file: Update Stats.log) = From: "Art Kagel" <art.kagel@gmail.com> = = To: ids@iiug.org = = Date: 09/02/2010 10:10 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112= 2] = Sent by: ids-bounces@iiug.org = = What level of statistics are you using on these tables? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinion= s and do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot= her organization with which I am associated either explicitly, implicitly, = or by inference. Neither do those opinions reflect those of other individuals= affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > I dropped the distributions again last night and run update statistic= s =3D > and > the query still hangs. Are there any variables out there that can for= ce=3D > the > optimizer to behave like in 7.31. > > Thanks! > > jp > > =3D > > From: "Obnoxio The Clown" <obnoxio@serendipita.com> =3D > > =3D > > To: ids@iiug.org =3D > > =3D > > Date: 09/01/2010 06:31 PM =3D > > =3D > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111=3D > 1] > > =3D > > Sent by: ids-bounces@iiug.org =3D > > =3D > > jpierrot@chubb.com wrote: > > The problem seems to be with the second piece of the query or the u= ni=3D > on=3D3D > > It seems like 11.50 is doing a sequential scan of the employee table,= > rather than traversing an index. You absolutely sure the statistics a= re=3D > > kosher? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > *********************************************************************= **=3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > =3D > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0050450175faf1dbe6048f475e82 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
jpierrot@chubb.com wrote: > Here is a sample output! > > (See attached file: Update Stats.log) You can't send attachments -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Attachments don't flow through the email gateway. Send it to my email address directly. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:25 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > Here is a sample output! > > (See attached file: Update Stats.log) > > = > > From: "Art Kagel" <art.kagel@gmail.com> = > > = > > To: ids@iiug.org = > > = > > Date: 09/02/2010 10:10 AM = > > = > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112= > 2] > > = > > Sent by: ids-bounces@iiug.org = > > = > > What level of statistics are you using on these tables? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinion= > s > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot= > her > organization with which I am associated either explicitly, implicitly, = > or > by > inference. Neither do those opinions reflect those of other individuals= > > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com > <jpierrot@chubb.com>wrote: > > > I dropped the distributions again last night and run update statistic= > s =3D > > and > > the query still hangs. Are there any variables out there that can for= > ce=3D > > the > > optimizer to behave like in 7.31. > > > > Thanks! > > > > jp > > > > =3D > > > > From: "Obnoxio The Clown" <obnoxio@serendipita.com> =3D > > > > =3D > > > > To: ids@iiug.org =3D > > > > =3D > > > > Date: 09/01/2010 06:31 PM =3D > > > > =3D > > > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111=3D > > 1] > > > > =3D > > > > Sent by: ids-bounces@iiug.org =3D > > > > =3D > > > > jpierrot@chubb.com wrote: > > > The problem seems to be with the second piece of the query or the u= > ni=3D > > on=3D3D > > > > It seems like 11.50 is doing a sequential scan of the employee table,= > > > rather than traversing an index. You absolutely sure the statistics a= > re=3D > > > > kosher? > > > > -- > > Cheers, > > Obnoxio The Clown > > > > http://obotheclown.blogspot.com > > I will now proceed to pleasure myself with this fish. > > > > -- > > This message has been scanned for viruses and > > dangerous content by OpenProtect(http://www.openprotect.com), and is > > believed to be clean. > > > > *********************************************************************= > **=3D > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > =3D > > > > > > > > > ***********************************************************************= > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0050450175faf1dbe6048f475e82 > > ***********************************************************************= > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > = > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636af02095d9f78048f47be48
Art Kagel wrote: > Attachments don't flow through the email gateway. Send it to my email > address directly. What about the rest of us, FFS? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Sure! I will do it in a minute. = From: "Art Kagel" <art.kagel@gmail.com> = = To: ids@iiug.org = = Date: 09/02/2010 10:37 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2113= 0] = Sent by: ids-bounces@iiug.org = = Attachments don't flow through the email gateway. Send it to my email address directly. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinion= s and do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot= her organization with which I am associated either explicitly, implicitly, = or by inference. Neither do those opinions reflect those of other individuals= affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:25 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > Here is a sample output! > > (See attached file: Update Stats.log) > > =3D > > From: "Art Kagel" <art.kagel@gmail.com> =3D > > =3D > > To: ids@iiug.org =3D > > =3D > > Date: 09/02/2010 10:10 AM =3D > > =3D > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112=3D > 2] > > =3D > > Sent by: ids-bounces@iiug.org =3D > > =3D > > What level of statistics are you using on these tables? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opini= on=3D > s > and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any = ot=3D > her > organization with which I am associated either explicitly, implicitly= , =3D > or > by > inference. Neither do those opinions reflect those of other individua= ls=3D > > affiliated with any entity with which I am affiliated nor those of th= e > entities themselves. > > On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com > <jpierrot@chubb.com>wrote: > > > I dropped the distributions again last night and run update statist= ic=3D > s =3D3D > > and > > the query still hangs. Are there any variables out there that can f= or=3D > ce=3D3D > > the > > optimizer to behave like in 7.31. > > > > Thanks! > > > > jp > > > > =3D3D > > > > From: "Obnoxio The Clown" <obnoxio@serendipita.com> =3D3D > > > > =3D3D > > > > To: ids@iiug.org =3D3D > > > > =3D3D > > > > Date: 09/01/2010 06:31 PM =3D3D > > > > =3D3D > > > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111=3D= 3D > > 1] > > > > =3D3D > > > > Sent by: ids-bounces@iiug.org =3D3D > > > > =3D3D > > > > jpierrot@chubb.com wrote: > > > The problem seems to be with the second piece of the query or the= u=3D > ni=3D3D > > on=3D3D3D > > > > It seems like 11.50 is doing a sequential scan of the employee tabl= e,=3D > > > rather than traversing an index. You absolutely sure the statistics= a=3D > re=3D3D > > > > kosher? > > > > -- > > Cheers, > > Obnoxio The Clown > > > > http://obotheclown.blogspot.com > > I will now proceed to pleasure myself with this fish. > > > > -- > > This message has been scanned for viruses and > > dangerous content by OpenProtect(http://www.openprotect.com), and i= s > > believed to be clean. > > > > *******************************************************************= **=3D > **=3D3D > > ******** > > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > =3D3D > > > > > > > > > *********************************************************************= **=3D > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > --0050450175faf1dbe6048f475e82 > > *********************************************************************= **=3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > =3D > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636af02095d9f78048f47be48 ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Will share the info with the post too. jp = From: "Obnoxio The Clown" <obnoxio@serendipita.com> = = To: ids@iiug.org = = Date: 09/02/2010 10:46 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2113= 1] = Sent by: ids-bounces@iiug.org = = Art Kagel wrote: > Attachments don't flow through the email gateway. Send it to my email= > address directly. What about the rest of us, FFS? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
A couple of things to note: 1. This is not an uncommon experience when moving from 7.31 to 11.50. I have a client with a similar problem, however, by working on the quality of the stats, adding a missing index, and optimizing the queries, I've generally gotten better performance out of 11.50. That said: 2. There is an outstanding concern in the community and within IBM about how 11.50 handles queries with many tables. See a similar post from about 2 weeks ago - an update was posted to that one yesterday indicating that IBM is finally leaning towards it being a bug. About the "quality" of the stats. If the tables have many rows, and especially if several of the tables have similar numbers of rows, the default resolution of the MEDIUM and HIGH stats may not be sufficient for the optimizer. You may have to increase the number of buckets in the MEDIUM and HIGH stats using the RESOLUTION clause if the number of unique key values for a column is large or if many keys are more numerous than other but not so much so as to move that value in to an overflow bucket. You can also use the SAMPLING SIZE option to increase the number of rows sampled to generate the MEDIUM stats. By default the maximum number of rows sampled is rather small, so for larger tables this may help. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:36 AM, Art Kagel <art.kagel@gmail.com> wrote: > Attachments don't flow through the email gateway. Send it to my email > address directly. > > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, implicitly, > or by inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor those > of the entities themselves. > > > > > On Thu, Sep 2, 2010 at 10:25 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > >> Here is a sample output! >> >> (See attached file: Update Stats.log) >> >> = >> >> From: "Art Kagel" <art.kagel@gmail.com> = >> >> = >> >> To: ids@iiug.org = >> >> = >> >> Date: 09/02/2010 10:10 AM = >> >> = >> >> Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112= >> 2] >> >> = >> >> Sent by: ids-bounces@iiug.org = >> >> = >> >> What level of statistics are you using on these tables? >> >> Art >> >> Art S. Kagel >> Advanced DataTools (www.advancedatatools.com) >> IIUG Board of Directors (art@iiug.org) >> >> Disclaimer: Please keep in mind that my own opinions are my own opinion= >> s >> and >> do not reflect on my employer, Advanced DataTools, the IIUG, nor any ot= >> her >> organization with which I am associated either explicitly, implicitly, = >> or >> by >> inference. Neither do those opinions reflect those of other individuals= >> >> affiliated with any entity with which I am affiliated nor those of the >> entities themselves. >> >> On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com >> <jpierrot@chubb.com>wrote: >> >> > I dropped the distributions again last night and run update statistic= >> s =3D >> > and >> > the query still hangs. Are there any variables out there that can for= >> ce=3D >> > the >> > optimizer to behave like in 7.31. >> > >> > Thanks! >> > >> > jp >> > >> > =3D >> > >> > From: "Obnoxio The Clown" <obnoxio@serendipita.com> =3D >> > >> > =3D >> > >> > To: ids@iiug.org =3D >> > >> > =3D >> > >> > Date: 09/01/2010 06:31 PM =3D >> > >> > =3D >> > >> > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111=3D >> > 1] >> > >> > =3D >> > >> > Sent by: ids-bounces@iiug.org =3D >> > >> > =3D >> > >> > jpierrot@chubb.com wrote: >> > > The problem seems to be with the second piece of the query or the u= >> ni=3D >> > on=3D3D >> > >> > It seems like 11.50 is doing a sequential scan of the employee table,= >> >> > rather than traversing an index. You absolutely sure the statistics a= >> re=3D >> > >> > kosher? >> > >> > -- >> > Cheers, >> > Obnoxio The Clown >> > >> > http://obotheclown.blogspot.com >> > I will now proceed to pleasure myself with this fish. >> > >> > -- >> > This message has been scanned for viruses and >> > dangerous content by OpenProtect(http://www.openprotect.com), and is >> > believed to be clean. >> > >> > *********************************************************************= >> **=3D >> > ******** >> > >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > =3D >> > >> > >> > >> > >> ***********************************************************************= >> ******** >> >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > >> >> --0050450175faf1dbe6048f475e82 >> >> ***********************************************************************= >> ******** >> >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> = >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --0016e64f8f4858cc35048f47f7e7
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table app nrows=3D 1341230 count(*)=3D 1341645
Index: 408_493 unique: 1341230
Index: app03n unique: 1583
Index: ix_app_subkind unique: 11
Index: 408_3086 unique: 767
Index: 408_3129 unique: 726
Index: 408_3130 unique: 3
Index: 408_3131 unique: 20
Index: 408_3135 unique: 13
Index: 408_3136 unique: 1572
Index: 408_3138 unique: 21
Index: i10app unique: 262
Index: ix_app1 unique: 349551
Index: ix_app2 unique: 14077
Index: ix_app3 unique: 509185
Index: ix_app4 unique: 5065
Index: ix_app5 unique: 89883
Index: ix_app6 unique: 1103
Index: ix_app7 unique: 136432
UPDATE STATISTICS LOW FOR TABLE app drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE app DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE app
(prdcrorgid,uwperid,prdcontperid,lobcd,apptype,appstatus,appid,apporgid=
,prdctcd,recvdate,mktseg,barcode,subkind,uwperid2,parntappid,rtlbrkrid,=
rtlbrkrcontid,prcsperid);
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table opt nrows=3D 1213769 count(*)=3D 1214184
Index: 876_3001 unique: 2
Index: 876_1946 unique: 1194564
Index: i10opt unique: 261
Index: i04_opt unique: 451483
Index: i09opt unique: 1194564
Index: i05_opt unique: 509667
Index: i07_opt unique: 81257
Index: i08_opt unique: 1194565
Index: i01_opt unique: 5158
Index: i02_opt unique: 3426
Index: i03_opt unique: 1112
Index: 876_2486 unique: 2469
Index: 876_2487 unique: 4475
Index: 876_2488 unique: 48
Index: 876_2489 unique: 13
Index: 876_2490 unique: 5
Index: 876_2491 unique: 2
Index: 876_2492 unique: 1005
Index: i06opt unique: 1498
UPDATE STATISTICS LOW FOR TABLE opt drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE opt DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE opt
(effdate,mktseg,prdcontperid,priorappid,recvdate,uwperid,apptypcd,prdct=
cd,priorpolid,programid,uwperid2,appid,siccd,show_prodrenlist,source_in=
d,slprdcontperid,slprdcrorgid);
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table employee nrows=3D 4964 count(*)=3D 4964
Index: 349_394 unique: 4964
Index: i01employee unique: 4964
Index: i04employee unique: 4964
Index: 349_2106 unique: 84
Index: ix_employee1 unique: 2695
Index: ix_employee2 unique: 3626
UPDATE STATISTICS LOW FOR TABLE employee drop distributions;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table prdct nrows=3D 305 count(*)=3D 305
Index: 767_1502 unique: 305
Index: 767_2174 unique: 12
Index: 767_2176 unique: 2
Index: 767_2215 unique: 2
Index: 767_2290 unique: 19
Index: 767_2477 unique: 3
Index: 767_2632 unique: 3
Index: 767_2633 unique: 2
UPDATE STATISTICS LOW FOR TABLE prdct drop distributions;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table org_insured nrows=3D 640479 count(*)=3D 6408=
36
Index: 739_1381 unique: 640479
Index: 739_1382 unique: 640479
Index: u_org_insured02 unique: 640479
Index: i_org_insured01 unique: 418343
Index: 739_2381 unique: 4
Index: 739_4864 unique: 116
UPDATE STATISTICS LOW FOR TABLE org_insured drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE org_insured DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE org_insured
(chbinsurednum,orgid,bp_gin,compustat_gics_key,exemption_ty_cd);-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table org_producer nrows=3D 65806 count(*)=3D 65=
863
Index: 515_698 unique: 65806
Index: 515_699 unique: 65806
Index: ix_op_branchnum unique: 171
Index: ix_op_chbprodnum unique: 54909
Index: u_org_producer02 unique: 65806
Index: 515_2265 unique: 1
UPDATE STATISTICS LOW FOR TABLE org_producer drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE org_producer DISTRIBUTIONS ONLY ;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table org nrows=3D 720090 count(*)=3D 720090
Index: 627_1010 unique: 720090
Index: 627_1011 unique: 720090
Index: orgkind unique: 10
Index: u_org02 unique: 720090
Index: i04_org unique: 152
Index: i06_org unique: 35
Index: 627_2053 unique: 121
Index: 627_2054 unique: 3
Index: 627_2475 unique: 3
Index: 627_2476 unique: 3
Index: ix_org1 unique: 5179
Index: ix_org2 unique: 4
Index: ix_org3 unique: 277636
Index: ix_org4 unique: 640440
Index: ix_org5 unique: 652517
Index: ix_org6 unique: 285186
Index: ix_org7 unique: 65871
Index: ix_org8 unique: 85421
Index: ix_org9 unique: 18252
Index: ix_org10 unique: 689310
Index: ix_org11 unique: 31427
Index: ix_org12 unique: 1160
Index: 627_3283 unique: 1068
Index: 627_3285 unique: 152
UPDATE STATISTICS LOW FOR TABLE org drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE org DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE org
(domstatecd,siccd,incstatecd,parntorgid,cityname,name,kind,zip,mstracct=
,legalname,riskmgrperid,lastupdate,soundexcode,acctnum,prodnum,orgid,cu=
rrency_cd,intfc_dt,taxstatus,tsales,ownership,countrycd,bp_gin);
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table chb_branch nrows=3D 248 count(*)=3D 24=
8
Index: 517_708 unique: 248
Index: 517_6187 unique: 15
Index: 517_2078 unique: 14
Index: 517_2112 unique: 115
UPDATE STATISTICS LOW FOR TABLE chb_branch drop distributions;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table pol nrows=3D 993764 count(*)=3D 993996
Index: 582_850 unique: 993764
Index: i01pol unique: 993764
Index: i02pol unique: 512189
Index: i03pol unique: 355322
Index: i05pol unique: 10322
Index: i09pol unique: 894710
Index: i06pol unique: 8516
Index: i07pol unique: 447082
Index: i10pol unique: 574968
Index: i11pol unique: 301934
Index: i12pol unique: 607272
Index: i13pol unique: 5933
Index: i14pol unique: 7735
Index: i08pol unique: 301951
Index: i04pol unique: 607272
Index: 582_2055 unique: 340
Index: 582_2056 unique: 395
Index: 582_2057 unique: 364
Index: 582_2058 unique: 2554
Index: 582_2059 unique: 4917
Index: 582_2060 unique: 13
Index: 582_2061 unique: 2
Index: 582_2076 unique: 282303
Index: 582_2097 unique: 8
Index: 582_2109 unique: 4
Index: 582_2110 unique: 35
Index: 582_2111 unique: 1432
Index: 582_2170 unique: 8
Index: 582_2171 unique: 6
UPDATE ST
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table app nrows=3D 1341230 count(*)=3D 1341645
Index: 408_493 unique: 1341230
Index: app03n unique: 1583
Index: ix_app_subkind unique: 11
Index: 408_3086 unique: 767
Index: 408_3129 unique: 726
Index: 408_3130 unique: 3
Index: 408_3131 unique: 20
Index: 408_3135 unique: 13
Index: 408_3136 unique: 1572
Index: 408_3138 unique: 21
Index: i10app unique: 262
Index: ix_app1 unique: 349551
Index: ix_app2 unique: 14077
Index: ix_app3 unique: 509185
Index: ix_app4 unique: 5065
Index: ix_app5 unique: 89883
Index: ix_app6 unique: 1103
Index: ix_app7 unique: 136432
UPDATE STATISTICS LOW FOR TABLE app drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE app DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE app
(prdcrorgid,uwperid,prdcontperid,lobcd,apptype,appstatus,appid,apporgid=
,prdctcd,recvdate,mktseg,barcode,subkind,uwperid2,parntappid,rtlbrkrid,=
rtlbrkrcontid,prcsperid);
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table opt nrows=3D 1213769 count(*)=3D 1214184
Index: 876_3001 unique: 2
Index: 876_1946 unique: 1194564
Index: i10opt unique: 261
Index: i04_opt unique: 451483
Index: i09opt unique: 1194564
Index: i05_opt unique: 509667
Index: i07_opt unique: 81257
Index: i08_opt unique: 1194565
Index: i01_opt unique: 5158
Index: i02_opt unique: 3426
Index: i03_opt unique: 1112
Index: 876_2486 unique: 2469
Index: 876_2487 unique: 4475
Index: 876_2488 unique: 48
Index: 876_2489 unique: 13
Index: 876_2490 unique: 5
Index: 876_2491 unique: 2
Index: 876_2492 unique: 1005
Index: i06opt unique: 1498
UPDATE STATISTICS LOW FOR TABLE opt drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE opt DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE opt
(effdate,mktseg,prdcontperid,priorappid,recvdate,uwperid,apptypcd,prdct=
cd,priorpolid,programid,uwperid2,appid,siccd,show_prodrenlist,source_in=
d,slprdcontperid,slprdcrorgid);
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table employee nrows=3D 4964 count(*)=3D 4964
Index: 349_394 unique: 4964
Index: i01employee unique: 4964
Index: i04employee unique: 4964
Index: 349_2106 unique: 84
Index: ix_employee1 unique: 2695
Index: ix_employee2 unique: 3626
UPDATE STATISTICS LOW FOR TABLE employee drop distributions;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table prdct nrows=3D 305 count(*)=3D 305
Index: 767_1502 unique: 305
Index: 767_2174 unique: 12
Index: 767_2176 unique: 2
Index: 767_2215 unique: 2
Index: 767_2290 unique: 19
Index: 767_2477 unique: 3
Index: 767_2632 unique: 3
Index: 767_2633 unique: 2
UPDATE STATISTICS LOW FOR TABLE prdct drop distributions;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table org_insured nrows=3D 640479 count(*)=3D 6408=
36
Index: 739_1381 unique: 640479
Index: 739_1382 unique: 640479
Index: u_org_insured02 unique: 640479
Index: i_org_insured01 unique: 418343
Index: 739_2381 unique: 4
Index: 739_4864 unique: 116
UPDATE STATISTICS LOW FOR TABLE org_insured drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE org_insured DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE org_insured
(chbinsurednum,orgid,bp_gin,compustat_gics_key,exemption_ty_cd);-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table org_producer nrows=3D 65806 count(*)=3D 65=
863
Index: 515_698 unique: 65806
Index: 515_699 unique: 65806
Index: ix_op_branchnum unique: 171
Index: ix_op_chbprodnum unique: 54909
Index: u_org_producer02 unique: 65806
Index: 515_2265 unique: 1
UPDATE STATISTICS LOW FOR TABLE org_producer drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE org_producer DISTRIBUTIONS ONLY ;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table org nrows=3D 720090 count(*)=3D 720090
Index: 627_1010 unique: 720090
Index: 627_1011 unique: 720090
Index: orgkind unique: 10
Index: u_org02 unique: 720090
Index: i04_org unique: 152
Index: i06_org unique: 35
Index: 627_2053 unique: 121
Index: 627_2054 unique: 3
Index: 627_2475 unique: 3
Index: 627_2476 unique: 3
Index: ix_org1 unique: 5179
Index: ix_org2 unique: 4
Index: ix_org3 unique: 277636
Index: ix_org4 unique: 640440
Index: ix_org5 unique: 652517
Index: ix_org6 unique: 285186
Index: ix_org7 unique: 65871
Index: ix_org8 unique: 85421
Index: ix_org9 unique: 18252
Index: ix_org10 unique: 689310
Index: ix_org11 unique: 31427
Index: ix_org12 unique: 1160
Index: 627_3283 unique: 1068
Index: 627_3285 unique: 152
UPDATE STATISTICS LOW FOR TABLE org drop distributions;
UPDATE STATISTICS MEDIUM FOR TABLE org DISTRIBUTIONS ONLY ;
UPDATE STATISTICS HIGH FOR TABLE org
(domstatecd,siccd,incstatecd,parntorgid,cityname,name,kind,zip,mstracct=
,legalname,riskmgrperid,lastupdate,soundexcode,acctnum,prodnum,orgid,cu=
rrency_cd,intfc_dt,taxstatus,tsales,ownership,countrycd,bp_gin);
-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table chb_branch nrows=3D 248 count(*)=3D 24=
8
Index: 517_708 unique: 248
Index: 517_6187 unique: 15
Index: 517_2078 unique: 14
Index: 517_2112 unique: 115
UPDATE STATISTICS LOW FOR TABLE chb_branch drop distributions;-- Update Statistics Report for the q2uws database
-- Created: 09/02/2010
Processing table pol nrows=3D 993764 count(*)=3D 993996
Index: 582_850 unique: 993764
Index: i01pol unique: 993764
Index: i02pol unique: 512189
Index: i03pol unique: 355322
Index: i05pol unique: 10322
Index: i09pol unique: 894710
Index: i06pol unique: 8516
Index: i07pol unique: 447082
Index: i10pol unique: 574968
Index: i11pol unique: 301934
Index: i12pol unique: 607272
Index: i13pol unique: 5933
Index: i14pol unique: 7735
Index: i08pol unique: 301951
Index: i04pol unique: 607272
Index: 582_2055 unique: 340
Index: 582_2056 unique: 395
Index: 582_2057 unique: 364
Index: 582_2058 unique: 2554
Index: 582_2059 unique: 4917
Index: 582_2060 unique: 13
Index: 582_2061 unique: 2
Index: 582_2076 unique: 282303
Index: 582_2097 unique: 8
Index: 582_2109 unique: 4
Index: 582_2110 unique: 35
Index: 582_2111 unique: 1432
Index: 582_2170 unique: 8
Index: 582_2171 unique: 6
UPDATE ST
OK, email it to everyone! Or if it's not too long, post it in the message body. I hope he isn't running an SAP system! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:46 AM, Obnoxio The Clown <obnoxio@serendipita.com>wrote: > Art Kagel wrote: > > Attachments don't flow through the email gateway. Send it to my email > > address directly. > > What about the rest of us, FFS? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e645b7cad5f9cc048f48059f
Thanks! jp = From: Art Kagel <art.kagel@gmail.com> = = To: ids@iiug.org, jpierrot@chubb.com = = Date: 09/02/2010 10:52 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [21126= ] = A couple of things to note: 1. This is not an uncommon experience when moving from 7.31 to 11.50= .=A0 I have a client with a similar problem, however, by working on the quality of the stats, adding a missing index, and optimizing the queries, I've generally gotten better performance out of 11.50.=A0= That said: 2. There is an outstanding concern in the community and within IBM a= bout how 11.50 handles queries with many tables.=A0 See a similar post= from about 2 weeks ago - an update was posted to that one yesterday indicating that IBM is finally leaning towards it being a bug. About the "quality" of the stats.=A0 If the tables have many rows, and especially if several of the tables have similar numbers of rows, the default resolution of the MEDIUM and HIGH stats may not be sufficient f= or the optimizer.=A0 You may have to increase the number of buckets in the= MEDIUM and HIGH stats using the RESOLUTION clause if the number of uniq= ue key values for a column is large or if many keys are more numerous than= other but not so much so as to move that value in to an overflow bucket= . You can also use the SAMPLING SIZE option to increase the number of row= s sampled to generate the MEDIUM stats.=A0 By default the maximum number = of rows sampled is rather small, so for larger tables this may help. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinion= s and do not reflect on my employer, Advanced DataTools, the IIUG, nor an= y other organization with which I am associated either explicitly, implicitly, or by inference.=A0 Neither do those opinions reflect those= of other individuals affiliated with any entity with which I am affiliated= nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:36 AM, Art Kagel <art.kagel@gmail.com> wrote:= Attachments don't flow through the email gateway.=A0 Send it to my em= ail address directly. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opini= ons and do not reflect on my employer, Advanced DataTools, the IIUG, nor = any other organization with which I am associated either explicitly, implicitly, or by inference.=A0 Neither do those opinions reflect tho= se of other individuals affiliated with any entity with which I am affiliat= ed nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:25 AM, jpierrot@chubb.com <jpierrot@chubb.c= om> wrote: Here is a sample output! (See attached file: Update Stats.log) =3D From: "Art Kagel" <art.kagel@gmail.com> =3D =3D To: ids@iiug.org =3D =3D Date: 09/02/2010 10:10 AM =3D =3D Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112=3D= 2] =3D Sent by: ids-bounces@iiug.org =3D =3D What level of statistics are you using on these tables? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opin= ion=3D s and do not reflect on my employer, Advanced DataTools, the IIUG, nor any= ot=3D her organization with which I am associated either explicitly, implicitl= y, =3D or by inference. Neither do those opinions reflect those of other individu= als=3D affiliated with any entity with which I am affiliated nor those of t= he entities themselves. On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > I dropped the distributions again last night and run update statis= tic=3D s =3D3D > and > the query still hangs. Are there any variables out there that can = for=3D ce=3D3D > the > optimizer to behave like in 7.31. > > Thanks! > > jp > > =3D3D > > From: "Obnoxio The Clown" <obnoxio@serendipita.com> =3D3D > > =3D3D > > To: ids@iiug.org =3D3D > > =3D3D > > Date: 09/01/2010 06:31 PM =3D3D > > =3D3D > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111=3D= 3D > 1] > > =3D3D > > Sent by: ids-bounces@iiug.org =3D3D > > =3D3D > > jpierrot@chubb.com wrote: > > The problem seems to be with the second piece of the query or th= e u=3D ni=3D3D > on=3D3D3D > > It seems like 11.50 is doing a sequential scan of the employee tab= le,=3D > rather than traversing an index. You absolutely sure the statistic= s a=3D re=3D3D > > kosher? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and = is > believed to be clean. > > ******************************************************************= ***=3D **=3D3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum= . > > =3D3D > > > > ********************************************************************= ***=3D ******** > Forum Note: Use "Reply" to post a response in the discussion forum= . > > --0050450175faf1dbe6048f475e82 ********************************************************************= ***=3D ******** Forum Note: Use "Reply" to post a response in the discussion forum. =3D ********************************************************************= *********** =A0Forum Note: Use "Reply" to post a response in the discussion foru= m. =
I'd just add LOWs on each multi-column index key after the HIGH. There's a debate about whether those are needed, but I've found that they improve the usage of those multi-column indexes. Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:54 AM, <jpierrot@chubb.com> wrote: > -- Update Statistics Report for the q2uws database > -- Created: 09/02/2010 > Processing table app nrows= 1341230 count(*)= 1341645 > Index: 408_493 unique: 1341230 > > Index: app03n unique: 1583 > > Index: ix_app_subkind unique: 11 > > Index: 408_3086 unique: 767 > <SNIP> --0016e644df4ccb0ae6048f4853c7
Also 'if in the like clause we have 0,1,2,3 at the beginning" AND B.NAM= E like 'Josu%' or '0Josu%' , '1Josu%' and so forth, it will clock but if = you start it with 4,5,6,7,8,9 , for example '4Josu%' it works fine, and it returns the data in split seconds. Crazy uh!!! jp = From: "jpierrot@chubb.com" <jpierrot@chubb.com> = = To: ids@iiug.org = = Date: 09/02/2010 10:57 AM = = Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2113= 9] = Sent by: ids-bounces@iiug.org = = Thanks! jp =3D From: Art Kagel <art.kagel@gmail.com> =3D =3D To: ids@iiug.org, jpierrot@chubb.com =3D =3D Date: 09/02/2010 10:52 AM =3D =3D Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [21126=3D ] =3D A couple of things to note: 1. This is not an uncommon experience when moving from 7.31 to 11.50=3D= ..=3DA0 I have a client with a similar problem, however, by working on the quality of the stats, adding a missing index, and optimizing the queries, I've generally gotten better performance out of 11.50.=3DA0=3D= That said: 2. There is an outstanding concern in the community and within IBM a=3D= bout how 11.50 handles queries with many tables.=3DA0 See a similar post=3D from about 2 weeks ago - an update was posted to that one yesterday indicating that IBM is finally leaning towards it being a bug. About the "quality" of the stats.=3DA0 If the tables have many rows, an= d especially if several of the tables have similar numbers of rows, the default resolution of the MEDIUM and HIGH stats may not be sufficient f= =3D or the optimizer.=3DA0 You may have to increase the number of buckets in t= he=3D MEDIUM and HIGH stats using the RESOLUTION clause if the number of uniq= =3D ue key values for a column is large or if many keys are more numerous than= =3D other but not so much so as to move that value in to an overflow bucket= =3D .. You can also use the SAMPLING SIZE option to increase the number of row= =3D s sampled to generate the MEDIUM stats.=3DA0 By default the maximum numbe= r =3D of rows sampled is rather small, so for larger tables this may help. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinion= =3D s and do not reflect on my employer, Advanced DataTools, the IIUG, nor an= =3D y other organization with which I am associated either explicitly, implicitly, or by inference.=3DA0 Neither do those opinions reflect tho= se=3D of other individuals affiliated with any entity with which I am affiliated= =3D nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:36 AM, Art Kagel <art.kagel@gmail.com> wrote:= =3D Attachments don't flow through the email gateway.=3DA0 Send it to my em= =3D ail address directly. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opini=3D= ons and do not reflect on my employer, Advanced DataTools, the IIUG, nor =3D= any other organization with which I am associated either explicitly, implicitly, or by inference.=3DA0 Neither do those opinions reflect tho= =3D se of other individuals affiliated with any entity with which I am affiliat=3D= ed nor those of the entities themselves. On Thu, Sep 2, 2010 at 10:25 AM, jpierrot@chubb.com <jpierrot@chubb.c=3D= om> wrote: Here is a sample output! (See attached file: Update Stats.log) =3D3D From: "Art Kagel" <art.kagel@gmail.com> =3D3D =3D3D To: ids@iiug.org =3D3D =3D3D Date: 09/02/2010 10:10 AM =3D3D =3D3D Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2112=3D3D=3D= 2] =3D3D Sent by: ids-bounces@iiug.org =3D3D =3D3D What level of statistics are you using on these tables? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opin=3D= ion=3D3D s and do not reflect on my employer, Advanced DataTools, the IIUG, nor any=3D= ot=3D3D her organization with which I am associated either explicitly, implicitl=3D= y, =3D3D or by inference. Neither do those opinions reflect those of other individu=3D= als=3D3D affiliated with any entity with which I am affiliated nor those of t=3D= he entities themselves. On Thu, Sep 2, 2010 at 9:36 AM, jpierrot@chubb.com <jpierrot@chubb.com>wrote: > I dropped the distributions again last night and run update statis=3D= tic=3D3D s =3D3D3D > and > the query still hangs. Are there any variables out there that can =3D= for=3D3D ce=3D3D3D > the > optimizer to behave like in 7.31. > > Thanks! > > jp > > =3D3D3D > > From: "Obnoxio The Clown" <obnoxio@serendipita.com> =3D3D3D > > =3D3D3D > > To: ids@iiug.org =3D3D3D > > =3D3D3D > > Date: 09/01/2010 06:31 PM =3D3D3D > > =3D3D3D > > Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2111=3D3= D=3D 3D > 1] > > =3D3D3D > > Sent by: ids-bounces@iiug.org =3D3D3D > > =3D3D3D > > jpierrot@chubb.com wrote: > > The problem seems to be with the second piece of the query or th=3D= e u=3D3D ni=3D3D3D > on=3D3D3D3D > > It seems like 11.50 is doing a sequential scan of the employee tab=3D= le,=3D3D > rather than traversing an index. You absolutely sure the statistic=3D= s a=3D3D re=3D3D3D > > kosher? > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and =3D= is > believed to be clean. > > ******************************************************************=3D= ***=3D3D **=3D3D3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum=3D= .. > > =3D3D3D > @@