Zurück

The Secrets of Oracle ASSM and Freelists

ASSM is Oracle's Automatic Segment Space Management. This article shows how to increase
Disk I/O for heavy loaded OLTP systems (INSERT's) using the optimal number of FREELISTS.


Overview

Most Oracle databases use locally managed tablespaces (LMT) today - dictionary managed tablespaces (DMT) are now obsolet. The reasons to use LMTs are the following:

  • Object fragmentation you would easily get in a DMT cannot happen in a LMT.
  • The number of extents in an object is not relevant. You need not be concerned with objects that have many extents.
  • Recursive SQL overhead is virtually entirely gone.
  • You do not need to try figure out what the optimal INITIAL, NEXT, PCTINCREASE, MAXEXTENTS are. They are no more relevant - if you use them, they are usually disastrous.
  • For most applications LMTs with system managed extent sizes are fine.

So, remove the STORAGE clause from your CREATE statements - at least the INITAL, NEXT, MAXEXTENTS, MINEXTENTS and PCTINCREASE clauses. But do you exactly know how many FREELISTS to set on your tables and indexes? Do you know the right PCTFREE value set on a segment? If you you answer is no, then Automatic Segment Space Management might be a point to look at. In this article we review FREELISTS with and without ASSM.

Freelists and Freelists Groups

A FREELIST is where Oracle keeps tracks of blocks under the high-water mark for an object. Each will have at least one FREELIST associated with it. As blocks are used, they will be placed or taken off the FREELIST as needed. It is important to note that only blocks under the high-water mark of an object will be found on the FREELIST. The blocks that remain above the high-water mark will be used only when the FREELISTS are empty. In this fashion, Oracle postpones increasing the high-water mark for an object until it must.

An object may have more than one FREELIST. If you anticipate heavy insert or update activity on an object by many concurrent users, configuring more than one FREELIST can make a major positive impact on performance (at the cost of possible additional storage). Individual sessions will be assigned to different FREELISTS, and when they need space, they will not contend with each other.

The cost for the multiple FREELISTS may be additional storage, because a given session will use only one FREELIST for a segment for its entire session. If an object has many FREELISTS and each FREELIST has some blocks on it, a single session doing a large insert operation will ignore all but one of the FREELISTS. When it exhausts the blocks on the FREELIST it is using, it will advance the high-water mark for the table and not use the other free blocks.

Example (Oracle 10.1.0.3)

Parts of this example was published by Tom Kyte. In order to see the differences between a segment with, and without, multiple FREELISTS in a highly concurrent environment, let's set up a test. We start by creating a table test. We'll have five sessions inserting into this table concurrently. We will set up the table with a fixed-width column of 255 bytes. The first test is done with manual segment space management, then the same test once more with auto segment space management.

ASSM Manual with 1 Freelist

sqlplus system/<password>

Create a tablespace which is manual segment space managed:

create tablespace assm_manual
  datafile '/u01/oracle/db/AKI1/tab/AKI1_assm_manual.dbf'
  size 500M segment space management manual;

Then a table with 1 Freelist within the tablespace:

drop table test;
create table test (
  x date,
  y char(255) default 'x'
)
storage (freelists 1)
tablespace assm_manual;

Now create a procedure to insert rows into table test for a five minute period. We use DBMS_JOB to run 5 concurrent do_insert procedures, so make sure that JOB_QUEUE_PROCESSES is set to at least 5 in the INIT.ORA file. To measure the I/O performance we use STATSPACK.

create or replace procedure do_insert
as
    l_stop  date default sysdate+5/24/60;
    l_date  date default sysdate;
begin
    while (l_date < l_stop)
    loop
        insert into test (x)
        values (sysdate)
        returning x into l_date;
        commit;
    end loop;
end;
/

exec perfstat.statspack.snap;

declare
    l_job number;
begin
    for i in 1 .. 5
    loop
        dbms_job.submit( l_job, 'do_insert;' );
    end loop;
    commit;
end;
/

Check that 5 oracle processes are running using the utility top.

$ top

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14728 oracle    19   0 23152  21m  20m R 18.6  4.3   0:06.39 oracle
14722 oracle    16   0 23104  21m  20m R 18.2  4.3   0:06.51 oracle
14724 oracle    16   0 23060  21m  20m R 18.2  4.3   0:06.44 oracle
14720 oracle    18   0 26152  24m  23m R 17.6  5.0   0:06.58 oracle
14726 oracle    19   0 23088  21m  20m R 16.6  4.3   0:06.39 oracle

Wait until all jobs has been finished using the following statement. If you get no more rows as a result, the jobs has been completed.

select substr(job,1,4) "job",
       substr(schema_user,1,10) "user",
       substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
       substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
       substr(broken,1,2) "b",
       substr(failures,1,6) "failed",
       substr(what,1,32) "command"
  from dba_jobs;

job  user       last date        next date        b failed command
---- ---------- ---------------- ---------------- - ------ ----------
41   SCOTT                       17.02.2005 13:28 N        do_insert;
42   SCOTT                       17.02.2005 13:28 N        do_insert;
43   SCOTT                       17.02.2005 13:28 N        do_insert;
44   SCOTT                       17.02.2005 13:28 N        do_insert;
45   SCOTT                       17.02.2005 13:28 N        do_insert;

When finished, perform a STATSPACK snapshot:

exec perfstat.statspack.snap

Create the STATSPACK  Report:

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                   Executes:              1,082.17                  2.01
               Transactions:                537.17

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                               Waits    Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
buffer busy waits                                  20,307         404     28.33
log file parallel write                            40,086         281     19.73
CPU time                                                          275     19.31
job scheduler coordinator slave wait                   12         192     13.46
latch: In memory undo latch                         8,478         149     10.47

ASSM Manual with 5 Freelists

Now, do exactly the same test, but with 5 FREELISTS.

drop table test;
create table test (
  x date,
  y char(255) default 'x'
)
storage (freelists 5)
tablespace assm_manual;

exec perfstat.statspack.snap

declare
    l_job number;
begin
    for i in 1 .. 5
    loop
        dbms_job.submit( l_job, 'do_insert;' );
    end loop;
    commit;
end;
/

exec perfstat.statspack.snap

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                   Executes:              1,351.61                  2.02
               Transactions:                670.30


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                               Waits    Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time                                                          270     25.10
log file parallel write                            34,750         246     22.85
job scheduler coordinator slave wait                   12         192     17.82
buffer busy waits                                   3,248          83      7.72
latch: In memory undo latch                         3,415          68      6.28

As you can see, we increased the transactions-per-second rate from 537 to 670 and reduced the buffer busy wait events from 20,307 to 3,248. The total time waited went from 404 seconds to 270 seconds - fairly a big change.

ASSM Auto with 1 Freelist

Next, repeat the test with auto segment space management - first again with 1 FREELIST. Create a tablespace which is auto segment space managed:

create tablespace assm_auto
  datafile '/u01/oracle/db/AKI1/tab/AKI1_assm_auto.dbf'
  size 500M segment space management auto;

drop table test;
create table test (
  x date,
  y char(255) default 'x'
)
storage (freelists 1)
tablespace assm_auto;

create or replace procedure do_insert
as
    l_stop  date default sysdate+5/24/60;
    l_date  date default sysdate;
begin
    while (l_date < l_stop)
    loop
        insert into test (x)
        values (sysdate)
        returning x into l_date;
        commit;
    end loop;
end;
/

exec perfstat.statspack.snap;

declare
    l_job number;
begin
    for i in 1 .. 5
    loop
        dbms_job.submit( l_job, 'do_insert;' );
    end loop;
    commit;
end;
/

$ top

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14994 oracle    15   0 30580  29m  27m R 18.2  5.8   0:39.86 oracle
14996 oracle    15   0 33704  30m  24m R 17.9  6.0   0:40.67 oracle
14998 oracle    15   0 33480  29m  24m R 17.9  5.9   0:40.89 oracle
15000 oracle    15   0 33628  30m  24m R 17.9  6.0   0:40.78 oracle
15002 oracle    14   0 27104  25m  24m R 17.9  5.1   0:39.81 oracle

select substr(job,1,4) "job",
       substr(schema_user,1,10) "user",
       substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
       substr(to_char(next_date,'dd.mm.yyyy hh24:mi'),1,16) "next date",
       substr(broken,1,2) "b",
       substr(failures,1,6) "failed",
       substr(what,1,32) "command"
  from dba_jobs;

job  user       last date        next date        b failed command
---- ---------- ---------------- ---------------- - ------ ------------
61   SCOTT                       17.02.2005 14:11 N        do_insert;
62   SCOTT                       17.02.2005 14:11 N        do_insert;
63   SCOTT                       17.02.2005 14:11 N        do_insert;
64   SCOTT                       17.02.2005 14:11 N        do_insert;
65   SCOTT                       17.02.2005 14:11 N        do_insert;

Wait until jobs has been completed ....

exec perfstat.statspack.snap;

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                   Executes:              1,295.10                  2.00
               Transactions:                646.65


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                               Waits    Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time                                                          272     26.35
log file parallel write                            31,920         225     21.71
job scheduler coordinator slave wait                   12         192     18.57
buffer busy waits                                   5,669          94      9.10
LGWR wait for redo copy                            57,001          57      5.54

ASSM Auto with 5 Freelist

Finally, do the same test, but with 5 FREELISTS.

drop table test;
create table test (
  x date,
  y char(255) default 'x'
)
storage (freelists 5)
tablespace assm_auto;

exec perfstat.statspack.snap;

declare
    l_job number;
begin
    for i in 1 .. 5
    loop
        dbms_job.submit( l_job, 'do_insert;' );
    end loop;
    commit;
end;
/

Wait until Jobs has been completed ....

exec perfstat.statspack.snap;

sqlplus perfstat/perfstat
SQL> @?/rdbms/admin/spreport.sql

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                   Executes:              1,224.09                  2.00
               Transactions:                610.75

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                      % Total
Event                                               Waits    Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
CPU time                                                          270     26.06
log file parallel write                            31,806         215     20.75
job scheduler coordinator slave wait                   12         192     18.55
buffer busy waits                                   5,925         111     10.69
LGWR wait for redo copy                            64,372          64      6.23

As you can see, there is no difference between 1 and 5 FREELISTS, if we use automatic space management. However the result is not as good as with 5 FREELISTS and manual space management.

Conclusion

The Automatic Segment Space Management features is a well for most applications. However, using ASSM in some high-DML environments can result in poorer performance, and you will need to manually set the values for INITRANS and FREELISTS (as well as FREELIST GROUPS and PCTFREE and PCTUSED) as seen in this article.