Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Segment management auto clause

Re: Segment management auto clause

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 15 May 2003 00:00:12 +1000
Message-ID: <zJrwa.34254$1s1.498229@newsfeeds.bigpond.com> 





"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:NKawa.33877$1s1.492365_at_newsfeeds.bigpond.com...


> The test results I got were posted right here not so very long ago (late

> last year, I believe).




Hi Howard,



I've looked back and I think you're refering to the thread "What space of a
Oracle Block is being used" which goes back to last August (boy, does time
fly !!)



Reading through it all again, a few points I would make.



Firstly, the results are based only on a couple of benchmarky sort of tests
and certainly can't be classified as conclusive proof (as can't my testing
although I've been playing with all this for quite a while).



Secondly, there was some debate regarding each extent requiring it's own set
of bitmap blocks. This is incorrect. One bitmap block can map to many
extents and indeed as an object grows, Oracle assigns more bitmaps within a
bitmap block. Basically the ratio of bitmap blocks is based on a sliding
decreasing proportion to the number of blocks (not extents) in a segment
(show anyone interested a few block dumps if you like)



>

> 3% extra space utilisation, 17+% extra full scan time.




OK, this is all incorrect generalisations. The extra space utilisation
really does depend. Some first level bitmap blocks may only span 16 blocks,
however some first level bitmap blocks span 64 blocks or 128 blocks or 256
blocks, etc depending of the current size of the segment and block size. So
tiny tables can have an extra space utilisation of somewhat more than 3%,
larger tables can have an extra space utlilisation of a lot less than 3%.



Extra full time scan time of 17%+, well let's see. I would be really
interested to see what results other people have with the following demo:
(WARNING: a touch on the long side !!)



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;



835 rows created.



SQL> /


1670 rows created.



SQL> /


3340 rows created.



SQL> /


6680 rows created.



SQL> /


13360 rows created.



SQL> /


26720 rows created.



SQL> /


53440 rows created.



SQL> commit;



Commit complete.



SQL> insert into assm_tab select * from assm_tab;



106880 rows created.



SQL> /


213760 rows created.



SQL> /


427520 rows created.



SQL> commit;



Commit complete.



SQL> insert into assm_tab select * from assm_tab;



855040 rows created.



SQL> commit;



Commit complete.




NOW CREATE THE NON ASSM EXAMPLE


SQL> create tablespace not_assm


  2  datafile 'c:\bowie\not_assm01.dbf' size 2000m
  3  ;



Tablespace created.



SQL> create table not_assm_tab tablespace not_assm as select * from
dba_tables;



Table created.



SQL> insert into not_assm_tab select * from not_assm_tab;



836 rows created.



SQL> /


1672 rows created.



SQL> /


3344 rows created.



SQL> /


6688 rows created.



SQL> /


13376 rows created.



SQL> /


26752 rows created.



SQL> /


53504 rows created.



SQL> /


107008 rows created.



SQL> commit;



Commit complete.



SQL> insert into not_assm_tab select * from not_assm_tab;



214016 rows created.



SQL> /


428032 rows created.



SQL> commit;



Commit complete.



SQL> insert into not_assm_tab select * from not_assm_tab;



856064 rows created.



SQL> commit;



Commit complete.






SQL> insert into assm_tab select * from assm_tab where rownum < 2049;



2048 rows created.



SQL> commit;



SQL> select table_name, num_rows, blocks, empty_blocks, avg_space
  2  from dba_tables


  3  where table_name like '%ASSM%';


TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE
------------------------------ ---------- ---------- ------------ ----------
NOT_ASSM_TAB                      1712128      43352          679        906
ASSM_TAB                          1712128      43505          527        922







SQL> select segment_name, count(*) from dba_extents
  2  where segment_name like '%ASSM%'


  3  group by segment_name;




SEGMENT_NAME           COUNT(*)


-------------------- ----------
ASSM_TAB                    114
NOT_ASSM_TAB                114








FTS performance after a few warm up runs for the non ASSM table ...


SQL> set autotrace traceonly statistics;
SQL> set timing on
SQL> alter session set sql_trace=true;





Session altered.



Elapsed: 00:00:00.00


