Need to be clarified and nice explanation!!!

2. Which two parameters might affect the concurrency of transactions on a data block? (choose 2)

INITTRANS makes sense of course, but what is the other & why?

A. PCTUSED
B. PCTFREE
C. INITRANS
D. MAXEXTENTS
E. NEXTEXTENT

The other one is A or B???

It seems to me neither A nor B should be the answer.

A. Maybe put and remove the data block into free list frequently.
B. Contention to allocation of data block when insert and update.

So how?

http://www.dbasupport.com/forums/sho...threadid=11896

I would say that answer C. INITRANS is definetely a correct answer...as INITRANS guarantees a minimum level of "concurrency". The number specified for INITRANS ensures that at least that many transactions can "concurrently" make changes to the block...and it occurs because of an allocation of transaction slots from free space in the block.

I don't agree with any of the other answers. My opinion is that the second best answer (which isn't presented in the answer choices) would be MAXTRANS, which sets the limit for the number of "concurrent" transactions that can make changes to a block. The number of transaction slots is limited by this number.

That's my take on this question!

I think there are certain situations where PCTFREE and PCTUSED definitly HAVE impact over concurrency...
It is because they have impact over how many rows resides in how many blocks.
Further thinking, if PCTFREE and PCTUSED are wrongly stated, this could emerge in migration phenomena, or in too much fragmentation. I think this definitly have a large impact on concurrency, even if I have to admit is not a direct impact. But could be dramatic, I think...
Other oppinions?

(between first 2 I'd pick PCTFREE)

[Edited by ovidius on 03-27-2002 at 08:21 AM]

In the original question "...concurrency of transactions..."

Conncurrency means that 2 or more transactions are occuring on the block at the same time. What does PCTFREE or PCTUSED have to do with 2 or more transactions occuring at the same time? I say nothing!

You say nothing, but if you look STATISTICALLY at the problem, meaning "which is the chance for this to occur on varying certain conditions" (like varying PCTFREE and then having some activity on the database and then look at the impact these changes had), it looks be very possible for you to have something to say...

The only explanation that I can think of for PCTFREE is in the case where concurrent transactions are attempting to make chnages to a data block, but the first transaction causes the block to dip below the PCTFREE parameter...so the second (or other) trasaction would then be prevented from INSERTING on the block. But again, this doesn't prevent UPDATES or DELETES...so those transactions could continue "concurrently".

Certainly not a very straight forward question based upon the answer choices!

The question is probably not among the easiest ones, but the answer is without a slightest doubt:
B
C

There is no doubt about C, INITRANS directly defines the minimal concurency for the rows in the same block.

But why PCTFREE, what is its role in this? Well, this is less known, but can be found in some documents on Metalink and elswhere on the net. It is tightly connected with ITLs (Interested Transaction Lists) allocated in a given block. Whenever a transaction wants to modify some rows in a block, it must first obtain ITL slot. The amount of space for initial ITLs is reserved by the setting of INITRANS. If more than one transaction is modifying (different) rows in the same block, each of those transaction must obtain its own ITL. Additional space can be dinamicaly accuired for ITLs in a block, up to the setting of MAXTRANS. However if initaily reserved space for ITLs is not sufficient, Oracle uses available free space in a block for additional ITL slots. Each ITL slot consumes 24 bytes of free space.

So for example if you have set PCTFREE=0, INITRANS=1 and MAXTRANS=10, then it is very likely that once the block is filled, you will never be able to have 5 concurent transaction on a block, even though your MAXTRANS is set to 10. On the other hand, if you set your PCTFREE=10, there is very high chance that additional transactions will be able to obtain ITLs from the free space reserved by PCTFREE. Thus PCTFREE is directly affecting the concurrency of transactions on a data block.

There is for example a recomendation that if you are suffering ITL contention, it is better to increase your PCTFREE than increasing INITRANS, because space reserved by INITRANS can not be reclamed for data later on, so it reduces the overall data density in a block.

Thx Jurij, you provided a nice path to study it carefully again. I really enjoy this forum as well as I am very active in MCDBA forum in another website.

So obviously we have to think about data block structure:
1) Common and variable header
2) Table directory
3) Row directory
4) Row data
5) Free space

Consider 1)
Common header: 24 bytes X 1
Variable header: 24 bytes X INITRANS (INITRANS default 1
for table, 2 for index)

Variable header is so-called transaction header.

Why should we specify INTRANS and MAXTRANS? For control the block space to be allocated for transactions.

The original allocation is defined by INTRANS and then dynamically gets free block space but controlled by MAXTRANS (The header space is never released)

So PCTFREE affects free space can be allocated to transaction header (24 bytes per one)