Re: Segment management auto clause

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 15 May 2003 10:16:49 +0100
Message-ID: <b9vlrt$isg$1$8300dec7@news.demon.co.uk>

Well,

It's amazing what odd results you can get by changing tests a little:

I took your test, and did it the quick way by looping with pl/sql execute immediate and an 'alter table allocate extent'.

In the first test I got the following:

16 extents with 8 blocks (8K blocksize)
8 of them had bitmap blocks covering two dba ranges, e.g.:

DBA Ranges :

   0x03000009  Length: 8      Offset: 0
   0x03000011  Length: 8      Offset: 8

which I took to mean the map covered two ranges of blocks, and the hex was the starting block address of each range.

Then there were 63 extents of 128 blocks. Each of these extents had its own bitmap, In all but the last extent the bitmap used two blocks.

Then there was a collection of extents
with 1024 blocks - each extent had its
own bitmap, and each bitmap used
four blocks.

So in the VERY SMALL case, using autoallocate, we ended up with two extents covered by one bitmap.

TEST 2:

Repeat, but create two tables in the same tablespace but alternate the allocation of extents. Results were very similar - except that there were 12 bitmaps in the first 16 extents OF THE FIRST TABLE only.

When I checked the ranges for these bitmaps, I found something odd. These are the first 3 ranges:

DBA Ranges :

   0x03000009  Length: 8      Offset: 0
   0x03000019  Length: 8      Offset: 8

  --------------------------------------------------------

DBA Ranges :

   0x03000019  Length: 8      Offset: 0    -- repeat cover ?
   0x03000021  Length: 8      Offset: 8    -- belongs to other table !

  --------------------------------------------------------

DBA Ranges :

   0x03000029  Length: 8      Offset: 0
   0x03000039  Length: 8      Offset: 8

The other table had ranges which looked fine - the 16 extents had 8 bitmaps, and each bitmap appeared to list two extents that really did exist in the table, and were the 'reasonable' pair of ranges for the bitmap.

Having dropped the tables to check back to the first experiment, I know find that when I create just one table in the ASSM tablespace, it has 12 bitmaps in the first 16 extents.

Looks like I may have to drop and recreate the tablespace to reproduce the first clean result.

But this does suggest that there may be room for caution when using ASSM.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June 4th - 6th
____Australia_June 18th - 20th (Perth)
____Australia_June 23rd - 25th (t.b.a)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:zJrwa.34254$1s1.498229_at_newsfeeds.bigpond.com...


>

> SQL> create tablespace assm

>   2  datafile 'c:\bowie\assm01.dbf' size 2000m

>   3  segment space management auto;

>

> Tablespace created.

>

> SQL> create table assm_tab tablespace assm as select * from

dba_tables;


>

> Table created.

>

> SQL> insert into assm_tab select * from assm_tab;

>

Received on Thu May 15 2003 - 04:16:49 CDT