| 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: Segment management auto clause
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:zJrwa.34254$1s1.498229_at_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).
Excuse me, Richard: those particular posts might have been based on a couple of "benchmarky" tests, but I wouldn't stick to a position if extensive subsequent and rather less "benchmarky" tests hadn't confirmed me in my views.
>
> 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)
Don't need to to me at any rate. It isn't one-bitmap-for-one-extent, I don't think I suggested with any absolute certainty that it *was* one-for-one, but I haven't been saying anything of late other than that it starts one-for-one.
>
> >
> > 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.
>
> *** added in 2048 rows to the assm table to make no of rows identical in
> both tables ****
>
> 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
> ------------------------------ ---------- ---------- ------------ --------
--Received on Wed May 14 2003 - 14:25:55 CDT
> NOT_ASSM_TAB 1712128 43352 679
906
> ASSM_TAB 1712128 43505 527
922
>
> *** note, approx 0.35% more blocks allocated ***
>
>
> 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
>
>
> **** note each have 114 extents, used autoallocate in this example ****
>
> 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
>
>
 |
 |