Re: Performance problems with really big tables

THere are probably 2 related keys to the performance: 1-are you doing full table scans
2-how big is your sga

FOr 1, you need to set up query indexes so that your SQL does not have to do tablescans. COmpound indexes are easier to document, but whatever works for your app sql

For 2, if you have to do full table scans, set your db_block_buffers and multi- block read count parameters in your initxxx.ora to as high as you can without paging and thrashing.

We have several apps with tables as large or larger than yours (100+M rows, 16- 20GB per table.) With the right indexing and SQL, as long as you are not doing full tablescans, retrieval times should be in seconds.

In article <01be276b$4fed7aa0$a12c6394_at_J00679271.ddc.eds.com>,   "Mark Powell" <Mark.Powell_at_eds.com> wrote:
> We have a dozen tables with row counts of 10 - 20 million rows each that
> are often involved in joins with acceptable performance levels. You need
> to look at each SQL statement involved where performance is a problem and
> tune these.
>
> Also the number of rows in a table is a lot less important than the number
> of rows times the average row length, i.e., total size in bytes of the data
> that will be processed by the query and how you get those rows.
>
> If you post some of the queries to try to get help on tuning them be sure
> to post the explain plan, the version level of Oracle, the database
> optimizer goal, and how the statistics if any were generated. You might
> want to post the stats.
>
> We do not use surrogate keys. We find them to be of little practical
> value.
>
> Marco Ribeiro <mar_at_bart.inescn.pt> wrote in article
> > We've got two tables with more than 6 million records in each one and
> > performance deteriorates as they grow, can anyone give some advice on
> > improving the performance of operations on these tables.
> >
> > Also is it more eficient to have a surrogate key or a compund key ?
> >
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 15 1998 - 09:56:44 CST