If the SQL change Ed suggested does not help, then I would suggest trying
a few things.

First, I would do a runstats on the SQL and save/review the output, Then,
I would probably modify two of your existing indexes and add include
columns (see below) followed by a runstats. These indexes should help
but make sure to do an explain after creating them and do runstats to
compare the before and after picture.

Next, I would review those unique indexes that are set to cluster. I have
found that clustering a unique index typically does not provide much of a
performance improvement. You generally want to cluster non-unique indexes
that have a high cardinality on the indexed columns.

You may also want to consider a reorganization of the data.

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_REQ_U1"
ON "SIEBEL"."S_ESCL_REQ"
("REQ_ID" ASC,
"RULE_ID" ASC
)
INCLUDE (CREATED)
CLUSTER
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_P1"
ON "SIEBEL"."S_ESCL_RULE"
("ROW_ID" ASC
)
INCLUDE(NAME)
PCTFREE 30
DISALLOW REVERSE SCANS;


Al Glinter ? Lead Database Administrator ? I.T. Solution Center
Lord & Taylor ? 500 North Broadway, Suite 1250 ? Saint Louis, MO 63102
phone (314) 552-3752 email [Email address protected]
www.lordandtaylor.com



"MChakravarty" <db2udbdba-ezmlmshield-x65248726.[Email address protected]
01/24/2008 05:09 AM

To
"LazyDBA Discussion" <[Email address protected]
cc

Subject
Query Tunning Help






Hello All,

We have following query which is running very slow for past few days :


select date(a.created) "Created", b.name "Policy Name", count(*) count
from siebel.s_escl_req a, siebel.s_escl_rule b
where a.rule_id = b.row_id
and date(a.created) >= '2008-01-22'
group by date(a.created),b.name
for fetch only;

Information About Tables :

1. SIEBEL.S_ESCL_REQ : is a very dynamic table. Every second a lot of
insertion and deletion happens in it. This table also contains a lot of
triggers.
Now the rcord count in this table is 267943.

It contains following indexes :
CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_REQ_U1"
ON "SIEBEL"."S_ESCL_REQ"
("REQ_ID" ASC,
"RULE_ID" ASC
)
CLUSTER
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE INDEX "SIEBEL"."S_ESCL_REQ_M1"
ON "SIEBEL"."S_ESCL_REQ"
("GROUP_ID" ASC,
"REQ_ID" ASC
)
PCTFREE 30
ALLOW REVERSE SCANS;

2. SIEBEL.S_ESCL_RULE : is a static table. Mostly used for readonly.
Current record count is 95.

It contains following indexes :
CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_U1"
ON "SIEBEL"."S_ESCL_RULE"
("NAME" ASC,
"CONFLICT_ID" ASC
)
CLUSTER
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_M1"
ON "SIEBEL"."S_ESCL_RULE"
("GROUP_ID" ASC,
"ROW_ID" ASC
)
PCTFREE 30
ALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_P1"
ON "SIEBEL"."S_ESCL_RULE"
("ROW_ID" ASC
)
PCTFREE 30
DISALLOW REVERSE SCANS;

CREATE INDEX "SIEBEL"."S_ESCL_RULE_R1"
ON "SIEBEL"."S_ESCL_RULE"
("OBJECT_NAME" ASC
)
PCTFREE 30
DISALLOW REVERSE SCANS;

CREATE UNIQUE INDEX "SIEBEL"."S_ESCL_RULE_U2"
ON "SIEBEL"."S_ESCL_RULE"
("SUB_TYPE_CD" ASC,
"NAME" ASC,
"CONFLICT_ID" ASC
) PCTFREE 30
ALLOW REVERSE SCANS;

Action Taken So Far :

I've updated the statistics of the table but it is still taking a lot of
time to execute.

I've replicated the same problem in a test environment.
There I tried the db2advis tool which recomended an index for S_ESCL_RULE.

After creating it in the test environment it did not resolve the problem.
Is there anything we can do to improve the performance of the above query
?

Thanks,
Mrinal



http://www.rac.co.uk
http://www.rac.co.uk/business
http://www.bsm.co.uk

Any opinions expressed in this e-mail are those of the individual and not
necessarily the company. This e-mail and any attachments are confidential
to RAC and/or BSM and are solely for use by the intended recipient.

If you are not the intended recipient you must not disclose, copy or
distribute its contents to any other person nor use its contents in any
way.
If you have received this e-mail in error please forward a copy of this
e-mail to "[Email address protected]

RAC Motoring Services: Registered England 1424399
VAT Reg No. GB 238640945
British School of Motoring Limited: Registered England 291902
VAT Reg No. GB 239505847
RAC Financial Services Limited: Registered England & Wales 5171817
VAT Reg No. GB 238640945
Registered Office(s): 8 Surrey Street, Norwich, NR1 3NG

RAC Motoring Services and RAC Financial Services Limited are authorised
and regulated by the Financial Services Authority, both companies are
members of the Aviva group.

This e-mail and any attachments has been scanned for the presence of
computer viruses. RAC/BSM accept no responsibility for computer viruses
once this e-mail has been transmitted.


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



DB2 & UDB email list listserv db2-l LazyDBA home page