You could only analyze the table and list chained rows to see if you suffer from row migration (look at the identified rows you get listed, if they could fit on a block, they are migrated)


If you have a large percentage of the rows being migrated, you should set pctfree higher for the future (it'll only affect blocks going forward, it won't 'fix' anything that already exists)

Then you could look at your block utilization, to see if most of your blocks are mostly empty - if they are, that would indicate a lower pctfree might be called for.

2% of a block isn't really meaningful here.

You need to understand how many rows you can get per block.


I can get seven 1KB rows on an 8k block. If I was to update 10% of them to be 20% larger - does 2% count or is the math more like:

- about 1 row on a block will grow
- if it grows by 20% - then I can only fit six rows on that block
- I'd better set up pctfree so that only six rows per block on average fit.


2% would be misleading because the granularity of your rows is so "large"

you could well see physical IO's go up, more logical IO's.

you'll have reserved 3k of your probable 8k block for ITLs. So, almost 50% of your block will be used for block management - not data. Your tables will be larger, you'll be able to cache less or your data in the cache, you'll do more physical io's and logical io's.

so sad to hear, you took a bunch of old fashioned slow by slow processes and run them in many threads instead of taking advantage of database parallelism (which would be contention free). You turned a batch process (which should be a big bulk operation) into an OLTP application!!!!!

is there any point in having INITRANS higher than the number of
rows per block ?

this is true, it would not be useful to have more initrans than rows. for most normal indexes, this would be more than 128 usually. for tables, probably less. good point.

be careful with that commit though - the more you commit - the more undo, redo, log file sync waits, etc you will see as well. and make sure your process is restartable so when it fails (when, not it), it can be safely restarted and picks up where it left off...

a batch has to be run to make
the application available for business the next day.

sorry, but your "batch" isn't batch, it is OLTP. Batch is "single thing, using parallel query". You have 128 things going concurrently - bigger than MOST OLTP systems!!! you have concurrency based waits in your future forever.

128 parallel client threads is not batch, that is OLTP. plain and simple. as I wrote before:

so sad to hear, you took a bunch of old fashioned slow by slow processes and run them in many threads instead of taking advantage of database parallelism (which would be contention free). You turned a batch process (which should be a big bulk operation) into an OLTP application!!!!!


1) each ITL is 23 bytes

ops$tkyte%ORA11GR2> select 128*23 from dual;

    128*23
----------
      2944

2) that is correct, you would have less than 4k for your data on each block probably.

3) if they have an average row length of 6-7k, why would you even consider increasing initrans????? you get one row per block in the first place, there would be no concurrency on this block by definition. it doesn't make sense to have lots of ITLs on a block that has ONE ROW.

4) you tell me, do you want your indexes to be 50% dead weight? do you have the cache to support caching 2x the data???


Bulk operations in SQL are currently in use in most of the
procedures

i disagree, you have 128 threads! that is not bulky, that is OLTP. I don't care if the individual programs do things 100 rows at a time - they aren't using bulky single sql statements - just lots and lots of tiny sqls that have an array bound to them.

pctfree only counts on an index block during the create or rebuild - it means nothing after that - index blocks always fill up to 100% full before they split.


pctfree cannot be used for that purpose in an index for sure.


pctfree only counts during inserts into a table, never updates. updates take the blocks to 100%

so pctfree cannot be used for that purpose in a table either in general.


but if you are just inserting into this table, just use ASSM or multiple freelist groups if using manual segment space management and the inserts will be spread out over the table - not just into the last block. That is how to avoid this issue - not ITLs on the tables really.


(actually, if this table is just being inserted into in batch - why not parallel direct path loads???)

I’m a bit confused why one needs to reserve space for UPDATEs not INSERTs

well, inserts do not cause an existing row on an existing block to GROW - but an update does (or at least it could)


pctfree tells us when to stop inserting into a block - by default - when the block only has 10% free space - we stop inserting into it and move onto a different block. that 10% free space is hopefully big enough to hold and growth in the rows due to future updates to the existing rows on that block.


A higher pctfree would have two effects for you:

a) more space would be reserved for future updates. If you are updating a number and growing the size of the number (remember - numbers in Oracle are between 0 and 22 bytes in length - the number 999,999 takes more space to store than 1,000,000 does since the number of significant digits is what we have to worry about - not the "size" of the number itself). So, if you are updating a NULL number to a non-null value - you are definitely causing the row to "grow" and if you get say 150 rows per block and have 10% free - you have about 700 bytes free - if each of those numbers need 4 or 5 bytes - you might cause rows to migrate after they are updated (they won't fit on the block anymore)

b) there would be less rows per block - decreasing the concurrency on the block when the updates take place, reducing the buffer busy waits for the update. Only one transaction at a time can have the block in current mode and you need the block in current mode in order to change the bits and bytes on it. So, if two transactions try to update different rows on the same block - one will have to wait BRIEFLY with a buffer busy wait while the first transaction changes the bits and bytes on the block. It will not have to wait for a commit - it just needs to wait for the first transaction to do its changes and put the modified block back into the cache - then it can make its modifications.




row movement only affects things like:

a) updating a partition key, if enabled - you can update the partition key in a manner that causes the row to move from one partition to another (and hence changes its rowid)

b) reorganizing a table with the shrink space command


It has nothing to do at all with inserts, updates or deletes.

pctused is *less* relevant nowadays with ASSM tablespaces (which is the default).

But pctfree is still very relevant.

By default, we reserve 10% of space for subsequent updates to rows in a table, ie, room for them to grow.

A *lot* of tables *never* have updates to rows - they only insert, or they only insert/delete. So that 10% is never used...so your table is 10% larger than it needs to be be.

If your table if 1TB, then that's an extra 100G of dead space - that you could use elsewhere, or also means 10% slower to scan that table for large scanning queries.

So if you have tables that are never/rarely updated, or the updates will never make the rows larger (eg, you are changing a status column from one value to another of the same size), then you could look at (say) pctfree of 2 or 1.

If the data is purely for archival, then perhaps pctfree 0 (plus compression). You'll see that when you compress data (using basic compression), we use pctfree 0 anyway because we assume that's your intent.


For an index, pctfree is applied at build/rebuild time, not generally normal operations. Similarly guidelines apply in terms of reduced pctfree when the data is ready for archival (ie, is now read only).