Same query, different access plan in Prod. and Dev.

[sql-db2-dba]

We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development
configuarations (at least for DB2) are identical albeit production is
a 2-way server while development has only one processor. Tables and
indexes have the same schema. In fact, the dev database was taken from
a prod backup recently. Size of the tables differ slightly. Yet, on a
given query (with 4 tables joined), it took 30-50 times longer to run
in prod than on development.

We ran db2batch and noticed that in production, the same query
generated thousands times of buffer pool reads (data) more than its
development counterpart.

In addition, db2expln showed production and development used 2 access
plans that were quite different from each other. Dev (the one with
much better response time) used Merge join in one of its joins while
in production's there was a Nested-loop join.

Would DB2 optimizer select completely different access path baseds on
hardware strength and available resources. There are about 4-6 active
connections on production most of the time.

Any ideas. I could pose the different access plans if anyone is
interested.

Thanks in advance.

Bill.

[Pierre Saint-Jacques]

IS the Dev. system identified with the same parms. for intra_parallel
and also for max_query_Degree at instance level and DFT_DEGREE as Prod.?
If stats are the same or very close, those parms. could have an effect.
Also, verify that CPUSPEED and Overhead and Transferrate are identical.

For your developpemnt , you can "lie" to your config. files, as long as
you don't want to benchmark.

Having said this, it is quite possible that the choices of the
optimizers could differ given hw and resources used. I know that there
were changes in the optimizer for V8 to take into account some more of
this (at least I think I heard that).

HTH, Pierre.

sql-db2-dba wrote:

We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development
configuarations (at least for DB2) are identical albeit production is
a 2-way server while development has only one processor. Tables and
indexes have the same schema. In fact, the dev database was taken from
a prod backup recently. Size of the tables differ slightly. Yet, on a
given query (with 4 tables joined), it took 30-50 times longer to run
in prod than on development.

We ran db2batch and noticed that in production, the same query
generated thousands times of buffer pool reads (data) more than its
development counterpart.

In addition, db2expln showed production and development used 2 access
plans that were quite different from each other. Dev (the one with
much better response time) used Merge join in one of its joins while
in production's there was a Nested-loop join.

Would DB2 optimizer select completely different access path baseds on
hardware strength and available resources. There are about 4-6 active
connections on production most of the time.

Any ideas. I could pose the different access plans if anyone is
interested.

Thanks in advance.

Bill.

--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Serge Rielau]

Use db2exfmt instead of db2expln to get the plans when you can. A lot
more information.

Cheers
Serge

[sql-db2-dba]

One of the key differences between the 2 access plan was the 2 plans
use different indexes for one of the 4 tables, namely EDITSEV. What is
interesting was that (see step 15 in Dev) EDITSEV1 has only one
column, which is not used in the query's predicates. In Dev, the A.P.
table scan it and merge scan that results at the end. It almost feels
like Prod (a 2-way server) just takes the easy way out while Dev (a
uniprocessor server) enginneered a much cleverer plan and paid off.

To Pierre's comments, the 2 servers' dbm cfgs are identical, so are
the overhead and transferrate; but the CPU_SPEED and COMM_BANDWIDTH
are different. Would that be the determining factor for the different
access plans?!?

Thanks.

Bill

Access Plan on Dev (Fast):
--------------------------
Total Cost: 3.833e+06
Query Degree: 1

Parallelism: None
CPU Speed: 5.156425e-07
Comm Speed: 2
Buffer Pool size: 4096
Sort Heap size: 10000
Database Heap size: 1200
Lock List size: 1000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 11300
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
3.833e+06
21088.8
|
2.90467e+10
MSJOIN
( 3)
88574.6
21088.8
/-------------+------------\
111546 260401
TBSCAN FILTER
( 4) ( 11)
69129 19356.5
16606.8 4482
| |
111546 29807
SORT TBSCAN
( 5) ( 12)
69114.6 18430.6
16606.8 4482
| |
111546 29807
HSJOIN SORT
( 6) ( 13)
68938.6 18430.6
16606.8 4482
/--------+--------\ |
111546 29958 29807
HSJOIN TBSCAN FETCH
( 7) ( 10) ( 14)
51737.3 17089.5 18400.7
12253.8 4353 4482
/------+-----\ | /----+---\
111546 142370 29958 29807
29807
TBSCAN IXSCAN TABLE: EDIENU32 IXSCAN TABLE:
EDIENU32
( 8) ( 9) EDITSGP ( 15) EDITSEV
40312.5 11387.4 1083.51
10271 1982.75 103
| | |
111546 142370 29807
TABLE: EDIENU32 INDEX: EDIENU32 INDEX: EDIENU32
EDITSTU EDITSTHX EDITSEV1
Access Plan on Prod (Slow) :
----------------------------

Parallelism: None
CPU Speed: 4.841528e-07
Comm Speed: 100
Buffer Pool size: 4096
Sort Heap size: 10000
Database Heap size: 1200
Lock List size: 1000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 11300

Total Cost: 4.62311e+06
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
4.62311e+06
15565.8
|
3.76074e+10
NLJOIN
( 3)
71175.7
15565.8
/----------+---------\
106539 352992
HSJOIN TBSCAN
( 4) ( 9)
58009.3 14298.9
12254.8 3311
/--------+--------\ |
106539 28610 28772
HSJOIN IXSCAN TABLE: EDIENU32
( 5) ( 8) EDITSGP
47656.1 10267.8
11265.8 989.011
/------+-----\ |
106539 107294 28610
TBSCAN IXSCAN INDEX: EDIENU32
( 6) ( 7) EDITSEVX
38231.9 9394.58
9741 1524.83
| |
106539 107294
TABLE: EDIENU32 INDEX: EDIENU32
EDITSTU EDITSTHX

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2u*************@uni-berlin.de>...

