Re: Query taking way to long

Hi.

1. Are you using COST or RULE based optimizer?
2. If COST - did you analyze all tables.
3. Post EXPLAIN or even better - Run the statements after

    "ALTER SESSION SET SQL_TRACE = TRUE;"     And post TKPROF output.

 Michael.

In article <38275f9e.23164008_at_client.nw.news.psi.net>,   chuck_at_phi.org (Chuck) wrote:
>
> I have the following query that was built by Seagate Crystal Reports
> (7). The query takes a very long amount of time against a database
> with relatively little data. The query is taking anywhere from 30 to
> 60 minutes on an idle box (Sun E3500 with 2 cpu's and 2 gig of ram)
> While monitoring TEMP tablespace I noticed that is uses up about
> 126Megs of space (out of 300Meg) and then continues to consume CPU
> cycles. About 25% of the query time is used writing to TEMP, the
> remaining 75% is all CPU time. I have tried various arrangements of
> indexes but none seem to affect the query time.
>
> Here is the query:
>
> SELECT
> ACACCTCATX."ACTIVITY", ACACCTCATX."ACCT_CATEGORY",
> ACACCTCATX."ACCT_CAT_CLASS",
> ACLEVEL."POSTING_FLAG",
> ACACCTCLSS."DESCRIPTION",
> ACACTIVITY."ACTIVITY", ACACTIVITY."DESCRIPTION",
> ACBUDDTLX."FISCAL_YEAR", ACBUDDTLX."PERIOD", ACBUDDTLX."AMOUNT",
> ACTRANS."TRAN_AMOUNT", ACTRANS."PERIOD", ACTRANS."FISCAL_YEAR"
> FROM
> "LAWDEV"."ACACCTCATX" ACACCTCATX,
> "LAWDEV"."ACLEVEL" ACLEVEL,
> "LAWDEV"."ACACCTCLSS" ACACCTCLSS,
> "LAWDEV"."ACMASTERX" ACMASTERX,
> "LAWDEV"."ACACTIVITY" ACACTIVITY,
> "LAWDEV"."ACBUDDTLX" ACBUDDTLX,
> "LAWDEV"."ACTRANS" ACTRANS
> WHERE
> ACACCTCATX."ACTIVITY_GRP" = ACLEVEL."ACTIVITY_GRP" AND
> ACACCTCATX."ACTIVITY" = ACLEVEL."ACTIVITY" AND
> ACACCTCATX."ACCT_CAT_CLASS" = ACACCTCLSS."ACCT_CAT_CLASS" AND
> ACACCTCATX."ACTIVITY" = ACMASTERX."ACTIVITY" AND
> ACACCTCATX."ACCT_CATEGORY" = ACMASTERX."ACCT_CATEGORY" AND
> ACACCTCATX."ACTIVITY" = ACACTIVITY."ACTIVITY" AND
> ACMASTERX."ACTIVITY" = ACBUDDTLX."ACTIVITY" AND
> ACMASTERX."ACCT_CATEGORY" = ACBUDDTLX."ACCT_CATEGORY" AND
> ACMASTERX."ACTIVITY" = ACTRANS."ACTIVITY" AND
> ACMASTERX."ACCT_CATEGORY" = ACTRANS."ACCT_CATEGORY" AND
> ACLEVEL."POSTING_FLAG" = 'P' AND
> ACTRANS."FISCAL_YEAR" = 1999. AND
> ACBUDDTLX."PERIOD" >= 1. AND
> ACBUDDTLX."PERIOD" <= 10. AND
> (ACACCTCATX."ACTIVITY" = '01995-01-01' OR
> ACACCTCATX."ACTIVITY" = '01596-19-05')
> ORDER BY
> ACACCTCATX."ACCT_CAT_CLASS" ASC,
> ACACCTCLSS."DESCRIPTION" ASC
>
> Here are the number of rows in each of the tables:
>
> ACACCTCATX=4389
> ACLEVEL=72
> ACACCTCLSS=55
> ACMASTERX=73
> ACACTIVITY=72
> ACBUDDTLX=7882
> ACTRANS=141
>
> I can provide further info such as table descriptions if needed.
>
> NOTE: By removing the last section of the WHERE clause containing the
> OR has relatively no effect on the query completion time.
>
> Thanks greatly,
> Chuck
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 09 1999 - 15:39:44 CST