Re: Ways to improve database speed

In article <378BA2C6.A52828E4_at_gelrevision.nl>, "Kristiaan J. Kolk" <akolk_at_gelrevision.nl> wrote:
>Buy enough disks !!
>
>
>bgumus_at_my-deja.com wrote:
>
>> Hi,
>> In our project, we process flat files and populate our 25 tables with
>> the data from the file. And later, we create reports from the database.
>> Our problem is speed. What do you advise us to increase database
>> population and data retrieving speed? Do we need to increase size of
>> the rollback segments? What should we do?

I think what Kristian is getting at is, you may have contention among your disk drives. Ideally every tablespace and redo log should be on its very own disk drive, as should your input and output files. Short of that you have to start compromising. At least try to keep data and index tablespaces on separate drives, and a third drive for redo logs and control files.

If you have multiple processors on your server, you may speed up the load part of your job stream by splitting it into parallel jobs, one to load each table. You can do the same thing with reports, especially if you don't have any programs that modify your database; run all your reports at once! Fine tune things by arranging dependencies among programs that use the same disk drives so they won't run at the same time.

It's also worth taking a step back and asking yourself why you build an entire database just for these reports - and why use Oracle for the job. It would be better to maintain the data in Oracle, or to keep the old data and just bring in changes, than to rebuild it again and again. And Oracle may not be a good choice for this job; you're paying for a lot of capacity and features you aren't using (and a lot of overhead).

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to
deanguer@       | rely on a giraffe filled with whipped cream."
quidnunc.net    | - Frank Zappa