Slow prod DB

From: John <jabliese_at_my-deja.com>
Date: Tue, 31 Aug 1999 20:08:24 GMT
Message-ID: <7qhcne$65c$1@nnrp1.deja.com>

We've got two supposedly identical databases for test and production. Indexes are identical. The test database is what the production was about 3 months ago. The following SQL runs in about 30 min on test, ~15 hours on production. Production is only slightly larger than test, each holds about 10 years of data.

Any help, from optimizing the SQL to what may cause such a disparity between similar databases will be appreciated.

select (various and sundry columns)
from a, t, ee
where ee.account_no = a.account_no

and a.cat like 'PER'
and a.type like 'EARN'
and t.type like 'PER'
and a.fiscal_month = 12
and (NVL(TO_NUMBER(SUBSTR(A.NUM,LENGTH(A.NUM) -1,2)),


          (TO_NUMBER(SUBSTR(A.NUMA,LENGTH(A.NUMA) -1,2)))) between

t.begin_digit and t.end_digit)
and ee.balance != 0
UNION
select (various and sundry columns)
from a, t, fe
where fe.account_no = a.account_no

and a.category like 'PER'
and a.type like 'EARN'
and t.type like 'PER'
and a.fiscal_month = 12
and (NVL(TO_NUMBER(SUBSTR(A.NUM,LENGTH(A.NUM) -1,2)),


          (TO_NUMBER(SUBSTR(A.NUMA,LENGTH(A.NUMA) -1,2)))) between

t.begin_digit and t.end_digit)
and fe.balance != 0

a has about 150,000 records
t about 20
ee and fe are views based on unions of other tables, the largest of which is about 500,000 records. The view sums up values in the union. We are running Oracle on NT, both the test and production databases share the same machine.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Aug 31 1999 - 15:08:24 CDT