Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Tony.

Asked: January 09, 2002 - 6:48 pm UTC

Last updated: January 31, 2013 - 2:22 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,
Lot has been said on this site and books about pctfree and pctused for creating tables. What about pctfree for indexes? I'm working on Data Warehouse application (no updates), it is easy to decide about pctfree and pctused for data but I'm not sure, how should I decide about indexes?, and why oracle gives default pctfree as 10 for indexes?
Your help will be highly appreciated.
Tony

and Tom said...

pctfree only counts when BUILDING an index. It reserves X% of the block for subsequent inserts into that block (inserts into the block can arise from inserts or UPDATES to the base table).

If you load and index and NEVER insert/update the table -- a pctfree of 0 would be just fine.

All the pctfree does on an index is defer the splitting of an index block due to subsequent inserts/updates AFTER the index is build. 10% is just a good "starting point".

Rating

  (39 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PCTFREE for Indexes

Tony, January 11, 2002 - 5:09 am UTC

Hi Tom,
Thanks for response.
But just to be more specific:-
24 million rows (one million per month) in a monthly partitioned table, having global and local indexes. Some are normal (B*Tree) and some are bitmap indexes. About 40,000 rows are added (INSERTS) daily to the table, so I can't keep PCTFREE 0. Okay, 10% is just the good starting point. But what is the algorithm to calculate the best value, specifically when I know the distribution of data in table-partitions-columns.
Tony

PCTFREE for indexes

Tony, January 11, 2002 - 5:24 pm UTC

Hi Tom,
In addition to previous comments, while reading 'Oracle 8i Application Developer's Guide', I found that:
--------------------
PCTFREE for Indexes
Indexes infrequently require the use of free space for updates to index data. Therefore, the PCTFREE value for index segment data blocks is normally very low (for example, 5 or less).
--------------------
It says 5 or less but ORACLE keeps default as 10!!!
Regards,
Tony


Tom Kyte
January 11, 2002 - 7:56 pm UTC

yes? point is? you don't like the default -- you set it.

10%, 5%, not a significant difference in the grand scheme of things. I see no conflict here between reality and the documentation. 10% is a fairly good rule of thumb. You set it when you need to or want to.

why there is no pctused for index?

A reader, May 03, 2002 - 2:56 pm UTC

Hi Tom

Why we cannot set pctused for index?

Tom Kyte
May 03, 2002 - 4:22 pm UTC

because it does not make sense (if you have my book, i go into detail on this)..

basically, a table is a "heap". We stick data WHERE EVER.

an index is a structure, we have to stick data where it belongs.

When we take an index block off of the free list -- it is "empty". We will then totally fill it up until we have to split it. It only goes back onto the free list when it is empty again. We cannot put a block on the freelist unless it is empty -- the fact that the INDEX IS A STRUCTURE, prevents us from sticking just any data on the block. Only data that can go onto that block can go there. It is just "not a heap".

Some more details required

Vinay Khandka, July 18, 2002 - 4:56 pm UTC

More more details with examples would have been helpful.

Tom Kyte
July 18, 2002 - 5:16 pm UTC

get my book then -- huge number of examples on over 1200 pages covering tons of stuff.

Harold Flores, July 18, 2002 - 8:26 pm UTC

Hello

Some oracle instructor recently told me that an index copy the structure and the data of the columns from the table where the index is use (???), that's why an index will defrag and use some space from the datafiles. i dont belive that is true, will you explain it to me.

Thanks in advanced.

Harold

Tom Kyte
July 19, 2002 - 7:12 am UTC

I don't understand your statement above at all. you'll need to be more clear.

Harold Flores, July 20, 2002 - 12:11 pm UTC

It goes like this

1. I create an index on column 1 and 2 over the table t1 (wich has 3 more columns)
2. The index will copy the structure and the data of the columns where the index whas created.

I do belive that the index will copy the estructure of the columns but not the data. i hope i make myselgf clear.

Thanks.



Tom Kyte
July 20, 2002 - 12:28 pm UTC

Nope, still not understanding your point.

The index will copy the values of column1 and column2 and the rowid that points to columns 1..5, sort them and store them.

I don't know what you mean by the "structure of the columns but not the data"

Harold Flores, July 20, 2002 - 1:32 pm UTC

When i create an index will actualy copy all the values of the columns of the table????

Tom Kyte
July 20, 2002 - 2:36 pm UTC

It will obviously copy the columns you are INDEXING -- but just those. so, if you have 5 columns and create an index on (c1,c2), c1 and c2 will be physically copied into the index structure (how else could it work?)

I don't understand why an index block must be 100% empty before returned to free list.

ken, August 13, 2002 - 9:06 am UTC

Tom,

You said:

. . . We will then totally fill it up until we have to split it. It only goes back onto the free list when it is empty again. . . .

I've read that second sentence in many places, but have never totally unsdrstood the reason why it is true. It seems like if we have an index on the ename column of the emp table, and one of the blocks points to 100 or so rows (filling this index-leaf block)for people with a name in the range of "SAWYER" to "SWAZE", and then we delete about 75 of those rows from the table we would have plenty of room to insert a value for the new employee "SMITH." Why would we not want to use this block for that entry? Isn't that the "right" place for the data to go? Where will it go, then? A brand new block, or one that has not reached PCTFREE?

I have re-read the B*Tree Index portion of your book and if this is addressed I missed it.

Thanks!

Tom Kyte
August 13, 2002 - 5:21 pm UTC

The free list for indexes is DIFFERENT from the free list for tables.

Consider a HEAP table. You want to insert a row into it. That row can go onto virtually ANY block. So, all blocks that have free space on them (sufficient free space) are pointed to by a free list. We goto the free list and find a block and put the row there (maybe taking the block off of the free list if we hit pctfree).


Now, consider an index. You want to "insert a row into it". That row MUST GO IN A VERY VERY SPECIFIC LOCATION (an index is a data structure). Hence, we find that very very specific location in the data structure and one of two things will be true:

a) the index block has room on it. We use that room (pctfree is meaningless here, we fill the block till it is totally full)

b) the index block has NO room on it, it is full. We must split the block into two to make room. Hence we need a totally empty block (cannot have ANY data on it as the data in an index has a specific place it must be!). We goto the freelist for the index which ONLY has totally empty blocks on it and insert that block into the index structure at that point.


As for your question about "sawyer" to "swaze" and then the delete -- we most CERTAINLY WOULD use that space for smith. We don't look to the free list to put smith in there -- smith HAS A SPECIFIC PLACE TO GO (between sawyer and swaze). since there is space there (must be, we just freed up 75 slots right) -- smith will go right there.

PCTFREE isn't considered in an index after the index is built. PCTFREE is only meaningful during an index build -- then we will reserve x% of the block for furture updates. After that -- we stuff those blocks as full as we can.

Read page 308 on...

You do address this, My apologies

ken, August 13, 2002 - 9:20 am UTC

Sorry Tom,

I see that you do address the subject of my question in pages 309 - 311.

