Re: Slow prod DB

From: Eike J. Heinze <eike.heinze_at_compuware.com>
Date: Wed, 01 Sep 1999 13:49:24 -0500
Message-ID: <37CD7534.6C11D653@compuware.com>

Hi John;

No solution, sorry. But some ideas?

1.    We need to know many more details: Are the INIT<sid>.ORA files equal?
2.    What version of Oracle?
3.    Cost- or Rule based optimization?
4.    Analyze done? Regularly?
5.    The smallest of your tables would not be the driving table by default

neither in rule- nor in cost-based optimization. 6. What is the performance of the views? Summary tables instead? 7. Your second note suggests possibly indexing problems. Equality between the two instances doesn't mean a thing when the original of a copy is already sick...

Unabashed note: Your statement (and the views?) could be a classic example for an application tuning seminar... no offense intended ;-)

Regards
Eike

John wrote:

> 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 Wed Sep 01 1999 - 13:49:24 CDT