Use db2exfmt instead of db2expln to get the plans when you can. A lot
more information.

Cheers
Serge

[Shawn Hurt]

le****@aptea.com (sql-db2-dba) wrote in message news:<c8**************************@posting.google. com>...

One of the key differences between the 2 access plan was the 2 plans
use different indexes for one of the 4 tables, namely EDITSEV. What is
interesting was that (see step 15 in Dev) EDITSEV1 has only one
column, which is not used in the query's predicates. In Dev, the A.P.
table scan it and merge scan that results at the end. It almost feels
like Prod (a 2-way server) just takes the easy way out while Dev (a
uniprocessor server) enginneered a much cleverer plan and paid off.

To Pierre's comments, the 2 servers' dbm cfgs are identical, so are
the overhead and transferrate; but the CPU_SPEED and COMM_BANDWIDTH
are different. Would that be the determining factor for the different
access plans?!?

Thanks.

Bill

Access Plan on Dev (Fast):
--------------------------
Total Cost: 3.833e+06
Query Degree: 1

Parallelism: None
CPU Speed: 5.156425e-07
Comm Speed: 2
Buffer Pool size: 4096
Sort Heap size: 10000
Database Heap size: 1200
Lock List size: 1000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 11300
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
3.833e+06
21088.8
|
2.90467e+10
MSJOIN
( 3)
88574.6
21088.8
/-------------+------------\
111546 260401
TBSCAN FILTER
( 4) ( 11)
69129 19356.5
16606.8 4482
| |
111546 29807
SORT TBSCAN
( 5) ( 12)
69114.6 18430.6
16606.8 4482
| |
111546 29807
HSJOIN SORT
( 6) ( 13)
68938.6 18430.6
16606.8 4482
/--------+--------\ |
111546 29958 29807
HSJOIN TBSCAN FETCH
( 7) ( 10) ( 14)
51737.3 17089.5 18400.7
12253.8 4353 4482
/------+-----\ | /----+---\
111546 142370 29958 29807
29807
TBSCAN IXSCAN TABLE: EDIENU32 IXSCAN TABLE:
EDIENU32
( 8) ( 9) EDITSGP ( 15) EDITSEV
40312.5 11387.4 1083.51
10271 1982.75 103
| | |
111546 142370 29807
TABLE: EDIENU32 INDEX: EDIENU32 INDEX: EDIENU32
EDITSTU EDITSTHX EDITSEV1
Access Plan on Prod (Slow) :
----------------------------

Parallelism: None
CPU Speed: 4.841528e-07
Comm Speed: 100
Buffer Pool size: 4096
Sort Heap size: 10000
Database Heap size: 1200
Lock List size: 1000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 11300

Total Cost: 4.62311e+06
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
4.62311e+06
15565.8
|
3.76074e+10
NLJOIN
( 3)
71175.7
15565.8
/----------+---------\
106539 352992
HSJOIN TBSCAN
( 4) ( 9)
58009.3 14298.9
12254.8 3311
/--------+--------\ |
106539 28610 28772
HSJOIN IXSCAN TABLE: EDIENU32
( 5) ( 8) EDITSGP
47656.1 10267.8
11265.8 989.011
/------+-----\ |
106539 107294 28610
TBSCAN IXSCAN INDEX: EDIENU32
( 6) ( 7) EDITSEVX
38231.9 9394.58
9741 1524.83
| |
106539 107294
TABLE: EDIENU32 INDEX: EDIENU32
EDITSTU EDITSTHX

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2u*************@uni-berlin.de>...

Use db2exfmt instead of db2expln to get the plans when you can. A lot
more information.

Cheers
Serge

I have encountered a similar situation before and I found that the
problem was the was the data was sorted differently in the tables on
the two systems, which caused the optimizer to choose different plans.
Be sure to check that you have statistics run on both systems. If
you have your data clustered to an index, do a reorgchk to be sure the
cluster ratio is similar between the two systems as well.

Your dev system does a MSJOIN scan which means the data is ordered and
it only scans the table once, which probably explains your improved
performance on that system. Your production system is doing a NLJOIN,
but it is doing a TBSCAN on the inner table which indicates you could
use an index there to improve performance.

Hope this helps!!

[Pierre Saint-Jacques]

It could as DB2's optimizer uses those numbers to figure out the cost of
the instructions to execute and to figure out the costs of I/O.
It is possible that they could cause each optimizer on DEV and PROD to
generate a different access palns but I really don't know that much that
down deep.
I would think that everything else would have to be absolutely identical
to "blame" these two parms for a different plan.
Regards, Pierre.

Shawn Hurt wrote:

le****@aptea.com (sql-db2-dba) wrote in message news:<c8**************************@posting.google. com>...

To Pierre's comments, the 2 servers' dbm cfgs are identical, so are
the overhead and transferrate; but the CPU_SPEED and COMM_BANDWIDTH
are different. Would that be the determining factor for the different
access plans?!?

Thanks.

Bill

Access Plan on Dev (Fast):

....clipped

Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.