So "SMITH" would go into the block in question. A block will be used if a row comes along that will "fit" in it.

"Space is reused on index blocks as soon as a row comes along that can reuse it." pg 311.

So, at that time, is the block on the freelist?

Tom Kyte
August 13, 2002 - 5:22 pm UTC

No, the block isn't on the freelist -- we don't use pctfree for indexes AFTER THE INDEX IS BUILT/REBUILT.

We just stuff those blocks as full as we can.

dml effects on an index

A reader, September 14, 2002 - 5:56 pm UTC

Hi Tom

from 8i Admin ILT Guide it states:

Effect of DML Operations on an Index
The Oracle server maintains all the indexes when DML operations are carried out on
the table. Here is an explanation of the effect of a DML command on an index:
1.- Insert operations result in the insertion of an index entry in the appropriate block.
2.- Deleting a row results only in a logical deletion of the index entry. The space used
by the deleted row is not available for new entries until all the entries in the block
are deleted.
3.- Updates to the key columns result in a logical delete and an insert to the index. The
PCTFREE setting has no effect on the index except at the time of creation. A new
entry may be added to an index block even if it has less space than that specified
by PCTFREE.

Point 2 it says that space is not reused unless all entries in the data block are deleted does that mean that, for example if I have a PK in my EMP table if I deleted a range of empno from 1 ~ 10 then I re-insert again will my new data need a new data block for the index segmens considering that deleting 10 empnos I have not deleted all entries in a data block?

In point 3 if I set 10 for PCTFREE I am reserving 10% of index structure (not the data block as opposed for a table segment) for future inserts, i.e if my index structure stores employee names and in one leaf block I stored names that starts from A to B and that leaf block holds 100 employee names at time of creation does that mean I have reserved space for further 10 more employee names (10%)? Then if I need more than 10 the leaf will split. Am I correct?

Tom Kyte
September 15, 2002 - 10:31 am UTC

what is an "ILT" guide.

It is obviously WRONG.  Would you like to prove it?  Just do this:

create table t ( x int );
create index t_idx on t(x);

insert into t values ( 1 );
insert into t values ( 2 );
insert into t values ( 9999999999999999 );
commit;

now, obviously 2 is between the other rows in the index right?  Ok, now run this block:

begin
    for i in 2 .. 99999999999999
    loop
         delete from t where x = i;
         commit;
         insert into t values ( i+1 );  
         commit;
    end loop;
end;
/

if that logically delete entry wasn't reused -- well, you would expect that index to grow and grow and grow and grow during this operation right?  Well, surprisingly (not) it does not grow at all.

The slot that was used by 2 is available to be used by ANY number between 1 and 99999999999999.  When 3 is put there and then deleted, any number between 1 and 99999999999999 can go there.

What they MIGHT mean to say is that the number -1 *might not be able to* use that slot, and 99999999999999+1 *might not be able* to use that slot.  Any number that falls between 1 and 99999999999999 however can.

Index space IS reused.


The INDEX block is never put back onto the FREELIST until all entries on the block are freed (deleted, removed, whatever) but the free space on that index block is most definitely used.  To get the LONG story with more examples, see my book "Expert One on One Oracle" -- I cover this myth in depth.



You can test your theories about the empno 1-10 easily on your own systems -- give it a whirl.  The easiest way to bust a myth is to give a counter case!  I'll do this one for you:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp ( empno int constraint emp_pk primary key, ename varchar2(30) );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp select rownum, username from all_users;
46 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze index emp_pk validate structure;
Index analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select lf_rows, del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS
---------- -----------
        46           0

<b>that shows there are 46 leaf row entries -- 46 index entries</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from emp where empno <= 10;
10 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze index emp_pk validate structure;
Index analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select lf_rows, del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS
---------- -----------
        46          10

<b>That shows there are still 46 entries HOWEVER, 10 of them happened to be "deleted" -- not pointing to an active row in the table anymore...</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp select rownum, lower(username) from all_users where rownum <= 10;

10 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze index emp_pk validate structure;

Index analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select lf_rows, del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS
---------- -----------
        46           0

<b>Lo and behold, there are still only 46 leaf block entries (rows) and now there are 0 deleted ones.  That proves that we'll reuse space.  Just to make it more interesting, we'll do the same thing but we'll reuse the entries WITH DIFFERENT values (not 1..10)</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from emp where empno <= 10;

10 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp select -rownum, lower(username) from all_users where rownum <= 10;

10 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze index emp_pk validate structure;

Index analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select lf_rows, del_lf_rows from index_stats;

   LF_ROWS DEL_LF_ROWS
---------- -----------
        46           0

ops$tkyte@ORA817DEV.US.ORACLE.COM>

<b>Now, we know the delete left us with 10 del_lf_rows, and the insert must have reused them.</b>


In point 3, if you set 10% free, you are saving 10% of each index block during index creation for subsequent modifications just like a table.  Not 10% more rows, 10% of the space.


 

ILT guide is those books

A reader, September 15, 2002 - 10:59 am UTC

you get when you go to Oracle University, actually I got this paragraphes this is from Oracle 8i Architecture and Administration Instructor Lead Tutorial... so I guess it's wrong

I checked Oracle 9i DBA Fundamentals 1 ILT guide now and it states the samething!

Time to correct them?

Tom Kyte
September 15, 2002 - 11:53 am UTC

Please do -- it is a very in-accurate portrayal of what really happens. It is a myth.

Reader

A reader, September 15, 2002 - 7:02 pm UTC

SQL> drop table emp;

Table dropped.

SQL> create table emp ( empno int constraint emp_pk primary key, ename varchar2(30) );

Table created.

SQL> insert into emp select rownum, owner from dba_objects;

3260 rows created.

SQL> insert into emp select rownum+3260 , owner from dba_objects;

3260 rows created.

SQL> insert into emp select rownum+6520 , owner from dba_objects;

3260 rows created.

SQL> commit;

Commit complete.

SQL> select min(empno), max(empno) from emp;

MIN(EMPNO) MAX(EMPNO)
---------- ----------
         1       9780

SQL> analyze index emp_pk validate structure;

Index analyzed.

SQL> set pages 9999
SQL> select * from index_stats;

    HEIGHT     BLOCKS NAME
---------- ---------- ------------------------------
PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN
-------------------- ---------- ------------
         2         24 EMP_PK
                                     9780         18      136724       8000
        17          1         170       8032           0               0
         9780                 1      152032     136894         91            1
                   3          0            0


I deleted 6519 rows , however index_stats shows 5948.
======================================================

SQL> delete from emp where rownum < 6520;

6519 rows deleted.

SQL> commit;

Commit complete.

SQL> select min(empno), max(empno) from emp;

MIN(EMPNO) MAX(EMPNO)
---------- ----------
      6520       9780

SQL> analyze index emp_pk validate structure;

Index analyzed.

SQL> select * from index_stats;

    HEIGHT     BLOCKS NAME
