关于freelist的一些实验

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.
标签: 暂无标签
oraunix

写了 199 篇文章,拥有财富 1026,被 339 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

P4 | 发表于 2010-12-3 17:33:18
zhang说的对,我没仔细看。重新看了试验后我觉得本地管理表空间(LMT)下BMB的管理模式下freelist没有多大意义,段手工管理且freelist足够时比BMB的insert性能要好。但查询性能呢?BMB解决了数据插入对段头的争用,但对查询性能应该没有什么影响。
回复

使用道具

P4 | 发表于 2010-12-3 13:36:33
本帖最后由 kevin.zhang 于 2010-12-3 13:43 编辑


     TABLESPACE-------->DMT   --- ->MUNNUAL (FREELIST)
                              |
                              ---->LMT   -----> ASSM  (BMB)                  tom的第二个测试     
                                               -----> MUNNUAL (FREE LIST)           tom的第一个测试

回复

使用道具

P4 | 发表于 2010-12-3 13:10:49
Tom Kate的这个实验是想证明freelist在LMT中作用不大,在DMT中作用明显,而且在freelist足够的情况下DMT甚至比LMT性能更好,但是则只是在insert插入数据时,我想这是因为DMT不用维护segment header的BMB吧的原因吧,但是在查询时LMT的性能就比DMT要好多了,因为LMT发生碎片的几率要小,扫描的块更少。
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