Hi all,
We finished a major migration a few weeks ago but the new system is
really misbehaving. Any thoughts are appreciated. Here is the low down.
The new dev system behaves fine but not on primary and secondary. We
have tried tuning many different things and running update stats many
different ways.
on dev, OPTCOMPIND is 0, and it is 0 on secondary and it is 2 on
primary.

Old system : IDS v10.fc4 with HDR on HP-UX 11i
New Prod system: LPAR partitions on i595 servers IDS v10.fc5 with HDR
New Dev system:  LPAR partitions on i595 servers IDS v10.fc5

Thank you in advance,
Tom

here is an example with SQL and explains - the last SELECT section is
having the problem by demanding to use DYNAMIC HASH JOINS on
Production:
+++++++++++++++++++
SELECT v.o_id id
FROM v_order_to_job_ids v, Event e, Order o, Activity a
WHERE o.id = v.o_id
AND o.cust_role_id = 28
AND e.from_id = v.a_id
AND e.from_class_id = 2
AND  e.status_cid = 1140
AND a.id = v.a_id
AND e.type_cid = 1144
AND e.date_time >= DateTime(2007-01-17 00:00) year to minute
AND e.date_time <= DateTime(2007-01-17 23:59) year to minute
UNION
SELECT o.id id
FROM order o, Event e
WHERE o.cust_role_id = 28
AND e.from_class_id = 20
AND e.from_id = o. id
AND e.status_cid = 1140
AND e.type_cid = 1144
AND e.date_time >= DateTime(2007-01-17 00:00) year to minute
AND e.date_time <= DateTime(2007-01-17 23:59) year to minute
into temp tmp_getAllOrderId with no log;

SELECT tmp.id,nvl(get_datetime(2,v.a_id,1144),get_datetime (20, tmp.id,
1144)) date_time
FROM tmp_getAllOrderId tmp,
OUTER (v_order_to_job_ids v, event ve)
WHERE v.o_id = tmp.id
AND ve.from_id = v.a_id
AND ve.from_class_id = 2
AND ve.status_cid = 1140
AND ve.type_cid = 1144
into temp tmp_getAllOrderIdDate with no log;

SELECT id order_id
FROM tmp_getAllOrderIdDate
WHERE date_time >= DateTime(2007-01-17 00:00) year to minute
AND  date_time <= DateTime(2007-01-17 23:59) year to minute
into temp tmp_getOrderId with no log;

create temp table tmp_order_search (
order_serial serial,
order_id integer
) with no log;

SELECT  distinct o.id
FROM  order o
,  tmp_getOrderId
,  order_detail od, order_shipment os, related_to rt,
activity avc,
role c_role                                        ,
stakeholder c_stake
WHERE o.cust_role_id = 28
AND o.status_cid !=1230
AND o.id = tmp_getOrderId.order_id
AND od.order_id = o.id
                                       AND od.status_cid != 1215
                                       AND os.order_detail_id = od.id
                                       AND os.status_cid != 1218
                                       AND os.id = rt.from_id
                                       AND rt.to_class_id = 2
                                       and rt.from_class_id = 22
                                       and rt.type_cid = 1260
                                       and rt.status_cid = 1258
                                       AND rt.to_id = avc.id

AND avc.type_cid = 1005
AND avc.sub_type_cid in (1363, 1365, 1361)
AND avc.status_cid in (1002, 1003, 1004)
AND c_role.id = avc.crp_role_id
AND c_stake.id = c_role.stake_id
and c_stake.id = 1936
AND o.type_cid in  (1669, 1233, 1999, 2752,4949,4950);

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
EXPLAIN from new Dev - no Dynamic Hash Joins performed:

Estimated Cost: 11
Estimated # of Rows Returned: 1

 1) informix.tmp_getorderid: SEQUENTIAL SCAN  (Serial, fragments: ALL)
 2) informix.o: INDEX PATH
       Filters: ((informix.o.cust_role_id = 28 AND informix.o.type_cid
IN (1669 , 1233 , 1999 , 2752 , 4949 , 4950 )) AND
informix.o.status_cid != 1230 )

   (1) Index Keys: id   (Serial, fragments: ALL)
       Lower Index Filter: informix.o.id =
informix.tmp_getorderid.order_id

NESTED LOOP JOIN
 3) informix.od: INDEX PATH
       Filters: informix.od.status_cid != 1215

   (1) Index Keys: order_id   (Serial, fragments: ALL)
       Lower Index Filter: informix.tmp_getorderid.order_id =
informix.od.order_id