---------- ---------- ------------------------------
PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN
-------------------- ---------- ------------
         2         24 EMP_PK
                                     9209         18      128736       8000
        17          1         170       8032        5948           83114
         9209                 1      152032     128906         85            1
                   3          0            0

I insert only 3260 rows and index_stats shows 0 del_lf_rows and these rows are not
==================================================================================
inserted in the same place where rows were deleted either
==========================================================
SQL> insert into emp select -rownum, owner from dba_objects;

3260 rows created.

SQL> commit;

Commit complete.

SQL> analyze index emp_pk validate structure;

Index analyzed.

SQL> select * from index_stats;

    HEIGHT     BLOCKS NAME
---------- ---------- ------------------------------
PARTITION_NAME                    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN
------------------------------ ---------- ---------- ----------- ----------
   BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ---------- ----------- ---------- ----------- ---------------
DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY
------------- ----------------- ----------- ---------- ---------- ------------
BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN
-------------------- ---------- ------------
         2         24 EMP_PK
                                     6521         19       94391       8000
        18          1         178       8032           0               0
         6521                 1      160032      94569         60            1
                   3          0            0


SQL> select min(empno), max(empno) from emp;

MIN(EMPNO) MAX(EMPNO)
---------- ----------
     -3260       9780 

Tom Kyte
September 15, 2002 - 7:20 pm UTC

Use my showspace.sql script to see the blocks go onto the freelist.

When you did your delete, some of the blocks were put back onto the freelist (hence the differing number of del_lf_rows). They were some of the last fully emptied blocks (they are put on the freelist)

The rest went on there the next time you made us play around with the structure.

Just more evidence that index space is reused.

pctfree and freelists and indexes

A reader, October 07, 2002 - 3:59 am UTC

hi

how does freelists work with index blocks?

In data blocks when the block usage reaches pctfree then it´s unlinked from freelists and when usage falls below pctused it´s linked to freelists again.

Since index blocks we dont have pctused how is it linked to freelists and unlinked?

Tom Kyte
October 07, 2002 - 7:14 am UTC

if you clear out a block, removing all of the index entries on it via an update or delete, it'll be placed on the freelist for the index and subsequently reused later when a new index block is needed in the structure.

Q on PCTFree / PCTUsed

Anil Pant, January 10, 2004 - 7:56 am UTC

Hi,
If I've primary or unique key index (no null values even if unique) on a column C1. Also Im very sure this column values will never change. Then for the Index in this case I can give PCTFree and PCTUsed both as 0. Can I do it ?

Tom Kyte
January 10, 2004 - 10:57 am UTC

do they values arrive in random order or sequentially.


And remember -- pctfree only counts during INDEX CREATION time, never after that.

And remember -- pctused is meaningless in the context of an index block.


So, pctfree = 0 if the data arrives "in order" (eg: the newly inserted data in this index will always be GREATER THAN or LESS THAN all of the values that existed at index creation time)

pctfree = N and N > 0 if the data arrives randomly (save some room on the index blocks in the newly created index for data that needs to "fit there")

For data arriving in order...

Connor, January 10, 2004 - 7:52 pm UTC

Even with pctfree=10, won't Oracle try to do "the right thing by us" by stuffing the blocks full and then splitting at the 99% mark (not the 90% mark - putting the aside the recent oak table discussion about some possible bugs in this area).

Admittedly then if you did rebuild the index you would then take the 10% freespace hit for nothing - but of course, no one should be rebuilding this index anyway :-)

Tom Kyte
January 11, 2004 - 6:06 am UTC

but the original index create (which is the only thing that looks at pctfree) will leave the original blocks 90% full

if the data arrives in order, when building the index, just build it "packed" 100% full since you'll never be splitting those leaf blocks from the original data.



Applicability to Index-Organized Tables

Doug Wingate, January 12, 2004 - 12:01 pm UTC

I skimmed through the discussion and didn't see that anyone has mentioned index-organized tables up to this point. Do the points about PCTFREE and PCTUSED apply to IOTs just as they do to ordinary B*tree indexes?

Tom Kyte
January 12, 2004 - 1:34 pm UTC

pctfree/pctused for iots are the same as for b*trees.

pctused = meaningless.
pctfree = used during create/rebuild

Just to clarify...

Jeff W., January 23, 2004 - 1:07 pm UTC

Lets say I am indexing a varchar2 field and I update the indexed column value such that it will not fit in the current block. I assume this causes the leaf block to split, same as it would if I tried to insert a row that would not fit in its appropriate leaf block.

The reason I ask is we have some DB2 customers and one of them read told us a field we have defined as a varchar2(30) field that they only populate with a max of 10 chars is indexed in DB2 as a char(30). I found this sort of odd that they would allocate space not needed.

I want to verify that Oracle does not behave in this manner.

Tom Kyte
January 23, 2004 - 6:31 pm UTC

updates like that in the index are done sort of like a "delete + insert" -- index entries generaly MOVE blocks when modified.


Oracle stores varchar2's as varchar2's. varying length -- not fixed.

pctfree for indexes

Simon, November 17, 2004 - 4:29 am UTC

I would like to get back to the original query of how to find out what PCTFREE should be set to.

Some of the followup has been along the lines of «you don't like the default -- you set it. »

But how ?

I is also suggested that « 10%, 5%, not a significant difference in the grand scheme of things. «

This surprises me. The method for setting PCTFREE for a very, very large index, should be something more precise than saying « 10% is a fairly good rule of thumb. You set it when you need to or want to. «

Surely if you can cut the size of an index by 5%, this is going to pay off in terms of i/o and space ?

So after finding out if my index is subject to ;
- inserts in index order, or random order
- updates
What should I be looking at ? Index height ? Clustering factor ?


Tom Kyte
November 17, 2004 - 9:31 am UTC


if your index is an index on a table that is empty upon installation of your product and then filled up -- pctfree = anything you like, as it has no bearing on anything at all.

That covers in my experience the vast majority of index creates!


