Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Query taking way to long
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" ANDACMASTERX."ACTIVITY" = ACTRANS."ACTIVITY" AND ACMASTERX."ACCT_CATEGORY" = ACTRANS."ACCT_CATEGORY" AND ACLEVEL."POSTING_FLAG" = 'P' AND
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
Received on Mon Nov 08 1999 - 17:52:45 CST