关于freelist的一些实验
OverviewMost 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_stopdate default sysdate+5/24/60;
l_datedate 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 PRNIVIRTRESSHR S %CPU %MEM TIME+COMMAND
14728 oracle 19 0 2315221m20m R 18.64.3 0:06.39 oracle
14722 oracle 16 0 2310421m20m R 18.24.3 0:06.51 oracle
14724 oracle 16 0 2306021m20m R 18.24.3 0:06.44 oracle
14720 oracle 18 0 2615224m23m R 17.65.0 0:06.58 oracle
14726 oracle 19 0 2308821m20m R 16.64.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;
jobuser 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 STATSPACKReport:
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_stopdate default sysdate+5/24/60;
l_datedate 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 PRNIVIRTRESSHR S %CPU %MEM TIME+COMMAND
14994 oracle 15 0 3058029m27m R 18.25.8 0:39.86 oracle
14996 oracle 15 0 3370430m24m R 17.96.0 0:40.67 oracle
14998 oracle 15 0 3348029m24m R 17.95.9 0:40.89 oracle
15000 oracle 15 0 3362830m24m R 17.96.0 0:40.78 oracle
15002 oracle 14 0 2710425m24m R 17.95.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;
jobuser 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.
Tom Kate的这个实验是想证明freelist在LMT中作用不大，在DMT中作用明显，而且在freelist足够的情况下DMT甚至比LMT性能更好，但是则只是在insert插入数据时，我想这是因为DMT不用维护segment header的BMB吧的原因吧，但是在查询时LMT的性能就比DMT要好多了，因为LMT发生碎片的几率要小，扫描的块更少。 本帖最后由 kevin.zhang 于 2010-12-3 13:43 编辑
TABLESPACE-------->DMT --- ->MUNNUAL (FREELIST)
|
---->LMT -----> ASSM(BMB) tom的第二个测试
-----> MUNNUAL (FREE LIST) tom的第一个测试
zhang说的对，我没仔细看。重新看了试验后我觉得本地管理表空间（LMT）下BMB的管理模式下freelist没有多大意义，段手工管理且freelist足够时比BMB的insert性能要好。但查询性能呢？BMB解决了数据插入对段头的争用，但对查询性能应该没有什么影响。
页:
[1]