if you are adding an index to a populated table that is an index on a sequence for example - pctfree = 0 is acceptable (you won't be inserting into any existing index blocks after the index creation)

if you are adding an index to a populated table that is an index on anything else (randomly inserted/updated/deleted from) the pctfree (only used at the start of this process) is not really possible to set perfectly for the reason that there is no perfect number to set it to since AT SOME POINT, the pctfree space that was reserved will be used up and the block will split anyway (putting off the inevitible).

height, clustering factor would have nothing to do with it.

so, maybe the answer is either 0 or 10

pctfree for indexes

Simon, November 26, 2004 - 10:28 am UTC

Excellent that is exactly the summary I was looking for.

Excellent

Frank Zhou, June 10, 2005 - 6:17 pm UTC

Very excellent explanation. If someone still confuse, it is better to understand little more about the B*Tree structure.

Jack, August 28, 2005 - 4:38 am UTC

I have seen significant "buffer busy waits" on one index. Would rebuilding the index with higher pctfree (40) help reduce "buffer busy waits" to some degree?

The data goes into the table in sequence. There are very few updates and deletes.

Thanks.

Tom Kyte
August 28, 2005 - 5:13 am UTC

if the index is on a sequence - rebuilding with a higher pctfree would simply make it such that the existing blocks that will never get another value (the left hand side of the index) go from about 90% full to about 60% full.  It would make the index bigger.


The buffer busy waits are likely coming from the right hand side block, where the  new inserts are happening.  If that is the case (very likely), then the following extract from my forthcoming book might be of interest.

<quote>

Reverse Key Indexes

Another feature of a B*Tree index is the ability to “reverse” its keys. At first you might ask yourself, “Why would I want to do that?” B*Tree indexes were designed for a specific environment and for a specific issue. They were implemented to reduce contention for index leaf blocks in “right-hand-side” indexes, such as indexes on columns populated by a sequence value or a timestamp, in an Oracle RAC environment.

Note    We discussed RAC in Chapter 2.

RAC is a configuration of Oracle in which multiple instances can mount and open the same database. If two instances need to modify the same block of data simultaneously, they will share the block by passing it back and forth over a hardware interconnect, a private network connection between the two (or more) machines. If you have a primary key index on a column populated from a sequence (a very popular implementation), everyone will be trying to modify the one block that is currently the left block on the right-hand side of the index structure as they insert new values (see Figure 11-1, which shows that higher values in the index go to the right, and lower values go to the left). Modifications to indexes on columns populated by sequences are focused on a small set of leaf blocks. Reversing the keys of the index allows insertions to be distributed across all the leaf keys in the index, though it could tend to make the index much less efficiently packed. 

Note    You may also find reverse key indexes useful as a method to reduce contention, even in a single instance of Oracle. Again, you will mainly use them to alleviate buffer busy waits on the right-hand side of a busy index, as described in this section.

Before we look at how to measure the impact of a reverse key index, letÂ’s discuss what a reverse key index physically does. A reverse key index simply reverses the bytes of each column in an index key. If we consider the numbers 90101, 90102, and 90103, and look at their internal representation using the Oracle DUMP function, we will find they are represented as follows:

ops$tkyte@ORA10GR1> select 90101, dump(90101,16) from dual
  2  union all
  3  select 90102, dump(90102,16) from dual
  4  union all
  5  select 90103, dump(90103,16) from dual
  6  /
 
     90101 DUMP(90101,16)
---------- ---------------------
     90101 Typ=2 Len=4: c3,a,2,2
     90102 Typ=2 Len=4: c3,a,2,3
     90103 Typ=2 Len=4: c3,a,2,4

Each one is 4 bytes in length and only the last byte is different. These numbers would end up right next to each other in an index structure. If we reverse their bytes, however, Oracle will insert the following:

ops$tkyte@ORA10GR1> select 90101, dump(reverse(90101),16) from dual
  2  union all
  3  select 90102, dump(reverse(90102),16) from dual
  4  union all
  5  select 90103, dump(reverse(90103),16) from dual
  6  /
 
     90101 DUMP(REVERSE(90101),1
---------- ---------------------
     90101 Typ=2 Len=4: 2,2,a,c3
     90102 Typ=2 Len=4: 3,2,a,c3
     90103 Typ=2 Len=4: 4,2,a,c3

Note    REVERSE is an undocumented function and, as such, should be used carefully. I do not recommend using REVERSE in “real” code, as its undocumented nature implies that it is not supported.

The numbers will end up “far away” from each other. This reduces the number of RAC instances going after the same block (the rightmost block) and reduces the number of block transfers between RAC instances. One of the drawbacks to a reverse key index is that you cannot use it in all cases where a regular index can be applied. For example, in answering the following predicate, a reverse key index on X would not be useful:

where x > 5

The data in the index is not sorted by X before it is stored, but rather by REVERSE(X), hence the range scan for X > 5 will not be able to use the index. On the other hand, some range scans can be done on a reverse key index. If I have a concatenated index on (X, Y), the following predicate will be able to make use of the reverse key index and will “range scan” it:

where x = 5

This is because the bytes for X are reversed, and then the bytes for Y are reversed. Oracle does not reverse the bytes of (X || Y), but rather stores (REVERSE(X) || REVERSE(Y)). This means all of the values for X = 5 will be stored together, so Oracle can range scan that index to find them all.
Now, assuming you have a surrogate primary key on a table populated via a sequence, and you do not need to use range scanning on this index—that is, you don’t need to query for MAX(primary_key), MIN(primary_key), WHERE primary_key < 100, and so on—then you could consider a reverse key index in high insert scenarios even in a single instance of Oracle. I set up two different tests, one in a pure PL/SQL environment and one using Pro*C to demonstrate the differences between inserting into a table with a reverse key index on the primary key and one with a conventional index. In both cases, the table used was created with the following DDL (we will avoid contention on table blocks by using ASSM so we can isolate the contention on the index blocks):

create table t tablespace assm
as
select 0 id, a.*
  from all_objects a
 where 1=0;
                                      
alter table t
add constraint t_pk
primary key (id)
using index (create index t_pk on t(id) &indexType tablespace assm);

create sequence s cache 1000;

whereby &indexType was replaced with either the keyword REVERSE, creating a reverse key index, or with nothing, thus using a “regular” index. The PL/SQL that would be run by 1, 2, 5, 10, or 15 users concurrently was

create or replace procedure do_sql
as
begin
    for x in ( select rownum r, all_objects.* from all_objects )
    loop
        insert into t
        ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
          OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
          LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
          GENERATED, SECONDARY )
        values
        ( s.nextval, x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME,
          x.OBJECT_ID, x.DATA_OBJECT_ID, x.OBJECT_TYPE, x.CREATED,
          x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY,
          x.GENERATED, x.SECONDARY );
        if ( mod(x.r,100) = 0 )
        then
            commit;
        end if;
    end loop;
    commit;
end;
/

Now, since we discussed the PL/SQL commit time optimization in Chapter 9, I wanted to run a test that was using a different environment as well, so as to not be misled by this commit time optimization. I used Pro*C to emulate a data warehouse extract, transform, load (ETL) routine that processed rows in batches of 100 at a time between commits:

    exec sql declare c cursor for select * from all_objects;
    exec sql open c;
    exec sql whenever notfound do break;
    for(;;)
    {
        exec sql
        fetch c into :owner:owner_i,
        :object_name:object_name_i, :subobject_name:subobject_name_i,
        :object_id:object_id_i, :data_object_id:data_object_id_i,
        :object_type:object_type_i, :created:created_i,
        :last_ddl_time:last_ddl_time_i, :timestamp:timestamp_i,
        :status:status_i, :temporary:temporary_i,
        :generated:generated_i, :secondary:secondary_i;

        exec sql
        insert into t
        ( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
          OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
          LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
          GENERATED, SECONDARY )
        values
        ( s.nextval, :owner:owner_i, :object_name:object_name_i,
          :subobject_name:subobject_name_i, :object_id:object_id_i,
          :data_object_id:data_object_id_i, :object_type:object_type_i,
          :created:created_i, :last_ddl_time:last_ddl_time_i,
          :timestamp:timestamp_i, :status:status_i,
          :temporary:temporary_i, :generated:generated_i,
          :secondary:secondary_i );
        if ( (++cnt%100) == 0 )
        {
            exec sql commit;
        }
    }
    exec sql whenever notfound continue;
    exec sql commit;
    exec sql close c;

The Pro*C was precompiled with a PREFETCH of 100, making this C code analogous to the PL/SQL code in Oracle 10g.

Note    In Oracle 10g Release 1 and above, a simple FOR X IN ( SELECT * FROM T ) in PL/SQL will silently array fetch 100 rows at a time, whereas in Oracle9i and before, it fetches just a single row at a time. Therefore, if you want to reproduce this example on Oracle9i and before, you will need to modify the PL/SQL code to also array fetch with the BULK COLLECT syntax.

Both would fetch 100 rows at a time and then single row insert the data into another table. The following tables summarize the differences between the various runs, starting with the single user test in Table 11-1.

Table 11-1. Performance Test for Use of Reverse Key Indexes with PL/SQL and Pro*C: Single User
                              Reverse  No Reverse  Reverse    No Reverse
                              PL/SQL   PL/SQL      Pro*C      Pro*C
Transaction/second            38.24    43.45       17.35      19.08
CPU time (seconds)            25       22          33         31
Buffer busy waits number/time 0/0      0/0         0/0        0/0
Elapsed time (minutes)        0.42     0.37        0.92       0.83
Log file sync number/time              6/0         1,940/7    1,940/7


From the first single-user test, we can see that PL/SQL was measurably more efficient than Pro*C in performing this operation, a trend weÂ’ll continue to see as we scale up the user load. Part of the reason Pro*C wonÂ’t scale as well as PL/SQL will be the log file sync waits that Pro*C must wait for, but which PL/SQL has an optimization to avoid.

It would appear from this single-user test that reverse key indexes consume more CPU. This makes sense because the database must perform extra work as it carefully reverses the bytes in the key. But, weÂ’ll see that this logic wonÂ’t hold true as we scale up the users. As we introduce contention, the overhead of the reverse key index will completely disappear. In fact, even by the time we get the two-user test, the overhead is mostly offset by the contention on the right hand side of the index, as shown in Table 11-2.

Table 11-2. Performance test for use of reverse key indexes with PL/SQL and Pro*C: 2 users
                                 Reverse   No Reverse  Reverse       No Reverse
                                 PL/SQL    PL/SQL      Pro*C         Pro*C
Transaction/second               46.59     49.03       20.07         20.29
CPU time (seconds)               77        73          104           101
Buffer busy waits number/time    4,267/2   133,644/2   3,286/0       23,688/1
Elapsed time (minutes)           0.68      0.65        1.58          1.57
Log file sync number/time        19/0      18/0        3,273/29      2,132/29


As you can see from this two-user test, PL/SQL still outperforms Pro*C, but the use of the reverse key index is showing some positive benefits on the PL/SQL side and not so much on the Pro*C side. That too is a trend that will continue. The reverse key index is solving the buffer busy wait problem we have due to the contention for the rightmost block in the index structure; however, it does nothing for the log file sync waits that affect the Pro*C program. This was the main reason for performing both a PL/SQL and a Pro*C test: to see the differences between these two environments. This begs the question, why would a reverse key index apparently benefit PL/SQL but not Pro*C in this case? It comes down to the log file sync wait event. PL/SQL was able to continuously insert and rarely had to wait for the log file sync wait event upon commit, whereas Pro*C was waiting every 100 rows. Therefore, PL/SQL in this case was impacted more heavily by buffer busy waits than Pro*C was. Alleviating the buffer busy waits in the PL/SQL case allowed it to process more transactions, and so the reverse key index positively benefited PL/SQL. But in the Pro*C case, the buffer busy waits were not the issue—they were not the major performance bottleneck, so removing the waits had no impact on overall performance.
LetÂ’s move on to the five-user test, shown in Table 11-3.
Table 11-3. Performance test for use of reverse key indexes with PL/SQL and Pro*C: 5 users
                                  Reverse     No Reverse    Reverse   No Reverse
                                  PL/SQL      PL/SQL        Pro*C     Pro*C
Transaction/second                43.84       39.78         19.22     18.15
CPU time (seconds)                389         395           561       588
Buffer busy waits number/time     19,259/45   221,353/153   19,118/9  157,967/56
Elapsed time (minutes)            1.82        2.00          4.13      4.38
Log file sync number/time                     691/14        6,655/73  5,391/82

We see more of the same. PL/SQL, running full steam ahead with few log file sync waits, was very much impacted by the buffer busy waits. With a conventional index and all five users attempting to insert into the right-hand side of the index structure, PL/SQL suffered the most from the buffer busy waits and therefore benefited the most when they were reduced. 
Now, taking a look at the ten-user test in Table 11-4, we can see the trend continues.
Table 11-4. Performance test for use of reverse key indexes with PL/SQL and Pro*C: 10 users
                              Reverse    No Reverse    Reverse    No Reverse
                              PL/SQL     PL/SQL        Pro*C      Pro*C
Transaction/second            45.90      35.38         17.88      16.05
CPU time (seconds)            781        789           1,256      1,384
Buffer busy waits number/time 26,846/279 456,231/1,382 25,871/134 364,556/1,702
Elapsed time (minutes)        3.47       4.50          8.90       9.92
Log file sync number/time                2,602/72      11,032/196 12,653/141

PL/SQL, in the absence of the log file sync wait, is very much helped by removing the buffer busy wait events. Pro*C is experiencing more buffer busy wait contention now but, due to the fact it is waiting on log file sync events frequently, is not benefiting. One way to improve the performance of the PL/SQL implementation with a regular index would be to introduce a small wait. That would reduce the contention on the right-hand side of the index and increase overall performance. For space reasons, I will not include the 15- and 20-user tests here, but I will confirm that the trend observed in this section continued.

We can take away two things from this demonstration. A reverse key index can help alleviate a buffer busy wait situation, but depending on other factors you will get varying returns on investment. In looking at Table 11-4 for the ten-user test, the removal of buffer busy waits (the most waited for wait event in that case) affected transaction throughput marginally, but it did show increased scalability with higher concurrency levels. Doing the same thing for PL/SQL had a markedly different impact on performance: we achieved a measurable increase in throughput by removing that bottleneck.
 

pctfree settings

A reader, October 17, 2005 - 4:33 pm UTC

Hi

I have some doubts about pctfree settings for an index.

Pctfree, in an index context, reserves a oracle data block space for future updates and inserts or reserves a leaf block space?

If my index is a right handed index I understand that pctfree 0 is good since I dont need to reserve any space for random keys. But if the index is randomly inserted then will I improve performance by minimizing block splits by setting a high pctfree? Because if I have a very low pctfree my index blocks will be splitting all the time correct?

Imagine a leaf block which can fit 100 keys has a pctfree of 5 so 5 more keys can be inserted, if it fills up then this block splits into two which each of those two blocks will reserve 5% of each. Is this correct....?


Thank you!

Tom Kyte
October 18, 2005 - 8:16 am UTC

pctfree is used in an index ONLY during the CREATE INDEX statement.

In a right hand index, pctfree of "default" (10%) is just fine since we'll do a 90/10 split anyway (resulting in 90% of the data going left and 10% going 'right' when we split). each leaf block will be about 90% full.


In an index that accepts random inserts (say an index on LAST_NAME for example) -- a higher pctfree on the initial build/rebuild could be useful as it would reserve room on each block for future inserts - preventing block splits from occuring really soon after the create/rebuild. An index on last_name will typically have lots of white space (since we do about a 50/50 split on the data when the block splits so each block is about 1/2 empty after a split - so the index is about 30-40% empty "normally")


but pctfree isn't consulted after the create/rebuild for index structures. data in an index "has a place to go" and index keys are not really "updated" to grow or shrink like in a table.

what happen after split

A reader, October 18, 2005 - 10:03 am UTC

Hi

When a 50/50 split happens does the new blocks reserve room for future updates and inserts? It should right? Otherwise it will have to split if there are further random inserts into same leaf blocks.

Tom Kyte
October 18, 2005 - 10:15 am UTC

it is about a 50/50 split (technically a 60/40 but it really isn't all that too important...)


there are no "updates" in an index - the data moves.

18 years and 1 day a go - I got married. My wifes last name changed from Mikelatis to Kyte, somewhere there was an update. Somewhere - that update DELETED Mikelatis from the index and INSERTED Kyte into it (that index on last_name).

There are never "random" inserts into leaf blocks - they are ALWAYS very specifically targeted to that block!!! the data has ORDER, a place to go, somewhere it needs to be. Hence - pctfree isn't used after create/rebuild - they just fill blocks up until they are FULL and then split them when they have to.

Alberto Dell'Era, October 18, 2005 - 2:56 pm UTC

> 18 years and 1 day a go - I got married.

Congratulations :)

(for having reduced the index size by 5 bytes of course ;)

David Aldridge, October 18, 2005 - 6:45 pm UTC

>> (for having reduced the index size by 5 bytes of course ;)

Assuming an uncompressed index, of course :D

I have table with 10849683 rows what is the pctfree i should use

mada, January 18, 2006 - 2:02 pm UTC

i have table abc wher i have 35 columns i added one more column.so now there are 36 columns .Now want to upadte
that one column with vlaues from another table.

please let me know how much value should i keep for the
pctfree for tha tble now the pct free is only 20% can i increase it to 40%

Tom Kyte
January 19, 2006 - 12:11 pm UTC

it is too late to decide your pctfree for this one.

pctfree is used during data insertion, I have to assume your data already exists, is already inserted. and now you are going to update it.

in that case, we'll use pctused after the update to see if the data on the block shrunk enough to let this block come off of the freelist (automatic segment space management - ASSM - will not do this however). But pctfree - not in this case.

Leaf Block Splitting

Bakunian, July 18, 2006 - 12:37 pm UTC

I have both books Expert Oracle and Effective Oracle however still missing concept of leaf block splitting.

For example we created new empty table with b-tree index.
Now we insert numeric values.
insert into t values (111)
insert into t values (222)
insert into t values (333)

Now letÂ’s just say that index block is full and we insert more data.
insert into t values (112)

Q1. Does it mean that Oracle has to split block in 60/40 to move values (222) and (333) to second block and then insert (112) in appropriate location into fist block?
If that is the case then block splitting is a nightmare of maintenance in bulk loads.
2. At what point Oracle decides to create new branch level?

Thanks in advance


Tom Kyte
July 19, 2006 - 8:31 am UTC

block splitting is a natural, unavoidable thing. I would hardly call it a "nightmare" - especially if you BULK LOAD (using bulk loads - direct path or insert /*+ APPEND */ - the newly added data creates a mini index off to the side which is MERGED INTO the original index after the bulk load completes).


but yes, if leaf block is full and you are doing slow by slow conventional path inserts - it'll do the split and put the data where it needs.


Oracle does a new branch level basically (conceptually, good enough for us to think of it this way) when the index gets too "fat" - too "wide"


RE: Ordered leaf block data

Bakunian, July 21, 2006 - 6:19 pm UTC

Tom,

In the 9.2 Concepts guide page 288, figure 10-7 illustrating internal structure of B-tree index that figure shows ordered leaf block data. So if leaf block has space and no split occurs and first transaction inserts row into table with value 112 and the second transaction inserts row with value 111, how Oracle assures that data within leaf block is ordered?

Thanks again


Tom Kyte
July 23, 2006 - 8:04 am UTC

Oracle maintains things "a block at a time", we own the block, we can do whatever we like to it - move stuff around.

Column Order in Multi Column Index

Rob, October 11, 2006 - 2:34 am UTC

Tom,

I have a table say X with 6 columns a,b,c,d,e,f.
Column Cardinality
a 5
b 50
c 500
d 5000
e 10000
f 10000

I want to create an index on a,b,c,d columns, selectivity of the query resultset will be 1. Is there any particular order I should create the index with?

create index x_index on x (a,b,c,d) or
create index x_index on x (d,c,b,a) etc

Query is like : select * from x where a=:a and b=:b and c=:c and d=:d and e=:e;

Will this have any kind of impact due to order of columns having different cardinality? In my case I am getting different performance in production env having similar stuff, table is about 2gb size. Index is nonunique.

Thanks



Tom Kyte
October 11, 2006 - 8:09 am UTC

the order of columns is driven by one and only one thing:

your predicates.


If this is the only predicate you have, the order can by "any" in your case - it always returns one row you say (so clustering factor doesn't really count), you don't need to order by it, and you won't use it for any other predicate.




Simple Example - growing indexes on table that has same count of rows

Henryk Paluch, February 09, 2009 - 5:41 am UTC

Hello!

Here is very simple demonstration of delete & insert operation that cause primary key index to grow substantially, although the number of table rows remains same.

1) Create test table and sequence (preferably use small storage, for example 64K for both table and indexes):

create table BULK_TEST
(
ID INTEGER not null
)
/

alter table BULK_TEST
add constraint BLK_PK primary key (ID)
/

create sequence blk_id_seq
/

2) Run this delete/insert operation:
declare
min_id integer;
max_id integer;
c integer;
d integer :=0;
del_limit integer := 5000;
insert_rows integer := 5000;
begin
-- we want table with holes - but not to be empty
select count(*) into c from bulk_test;
if c = 0 then
insert_rows := 20000;
end if;

