Re: What space of a Oracle block is being used?

Hi Niall,

OK, time for me to put up or shut up:

Some preliminaries...

create tablespace noassm datafile 'whatever' size 30m autoextend on; create tablespace assm datafile 'somewhere else' size 30m segment space management auto autoextend on;

create table noassmtbl tablespace noassm as select * from dba_objects; create table assmtbl tablespace assm as select * from dba_objects;

repeated inserts into both tables as select * from original table. About 1.9 million rows in both tables by the end of it.

The inserts are interesting in their own right, and deserve more investigation:

SQL> insert into assmtbl select * from assmtbl; 473936 rows created.
Elapsed: 00:01:03.06

SQL> insert into assmtbl select * from assmtbl; 947872 rows created.
Elapsed: 00:03:17.05

SQL> insert into noassmtbl select * from noassmtbl; 473920 rows created.
Elapsed: 00:01:19.01

SQL> insert into noassmtbl select * from noassmtbl; 947840 rows created.
Elapsed: 00:03:05.07

Inserting half a million rows or thereabouts took 1:03 in ASSM, 1:19 in NOASSM. One up for ASSM. Then the insertion of a million rows took 3:17 in ASSM, but only 3:05 in NOASSM. One up for freelists.

Now for the selects:

SQL> startup force
SQL> select count(*) from scott.noassmtbl;

  COUNT(*)

   1895680

Elapsed: 00:00:32.06

SQL> startup force
SQL> select count(*) from scott.assmtbl;

  COUNT(*)

   1895744

Elapsed: 00:00:41.01

So now ASSM takes longer (9 seconds or so).

Repeat runs without clearing the cache in between yielded this:

ASSM 1st run : 41.01
ASSM 2nd run: 36.05 (the benefits of cacheing, I guess)

startup force

NOASSM 1st run: 30.06
NOASSM 2nd run: 30.04 (hmmm. perhaps the cache went on strike?)

How about computing statistics (which induces a full scan, amongst other things)?

SQL> analyze table scott.assmtbl compute statistics; Table analyzed.
Elapsed: 00:02:43.03

SQL> analyze table scott.noassmtbl compute statistics; Table analyzed.
Elapsed: 00:02:35.04

One more point for freelists.

And the results of that analyzing?

TABLE_NAME                         BLOCKS EMPTY_BLOCKS  AVG_SPACE
------------------------------ ---------- ------------ ----------
ASSMTBL                             25921          703        897
NOASSMTBL                           25734          889        857

The ASSM blocks are emptier, and there are more of them (hence the longer scan times, I guess). ASSMTBL only has 64 rows more than NOASSMTBL, so that can't explain the extra 187 blocks (this is an 8K system, so that's an additional 1.5Mb).

Based on these results, the differential is only likely to get worse when there are multi-tens of millions of rows.

I'd be interested if you (and others) could run exactly the same tests and report back. This is 9i R2 on Windows 2000, but I'll do a Solaris test tomorrow.

But I'll shut up in the meantime, to the extent of saying that ASSM for small tables (anything up to half a million rows, say) probably makes no discernible performance difference than non-ASSM.

Regards
HJR "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3d58cbb9$0$232$ed9e5944_at_reading.news.pipex.net...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:3d58c34d_at_dnews.tpgi.com.au...
> > It's either the nasal hairs growing in my nostrils, or I can smell a new
> > Oracle myth in the making: "Use ASSM! All the time! It's brilliant!".
> >
> > It's not. It's bloody awful for full table scans unless you have
freelist
> > contention growing as prolifically as my nasal hairs. So there!
> >
> > Regards
> > HJR
>
> Do you have tests (or a pointer to someone elses work) for this statement?
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
>
>
Received on Tue Aug 13 2002 - 08:15:03 CDT