Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Large table and buffer cache concerns

Re: Large table and buffer cache concerns

From: <>
Date: 16 Apr 2007 15:03:47 GMT
Message-ID: <20070416110348.904$> wrote:
> Hello all,
> I've an application developer with a rather unique request, for a
> High Volume OLTP system. I've yet to work with tables of this
> magnitude before so I'd love some second opinions.
> Essentially, a 400 million record table consisting of 43 byte records.
> 7 int and 2 date columns. Which accounts for approximately half the
> overall database storage. This is an associative table along these
> lines:

> SQL> create table j ( a references c, x references q, z int, k int, l
> date, m date, n int, o int, p int, primary key(a,x) );

> The data is routinely (once every 5 to 30 min) selected by c.a:
> select x,z,l,m,n,o,p from j where a = :1;
> expecting a result set of 1000 records, (once every 5 - 30 min) for 3K
> different values of :1 at the same frequency.

So 3000 different queries, with 3000 different :1, occuring evenly spaced over a period of 5 to 30 minutes, then starting over again? So somewhere between 2 and 10 queries per second?

> The data is laid down pretty much randomly over time, with a single
> insert or update per 3K customers once every 30 min or so.

So your insert pattern would suggest, in a ordinary heap table, that the frequently co-selected rows will be scattered throughout the table giving a very bad cluster factor. This seems to be the very thing that hash tables and cluster tables were designed for, but I have heard so little about people actually using them that I am leery of them. Maybe just my ignorance. How about using an IOT? That will automatically compact all of one customers records together into just a few blocks (assuming you make the customer the leading field in the PK)

> So back to what has me concerned... The buffer cache.

Are you worried about queries on this table causing itself to have problems with the buffer cache, or causing other queries to have trouble with the buffer cache?

> Potential option 1:
> Tune the DB_BLOCK_SIZE from 8K to 2K as the average row length for all
> tables is 43 bytes as well. (ok this one table has certainly skewed
> that average)

That strikes me as a deck-chairs on the titanic kind of thing.

> Potential option 2:
> Shoe-horn these 1000 records per 400K customers into as few 8K DB
> blocks as I can through the use of BLOBs.

Or hash tables, or cluster tables, or index-organized tables. Or just rebuilding the table occasionally, sorting it by the key that you need to sort it on in order to improve the cluster factor.

> The data represented by
> this single 400 million record table can be looked at logically as
> three lists. Active, repeatable and historical.
> Active could be condensed to about 640 bytes and store in row.
> Repeatable to about 6KB and stored out of row.
> Historical to just under 4000 bytes and stored in row.
> Create table j (a references c, d BLOB, e BLOB, f BLOB)
> lob (d,f) STORE AS
> (TABLESPACE example
> enable storage in row)
> lob (e) STORE AS
> (TABLESPACE example
> disable storage in row)
> ;

That seems like a very bad idea. Now you will probably have a nasty bottleneck at the insert/update stage rather than the select stage, and of course all the stuff that the database is supposed to handle for you nicely will now need to be handled in the application code.


-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB
Received on Mon Apr 16 2007 - 10:03:47 CDT

Original text of this message