NESTED LOOP JOIN
 4) informix.os: INDEX PATH
       Filters: informix.os.status_cid != 1218

   (1) Index Keys: order_detail_id   (Serial, fragments: ALL)
       Lower Index Filter: informix.os.order_detail_id =
informix.od.id

NESTED LOOP JOIN
 5) informix.rt: INDEX PATH
   (1) Index Keys: status_cid from_class_id from_id type_cid seq_num
to_class_id to_id   (Key-Only)  (Serial, fragments: ALL)

       Lower Index Filter: (((informix.os.id = informix.rt.from_id AND
informix.rt.type_cid = 1260 ) AND informix.rt.from_class_id = 22 ) AND
informix.rt.status_cid = 1258 )

       Index Key Filters:  (informix.rt.to_class_id = 2 )

NESTED LOOP JOIN
 6) informix.avc: INDEX PATH
       Filters: ((informix.avc.sub_type_cid IN (1363 , 1365 , 1361
)AND informix.avc.status_cid IN (1002 , 1003 , 1004 )) AND
informix.avc.type_cid = 1005 )

   (1) Index Keys: id   (Serial, fragments: ALL)
       Lower Index Filter: informix.rt.to_id = informix.avc.id

NESTED LOOP JOIN
 7) informix.c_role: INDEX PATH
       Filters: informix.c_role.stake_id = 1936

   (1) Index Keys: id   (Serial, fragments: ALL)
       Lower Index Filter: informix.c_role.id =
informix.avc.crp_role_id

NESTED LOOP JOIN
 8) informix.c_stake: INDEX PATH
   (1) Index Keys: id   (Key-Only)  (Serial, fragments: ALL)

       Lower Index Filter: informix.c_stake.id =
informix.c_role.stake_id
NESTED LOOP JOIN

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
EXPLAIN from new Production:

Estimated Cost: 61
Estimated # of Rows Returned: 1

 1) informix.c_role: INDEX PATH
   (1) Index Keys: stake_id   (Serial, fragments: ALL)
       Lower Index Filter: informix.c_role.stake_id = 1936

 2) informix.avc: INDEX PATH
       Filters: ((informix.avc.sub_type_cid IN (1363 , 1365 , 1361
)AND informix.avc.status_cid IN (1002 , 1003 , 1004 )) AND
informix.avc.type_cid = 1005 )

   (1) Index Keys: crp_role_id   (Serial, fragments: ALL)
       Lower Index Filter: informix.c_role.id =
informix.avc.crp_role_id

NESTED LOOP JOIN

 3) informix.rt: INDEX PATH
   (1) Index Keys: status_cid to_class_id to_id type_cid seq_num
from_class_id from_id   (Key-Only)  (Serial, fragments: ALL)

       Lower Index Filter: (((informix.rt.to_id = informix.avc.id AND
informix.rt.type_cid = 1260 ) AND informix.rt.to_class_id = 2 ) AND
informix.rt.status_cid = 1258 )
       Index Key Filters:  (informix.rt.from_class_id = 22 )

NESTED LOOP JOIN
 4) informix.os: INDEX PATH
       Filters: informix.os.status_cid != 1218

   (1) Index Keys: id   (Serial, fragments: ALL)
       Lower Index Filter: informix.os.id = informix.rt.from_id

NESTED LOOP JOIN
 5) informix.od: INDEX PATH
       Filters: informix.od.status_cid != 1215
   (1) Index Keys: id   (Serial, fragments: ALL)
       Lower Index Filter: informix.os.order_detail_id =
informix.od.id

NESTED LOOP JOIN

 6) informix.tmp_getorderid: SEQUENTIAL SCAN  (Serial, fragments: ALL)

DYNAMIC HASH JOIN
   Dynamic Hash Filters: informix.tmp_getorderid.order_id =
informix.od.order_id
 7) informix.o: INDEX PATH

       Filters: ((informix.o.cust_role_id = 28 AND informix.o.type_cid
IN (1669 , 1233 , 1999 , 2752 , 4949 , 4950 )) AND
informix.o.status_cid != 1230 )

   (1) Index Keys: id   (Serial, fragments: ALL)
       Lower Index Filter: informix.o.id =
informix.tmp_getorderid.order_id

NESTED LOOP JOIN

 8) informix.c_stake: INDEX PATH

   (1) Index Keys: id   (Key-Only)  (Serial, fragments: ALL)
       Lower Index Filter: informix.c_stake.id = 1936

DYNAMIC HASH JOIN
   Dynamic Hash Filters: informix.c_stake.id = informix.c_role.stake_id