Query running very slow after the upgrade to 11.70 from 11.50

Discussion:

(too old to reply)

Achyut Gadre

2013-07-31 18:00:59 UTC

Hi all,

After upgrading the instance to 11.70 FC7 from 11.50.FC5, some of the queries are running slow. These are mostly application generated queries like look up queries, so there is not much to tune the queries itself.

I have tried changing OPTCOMPIND and some optimizer directives like FIRST_ROWS. Also I have tried update statistics low with drop distributions.

Here are the output of explain plan for each version,

Version 11.50 explain plan
QUERY: (OPTIMIZATION TIMESTAMP: 07-08-2013 21:18:38)
------
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
order by
multicaser1_.id_case asc

Estimated Cost: 22
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) dpdars.poreceive0_: INDEX PATH

Filters: ((dpdars.poreceive0_.receivecancel_date IS NULL AND dpdars.poreceive0_.payrequestcancel_date IS NULL ) AND dpdars.poreceive0_.warrantcancel_date IS NULL )

(1) Index Name: dmdars.xpor_svcplndtl
Index Keys: id_service_plan_dtl (Serial, fragments: ALL)
Lower Index Filter: dpdars.poreceive0_.id_service_plan_dtl = 5095515

2) dpdars.multicaser1_: INDEX PATH

(1) Index Name: dmdars.xif2multi_case_receive
Index Keys: id_po_receive (Serial, fragments: ALL)
Lower Index Filter: dpdars.multicaser1_.id_po_receive = dpdars.poreceive0_.id
NESTED LOOP JOIN

3) dpdars.case2_: INDEX PATH

(1) Index Name: dmdars.xcstb_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id = dpdars.multicaser1_.id_case

4) dpdars.caseload3_: INDEX PATH

(1) Index Name: dmdars.xpkcaseload
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id_caseload = dpdars.caseload3_.id

ON-Filters:dpdars.case2_.id_caseload = dpdars.caseload3_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

5) dpdars.networkuse4_: INDEX PATH

(1) Index Name: dmdars.xpknetwork_user
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.caseload3_.id_caseload_mgr = dpdars.networkuse4_.id

ON-Filters:dpdars.caseload3_.id_caseload_mgr = dpdars.networkuse4_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

6) dpdars.demographi5_: INDEX PATH

(1) Index Name: dmdars.xdmog_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id_demographic = dpdars.demographi5_.id

ON-Filters:dpdars.case2_.id_demographic = dpdars.demographi5_.id
NESTED LOOP JOIN
NESTED LOOP JOIN

PostJoin-Filters:(dpdars.multicaser1_.id_po_receive = dpdars.poreceive0_.id AND dpdars.case2_.id = dpdars.multicaser1_.id_case )

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 poreceive0_
t2 multicaser1_
t3 case2_
t4 caseload3_
t5 networkuse4_
t6 demographi5_

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 9 1 9 00:00.00 4

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 0 127970 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1 00:00.00 7

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 0 360542 0 00:00.00 1

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 0 962 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360542 00:00.00 63

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 0 9311 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360543 00:00.00 670

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 0 312425 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360544 00:00.00 76516

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 2 00:00.00 23

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 1 0 00:00.00

This query takes about 0.03 secs as you can see below from the timex command I used with dbaccess.

real 0.03
user 0.01
sys 0.00

Version 11.70 explain plan
QUERY: (OPTIMIZATION TIMESTAMP: 07-11-2013 13:39:30)
------
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
order by
multicaser1_.id_case asc

Estimated Cost: 1471187
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) chandan.case2_: SEQUENTIAL SCAN

2) chandan.caseload3_: INDEX PATH

(1) Index Name: dmdars.xpkcaseload
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id_caseload = chandan.caseload3_.id

ON-Filters:chandan.case2_.id_caseload = chandan.caseload3_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

3) chandan.networkuse4_: INDEX PATH

(1) Index Name: dmdars.xpknetwork_user
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.caseload3_.id_caseload_mgr = chandan.networkuse4_.id

ON-Filters:chandan.caseload3_.id_caseload_mgr = chandan.networkuse4_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

4) chandan.demographi5_: INDEX PATH

(1) Index Name: dmdars.xdmog_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id_demographic = chandan.demographi5_.id

ON-Filters:chandan.case2_.id_demographic = chandan.demographi5_.id
NESTED LOOP JOIN

5) chandan.multicaser1_: INDEX PATH

(1) Index Name: dmdars. 257_925
Index Keys: id_case (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id = chandan.multicaser1_.id_case
NESTED LOOP JOIN

6) chandan.poreceive0_: INDEX PATH