for i in 1..insert_rows loop
insert into bulk_test values (blk_id_seq.nextval);
end loop;

select min(id),max(id),count(*) into min_id,max_id,c from bulk_test;
dbms_output.put_line('inserted rows: '||insert_rows||' min id'||min_id||' max_id '||max_id);

while ( d < del_limit ) loop
delete from bulk_test
where id = min_id + trunc((max_id-min_id)*dbms_random.value)
;
if ( sql%rowcount > 0 ) then
d:= d+1;
end if;
end loop;
dbms_output.put_line( d ||' deleted');
select min(id),max(id),count(*) into min_id,max_id,c from bulk_test;
dbms_output.put_line('min_id '||min_id||' max_id '||max_id||' count: '||c);
commit;
end;
/
3) Watch segment allocation as sysdba:

select segment_name,segment_type,bytes,extents
from dba_segments
where segment_name in ('BLK_PK','BULK_TEST')
/

When you repeat 2) and 3) you should see, that extent allocation for table remains same, but extent allocation for primary key is steadily increasing - thanks to holes fragmentation it could theoretically reach a situation where each index entry has its own extent thus the index would grow up to rows * extent size => 15000 * 64K in my example :-)

For example I got (the table BULK_TEST has always 15000 rows) these results:

After 1st run the segments are:

SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
BULK_TEST TABLE 327680 5
BLK_PK INDEX 393216 6

After 2nd run:

SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
BULK_TEST TABLE 327680 5
BLK_PK INDEX 458752 7

After 3rd run:

SEGMENT_NAME SEGMENT_TYPE BYTES EXTENTS
BULK_TEST TABLE 327680 5
BLK_PK INDEX 524288 8

However the table contains everytime just 15000 rows.

I know that it is not a bug but rather feature, but anyway it is good to be aware of such behaviour - that is - if someone delete X rows in nearly random location and later add X rows (with different ID) then such index may grow substantially.


Tom Kyte
February 09, 2009 - 7:19 pm UTC

pctfree for index

A reader, October 03, 2010 - 12:33 am UTC

Hi Tom,

I have read the effect of PCTFREE for index in this topic. I would like to recollect few things from the above discussion:
1) In an index entry if an key value is deleted then that space is never re-used until the entire index is emptied. Is it correct? If yes then suppose I have key value like 1111,1112,1115. What would be the effect if I delete 1111 and re-insert 1111? Would that previous space be used again? What would be the effect if I insert 1114?
2) What would be the effect of PCTFREE after we rebuild the index?
3) I assume we don't use PCTUSED in the index since after deletion of some rows in the index insertion of new rows takes place only after the entire index leaf block becomes empty (which is quite different from table) and therefore FREELIST of index only holds those indexes which are empty.
Am I correct in this assumption?
Tom Kyte
October 04, 2010 - 2:08 am UTC

