Re: MY DATABASE IS SO SLOW

Do an explain plan on the sql and see what the execution plan is. This is the first step.

Second, find out what's running at the same time. It could be a resource contention problem.

Third, take the oracle perfomance tuning class.

Here are a few tuning rules of thumb:

• Place indexes and tables in separate tablespaces, on separate physical disks.
• Do an ANALYZE TABLE... on all the tables.
• Placed hot tables and indexes in their own tablespaces on separate disks.
• Stripe your data on the larger tables across multiple disks using multiple datafiles, and enable parallel query on them.
• What is your block size. I'd recommend it be at least 4k. For data warehouses, make it larger. Perhaps 16k.
• Set db_multiblock_read count appropriately.This number, multiplied by the block size is the logical i/o size for full table scans. Check with the SCO tuning guide that Oracle provides. It may provide some benchmark figures and recommendations. You'll probably want it to be at least 64k. If using RAID, make sure it's a multiple of the strip size.

Michael Wagner wrote:

> I have a database with approx 3.500.000 entrys with 110 byte/set and the
> database grow i this year up to 80.000.000 entrys. If i make a query on the
> 3.500.000 entrys it needs 1 hour before i get the result.
>
> select data1.year,data2.fare,data2.value,data2.class,names.n_desc,
> count(data2.fare), sum(data2.numbers) from
> data1,data2,names
> where data1.key=data2.key
> and names.n_key=data2.class and class >'0' and data1.year='98' and
> data1.company='33'
> group by data1.year,data2.fare,data2.value,data2.class,names.n_desc;
>
> The database is on SCO 5.0.4 and ORACLE 7.3.4 I have create a index on
> every query part. The database is now 390 MB the database grows up to 8-9
> GB
> What can i do to make the query faster.
>
> Thanks Mike !

Received on Mon Dec 07 1998 - 14:12:47 CST