Hi,

I am trying to understand why following simple query is running very
slow (33 hours)

select ROW_ID
, a.CREATED
, a.CREATED_BY
, a.LAST_UPD
, a.LAST_UPD_BY
, a.MODIFICATION_NUM
, a.CONFLICT_ID
, a.ROW_ID
, a.OWNER_PER_ID
, a.ROW_STATUS
, a.ASGN_DNRM_FLG
, coalesce(a.ASGN_MANL_FLG, 'Y')
, a.ASGN_SYS_FLG
, a.APPT_REPT_FLG
, a.APPT_REPT_END_DT
, a.APPT_START_DT
, a.TODO_PLAN_END_DT
, a.TODO_PLAN_START_DT
, a.ALARM_FLAG
, a.CAL_DISP_FLG
, a.TEMPLATE_FLG
, a.EVT_STAT_CD
from S_EVT_ACT a
where OWNER_PER_ID is not null


The table S_EVT_ACT has about 78M rows and the query above is expected
to select 76M rows. The access path chosen by DB2 optimizer selects
indexed access. Index defined is

CREATE INDEX "SIEBEL "."S_EVT_ACT_M6" ON "SIEBEL "."S_EVT_ACT"
("OWNER_PER_ID" ASC,
"APPT_START_DT" ASC,
"APPT_START_TM" ASC) PCTFREE 30 ;

The stats (on key columns with distribution) are updated, index is not
clustering and the filter factor according to db2exfmt is 0.997.

If I drop the index, DB2 performs tablescan and the query runs in about
an hour (versus 33 hours). This is probably due to prefetching during
table scan, versus no prefetching during non-clustered index access.

Why DB2 chooses to select more expensive indexed scan and what can I do
to make DB2 optimizer select a better access path without dropping the
index or modifying the sql?

TIA

P. Adhia

P Adhia wrote:

Hi,
>
I am trying to understand why following simple query is running very
slow (33 hours)
>
select ROW_ID
, a.CREATED
, a.CREATED_BY
, a.LAST_UPD
, a.LAST_UPD_BY
, a.MODIFICATION_NUM
, a.CONFLICT_ID
, a.ROW_ID
, a.OWNER_PER_ID
, a.ROW_STATUS
, a.ASGN_DNRM_FLG
, coalesce(a.ASGN_MANL_FLG, 'Y')
, a.ASGN_SYS_FLG
, a.APPT_REPT_FLG
, a.APPT_REPT_END_DT
, a.APPT_START_DT
, a.TODO_PLAN_END_DT
, a.TODO_PLAN_START_DT
, a.ALARM_FLAG
, a.CAL_DISP_FLG
, a.TEMPLATE_FLG
, a.EVT_STAT_CD
from S_EVT_ACT a
where OWNER_PER_ID is not null
>
>
The table S_EVT_ACT has about 78M rows and the query above is expected
to select 76M rows. The access path chosen by DB2 optimizer selects
indexed access. Index defined is
>
CREATE INDEX "SIEBEL "."S_EVT_ACT_M6" ON "SIEBEL "."S_EVT_ACT"
("OWNER_PER_ID" ASC,
"APPT_START_DT" ASC,
"APPT_START_TM" ASC) PCTFREE 30 ;
>
The stats (on key columns with distribution) are updated, index is not
clustering and the filter factor according to db2exfmt is 0.997.
>
If I drop the index, DB2 performs tablescan and the query runs in about
an hour (versus 33 hours). This is probably due to prefetching during
table scan, versus no prefetching during non-clustered index access.
>
Why DB2 chooses to select more expensive indexed scan and what can I do
to make DB2 optimizer select a better access path without dropping the
index or modifying the sql?

Could it be the table is defines as VOLATILE?
Also it would be really interesting to know how much of that time is
spent on the wire (processing 78M rows should not take 1hr, not to
mention 33hrs).
To test this the easiest is probably to run a MAX() on each column.
This way Db2 does the same processing but will return only one row.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Serge Rielau wrote:

Could it be the table is defines as VOLATILE?

Are you sure you don't have a second job working as a psychic? :)

You are right. Knowing the table, I never would have thought this table
to be defined as volatile, but again I have been wrong numerous times
before!

Also it would be really interesting to know how much of that time is
spent on the wire (processing 78M rows should not take 1hr, not to
mention 33hrs).

I didn't tell the whole story; although select is skinny, the table
itself is somewhat fat (1810 row length, 138G not counting indexes). The
select was actually a part of an insert sql, so counting insert of 76M
rows and log overhead, one hour doesn't sound too much. Or does it?

To test this the easiest is probably to run a MAX() on each column.
This way Db2 does the same processing but will return only one row.

Much faster. 12 minutes.

>
Cheers

Yes it did bring cheers to me.

Serge

Thanks