Filters: (((chandan.poreceive0_.id_service_plan_dtl = 5095515 AND chandan.poreceive0_.receivecancel_date IS NULL ) AND chandan.poreceive0_.payrequestcancel_date IS NULL ) AND chandan.poreceive0_.warrantcancel_date IS NULL )

(1) Index Name: dmdars.xpkporec_id
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.multicaser1_.id_po_receive = chandan.poreceive0_.id
NESTED LOOP JOIN

PostJoin-Filters:(chandan.multicaser1_.id_po_receive = chandan.poreceive0_.id AND chandan.case2_.id = chandan.multicaser1_.id_case )

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 case2_
t2 caseload3_
t3 networkuse4_
t4 demographi5_
t5 multicaser1_
t6 poreceive0_

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 359903 359864 359903 00:07.65 62238

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 719382 962 359691 00:35.60 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359864 00:44.65 267417

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 719382 9302 359691 00:34.18 0

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359865 01:20.66 430733

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 719806 311927 359903 01:51.81 2

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359865 03:14.20 1112452

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 254910 127455 127455 02:42.75 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 127455 127456 04:42.37 1345665

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 0 1 127455 01:35.22 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1 05:30.37 1471188

type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 0 1 0 05:30.37 0

Same query takes more than 53 secs for this 11.70 version, as you can see below from the timex command I used with dbaccess.

real 53.65
user 0.01
sys 0.01

BTW, this is on Solaris SPARC platform with the same OS version.

Thanks!
Achyut

Fernando Nunes

2013-07-31 18:18:50 UTC

Permalink

This may look like a group joke, but it's a serious question:
Have you tried update statistics as IBM recommends? Dropping distributions
in general will not produce better query plans...
Regards

