Re: Oracle Performance

From: Walter Muntzenberger <wmuntz_at_ix.netcom.com>
Date: 1996/09/06
Message-ID: <3230EBA7.75A4_at_ix.netcom.com>#1/1


Bill Roney wrote:
>
> I'm having a problem with a 7.2.3 server. It is running on an NCR system with 8 P133s and 3GB of
> physical memory (sorry, I don't know the model number) running AT&T UNIX.
>
> I have 2 tables that form a master detail relationship, the general structure of the tables are as
> follows:
>
> table T1
> (
> fielda char(5) not null,
> fieldb date not null,
> fieldc number not null,
> ...
> ) (row with is around 250 bytes)
> table T2
> (
> fielda char(5) not null,
> fieldb date not null,
> fieldc number not null,
> fieldd char(1) not null,
> ...
> ) (row with is around 150 bytes)
>
> The sizes of these tables are 2.5 million rows in T1, and 6 million rows in T2.
>
> These 2 tables are joined with the following where and order by clauses
> where T1.fielda = T2.fielda
> and T1.fieldb = T2.fieldb
> and T1.fieldc = T2.fieldc
> and T1.fieldb >= '[a start date]'
> and T1.fieldb <= '[an end date]'
> order by T2.fielda, T2.fieldb, T2.fieldc, T2.fieldd
>
> The result set returns 20 minutes later, with the sar report during the period reporting 0% idle
> time.
>
> I have a requirement that the result set be built < 5 minutes. Is this possible based upon anyone's
> experience? I have been told that the requirement is not possible.
>
> On a side note, selecting just the rows from T1 (no join) takes 3 minutes. This would imply that I
> may be able to perform the join on the client faster than the join being done by the RDBMS?!?
>
> Any help or insight would be greatly appreciated.
>
> Thanks in advance.
> Bill

-- 
Bill,
A couple of questions before attempting to prescribe.....with some
comments. 

What indexes are built on these tables?
Are you using PQO? If yes, what degree?
What is the selectivity of your date range in percentage of rows
returned? How many rows returned?
Is this result set being loaded into a table (CTAS), or dumped to flat
file?
What is your block size?
What is your sort area size?
What hint are you applying if any?
Whats the explain say?

Indexes are not always an answer unless the selectivity of the query is
low. If the query really requires a FTS, then an index on date in this
case will slow the query down drastically if using rule based
optimization. Override this with hints. Also, a degree of parallelism
using OPQ may also break up the workload. Since you are ordering, I
would also be concerned about sort area size tuning. Do other sorts run
slowly also? Finally, if you are exporting many rows this may also be a
bottleneck. 

Other features to check into might be to make sure that async reads and
block read count is tuned for FTS and direct writes are enabled. Id have
to check the NCR port to see about these parameters.

Walt Muntzenberger
Received on Fri Sep 06 1996 - 00:00:00 CEST

Original text of this message