I'll suggest those books again for you - you are asking very basic questions that can be answered by reading:

a) Expert Oracle Database Architecture (see homepage)
OR
b) the 11g Release 2 server concepts guide (freely available on otn.oracle.com)

You will not be sorry if you read them.

1) wrong, totally wrong. Index space is reused very efficiently. We reuse index space constantly. It is totally wrong to say index space is not reused.


2) it would set aside X% of each index block for future inserts/updates

3) insertion will take place on index leaf blocks all of the time - even if they are not totally empty. That is because in an index rows have a place they MUST GO.

If you have an index on last name and you delete where last_name = 'KYTE' and you later insert/update a last_name of 'KYLE', it will likely go where KYTE used to be. Because all of the K's must be found together in the index. We reuse the space all of the time.

PCTUSED for index

A reader, October 04, 2010 - 7:56 am UTC

Hi Tom,

I would definitely read it. I am asking these basic questions because I am new to this area and want to clear atleast my basic.I have seen that many of my senior collegues have lack of fundamental concept in this area and I get different views from them while seeking for even a basic question.

Now for #3, I am getting confused. you have said above that: When we take an index block off of the free list -- it is "empty". We will then totally fill it up until we have to split it. It only goes back onto the free list when it is empty again.
What do you mean by this then? Then if an index doesn't wait to be emptied to fill it up again then why we don't use PCTUSED here?
Tom Kyte
October 05, 2010 - 1:21 am UTC

Indexes do not have conventional freelists like tables do.

In a table, when you want to insert a row, you need only to find ANY block that has sufficient free space. Therefore, all blocks with sufficient free space are kept on a freelist of some sort in a table (either an old fashioned freelist with manual segment space management - MSSM - or a bitmap freelist with automatic segment space management - ASSM). When you go to insert a row, you consult a freelist to find ANY block and insert into it.


Now, in a index - you cannot put the "rows" on just any old block - they have to go onto a very specific block - that block is the block their key 'sorts' onto. Even if that block is 100% full - that is where that row MUST go (unlike a table, in a table if a block was 100% full - we would of course just look for another block - you cannot do that in an index). Therefore - we do not, cannot consult a freelist to find just ANY block to insert into with an index - we look into the index and find out where that row MUST go.

If that block it must go on is 100% full - we will split that block in two - Now we need an empty block to put half of the data on. This is where the freelist for the index comes into play. All of the blocks on the freelist for an index are 100% empty (if they were not empty - they would be in a very specific position in the index structure itself already and could not be moved). We get an empty block and link it into the index right next to the full block and put about half of the data from the full block onto it. Now we have two half empty blocks and plenty of room to insert our new index key entry.

pctused - which is used in a TABLE when using MSSM, tells us when to put a block back onto the freelist. With an index - a block must be 100% empty before it can be put back onto a freelist - therefore pctused doesn't count, it has only one value and that value is 'when the block is completely empty'.

PCTUSED

A reader, December 05, 2010 - 9:37 am UTC

Hi Tom,

I would like to ask a basic question:

Can I insert data in a block beyond PCTUSED value? i.e. if my PCTUSED is 40 then can I insert data when the data space is 40% filled?
Tom Kyte
December 07, 2010 - 9:34 am UTC

pctused governs when a block is put ON the freelist. It doesn't prevent you from inserting data in the block, if the block is on the freelist - inserts can use it.

pctfree is probably what you meant?

PCTUSED

A reader, December 07, 2010 - 12:10 pm UTC

Hi Tom,

Actually I thought that when I delete data from a block it gradually comes from top to bottom of the Block. I cannot insert data until I get below PCTUSED value(the block comes in FREELIST then. Isn't it? If yes then while inserting if the data space crosses above PCTUSED, then will it permit the insert still?
Tom Kyte
December 08, 2010 - 9:14 am UTC

a normal, heap table block starts life empty with a pcfree and (ONLY WHEN USING MSSM - manual segment space management) and a pctused setting


You can insert into the block until the block exceeds (100-pctfree)% utilization. That is, under normal circumstances with pctfree = 10, you can insert into the block until it is about 90% full - then we stop inserting into it.

when using MSSM, we will refuse to insert into the block until the free space exceeds (100-pctused)% - or 60% in the default case with pctused = 40. Once the block is 60% 'free space', we'll start inserting into it again.



A reader, December 08, 2010 - 11:45 am UTC

Hi Tom,

could you please confirm my below understanding is correct or not?

(1) in MSSM (manual segment space management) pctused decide when to move block to free list
(2) in ASSM (automatic segment space management) pctfree will decide when to move block to free list

Thanks
Tom Kyte
December 08, 2010 - 12:20 pm UTC

1) yes, pctused it used to put blocks back onto the freelist.