Post by Achyut Gadre
Hi all,
After upgrading the instance to 11.70 FC7 from 11.50.FC5, some of the
queries are running slow. These are mostly application generated queries
like look up queries, so there is not much to tune the queries itself.
I have tried changing OPTCOMPIND and some optimizer directives like
FIRST_ROWS. Also I have tried update statistics low with drop distributions.
Here are the output of explain plan for each version,
Version 11.50 explain plan
QUERY: (OPTIMIZATION TIMESTAMP: 07-08-2013 21:18:38)
------
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
order by
multicaser1_.id_case asc
Estimated Cost: 22
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By
1) dpdars.poreceive0_: INDEX PATH
Filters: ((dpdars.poreceive0_.receivecancel_date IS NULL
AND dpdars.poreceive0_.payrequestcancel_date IS NULL ) AND
dpdars.poreceive0_.warrantcancel_date IS NULL )
(1) Index Name: dmdars.xpor_svcplndtl
Index Keys: id_service_plan_dtl (Serial, fragments: ALL)
Lower Index Filter: dpdars.poreceive0_.id_service_plan_dtl = 5095515
2) dpdars.multicaser1_: INDEX PATH
(1) Index Name: dmdars.xif2multi_case_receive
Index Keys: id_po_receive (Serial, fragments: ALL)
Lower Index Filter: dpdars.multicaser1_.id_po_receive =
dpdars.poreceive0_.id
NESTED LOOP JOIN
3) dpdars.case2_: INDEX PATH
(1) Index Name: dmdars.xcstb_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id =
dpdars.multicaser1_.id_case
4) dpdars.caseload3_: INDEX PATH
(1) Index Name: dmdars.xpkcaseload
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id_caseload =
dpdars.caseload3_.id
ON-Filters:dpdars.case2_.id_caseload = dpdars.caseload3_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)
5) dpdars.networkuse4_: INDEX PATH
(1) Index Name: dmdars.xpknetwork_user
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.caseload3_.id_caseload_mgr
= dpdars.networkuse4_.id
ON-Filters:dpdars.caseload3_.id_caseload_mgr =
dpdars.networkuse4_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)
6) dpdars.demographi5_: INDEX PATH
(1) Index Name: dmdars.xdmog_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id_demographic = dpdars.demographi5_.id
ON-Filters:dpdars.case2_.id_demographic = dpdars.demographi5_.id
NESTED LOOP JOIN
NESTED LOOP JOIN
PostJoin-Filters:(dpdars.multicaser1_.id_po_receive =
dpdars.poreceive0_.id AND dpdars.case2_.id = dpdars.multicaser1_.id_case )
-----------------
----------------------------
Internal name Table name
----------------------------
t1 poreceive0_
t2 multicaser1_
t3 case2_
t4 caseload3_
t5 networkuse4_
t6 demographi5_
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 9 1 9 00:00.00 4
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 0 127970 0 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1 00:00.00 7
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 0 360542 0 00:00.00 1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 0 962 0 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360542 00:00.00 63
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 0 9311 0 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360543 00:00.00 670
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 0 312425 0 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360544 00:00.00 76516
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 2 00:00.00 23
type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 1 0 00:00.00
This query takes about 0.03 secs as you can see below from the timex
command I used with dbaccess.
real 0.03
user 0.01
sys 0.00
Version 11.70 explain plan
QUERY: (OPTIMIZATION TIMESTAMP: 07-11-2013 13:39:30)
------
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
order by
multicaser1_.id_case asc
Estimated Cost: 1471187
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By
1) chandan.case2_: SEQUENTIAL SCAN
2) chandan.caseload3_: INDEX PATH
(1) Index Name: dmdars.xpkcaseload
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id_caseload
= chandan.caseload3_.id
ON-Filters:chandan.case2_.id_caseload =
chandan.caseload3_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)
3) chandan.networkuse4_: INDEX PATH
(1) Index Name: dmdars.xpknetwork_user
Index Keys: id (Serial, fragments: ALL)
chandan.caseload3_.id_caseload_mgr = chandan.networkuse4_.id
ON-Filters:chandan.caseload3_.id_caseload_mgr = chandan.networkuse4_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)
4) chandan.demographi5_: INDEX PATH
(1) Index Name: dmdars.xdmog_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id_demographic =
chandan.demographi5_.id
ON-Filters:chandan.case2_.id_demographic =
chandan.demographi5_.id
NESTED LOOP JOIN
5) chandan.multicaser1_: INDEX PATH
(1) Index Name: dmdars. 257_925
Index Keys: id_case (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id =
chandan.multicaser1_.id_case
NESTED LOOP JOIN
6) chandan.poreceive0_: INDEX PATH
Filters: (((chandan.poreceive0_.id_service_plan_dtl = 5095515
AND chandan.poreceive0_.receivecancel_date IS NULL ) AND
chandan.poreceive0_.payrequestcancel_date IS NULL ) AND
chandan.poreceive0_.warrantcancel_date IS NULL )
(1) Index Name: dmdars.xpkporec_id
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.multicaser1_.id_po_receive = chandan.poreceive0_.id
NESTED LOOP JOIN
PostJoin-Filters:(chandan.multicaser1_.id_po_receive =
chandan.poreceive0_.id AND chandan.case2_.id = chandan.multicaser1_.id_case
)
-----------------
----------------------------
Internal name Table name
----------------------------
t1 case2_
t2 caseload3_
t3 networkuse4_
t4 demographi5_
t5 multicaser1_
t6 poreceive0_
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 359903 359864 359903 00:07.65 62238
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 719382 962 359691 00:35.60 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359864 00:44.65 267417
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 719382 9302 359691 00:34.18 0
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359865 01:20.66 430733
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 719806 311927 359903 01:51.81 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359865 03:14.20 1112452
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 254910 127455 127455 02:42.75 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 127455 127456 04:42.37 1345665
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 0 1 127455 01:35.22 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1 05:30.37 1471188
type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 0 1 0 05:30.37 0
Same query takes more than 53 secs for this 11.70 version, as you can see
below from the timex command I used with dbaccess.
real 53.65
user 0.01
sys 0.01
BTW, this is on Solaris SPARC platform with the same OS version.
Thanks!
Achyut
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Art Kagel

2013-07-31 18:55:30 UTC

Permalink

OK, several points:

1. It is clear from the fact that the 11.50 explain plan has different
values for the estimated and actual rows for a number of the partial steps
in the query plan execution that the level of data distributions you had in
11.50 was not optimal.
2. After the upgrade did you
1. Drop all distributions for all tables and rebuild the according to
the recommended levels? You can do this easily if you are using dostats
with:
dostats -d dpdars --drop-distributions -m
2. If you want to try to duplicate the level of stats that was in
11.50, run:
myschema -d dbdars -u | grep 'UPDATE STATISTICS' > dbdars.stats.sql
3. Your query contains six tables. The default optimization method is
to test all possible query paths. With six tables, in 11.50, that is at
least 720 query paths just in the ordering of the tables and 11.70 has a
few more options which might multiply that number more than 11.50 does.
You could try SET OPTIMIZATION LOW; which will limit the optimizer to
testing only 20 query paths.
4. Your query is not optimal in either engine. Notice the post-join
filters. I would move all of the filters and the one join condition out of
the WHERE clause and into the ON clauses, only filters that MUST be applied
post-join should appear in the WHERE clause when you are using ANSI '92
syntax.
5. You are mixing ANSI '92 syntax with the earlier Informix syntax.
This may be preventing the optimizer from properly parsing the query and
developing a good query plan. Since 11.70 has a more advanced optimization
of the newer syntax, that confusion may be worse in 11.70 than it was in
11.50. Try running the query using only one syntax or the other. Here's
an attempt at rewriting it both ways (hope I got it right):

