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

Hi Howard,

Interesting results.

I think it's nearly time for me to roll my sleeves up and have a good play with all this. A few factors to throw into the mix though.

1. How many extents did your ASSM table use. My understanding is that at least one BMB is required for each extent, more if necessary. Therefore this would suggest that small extent sizes (and hence lots of extents and lots of BMB) would be less efficient that few big ones. Or are fewish blocks allocated per BMB that it makes no practical difference ?
2. Average row sizes. If the average row size were to be similar, then non ASSM tables would cope quite OK (all things being equal). But if you have quite a variance between row sizes, then non ASSM will often prematurely take blocks off the free list, space that could quite easily have been taken up by smaller rows. This would mean more blocks being allocated than ASSM tables which can fill each block up the the pctfree very nicely.
3. Subsequent delete activity. If the table characteristic includes significant deletes and more inserts to follow, then with a poorly set PCTUSED, non ASSM could have much wasted space under the HWM and new blocks being allocated instead of emptyish ones being reused. This would mean again more allocated blocks below the HWM over time (whereas ASSM has blocks available again once 75% threshold is reach, providing PCTFREE is less than 25%).

So, I think there are other issues to consider with all this ...

Cheers (and keep up the good work ;)

Richard

"Howard J. Rogers" wrote:
>
> 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 Wed Aug 14 2002 - 03:28:49 CDT