By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,276 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,276 IT Pros & Developers. It's quick & easy.

Query access path question

P: n/a
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
Aug 12 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/
Aug 12 '06 #2

P: n/a
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
Aug 13 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.