2) no, pctfree tells both ASSM and MSSM the same thing - when to STOP inserting into a block.

PCTUSED

A reader, December 13, 2010 - 7:05 am UTC

Hi Tom,

So it means, For ASSM the insertion will stop until the used space of the block exceeds (100-pctfree)%. And for MSSM, the insertion will stop until the used space of the block exceeds (100-pctused)%. Isn't it correct?

Now suppose my PCTFREE=10 and PCTUSED=40 in a 8kb. Now while using MSSM we cannot insert data into that block once it exceeds 60% i.e. 4.8 KB used space?
Tom Kyte
December 14, 2010 - 2:44 am UTC

it means that for either of automatic or manual, we'll fill a block up to 100-pctfree%, yes.

In MSSM, if pctree is 10, we'll fill the block up to 90% full. THEN we'll take it off the free list and not insert anymore. It'll still off the freelist until is 60% or more free space.

pctfree controls when we STOP putting new rows in the block.

pctused controls when we START putting new rows in the block again - after pctfree kicked in.

Curious about PCTFREE

Dana, January 24, 2013 - 9:51 am UTC

I am using Oracle XE version 11.2 to test.
For context, I am trying to resolve a Enq TX wait issue. 
I decided to try both minimizing records per block in the table and creating the primary key index with maximum blocks.

Test case below. My question is the blocks for the index are increased/decreased in a non-linear fashion. I wondered if you could explain the algorithm so I can better understand how it works. 

Thank you.

SQL> create table usage_log(user_id number not null,last_action date not null,action_desc varchar2(20 char) not null);

Table created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
     30002

SQL> insert into usage_log
  2  (user_id,
  3   last_action,
  4   action_desc)
  5  values
  6  (s.nextval,
  7   sysdate,
  8   'First row');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table usage_log minimize records_per_block;

Table altered.

SQL> insert into usage_log
  2  (user_id,
  3  last_action,
  4  action_desc)
  5  select s.nextval,
  6  sysdate,
  7  'second run'
  8  from dual
  9  connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> alter table usage_log
  2  add constraint
  3  usage_log_pk
  4  primary key(user_id);

Table altered.

SQL> alter index usage_log_pk unusable;

Index altered.

SQL> alter index usage_log_pk rebuild pctfree 90 initrans 100;

Index altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'USAGE_LOG');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,blocks
  2  from user_segments
  3  where segment_name like 'USAGE%';

SEGMENT_NAME         SEGMENT_TYPE         BLOCKS
-------------------- -------------------- ------
USAGE_LOG            TABLE                  5120
USAGE_LOG_PK         INDEX                 10240

SQL> SELECT COUNT(*) FROM USAGE_LOG;

  COUNT(*)
----------
     10001

SQL> ALTER INDEX USAGE_LOG_PK UNUSABLE;

Index altered.

SQL> ALTER INDEX USAGE_LOG_PK REBUILD PCTFREE 99 INITRANS 100;

Index altered.

SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'USAG%';

SEGMENT_NAME         SEGMENT_TYPE         BLOCKS
-------------------- -------------------- ------
USAGE_LOG            TABLE                  5120
USAGE_LOG_PK         INDEX                 10240

SQL> ALTER INDEX USAGE_LOG_PK UNUSABLE;

Index altered.

SQL> ALTER INDEX USAGE_LOG_PK REBUILD PCTFREE 50 INITRANS 100;

Index altered.

SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'USAG%';

SEGMENT_NAME         SEGMENT_TYPE         BLOCKS
-------------------- -------------------- ------
USAGE_LOG            TABLE                  5120
USAGE_LOG_PK         INDEX                   112

SQL> ALTER INDEX USAGE_LOG_PK UNUSABLE;

Index altered.

SQL> ALTER INDEX USAGE_LOG_PK REBUILD PCTFREE 0 INITRANS 100;

Index altered.

SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'USAG%';

SEGMENT_NAME         SEGMENT_TYPE         BLOCKS
-------------------- -------------------- ------
USAGE_LOG            TABLE                  5120
USAGE_LOG_PK         INDEX                    32

SQL>


Tom Kyte
January 30, 2013 - 1:05 pm UTC

enqueue tx is not a function of the block size - it is a function of two transactions wanting the same row/rows - it won't matter if those rows are on 1 block or 1,000,000 different blocks - if you want two people to have the same row - one will wait for it with Enq TX



also, pctfree is obeyed for indexes *only during a rebuild or create*. after that - we go to 100% full on them. Rows in an index have a place to "be", we don't update them - we only ever delete and insert them. pctfree doesn't live for very long in an index so even if it (Enq TX) was being caused by too many things on a block in an index - it wouldn't work for very long.


what you need to do it figure out why two transactions are going after the same row(s). what is the design of the application that is causing that.

Hmmm...

Dana, January 30, 2013 - 8:07 pm UTC

OK, using the table structure above, the number of rows is about 1,000, and is supposed to represent the users.
According to the developers, the table is used to "record" the last action by a single user.

What I am seeing in the AWR is this:
Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 1,656 2,045 1235 40.52 Application
virtual circuit wait 10,876 1,776 163 35.20 Network
db file sequential read 25,349 178 7 3.53 User I/O
DB CPU 173 3.43
library cache lock 819 70 85 1.38 Concurrency


Subobject Name Obj. Type Row Lock Waits % of Capture
USER_LOG TABLE 1,655 89.27
QUOTE_LINE_PRICE_CONDITION_PK INDEX 35 1.89
QTE_LN_MTRL_TRADE_VNDR_AK01 INDEX 33 1.78
QUOTE_LN_PRC_CND_RF_IDX01 INDEX 28 1.51
QUOTE_CONFIG_LINE_ATTRIB_PK INDEX 15 0.81

My confusion is I can see the statement.
It is in the form of
update user_log set last_action = sysdate where user_id=:b1;

I would conclude based on the description of Enq TX that the same user is trying to update in multiple sessions?

Not multiple users who may have their data residing on the same block of the table?
Tom Kyte
January 31, 2013 - 2:22 pm UTC

I would conclude based on the description of Enq TX that the same user is
trying to update in multiple sessions?


sounds reasonable. Or they have a background process that goes out and does something that locks large portions of the table.


Not multiple users who may have their data residing on the same block of the
table?


unless you see ITL waits - it wouldn't be block level. (query up v$segment_statistics for that table).



using pctfree to spread entries to prevent latch contention

Naresh Bhandare, September 16, 2014 - 10:22 am UTC

Hello Tom,

We are facing severe cache buffers chains latch contention on an index on a small "reference" table - so the table is not very big but its accessed a lot. The most accessed block is an index branch block.

Question is: Does pctfree also apply to branch blocks? Will setting the pctfree to a high value (say 60) reduce the entries in branch block also so that, in theory, we will spread the hits to that hot block to more than one block?