Ingres 2.6 03/05 9889 (rs4.us5/00) Aix 5.2

Environment is Multi (5) dbms servers, OS threads, shared cache.

We had a problem today with a one of the dbms servers, it appears that
the server had problems dealing with a large SQL query the following
error was produced.

BIRDB002::[/tmp/ii.29000 , 38006200]: Tue Aug 30 11:14:06 2005
E_OP0901_UNKNOWN_EXCEPTION
Unknown exception occurred in optimizer utility. Value is 68197.
(full errlog.log pasted below)

CA have suggested beefing up some parameters:
stack_size (currently 196608), OPF (20Mb) & QSF (20Mb) memory,
(The servers are configures for 500 connected sessions)
and we will probably make the suggested changes.

This appears to be a query blowing its Server memory limitations,
however on 2 previous occasions this happened during routine SQL
activity (we think) and there are other similarities between
this and the other occurances;

The similarities are:
1) A user aborted a running SQL query just prior to the server going
haywire.
2) The server was rapidly expanding its OPF and other cache(s) just
prior to the problem.

Has anyone seen anything similar?

TIA
Steve



Section from errlog.log
E_OP0901_UNKNOWN_EXCEPTION Unknown exception occurred in optimizer
utility.
Value is 68197.
BIRDB002::[/tmp/ii.29000 , 38006200]: An error occurred in the
following
session:
BIRDB002::[/tmp/ii.29000 , 38006200]: >>>>>Session
38006200:38406<<<<<
BIRDB002::[/tmp/ii.29000 , 38006200]: DB Name: merg
(Owned by: ingres )
BIRDB002::[/tmp/ii.29000 , 38006200]: User: ethnic
(ethnic )
BIRDB002::[/tmp/ii.29000 , 38006200]: User Name at Session
Startup:
ethnic
BIRDB002::[/tmp/ii.29000 , 38006200]: Terminal: batch
BIRDB002::[/tmp/ii.29000 , 38006200]: Group Id:
BIRDB002::[/tmp/ii.29000 , 38006200]: Role Id:
BIRDB002::[/tmp/ii.29000 , 38006200]: Application Code:
00000000
Current Facility: OPF (00000004)
BIRDB002::[/tmp/ii.29000 , 38006200]: Client user: xxxxxxxxx
BIRDB002::[/tmp/ii.29000 , 38006200]: Client host: BIR10393
BIRDB002::[/tmp/ii.29000 , 38006200]: Client tty: 10393
BIRDB002::[/tmp/ii.29000 , 38006200]: Client pid: 1228
BIRDB002::[/tmp/ii.29000 , 38006200]: Client connection target:

AIX1::merg/INGRES
BIRDB002::[/tmp/ii.29000 , 38006200]: Client information:
user='xxxxxxxx',host='BIR10393',tty='BIR10393',pid =1228,conn='AIX1
::merg/INGRES'
BIRDB002::[/tmp/ii.29000 , 38006200]: Description:
BIRDB002::[/tmp/ii.29000 , 38006200]: Query: SELECT
"region"."region", "offices"."office_name",
"subject"."case_id", "subject"."subj_refno",
"ethnic_information"."group_id",
"offices"."office_id", "case_open"."insol_type",
"case_subj_event"."act_date",
"case_subj_event"."event_type", "subject"."subj_type",
"case_event"."event_type", "case_event"."event_date",
"case_event"."act_date"
FROM ((((("ingres"."case_open" "case_open" INNER JOIN
"ingres"."subject"
"subject" ON (((("case_open"."court"="subject"."court") AND
("case_open"."court_no"="subject"."court_no")) AND
("case_open"."yr"="subject"."yr")) AND
("case_open"."insol_type"="subject"."insol_type")) AND
("case_open"."case_id"="subject"."case_id")) INNER JOIN
"ingres"."case_event"
"case_event" ON (((("case_open"."case_id"="case_event"."case_id") AND
("case_open"."court"="case_event"."court")) AND
("case_open"."court_no"="case_event"."court_no" )) AND
("case_open"."yr"="case_event"."yr")) AND
("case_open"."insol_type"="case_event"."insol_type ")) INNER JOIN
"ingres"."offices" "offices" ON
"case_open"."owner"="offices"."office_id")
LEFT
JOIN "ingres"."ethnic_information" "ethnic_information" ON
("subject"."subj_refno"="ethnic_information"."subj _refno") AND
("subject"."case_id"="ethnic_information"."case_id ")) INNER JOIN
"ingres"."case_subj_event" "case_subj_event" ON ((((
("subject"."court"="case_subj_event"."court") AND
("subject"."court_no"="case_subj_event"."court_no" )) AND
("subject"."yr"="case_subj_event"."yr")) AND
("subject"."insol_type"="cas
BIRDB002::[/tmp/ii.29000 , 38006200]: e_subj_event
birdb002::[/tmp/ii.29000 , 38006200]: Tue Aug 30 11:14:06 2005
Segmentation Violation (SIGSEGV)
BIRDB002::[/tmp/ii.29000 , 38006200]: Tue Aug 30 11:14:06 2005
E_OP0082_UNEXPECTED_EX consistency check - unexpected
exception
occurred
Associated error messages which provide more detailed information about
the
problem can be found in the error log (errlog.log)
BIRDB002::[/tmp/ii.29000 , 38006200]: An error occurred in the
following
session:
BIRDB002::[/tmp/ii.29000 , 38006200]: >>>>>Session
38006200:38406<<<<<
BIRDB002::[/tmp/ii.29000 , 38006200]: DB Name: merg
(Owned by: ingres )
BIRDB002::[/tmp/ii.29000 , 38006200]: User: ethnic
(ethnic )
BIRDB002::[/tmp/ii.29000 , 38006200]: User Name at Session
Startup:
ethnic
BIRDB002::[/tmp/ii.29000 , 38006200]: Terminal: batch
BIRDB002::[/tmp/ii.29000 , 38006200]: Group Id:
BIRDB002::[/tmp/ii.29000 , 38006200]: Role Id:
BIRDB002::[/tmp/ii.29000 , 38006200]: Application Code:
00000000
Current Facility: OPF (00000004)
BIRDB002::[/tmp/ii.29000 , 38006200]: Client user: xxxxxxxxx
BIRDB002::[/tmp/ii.29000 , 38006200]: Client host: BIR10393
BIRDB002::[/tmp/ii.29000 , 38006200]: Client tty: BIR10393
BIRDB002::[/tmp/ii.29000 , 38006200]: Client pid: 1228
BIRDB002::[/tmp/ii.29000 , 38006200]: Client connection target:

AIX1::merg/INGRES
BIRDB002::[/tmp/ii.29000 , 38006200]: Client information:
user='xxxxxxxx',host='BIR10393',tty='BIR10393',pid =1228,conn='AIX1
::merg/INGRES'
BIRDB002::[/tmp/ii.29000 , 38006200]: Description:
BIRDB002::[/tmp/ii.29000 , 38006200]: Query: SELECT
"region"."region", "offices"."office_name",
"subject"."case_id", "subject"."subj_refno",
"ethnic_information"."group_id",
"offices"."office_id", "case_open"."insol_type",
"case_subj_event"."act_date",
"case_subj_event"."event_type", "subject"."subj_type",
"case_event"."event_type", "case_event"."event_date",
"case_event"."act_date"
FROM ((((("ingres"."case_open" "case_open" INNER JOIN
"ingres"."subject"
"subject" ON (((("case_open"."court"="subject"."court") AND
("case_open"."court_no"="subject"."court_no")) AND
("case_open"."yr"="subject"."yr")) AND
("case_open"."insol_type"="subject"."insol_type")) AND
("case_open"."case_id"="subject"."case_id")) INNER JOIN
"ingres"."case_event"
"case_event" ON (((("case_open"."case_id"="case_event"."case_id") AND
("case_open"."court"="case_event"."court")) AND
("case_open"."court_no"="case_event"."court_no" )) AND
("case_open"."yr"="case_event"."yr")) AND
("case_open"."insol_type"="case_event"."insol_type ")) INNER JOIN
"ingres"."offices" "offices" ON
"case_open"."owner"="offices"."office_id")
LEFT
JOIN "ingres"."ethnic_information" "ethnic_information" ON
("subject"."subj_refno"="ethnic_information"."subj _refno") AND
("subject"."case_id"="ethnic_information"."case_id ")) INNER JOIN
"ingres"."case_subj_event" "case_subj_event" ON ((((
("subject"."court"="case_subj_event"."court") AND
("subject"."court_no"="case_subj_event"."court_no" )) AND
("subject"."yr"="case_subj_event"."yr")) AND
("subject"."insol_type"="cas
BIRDB002::[/tmp/ii.29000 , 38006200]: e_subj_event
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Tue Aug 30 11:14:52 2005
E_OP04AA_HISTOMOD Histogram modification failed. Original histogram
will
be used.
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: An error occurred in the
following
session:
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: >>>>>Session
3A1B8240:23234<<<<<
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: DB Name: merg
(Owned by: ingres )
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: User: dafe67
(dafe67 )
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: User Name at Session
Startup:
dafe67
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Terminal: pts/369
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Group Id:
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Role Id:
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Application Code:
00000000
Current Facility: OPF (00000004)
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Client user: dafe67
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Client host: birdb002
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Client tty: 369
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Client pid: 247514
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Client connection target:

merg
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Client information:
user='dafe67',host='birdb002',tty='369',pid=247514 ,conn='merg'
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Description:
BIRDB002::[/tmp/ii.29000 , 3a1b8240]: Query: select
co.case_name as
name, co.court as court, co.court_no as
court_no, co.yr as yr, co.insol_type as insol_type, o.office_name as
owner_office from case_open co, office o where case_id= ~V and
o.office_id=
~V

BIRDB002::[/tmp/ii.29000 , 3a212200]: Tue Aug 30 11:14:56 2005
E_OP0487_NOEQCLS consistency check - no joining equivalence
class
found when expected
BIRDB002::[/tmp/ii.29000 , 3a212200]: An error occurred in the
following
session:
BIRDB002::[/tmp/ii.29000 , 3a212200]: >>>>>Session
3A212200:22348<<<<<
BIRDB002::[/tmp/ii.29000 , 3a212200]: DB Name: merg
(Owned by: ingres )
BIRDB002::[/tmp/ii.29000 , 3a212200]: User: ethnic
(ethnic )
BIRDB002::[/tmp/ii.29000 , 3a212200]: User Name at Session
Startup:
ethnic
BIRDB002::[/tmp/ii.29000 , 3a212200]: Terminal: batch
BIRDB002::[/tmp/ii.29000 , 3a212200]: Group Id:
BIRDB002::[/tmp/ii.29000 , 3a212200]: Role Id:
BIRDB002::[/tmp/ii.29000 , 3a212200]: Application Code:
00000000
Current Facility: OPF (00000004)
BIRDB002::[/tmp/ii.29000 , 3a212200]: Client user: xxxxxxxx
BIRDB002::[/tmp/ii.29000 , 3a212200]: Client host: BIR10393
BIRDB002::[/tmp/ii.29000 , 3a212200]: Client tty: BIR10393
BIRDB002::[/tmp/ii.29000 , 3a212200]: Client pid: 1228
BIRDB002::[/tmp/ii.29000 , 3a212200]: Client connection target:

AIX1::merg/INGRES
BIRDB002::[/tmp/ii.29000 , 3a212200]: Client information:
user='xxxxxxxx',host='BIR10393',tty='BIR10393',pid =1228,conn='AIX1
::mergedb/INGRES'
BIRDB002::[/tmp/ii.29000 , 3a212200]: Description:
BIRDB002::[/tmp/ii.29000 , 3a212200]: Query: SELECT
"region"."region", "offices"."office_name",
"subject"."case_id", "subject"."subj_refno",
"ethnic_information"."group_id",
"offices"."office_id", "case_open"."insol_type",
"case_subj_event"."act_date",
"case_subj_event"."event_type", "subject"."subj_type",
"case_event"."event_type", "case_event"."event_date",
"case_event"."act_date"
FROM ((((("ingres"."case_open" "case_open" INNER JOIN
"ingres"."subject"
"subject" ON (((("case_open"."court"="subject"."court") AND
("case_open"."court_no"="subject"."court_no")) AND
("case_open"."yr"="subject"."yr")) AND
("case_open"."insol_type"="subject"."insol_type")) AND
("case_open"."case_id"="subject"."case_id")) INNER JOIN
"ingres"."case_event"
"case_event" ON (((("case_open"."case_id"="case_event"."case_id") AND
("case_open"."court"="case_event"."court")) AND
("case_open"."court_no"="case_event"."court_no" )) AND
("case_open"."yr"="case_event"."yr")) AND
("case_open"."insol_type"="case_event"."insol_type ")) INNER JOIN
"ingres"."offices" "offices" ON
"case_open"."owner"="offices"."office_id")
LEFT
JOIN "ingres"."ethnic_information" "ethnic_information" ON
("subject"."subj_refno"="ethnic_information"."subj _refno") AND
("subject"."case_id"="ethnic_information"."case_id ")) INNER JOIN
"ingres"."case_subj_event" "case_subj_event" ON ((((
("subject"."court"="case_subj_event"."court") AND
("subject"."court_no"="case_subj_event"."court_no" )) AND
("subject"."yr"="case_subj_event"."yr")) AND
("subject"."insol_type"="cas
BIRDB002::[/tmp/ii.29000 , 3a212200]: e_subj_event
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Tue Aug 30 11:15:44 2005
E_OP04AA_HISTOMOD Histogram modification failed. Original histogram
will
be used.
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: An error occurred in the
following
session:
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: >>>>>Session
3A4A8200:20890<<<<<
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: DB Name: merg
(Owned by: ingres )
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: User: daco27
(daco27 )
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: User Name at Session
Startup:
daco27
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Terminal: pts/316
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Group Id:
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Role Id:
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Application Code:
00000000
Current Facility: OPF (00000004)
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Client user: daco27
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Client host: birdb002
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Client tty: 316
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Client pid: 160632
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Client connection target:

merg
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Client information:
user='daco27',host='birdb002',tty='316',pid=160632 ,conn='merg'
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Description:
BIRDB002::[/tmp/ii.29000 , 3a4a8200]: Query: select count (*)
as
h_funccount from functions where func_grp is null and func_opt_no= ~V
BIRDB002::[/tmp/ii.29000 , 37608200]: Tue Aug 30 11:15:46 2005
E_OP04AA_HISTOMOD Histogram modification failed. Original histogram
will
be used.
BIRDB002::[/tmp/ii.29000 , 37608200]: An error occurred in the
following
session:
BIRDB002::[/tmp/ii.29000 , 37608200]: >>>>>Session
37608200:41530<<<<<
BIRDB002::[/tmp/ii.29000 , 37608200]: DB Name: merg
(Owned by: ingres )
BIRDB002::[/tmp/ii.29000 , 37608200]: User: laca26
(laca26 )
BIRDB002::[/tmp/ii.29000 , 37608200]: User Name at Session
Startup:
laca26
BIRDB002::[/tmp/ii.29000 , 37608200]: Terminal: pts/1119
BIRDB002::[/tmp/ii.29000 , 37608200]: Group Id:
BIRDB002::[/tmp/ii.29000 , 37608200]: Role Id:
BIRDB002::[/tmp/ii.29000 , 37608200]: Application Code:
00000000
Current Facility: OPF (00000004)
BIRDB002::[/tmp/ii.29000 , 37608200]: Client user: laca26
BIRDB002::[/tmp/ii.29000 , 37608200]: Client host: birdb002
BIRDB002::[/tmp/ii.29000 , 37608200]: Client tty: 1119
BIRDB002::[/tmp/ii.29000 , 37608200]: Client pid: 198064
BIRDB002::[/tmp/ii.29000 , 37608200]: Client connection target:

merg
BIRDB002::[/tmp/ii.29000 , 37608200]: Client information:
user='laca26',host='birdb002',tty='1119',pid=19806 4,conn='merg'
BIRDB002::[/tmp/ii.29000 , 37608200]: Description:
BIRDB002::[/tmp/ii.29000 , 37608200]: Query: select case_id as
h_caseid from case_open where court= ~V and court_no= ~V and yr= ~V
and
insol_type= ~V
BIRDB002::[/tmp/ii.29000 , 37608200]: Tue Aug 30 11:15:46 2005
E_OP04AA_HISTOMOD Histogram modification failed. Original histogram
will
be used.
<<<<<<<<<<<<<<<<<<<<<<<<

At 1:31 PM -0700 8/30/2005, Steve McElhinney wrote:

I'd agree with a bit more stack and maybe 2x or 3x the OPF memory.
I doubt that QSF memory has anything to do with it, but doubling
the size won't hurt anything.

OPF is supposed to deal nicely with running out of memory, but it doesn't
always, I guess. Or, you're falling off the end of a stack, which is
almost guaranteed to produce weird random errors. That's usually
(not always) reproducible in isolation, though.

One other thing: take a look at opf_active_limit and opf_maxmemf.
opf_active_limit defaulted a bit higher than usually necessary in 2.6,
unless your load issues lots of nasty simultaneous queries.
If you really need opf_active_limit higher than maybe 10 or so, letting
any one of them take up to 50% of opf_memory (which is the opf_maxmemf
default) is probably being optimistic; in that case you might drop
opf_maxmemf to 33% or 25% (or even less in extreme cases).


Karl