SQL> select * from not_assm_tab;



1712128 rows selected.



Elapsed: 00:08:44.05



Statistics



          0  recursive calls
          0  db block gets
     154571  consistent gets
      43294  physical reads
          0  redo size




   75588057  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



SQL> /


1712128 rows selected.



Elapsed: 00:08:48.05



Statistics



          0  recursive calls
          0  db block gets
     154571  consistent gets
      43294  physical reads
          0  redo size




   75588057  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



SQL> /


1712128 rows selected.



Elapsed: 00:08:55.04



Statistics



          0  recursive calls
          0  db block gets
     154571  consistent gets
      43294  physical reads
          0  redo size




   75588057  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed




So we're looking at a time in the region of  8min 50 secs ....



And the ASSM table ...



SQL> select * from assm_tab;



1712128 rows selected.



Elapsed: 00:08:01.03



Statistics



          0  recursive calls
          0  db block gets
     154805  consistent gets
      43203  physical reads
          0  redo size




   80025841  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



SQL> /


1712128 rows selected.



Elapsed: 00:07:52.02



Statistics



          0  recursive calls
          0  db block gets
     154805  consistent gets
      43203  physical reads
          0  redo size




   80025841  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



SQL> /


1712128 rows selected.



Elapsed: 00:07:50.09



Statistics



          0  recursive calls
          0  db block gets
     154805  consistent gets
      43203  physical reads
          0  redo size




   80025841  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



Approx 7min 50 sec, 1 minute faster ?????



Well go figure ??????



One final run for good measure ....


SQL> set timing on
SQL> set autotrace traceonly statistics
SQL> select * from not_assm_tab;





1712128 rows selected.



Elapsed: 00:08:53.07



Statistics



          0  recursive calls
          0  db block gets
     154571  consistent gets
      43294  physical reads
          0  redo size




   75588057  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



Still the same, and it's trace file shows:



select *


from


 not_assm_tab




call     count       cpu    elapsed       disk      query    current
rows


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




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch   114143     16.20     113.56      43294     154571          0
1712128


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




total   114145     16.20     113.56      43294     154571          0
1712128



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: 59



Rows     Row Source Operation

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


1712128  TABLE ACCESS FULL NOT_ASSM_TAB











and a final run for the ASSM table ...


SQL> set autotrace traceonly statistics;
SQL> set timing on
SQL> select * from assm_tab;





1712128 rows selected.



Elapsed: 00:07:44.08



Statistics



          0  recursive calls
          0  db block gets
     154805  consistent gets
      43203  physical reads
          0  redo size




   80025841  bytes sent via SQL*Net to client
    1256054  bytes received via SQL*Net from client

     114143  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)




    1712128  rows processed



Still the same, and it's trace file ....



select *


from


 assm_tab




call     count       cpu    elapsed       disk      query    current
rows


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




Parse        1      0.00       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch   114143     15.56      49.44      43203     154805          0
1712128


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




total   114145     15.56      49.44      43203     154805          0
1712128



Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 59



Rows     Row Source Operation

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


1712128  TABLE ACCESS FULL ASSM_TAB









Better CPU, better elapsed ???



Well go figure ????



If you notice, the number of LIOs is a little higher with ASSM but due to
the way FTS load data, the number of PIOs is somewhat less. Then again,
perhaps the file management layer ain't so hot for the not_assm tablespace
...



There's more to all this than meets the eye ....



>

> Repeated ad nauseam on Windows and Linux, and demo'd off the top of my

head


> every time I do the 9i New Features course (ie, on Solaris too, and

without


> much effort).




Like I said, it depends ....



>

> And ask Jonathan about having 20%+ of his Buffer Cache full of bitmap

> blocks.




Huummmm, like to see the configuration for this. One of our prod databases
has a 3.5G buffer cache, curious if 20% + would be used by bitmap blocks in
this case ...



Again, an obvious big depends.



Now I'm not saying that ASSM is *it* but I have I see and I'm using ASSM
segments that work very nicely thank you kindly. Testing, benchmarking and
just giving it a go is the only way to really tell.



Food for further thought I hope ...



Cheers



Richard
Received on Wed May 14 2003 - 09:00:12 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US