Using ANSI '92:
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_
inner join
multi_case_receive multicaser1_
on poreceive0_.id_service_plan_dtl = 5095515
and poreceive0_.receivecancel_date is null
and poreceive0_.payrequestcancel_date is null
and poreceive0_.warrantcancel_date is null
and multicaser1_.id_po_receive=poreceive0_.id
inner join
case_tbl case2_
on case2_.id=multicaser1_.id_case
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
order by
multicaser1_.id_case asc;

Using Older Syntax:

select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_,
outer (caseload caseload3_,
outer network_user networkuse4_ ),
demographic demographi5_
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
and case2_.id_caseload=caseload3_.id
and caseload3_.id_caseload_mgr=networkuse4_.id
and case2_.id_demographic=demographi5_.id
order by
multicaser1_.id_case asc;

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

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.

Nathaniel Hicks

2013-08-02 16:11:08 UTC

Permalink

What are your readahead values? We had an issue like this when we upgraded some things from 11.50.FC5 to 11.70.FC4 but it seemed to be remedied with 6 so I would assume 7 is fine.

-----Original Message-----
From: Achyut Gadre [mailto:***@gmail.com]
Sent: Wednesday, July 31, 2013 2:01 PM
To: informix-***@iiug.org
Subject: Query running very slow after the upgrade to 11.70 from 11.50

Hi all,

After upgrading the instance to 11.70 FC7 from 11.50.FC5, some of the queries are running slow. These are mostly application generated queries like look up queries, so there is not much to tune the queries itself.

I have tried changing OPTCOMPIND and some optimizer directives like FIRST_ROWS. Also I have tried update statistics low with drop distributions.

Here are the output of explain plan for each version,

Version 11.50 explain plan
QUERY: (OPTIMIZATION TIMESTAMP: 07-08-2013 21:18:38)
------
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
order by
multicaser1_.id_case asc

Estimated Cost: 22
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) dpdars.poreceive0_: INDEX PATH

Filters: ((dpdars.poreceive0_.receivecancel_date IS NULL AND dpdars.poreceive0_.payrequestcancel_date IS NULL ) AND dpdars.poreceive0_.warrantcancel_date IS NULL )

(1) Index Name: dmdars.xpor_svcplndtl
Index Keys: id_service_plan_dtl (Serial, fragments: ALL)
Lower Index Filter: dpdars.poreceive0_.id_service_plan_dtl = 5095515

2) dpdars.multicaser1_: INDEX PATH

(1) Index Name: dmdars.xif2multi_case_receive
Index Keys: id_po_receive (Serial, fragments: ALL)
Lower Index Filter: dpdars.multicaser1_.id_po_receive = dpdars.poreceive0_.id
NESTED LOOP JOIN

3) dpdars.case2_: INDEX PATH

(1) Index Name: dmdars.xcstb_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id = dpdars.multicaser1_.id_case

4) dpdars.caseload3_: INDEX PATH

(1) Index Name: dmdars.xpkcaseload
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id_caseload = dpdars.caseload3_.id

ON-Filters:dpdars.case2_.id_caseload = dpdars.caseload3_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

5) dpdars.networkuse4_: INDEX PATH

(1) Index Name: dmdars.xpknetwork_user
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.caseload3_.id_caseload_mgr = dpdars.networkuse4_.id

ON-Filters:dpdars.caseload3_.id_caseload_mgr = dpdars.networkuse4_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

6) dpdars.demographi5_: INDEX PATH

(1) Index Name: dmdars.xdmog_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: dpdars.case2_.id_demographic = dpdars.demographi5_.id

ON-Filters:dpdars.case2_.id_demographic = dpdars.demographi5_.id
NESTED LOOP JOIN
NESTED LOOP JOIN

PostJoin-Filters:(dpdars.multicaser1_.id_po_receive = dpdars.poreceive0_.id AND dpdars.case2_.id = dpdars.multicaser1_.id_case )

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 poreceive0_
t2 multicaser1_
t3 case2_
t4 caseload3_
t5 networkuse4_
t6 demographi5_

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 9 1 9 00:00.00 4

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 0 127970 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1 00:00.00 7

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 0 360542 0 00:00.00 1

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 0 962 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360542 00:00.00 63

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 0 9311 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360543 00:00.00 670

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 0 312425 0 00:00.00 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 360544 00:00.00 76516

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 2 00:00.00 23

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 1 0 00:00.00

