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 oracleWait 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.sqlLoad 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.sqlLoad 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.28As 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 oracleselect 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.sqlLoad 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.sqlLoad 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.23As 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.