| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: What space of a Oracle block is being used?
"Richard Foote" <Richard.Foote_at_oracle.com> wrote in message
news:3D5A14C1.872699C4_at_oracle.com...
> 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 ?
I thought about this afterwards, because I was a bit disappointed that ASSM wasn't nearly as thoroughly thrashed as I'd expected. Then I realised I made a fatal faux pas: my create tablespace statement mentioned no uniform extent sizes. So I would have gotten auto-allocate, which means I would probably have quite quickly been getting very large extents As it is, both the ASSM and non-ASSM segments acquired 97 extents for their 1.8million rows... sure enough, I was quickly acquiring 8Mb extents. (In my defence, it *was* midnight!)
Had I specified, say, a 64K uniform size, then the bitmap overhead per extent would have been very much more considerable, and I'd expect the results to be awful for ASSM.
So, let's try again, shall we?
SQL> create tablespace assm
2 datafile 'd:\oracle\ora92\db9\assm01.dbf' size 40m autoextend on next
10m
3 segment space management auto
4 uniform size 64k;
Tablespace created.
SQL> create tablespace noassm
2 datafile 'd:\oracle\ora92\db9\noassm01.dbf' size 40m autoextend on next
10m
3 uniform size 64k;
Tablespace created.
SQL> create table assm tablespace assm as select * from dba_objects; Table created.
SQL> create table noassm tablespace noassm as select * from dba_objects; Table created.
SQL> set timing on
SQL> insert into assm select * from assm;
29620 rows created.
Elapsed: 00:00:02.09
SQL> /
59240 rows created.
Elapsed: 00:00:08.09
SQL> /
118480 rows created.
Elapsed: 00:00:21.06
SQL> /
236960 rows created.
Elapsed: 00:00:38.07
SQL> /
473920 rows created.
Elapsed: 00:01:26.02
SQL> /
947840 rows created.
Elapsed: 00:03:02.02
SQL> commit;
SQL> insert into noassm select * from noassm;
29621 rows created.
Elapsed: 00:00:04.00
SQL> /
59242 rows created.
Elapsed: 00:00:08.03
SQL> /
118484 rows created.
Elapsed: 00:00:15.04
SQL> /
236968 rows created.
Elapsed: 00:00:39.01
SQL> /
473936 rows created.
Elapsed: 00:01:20.09
SQL> /
947872 rows created.
Elapsed: 00:02:48.06
SQL> commit;
Now there's an interesting sight!! Inserts are considerably cheaper in non-ASSM than ASSM. Extent acquisition must be a bit of a bummer if you have to map out a bitmap each time!
Right, now for the acid test:
SQL> startup force
ORACLE instance started.
SQL> select count(*) from system.assm;
COUNT(*)
1895680
Elapsed: 00:00:39.05
SQL> startup force
ORACLE instance started.
SQL> select count(*) from system.noassm;
COUNT(*)
1895744
Elapsed: 00:00:23.00
Oooo-er. 39 seconds for ASSM to 23 seconds for non-ASSM. That's quite a performance difference (something like 33% if my maths is anything to go by -which it generally isn't!)
Let's do the stats calculations:
SQL> analyze table system.assm compute statistics;
Table analyzed.
Elapsed: 00:02:44.04
SQL> analyze table system.noassm compute statistics;
Table analyzed.
Elapsed: 00:02:36.06
So, much as last time with autoallocated extents.
What about segment sizes?
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,AVG_SPACE 2 from dba_tables where table_name like '%ASSM%';
TABLE_NAME BLOCKS EMPTY_BLOCKS AVG_SPACE ------------------------------ ---------- ------------ ---------- ASSM 25773 267 856 NOASSM 25732 0 857
There's an awful lot of empty blocks for the ASSM table, which the FTS must dodge.
SQL> select segment_name, bytes from dba_segments where segment_name like '%ASSM%'; SEGMENT_NAME BYTES
ASSM 213319680 NOASSM 210829312
That's 2490368 bytes extra for the ASSM segment (2.5Mb this time -it was only 1.5Mb last time).
SQL> select segment_name, count(*) from dba_extents where segment_name like '%ASSM%' group by segment_name;
SEGMENT_NAME COUNT(*)
>
> 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.
>
I agree that the finer-granularity of block availability you get with ASSM should mean more efficient packing of odd-sized rows. I just wonder how likely this (ie, wildly different row sizes) is to happen in practice.
> 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%).
>
Agreed. (Though nothing a 'move tablespace' wouldn't fix up).
Regards
HJR
> 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 - 05:33:11 CDT
 |
 |