This query takes about 0.03 secs as you can see below from the timex command I used with dbaccess.

real 0.03
user 0.01
sys 0.00

Version 11.70 explain plan
QUERY: (OPTIMIZATION TIMESTAMP: 07-11-2013 13:39:30)
------
select
multicaser1_.id_case as col_0_0_,
multicaser1_.id_po_receive as col_1_0_,
multicaser1_.case_rcv_qty as col_2_0_,
demographi5_.ssn as col_3_0_,
case2_.case_type_cd as col_4_0_,
demographi5_.first_name as col_5_0_,
demographi5_.middle_name as col_6_0_,
demographi5_.last_name as col_7_0_,
networkuse4_.first_name as col_8_0_,
networkuse4_.middle_name as col_9_0_,
networkuse4_.last_name as col_10_0_,
caseload3_.id as col_11_0_
from
po_receive poreceive0_,
multi_case_receive multicaser1_,
case_tbl case2_
left outer join
caseload caseload3_
on case2_.id_caseload=caseload3_.id
left outer join
network_user networkuse4_
on caseload3_.id_caseload_mgr=networkuse4_.id
inner join
demographic demographi5_
on case2_.id_demographic=demographi5_.id
where
poreceive0_.id_service_plan_dtl=5095515
and (
poreceive0_.receivecancel_date is null
)
and (
poreceive0_.payrequestcancel_date is null
)
and (
poreceive0_.warrantcancel_date is null
)
and multicaser1_.id_po_receive=poreceive0_.id
and case2_.id=multicaser1_.id_case
order by
multicaser1_.id_case asc

Estimated Cost: 1471187
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) chandan.case2_: SEQUENTIAL SCAN

2) chandan.caseload3_: INDEX PATH

(1) Index Name: dmdars.xpkcaseload
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id_caseload = chandan.caseload3_.id

ON-Filters:chandan.case2_.id_caseload = chandan.caseload3_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

3) chandan.networkuse4_: INDEX PATH

(1) Index Name: dmdars.xpknetwork_user
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.caseload3_.id_caseload_mgr = chandan.networkuse4_.id

ON-Filters:chandan.caseload3_.id_caseload_mgr = chandan.networkuse4_.id
NESTED LOOP JOIN(LEFT OUTER JOIN)

4) chandan.demographi5_: INDEX PATH

(1) Index Name: dmdars.xdmog_id_pk
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id_demographic = chandan.demographi5_.id

ON-Filters:chandan.case2_.id_demographic = chandan.demographi5_.id
NESTED LOOP JOIN

5) chandan.multicaser1_: INDEX PATH

(1) Index Name: dmdars. 257_925
Index Keys: id_case (Serial, fragments: ALL)
Lower Index Filter: chandan.case2_.id = chandan.multicaser1_.id_case
NESTED LOOP JOIN

6) chandan.poreceive0_: INDEX PATH

Filters: (((chandan.poreceive0_.id_service_plan_dtl = 5095515 AND chandan.poreceive0_.receivecancel_date IS NULL ) AND chandan.poreceive0_.payrequestcancel_date IS NULL ) AND chandan.poreceive0_.warrantcancel_date IS NULL )

(1) Index Name: dmdars.xpkporec_id
Index Keys: id (Serial, fragments: ALL)
Lower Index Filter: chandan.multicaser1_.id_po_receive = chandan.poreceive0_.id
NESTED LOOP JOIN

PostJoin-Filters:(chandan.multicaser1_.id_po_receive = chandan.poreceive0_.id AND chandan.case2_.id = chandan.multicaser1_.id_case )

Query statistics:
-----------------

Table map :
----------------------------
Internal name Table name
----------------------------
t1 case2_
t2 caseload3_
t3 networkuse4_
t4 demographi5_
t5 multicaser1_
t6 poreceive0_

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 359903 359864 359903 00:07.65 62238

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 719382 962 359691 00:35.60 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359864 00:44.65 267417

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 719382 9302 359691 00:34.18 0

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359865 01:20.66 430733

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t4 719806 311927 359903 01:51.81 2

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 359903 359865 03:14.20 1112452

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t5 254910 127455 127455 02:42.75 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 127455 127456 04:42.37 1345665

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t6 0 1 127455 01:35.22 1

type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 0 1 05:30.37 1471188

type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 0 1 0 05:30.37 0

Same query takes more than 53 secs for this 11.70 version, as you can see below from the timex command I used with dbaccess.

real 53.65
user 0.01
sys 0.01

BTW, this is on Solaris SPARC platform with the same OS version.

Thanks!
Achyut