Further question
Rob Balter, March 22, 2002 - 11:24 am UTC
If I have a table with 100 allocated extents and I do
a truncate table reuse storage, will the direct path insert
reuse the 100 extents for data or create an additonal
100 extents.
Does a partitioned table have freelists per partition or
per table.
March 22, 2002 - 12:21 pm UTC
direct path INSERTS insert above the HWM, into existing allocated but not used space. So, yes, it'll reuse them if they are "empty" and above the HWM.
each partition will have its own list since a block can only belong to one partition (so they must be separated out)
Irfan, March 25, 2002 - 2:18 am UTC
Hi Tom,
a) I would like to know how the one block is created in free list as normally after deletion it goes into free list.
SQL> create table t ( x int, y char(2000) default '*' )
2 storage ( initial 40k next 40k minextents 5 )
3 tablespace system;
Table created.
SQL> insert into t (x) values ( 1 );
1 row created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> compute sum of blocks on report
SQL> break on report
SQL> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 and segment_type = 'TABLE';
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 61440 15
3 102400 25
4 143360 35
0 40960 10
1 40960 10
----------
sum 95
SQL> clear breaks
breaks cleared
SQL> select blocks, empty_blocks, avg_space, num_freelist_blocks
2 from user_tables
3 where table_name = 'T';
BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
1 93 1971 1
b) In one of your followup you have mentioned the first block of each table is used by the system. So it means
totally 96 blocks are allocated to this table?
c) Your solution
(an insert of a new row that finds the freelists empty will
get some blocks from beyond the HWM and put them on the freelist if the new row does not exceed the pctfree)
Can you please explain - put them on the freelist if the new row does not exceed the pctfree -
Thanks.
March 25, 2002 - 8:02 am UTC
a) blocks are on the freelist UNTIL they exceed their pctfree. Assuming an 8k blocksize and 10 pctfree, you would have to insert about 3 rows before that block would be taken OFF of the freelist in the first place (all blocks originate on the freelist for normal inserts like that).
It stays on the freelist till pctfree is exceeded, then comes back on after it hits pctused. If you have my book -- I go over this in some detail in the chapter on tables.
b) search this site for show_space. It will show you exactly what is allocated, used, on the freelist, etc. You have 95 blocks. One of them is in use by the system. One of them is in use by you.
Irfan, March 25, 2002 - 1:25 pm UTC
March 25, 2002 - 2:51 pm UTC
One is used by you AND it is the same one that is on the freelist. The block with your single row is the same block as is on the freelist.
You are double counting your block.
Empty Freelist
Vikas Sharma, July 08, 2002 - 6:35 am UTC
I read in your book and in this thread also, that the block which are above the high water marks are included in to freelist when freelist are empty. Can you please tell when freelist get empty.
July 08, 2002 - 7:44 am UTC
when you use them and they exceed their pctfree and get taken off of the freelist. So, when a table is "full" and the very next insert would cause it to allocate another extent.
Freelist
Vikas Sharma, July 09, 2002 - 1:28 pm UTC
Thanks Tom it clears my doubt.
Thanks
More clarifications
Sanjaya Balasuriya, April 04, 2004 - 1:53 am UTC
Hi Tom,
Say we set free lists to 20 for a table.
Then 20 free block are kept reserved for the table ?
And what is the impact of free list groups on a table ?
Since to set the value for free list we should know the concurrent access (updates or inserts), how can I know the number of concurrent access ? Is there any views to get this information ?
Thanks in advance.
April 04, 2004 - 8:40 am UTC
no, 20 free blocks are not kept, it means you have 20 pointers to 20 lists.
do you have my book Effective Oracle by Design? see:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12209165088566 <code>
for a short extract on freelist groups.
You (as the designer of the application) needed to have that sort of information during design time (in order to properly build the application). If you did not, you just have to "guess" -- no, there are no v$ views that'll tell you the maximum number of concurrent transactions -- but you as the designer should have a pretty good gut feel.
Truncate Table with Reuse Storage
Senthil, June 03, 2004 - 1:34 am UTC
sir,
What is difference between Truncate Table without Reuse Storage and Truncate Table with Reuse Storage clause.
thanks
June 03, 2004 - 8:07 am UTC
pointers to freelists
Sam, March 04, 2005 - 9:59 am UTC
<quote>it means you have 20 pointers to 20 lists.<quote>
(1) say I have 20 freelists and if 20 processes simultaneously inserting rows, I was wondering how does 20 processes access 20 freelist that could all be in the same block. Does oracle use the concept of ITLs as in the data block so that multiple processes can access the same block?
(2)Conceptually, can I relate 20 freelists as 20 rows in a block so that multiple processes can access its own freelist just like multiple processes can update different rows at the same time in a block as long as space is there to for all ITLs? Thanks.
You are awesome!
March 04, 2005 - 10:58 am UTC
1) a block will be on a single freelist. not sure what you meant.
2) 20 freelists is just that -- 20 separate and mutually exclusive list of blocks.
pointers to freelist
Sam, March 04, 2005 - 11:24 am UTC
I understand. However, all of the information related to say, 10 freelists are stored in one header block. right? Considering this, now multiple processes access the same block to get a freelist. How does oracle do? I was wondering is it similar to multiple processes accessing a diffent row in the same block for updates for example using ITLs? Hope my question makes some sense. I don't want to waste your time. Thanks.
March 04, 2005 - 11:33 am UTC
see Note 157250.1 on metalink, very good discussion, has pictures to boot