Just Excellent Reply
KP, July 12, 2001 - 10:26 am UTC
Yr reply is excellent
Now I know what a freelist is!
Mark J. Bobak, July 12, 2001 - 11:21 am UTC
Thanks Tom, for an excellent response, and the quick
turnaround time on my question.
Freelists explained --- Freed from headache
Aslam Momin, July 12, 2001 - 11:21 am UTC
Tom, this is an excellent -technical response.
You are simply Great !
Terrific
Rajesh Hassija, July 12, 2001 - 1:45 pm UTC
No better words can be said to explain free lists.
Excellent
Murthy P. N., July 12, 2001 - 4:05 pm UTC
Thats an excellent explanation.
Dot excellent .... but one question
Vidyesh, July 13, 2001 - 12:41 am UTC
That makes very clear. For total clear I have one question.
In parallel server:
If one of the instance needs more space than at present
available in the freelist group, what happnes???
Does a new space is acquired eventhough on total basis,
there is a free space available ?
OR
Oracle breaks the available freelist group(s) and makes
the space available ??
Thanks in advance
gr8
Vijay, July 13, 2001 - 6:01 am UTC
This is excellent reply. Cleared all the doubts reg. freelists.
Thanks Tom you are doing good job.
excellant explanation. no comment
A reader, July 13, 2001 - 1:27 pm UTC
Pretty Helpful
VS, December 18, 2001 - 4:33 pm UTC
How to set Freelists - Feedback requested
Pawan, February 07, 2002 - 4:18 pm UTC
Thanks Tom for the wonderful explanation. I understand it much better now. However I have a question. In a Data Warehouse environment ( running on N class HP box with HP UX 11 and 64 bit Oracle 8.1.7 with 20 GB SGA) I perceive that loading data is very slow ( relative ?). The DBA says he can increase the FREELISTS but then I want to know if there is an upper limit to what it should be set before there no further gain in performance. I think I read somewhere that we should optimally set it to 20 for heavy inserts. What is yr opinion and how does one go about calculating what the FREELIST (and may be INITRANS) should be set to. Assume that only 5 users are using the datawarehouse (1.% TB in size) and there is only user inserting data (500 million records) into a NON PARTIONED table.
Thanks
February 08, 2002 - 9:57 am UTC
you should set it to the number of load processes (so the advice to set to "20" is not correct UNLESS you just happen to be using that number of load processes).
Loading data in a data warehouse is typically done in direct path mode, meaning freelists are not even considered or used. Use the direct path loader and freelist contention is not an issue (and the load goes faster)
One user loading -- 500 million records -- freelists are NOT your issue.
" very very uderstandable, plain language.
oralearner, February 09, 2002 - 1:01 pm UTC
1. i need to know the name of your book you mentioned in your answer.
2. is the breaking up of freelist equal, what if one needs more space.
3. please explain a bit ---If your insert makes the block go over its pctfree, that block will be removed from the freelist -- else it'll stay on there---
please answer
February 09, 2002 - 2:39 pm UTC
1) </code>
http://www.amazon.com/exec/obidos/ASIN/1861004826 <code>
2) space, when freed, goes into a "pool", as each freelist runs out of room -- it'll grab a couple (like 5) blocks from this "pool" and put them on its freelist. So, the free space is allocated across the freelists as needed.
3) well -- it is pretty straightforward. If there is to be 10% "free" on the block for updates and your insert makes it go over that 10% free, that block is removed from the freelist -- its no longer a candidate for subsequent insertions, its removed from consideration. After a delete or an update (that shrinks a row), it may go back onto the freelist after getting under the pctused threshold.
Very nice
Abdal-Rahman Agha, February 10, 2002 - 10:40 am UTC
Hi Tom,
Its really what we need always, "to make these Oracle concepts simpler". I am really appreciate your nice help to explain what, I think, Oracle Documentations couldn't make it simple. Hay Tom, I will be back asking questions after this long period in which I didn't send you a question.
THANX
Abdal-Rahman Agha,
Developer & DBA
A reader, July 16, 2002 - 12:14 pm UTC
Can you show me how to check a table to see if I need to add freelist to it. Our application is mostly read except for one table CHANGELOG. It only gets inserts but does have two indexes. Rows inserted average about 500 - 1000 rows an hour. But can get hit real heavy during peak times.
Thanks
July 16, 2002 - 5:46 pm UTC
how about the indexes?
Prince, October 22, 2002 - 7:41 pm UTC
If I increase the number of freelists on a table, do I need to increase the same on ALL the indexes of that table?
Thanks,
October 22, 2002 - 9:15 pm UTC
pretty much, yes.
Bhagat Singh, October 22, 2002 - 11:35 pm UTC
Although I have fair Idea about the freelist from your book only, but the answer was excellent and I say I have learnt more out of it.
Dave, October 23, 2002 - 12:11 am UTC
In order to pack as many rows as possible into a data warehouse fact table, where there will be no future updates or deletes, do we need to be careful not to be too aggressive with pctused?
Suppose that due to the TS block size and table row length the table cannot be filled to more than 98.5%, for example. Presumably a pctused 99 would result in having many blocks on the freelist that cannot accept another row, and potentially degraded insert performance might be experienced (except with INSERT /*+ APPEND */ of course). Should we check that the number of blocks on the freelist(s) of such tables is not unexpectedly high?
October 23, 2002 - 7:01 am UTC
It won't be too much of an issue unless you update the data after inserting it. The block will be taken off and not put back on. Consider something simple like this:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( a char(2000), b char(2000), c char(2000) ) pctused 99 pctfree 0;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 for i in 1 .. 100
3 loop
4 insert into t values ( 'x', 'x', 'x' );
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count( DISTINCT dbms_rowid.rowid_block_number( rowid )) from t;
COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
100
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................4
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................24
Unused Bytes............................196608
Last Used Ext FileId....................7
Last Used Ext BlockId...................28041
Last Used Block.........................40
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> update t set a=b;
100 rows updated.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
Free Blocks.............................103
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................24
Unused Bytes............................196608
Last Used Ext FileId....................7
Last Used Ext BlockId...................28041
Last Used Block.........................40
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
See how the blocks after the insert are not on the freelist until AFTER the update?
Dave, October 23, 2002 - 8:34 am UTC
That's a great demonstration. I ran your script and got pretty much the same numbers, and noted that when another hundred rows are added it drops Free Blocks right down again, giving ...
SQL> exec show_space( 'T' )
Free Blocks.............................13
Total Blocks............................224
Total Bytes.............................1835008
Unused Blocks...........................22
Unused Bytes............................180224
Last Used Ext FileId....................6
Last Used Ext BlockId...................5769
Last Used Block.........................10
Here's my understanding of this, perhaps you could let me know if this is correct ...
i) If a block is on the freelist, but Oracle finds that there is not enough space to add another row, then it is taken off the freelist.
ii) During a subsequent update operation it is noted that the "actual" pctused is less than the table pctused of 99, and the blocks affected by the update go back on the freelist.
iii) So nothing to worry about, except if a table maintenance operation requires an update to all the rows (even if they do not get longer), and the blocks could be cleared off the freelist (if you were really fussy about this) by the insert and delete of a single row?
October 23, 2002 - 8:45 am UTC
yes -- and if you use ASSM (auto segment space mgmt) in 9i -- all bets are off cause the algorithms are all *totally* different for space management ;)
Thanks! One more topic in which I have much better and clearer understanding!
Robert, October 23, 2002 - 10:48 am UTC
Freelists
sush, January 12, 2003 - 11:17 am UTC
Tom...Thanks for the excellent answers.Once when I increased the freelists to 8 as I'm running 8 concurrent sessions to load the data into a table.A colleague of mine(who always suggests to work out of the box) tells look we are using LTS it will automatically take care of freelists and told me that I don't need to increase the freelists to 8.Please comment whether this is a true statement.
Thanks Sush
January 12, 2003 - 11:47 am UTC
what is an LTS?
do you mean "LMT" for locally managed tablespace? If so, that does not do anything for freelist contention.
if you mean "ASSM" for automatic segment space management -- then you are correct, it will remove the need to fine tune the freelists, but it is only available in 8i and up -- and you don't have a version listed and ASSM is not even close to LTS so I don't know.
freelists
sush, January 12, 2003 - 12:57 pm UTC
yes tom it is LMT..(locally managed tablespace)...
January 12, 2003 - 2:49 pm UTC
LMTs don't do freelists -- so freelists would still be something to look at in 8i. In 9i, you can consider ASSM.
Could you please explain the following in this connection:
A reader, January 14, 2003 - 10:44 am UTC
"we must ensure only one session at a time modifies it. Hence, in a heavy insert/update system -- the single freelist can become a point of contention -- one data
structure for ALL inserters. "
1.When two or more sessions are under contention how long is the contention? I mean a session estimates how many blocks to reserve for its insert and yield for the next session eager to modify the freelist for insertion? Hopefully it does not wait for the first session's insertion to complete.
2. Can Sessions for Deletes also cause contention. Or is it that such sessions do not make any modification to the free list because the freed blocks are returned to pool?
Thanks
January 14, 2003 - 10:51 am UTC
1) short, brief. not the life of the transaction -- just long enough to get the free block, modify it, and let someone else use it.
2) inserts/updates grab from the freelist. deletes just throw things back onto a master freelist. contention for deletes -- a little but since it is just putting ONTO the freelist, it is very very short
New Query raised by your answer !
Asim Ahmed., April 01, 2003 - 1:42 pm UTC
Hi Tom,
I'm an Oracle 8i DBA in KHI-Pakistan. I frequently visit this site just to increase my knowledge base. your recent answer refreshed many concepts regarding FreeLists. but raised a trivial one ! How does Oracle cope with the problem of managing multiple freelists because if multiple inserts are using multiple freelists, then any one of them can potentially damage the integrity of underlying data structures by allocating a block on FreeList to the request they are working for ? same question arises in case of FreeLists groups.
thanks in advance for this query. i hope u'll give a response.
April 01, 2003 - 7:25 pm UTC
we use latches to coordinate (eg: serialize) access to the freelists. That is the point of multiple freelists actually -- that way we can have many freelists being concurrently used. No more then one session is grabing (modifying) a given freelist at a time however. lightweight latches are used to protect these structures.
freelist groups spread it out even more is all. but not to worry -- we protect these shared datastructures -- just like we protect the structures in the SGA and all.
Can set freelist a higher number for all the tables?
A Reader, April 02, 2003 - 3:50 pm UTC
Hi Tom,
Can we simply set freelist for all the tables and their indexes in the database to a higher number, say 20 (Oracle default is 1). If so, what is the drawback of doing so? If not, why and how big the effect would be?
Thank you.
April 02, 2003 - 4:37 pm UTC
space utilization. There could be 95 free blocks (5 each on the other 19 freelists) that you would not reuse in your session -- and so you might cause the table to extend un-necessarily. so, it could use space less efficiently.
freelist
Vikas Sharma, April 06, 2003 - 5:55 pm UTC
Hi Tom,
Thanks for explaining. Pl clear me the following.
If I have a table in which the heavy data is getting inserted for about 100000 to 500000 but only buy one user/session. Then if I alter the table to 2 freelist then will I have performance gain?
What if the data is more and there are concurrent insert by two or more user/session then I should increase the freelist.
Regards,
Vikas Sharma
April 06, 2003 - 6:08 pm UTC
multiple freelists are good for concurrent inserts by separate sessions.
If you have but one session -- only one freelist will be used.
If you have more then one session all inserting, you would want more then one freelist to help spread the work around and descrease waits for blocks with room to insert into.
Thanks
Vikas Sharma, April 06, 2003 - 6:17 pm UTC
Hi Tom,
Thanks for immediate response.
Reagrds,
Vikas Sharma
More about this "pool" ?
Christo Kutrovsky, April 06, 2003 - 8:56 pm UTC
<quote>
All blocks belonging to a segment that have sufficient free
space on them (pctfree and pctused control when blocks come on and off of the freelist) are in this freelist.
</quote>
later on:
<quote>
2) space, when freed, goes into a "pool", as each freelist runs out of room --
it'll grab a couple (like 5) blocks from this "pool" and put them on its
freelist. So, the free space is allocated across the freelists as needed.
</quote>
Can you explain more about this speacial "pool". Why have this "pool" and freelists ?
And also, what happens if you delete a large amount of rows? Does this mean that there will be lots of "blocks" used for the freelists ?
Will this overwhelm the freelists ?
April 07, 2003 - 7:50 am UTC
the freelists contain blocks with sufficient space for new inserts on them. Whether a block is on the freelist is controlled by pctused and pctfree (or with auto-segment space mgmt in 9i, it is controlled by internal algorithms)...
In order to ensure maximum concurrency -- you may have more then one freelist. In this fashion, more then one session at a time may be pulling blocks off of the freelist of the segment at the same exact point in time. If you had but one freelist, the sessions would momentarily serialize at the freelist as this shared data structure cannot be modified by more then one session at a time (hence, by having more then one freelist, you can have more then one session pulling from a freelist concurrently).
Now, the question is how to manage blocks that are updated or deleted from and must go onto the freelist. If we simply put them on one of these freelists -- one freelist could easily end up with ALL of the free blocks -- and other sessions would not be able to use them.
So, we put them on a "master freelist" from which the other process or session freelists pull from. Each freelist pulls a couple blocks at a time from this master freelist -- spreading the free space around.
Think of it like the security checkpoint at the airport. You have 5 metal detectors. One master line feeds the 5 metal detectors and as you get closer to them, you are routed to one of the five lines. If one of the five lines is moving slowly (that would be the one I'm in if history keeps repeating itself), it consumes less blocks (people) from the master line. If one of the five lines is moving really fast (usually the line found next to the one I get put into), it consumes blocks faster. So, you end up with no single long line at a metal detector -- the wealth of blocks is spread out over all possible "freelists" (metal detectors)
Overwhelm is a strong term -- it will not overwhelm (that would imply "breaks"). It will however put all of those blocks onto the freelist, yes.
Aha !
Christo Kutrovsky, April 07, 2003 - 9:18 am UTC
Aha, I understand now.
The question was more oriented to the size of the master pool, but this clears it enought.
When you say, that it'l "grab a couple (like 5) blocks " I now figured out that you ment 5 blocks containing free rowids, not 5 free rowids.
I think my imagination of the exact way this is happening is close enought to the real one.
Thaks again Tom.
Default 1 Freelist per segment. How about partition?
Chuan Sat, July 08, 2003 - 12:53 am UTC
Tom
You mentioned that the default is one freelist per segment. For a table with partition and subpartition that are being inserted through conventional path, are all the pararell partition insert processes share one segment freelists? Or is there one free list per partition and subpartition?
Thanks
July 08, 2003 - 7:33 am UTC
in the case of partition tables -- each partition is the segment, each will have its own freelists.
free lists ???
Reader, July 09, 2003 - 5:56 am UTC
Hi Tom.
How can i add more free lists to my table. Can u tell me the syntax ? If, table is currently using.
July 09, 2003 - 11:05 am UTC
ops$tkyte@ORA920> alter table t storage ( freelists 5 );
Table altered.
Syntax
Raj, September 04, 2003 - 6:14 pm UTC
Hi Tom,
What is the syntax to add freelist for a current read/write only partition in a partitioned table?
Alter table <table name> modifty partition <partition name> storage ( freelists <n> );
Something like this?
September 05, 2003 - 3:30 pm UTC
ops$tkyte@ORA817DEV> alter table t modify partition part1 storage (freelists 20 );
Table altered.
(could have been figured out just the way i did it -- by "trying it" or by reading it........)
Thanks, Tom. This was the best Article I've seen on FreeLists.
A reader, September 24, 2003 - 4:29 pm UTC
Can freelists span multiple blocks
Arun Gupta, November 25, 2003 - 10:10 am UTC
I have some questions about freelists:
a) When documentation says that the block goes on freelist, which attribute of block is recorded in the freelist? Is it the database block address? How much space (bits/bytes) does this occupy?
b) Are all the freelists, master, process and transaction contained within one block which is the segment header?
Thanks
November 25, 2003 - 12:31 pm UTC
a) the dba (data block address). it is a file/block combo. just a couple of bytes.
b) they can span blocks as the size of the number of "free" blocks is somewhat unlimited (bigger then would fit on a block)
Arun Gupta, November 25, 2003 - 3:00 pm UTC
Tom,
Please see this posting on Metalink from Jonathan Lewis which prompted my question:
<quote>
TX locks in mode 4 can appear because freelists
is too high. The segment header block has space
for a fixed number of freelist structures which
are split between SEG and XCT lists. The SEG
lists get called the Process Free Lists (and
the number of these is set by the FREELIST parameter)
plus one Master Free list. The XCT free lists are
the remainder and get called transaction free lists.
When a transaction makes enough free space in a
block that the block has to go 'on the free list',
it has to go on a transaction free list until the
transaction commits. So if lots of transactions
free up space in blocks concurrently without committing
you need lots of transaction free lists - and if you
run out of transaction freelists, then the next new
transaction to free some space has to wait for one
of the current transactions to commit/rollback and
release its transaction free list. The waiting
transaction picks a transaction freelist, and waits
on the holder using a mode 4 TX lock.
However, since there are (I think) about 80 structures
in a 4K block (and I guess at least twice that in an
8K block), you have to set freelists very high, or
have a lot of concurrent deletes going on to see this
happen. (It can be engineered quite easily, of course).
</quote>
This implies that there is only one block available for freelists, so if I increase the process freelists, transaction freelists can potentially run out of space. The Metalink article 1029850.6 is also very informative, but I still cannot understand that how they say that freelists occupy only one block? Please bridge this gap in my understanding. Has something changed between 8i and 9i (except for ASSM)?
Thanks
November 25, 2003 - 3:43 pm UTC
no, it implies there is a root block that points to other blocks and this root block can in excessively extreme cases be a point of contention.
it does not imply "there is only one block for freelists"
think of it like a root index block, the one we all must start at in order to access index data.
A reader, February 11, 2004 - 6:51 am UTC
inserts blocking each other?
A reader, October 11, 2004 - 7:29 am UTC
In oracle, from what I understand there are two times
when you get "blocked" on an insert.
1. In one session you have inserted a record with primary
key values
2. In another session you insert another record with same
PK values. This session will block till first session
commits or rolls back. It will succeed or fail depending
on the commit or rollback.
The second case is when the session is waiting for free lists
to be freed.
Is there any other reason, why a table with heavy inserts
could be facing a situation where it "blocks"? We are
not talking about lots of inserts from one or two sessions.
we are talking about lots of inserts due to millions of
users logging on a system - the logon process actually inserting one or two records into a table.
Thank you.
October 11, 2004 - 8:26 am UTC
insufficient ITL entries can cause artificial blocking.
you would use a higher initrans for the segments (table and index) or use ASSM - automatic segment space management.
Your second case would manifest itself as buffer busy waits -- multiple freelists and freelist groups OR assm would be used to alleviate that.
thanx!
A reader, October 11, 2004 - 8:37 am UTC
No ITL waits on (non-IOT) table
Mark J. Bobak, October 11, 2004 - 9:04 am UTC
Actually, on a heap table, Oracle will NOT wait on ITL
slot for inserts. It will just grab the next block on the
freelist.
-Mark
October 11, 2004 - 9:43 am UTC
i was thinking "indexes" sorry -- should have been more clear:
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> create index t_idx on t(x) maxtrans 2;
Index created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values ( 2 );
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> create index t_idx on t(x) ;
Index created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values ( 2 );
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
thanx Tom and Mark
A reader, October 11, 2004 - 2:09 pm UTC
btw, the scenario where you insert some user context
info in a table during logging - is it a commonly used
solution? If so, is the above problem then seen by people?
I was thinking of application contexts as an
alternative.
October 11, 2004 - 4:45 pm UTC
not sure what application contexts have to do with space mgmt and itl's?
if what you need is available via sys_context('userenv', ....) get it there, yes.
Free list contention
RD, October 28, 2004 - 12:45 am UTC
Hi Tom,
Great thread this !!!
Oracle docs. show us how to find out segments which are candidates for more free lists. I am following the required steps here but am puzzled by the fact that though my
v$waitstat and v$system_event show plenty of waits for 'data blocks' and 'buffer busy waits' respectively but when I query the v$session_wait view I am getting no event called 'buffer busy waits'.
Now I am really confused here. How is this possible??
I am using oracle 8i and am querying as system.
vanp > select * from v$waitstat where class = 'data block';
CLASS COUNT TIME
------------------ ---------- ----------
data block 99525 32251
vanp > select event, total_waits from v$system_event
2 where event = 'buffer busy waits';
EVENT TOTAL_WAITS
----------------------------- -----------
buffer busy waits 99537
vanp > select distinct event from v$session_wait ;
EVENT
-----------------------------------------------------
SQL*Net message from client
SQL*Net message to client
pmon timer
rdbms ipc message
smon timer
See no 'buffer busy waits'.
Pleaseeeeeeee help.
Thanks lots as always,
Regards,
RD.
October 28, 2004 - 7:40 am UTC
buffer busy waits normally happen really fast, you would have to hit "/" alot normally.
But -- cardinality is just a teeny tiny bit of the math you need here. TIME SPENT waiting and the time span being measured is relevant.
Some numbers
100,000 waits for 30 seconds.
good, bad or indifferent? no matter what you pick -- i'll add a 3rd number "time span it took to get 100,000 waits for 30 seconds" and argue the other two are correct.
It could be that your buffer busy waits happened last week and aren't happening now. The waits I see listed are idle waits mostly.
Still not got it....
RD, October 28, 2004 - 4:46 pm UTC
Hi Tom,
I frankly haven't got it just as yet. The database has been up for 4 days. Used for about 10 hrs a day. That's 4 * 10 * 60 * 60 secs = 144000 seconds. Out of this I spent 32251 cs's - 322.5 seconds waiting for 'buffer busy waits'.
How did you get 30 seconds ? Think I'm missing something basic here trying to learn about performance tuning.
Does it also mean that v$session_wait has only wait events that the system is waiting on at that particular moment when you fire the select * from v$session_wait query???
and not session_waits since system startup.
Then how does a DBA get information of session waits since system startup?
Also how does he then get to identify which seg's are candidates for more freelists??
Thanks again,
RD.
October 28, 2004 - 7:48 pm UTC
I made up both 100,000 and 30.
how did you tie the 322.5 to the bbw's?
but the fact is, when you went to look -- there were no bbw's in the system, system was idle basically.
I like tuning with tkprof -- statspack is so aggregate. these views you are looking at are totally aggregated (for days and days)
maybe you had a problem 9 days ago and not since.
i tune applications, not "systems"
Woods are lonely, dark and deep, I have.........................
ARU, October 28, 2004 - 8:11 pm UTC
Thanks,
Yes! that's what I,ve realised in my short career so far that almost all the time it's the application that needs the tuning not the "system". But that's another story. Getting to pinpoint what exactly it is in the application that requires attention has got to be identified and acted upon and that's the tough part.
Tom, you've been there, done that (doing it in fact), suppose you had to start all over again, what and where would you start the long journey to get there? Please advice on the first few steps.
Thanks,
Regards,
ARU.
October 28, 2004 - 9:55 pm UTC
the advice --
"nothing is off the table"
never
not ever
(i hate it when people say, yah but we cannot do that. of course you can)
but in your case.... you have these numbers. do you have a performance issue? if so, with what? what is the *most important thing* in this system. find it, trace it, look at it. work on that.
otherwise we are back to "42".
(very vague, i know..)
master free list
John, March 04, 2005 - 9:50 am UTC
from above "So, we put them on a "master freelist" from which the other process or session freelists pull from. Each freelist pulls a couple blocks at a time from this
master freelist -- spreading the free space around."
(1) Does it mean that when oracle allocates an extent, the blocks go into master free list?
(2) As there is only one master freelist, is it a single point of contention for concurrent processes puuling blocks from this list?
Thanks.
March 04, 2005 - 10:17 am UTC
1) no, blocks do not go onto the freelist until we advance the high water mark of the table to include them.
So, when you allocate an extent -- initially all of the blocks in the extent are above the high water mark (HWM).
As we need space, we advance the HWM a little at a time. This is when the blocks first get onto the freelist. The rest of the blocks above the HWM are not on the freelist.
2) enter.... freelist groups.
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12209165088566 <code>
excellent as like always!
aman, March 07, 2005 - 8:45 am UTC
sir
please give me the link where this information about the Freeslit and Freelist Groups(Transaction free list and process freelist etc) and ASSM is there in documentation.
Just want to say that this is the best description for the freelist that i have read ever in all this time in my learning of oracle.once again nothing is that can match you.YOU ARE THE BEST!!
with warm regards
aman
March 07, 2005 - 3:01 pm UTC
See MetaLink doc id 157250.1, Freelist Management
by Stephan Haisley
problem:-(
aman, March 08, 2005 - 12:07 am UTC
sir i am sorry i dont have the access to metalink.So only you can help.so sorry to disturb you over such a small issue .So only one that can give a clear,precise explanation is you sir.
in wait
aman
March 08, 2005 - 8:02 am UTC
it is not a small issue - that is a couple page paper (and he gives a clear, precise explaination too -- I hold no monopoly on that ability at all)
this deeper internal stuff isn't always documented in the manuals (not generally relevant, beyond what most of us need to know).
It would take too much to go into here in a followup. If you have effective Oracle by design, I cover them conceptually (using a water cooler metaphor).
Other sources.....
Mark J. Bobak, April 23, 2005 - 5:52 pm UTC
That paper is avialable outside of MetaLink, as well.
(Hint: Try a google search.)
Some clarification needed
A reader, November 10, 2006 - 9:30 pm UTC
In your first response above, "If your insert makes the block go over its pctfree, that block will be removed from the freelist -- else it'll stay on there."
I am not quire clear about this point. Just because one "big row insert" cannot be accomdated in the block, Oracle will not remove that blocks from the freelist. Is this what you are saying above ?
1. How does Oracle know whether a block available in the freelist will fit the entire row ? In other words, Does Oracle know before hand whether this block will fit the row we are inserting.
Lets say, the block in freelist does not fit "my row insert", either due to exceeding PCTFREE or "row migration", at this point, Will Oracle remove that attempted block from the freelist Or leave as is for fitting "future row insert.. ? I need clarification here.
2. When Oracle cannot find enough freespace in an attempted block (on the freelist), my understanding is, Oracle will keep searching for next fitting block in the freelist for the "previous attempted case" until it finds one. If no blocks in the freelist can be used, then uses new blocks above HWM. Did i get it right ?
November 11, 2006 - 8:13 am UTC
I did not say
...one "big row insert" cannot
be accomdated in the block ...
I said
"if your insert makes the block go overs its pctfree"
big difference.
1) the block is on the freelist, as you walk the freelist, you are walking the blocks - the block is there for inspection. It is self evident whether there exists space or not.
The row would have to fit before that block was even considered - it would be "skipped" otherwise.
2) correct, if we cannot find something on the process freelist, we'll bump the High Water Mark (HWM) by typically 5 blocks and use them.
Thanks for the clarification
A reader, November 11, 2006 - 11:08 am UTC
<< The row would have to fit before that block was even considered - it would be "skipped" otherwise.
Does oracle always store the "available freespace in a block" in the header upfront ? I would assume so because otherwise oracle has to perform space calculations on the blocks in the freelist at the runtime of the related DML statements (rows). I guess the only calculation Oracle may have to do, "calculate the requested row size", right ?
Thank you.
November 11, 2006 - 3:25 pm UTC
we'd have to dump a block and see what is there - and that, that is just "too much information" for me. I just know they find the right stuff.
Thanks
A reader, November 11, 2006 - 3:38 pm UTC
Freelist location
Anand, March 06, 2007 - 2:19 pm UTC
Hi Tom,
I was going through this thread, I understand Freelist manages free blocks for the whole segment.
Can you tell me where is it located in the segment?
Thanks
Anand
March 06, 2007 - 4:09 pm UTC
they start in the first block, however - they can grow in size and allocate more storage within the segment and are managed like a linked list.
Reducing "buffer busy waits" for tables that are already on ASSM
Logan Palanisamy, September 17, 2008 - 10:19 pm UTC
Tom,
For segments that are already on ASSM tablespace, how to go about:
1. Finding the reasons for "buffer busy waits"
2. Reducing these buffer busy waits.
The index IDX_ME25_MDST_CREATED was rebuilt only yesterday. In twenty four hours, it has close to 1million buffer busy waits.
SQL> select owner, object_name, subobject_name, object_type, value, tablespace_name,
2 (select segment_space_management from dba_tablespaces where tablespace_name = s.tablespace_name) ssm
3 from v$segment_statistics s
4 where owner = 'CATALOG'
5 and statistic_name = 'buffer busy waits'
6 and value > 10000
7 order by value desc;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE VALUE TABLESPACE_NAME SSM
---------- ------------------------------ ------------------------------ ------------------ ---------------- ------------------------------ ------
CATALOG SD_ATTR_NORM_DIRTY SYS_P9374 TABLE PARTITION 3,268,298 SD_ATTR_DIRTY_DATA AUTO
CATALOG IDX_ME25_MDST_CREATED INDEX 813,303 DIRTY_DATA_ASSM AUTO
CATALOG SD_ATTR_NORM_DIRTY SYS_P9375 TABLE PARTITION 106,353 SD_ATTR_DIRTY_DATA AUTO
CATALOG SD_MATCHING_SKUREUSE TABLE 100,894 SOURCEDATA_DATA MANUAL
CATALOG PK_SD_ATTR_NORM_DIRTY INDEX 70,652 SD_ATTR_DIRTY_INDX AUTO
CATALOG IDX_SD_ATTR_PID_SKU_K_M SYS_P9705 INDEX PARTITION 65,447 SD_ATTR_INDX AUTO
September 18, 2008 - 7:47 am UTC
why did you rebuild the index....
if this is a heavily modified index (eg: table is heavily inserted into) and if it is on a date column populated by sysdate (appears do - "mdst created" sounds like a created date) - then all of the modifications are going into the very hot right hand side (eg: everyone needs the right most block in current mode - at the same time - but only one transaction can have that block in current mode to modify it at any time of course)
And that would be the cause of the buffer busy waits. The buffer is in fact busy. ASSM or MSSM would show the same behavior - this is not a freelist/freelist group issue - this is "everyone wants the same block"
If you have identified this as a true issue (that 3.2 million number is relatively meaningless by itself - was that over 1 minute, 1 day, 1 week, 1 month, 1 year...), you can look at
a) reverse key index
b) hash partition index
in order to spread the load out - beware, both of those would preclude a simple range scan on the date column, eg: where dt_column > sysdate-1/24 - that predicate would not be able to range scan on a reverse key or hash partitioned index.
Buffer Busy Waits on a heavily inserted index on a date colum with default sysdate
Logan Palanisamy, September 18, 2008 - 1:20 pm UTC
Tom,
You are right. This index is on a date column populated with SYSDATE. At times, we insert as many as 1500 rows/second.
I rebuilt it as part of moving to an ASSM tablespace. I will check if reverse key index works.
Sorry about the messed up column alignment in my post above. While posting itself I knew it, and tried the "Formatting" options. But it gives only Bold, Italics, Underline or Code options. Had there been a "Font Size" option, I would have posted it with a smaller font size to make it all aligned and easy to read.
I generated the output in SQL*Plus with "linesize 120 trimspool on". I am not sure how people on this forum fit in wider outputs with column aligment intact.
September 18, 2008 - 9:31 pm UTC
the code button is all you need.
beware that the reverse key index might turn your buffer busy wait problem into a physical IO problem....
The reverse key index will have the sysdate values going all over the place in the index, reducing contention - but necessitating that most all of the index need be cached to be efficient. Right now, only the hot right hand side needs to be cached.
Global hash partitioned index might be a better idea - there you will have N "hot right hand sides" where N = number of hash partitions.
Buffer busy waits even after creating Hash partitioned global index
Logan Palanisamy, October 01, 2008 - 2:07 pm UTC
Tom,
I still see a lot of buffer busy and row lock waits after creating a global hash partitioned index.
1.The stats below is sixteen hours after creating the index.
2. The table is not partitioned.
3. Number of inserts/deletes varies anywhere from 0 to 5000/second.
Does it hurt to increase the number of partitions?
SQL> CREATE INDEX CATALOG.IDX_ME25_MDST_CREATED ON CATALOG.MAPTASTIC_DIRTY_ST
2 (CREATEDON)
3 TABLESPACE DIRTY_DATA_ASSM
4 PCTFREE 10
5 INITRANS 32
6 GLOBAL PARTITION BY HASH (CREATEDON)
7 PARTITIONS 64;
Index created.
... stats after 16 hours ....
SQL> l
1 select statistic_name, sum(value) total, round(avg(value)) average , min(value) min, max(value) max, count(*) partitions
2 from v$segment_statistics
3 where owner = 'CATALOG'
4 and object_Name = 'IDX_ME25_MDST_CREATED'
5 AND STATISTIC_NAME LIKE '%wait%'
6* group by statistic_name
SQL> /
STATISTIC_NAME TOTAL AVERAGE MIN MAX PARTITIONS
-------------------- ------------ ------------ ------------ ------------ ------------
row lock waits 217,795 3,403 1,742 4,824 64
buffer busy waits 250,294 3,911 2,347 5,577 64
ITL waits 1,661 26 12 38 64
October 01, 2008 - 2:22 pm UTC
those numbers are cumulative since the instance is brought up.
They are meaningless in this context - those numbers could be tiny for all I know.
what were the waits for some small period of time in the past (before you did this) and what are the waits for some small period of time now - that is, did it get better.
you cannot remove all waits, there will be some contention - you have a lot of concurrent activity.
You haven't shown us these are significant numbers.
hi tom
venkat, February 28, 2011 - 7:46 pm UTC
thanks for the answers...
i didnt get the concept of master freelist can you pls give me a simple explanation...with simple example...
March 01, 2011 - 8:56 am UTC
see
"Followup April 7, 2003 - 7am Central time zone"
on this page.
Freelists
A reader, June 02, 2011 - 9:09 pm UTC
Tom:
We moved a table segment from manual segment managed tablespace to auto segment managed table. The inserts now are faster. How ever we are facing a unique problem, When ever a massive/long running delete operation runs against this table, the inserts come to a halt waiting on a CPU. As soon as we kill the session which is running delete, the inserts pick up and continue at a faster rate. How do we approach, as there are no blocking locks between insert and delete.
Thanks for your help.
Regards,
June 03, 2011 - 12:28 pm UTC
when you run the massive delete - the inserts will take longer and need more CPU due the fact that the indexes they are maintaining are being modified by the delete as well - they have to roll back (undo) the operations the delete did to get consistent reads.
Is there a way to use partitioning and partition operations to do the delete in a single DDL rather than a rather laborious delete to purge this information? Or to do the delete more often (so it is smaller). Or to do multiple deletes one after the other - committing in between - to make the batch operation more compatible with the oltp operations?
Thank you
Armin, September 18, 2011 - 1:35 pm UTC
Thank you very much. I had problem to understand it.
wonderful!!!
Sudha, October 08, 2013 - 5:04 pm UTC
Your explanations are simple and understandable. Appreciate your work.
Thanks for a very important article
Arvind Singh, August 27, 2015 - 5:53 am UTC
This is very powerful message , as we know one of the classic field problem of Oracle database is "How to speed up data loads " .
The article is very detailed and has very practical use case.
Thanks ,
Arvind Singh