I checked your verification code for the yesterday challenge about isolation level. On my database (Ora 10.2.0.4-64) the choices 9005 and 9007 are working fine and the output is "b = 0". If you run the verification code without the choices 9004 and 9006 it runs without error. Here is my testcase:
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 11 16:03:28 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table plch_test (a number, b number);
Table created.
SQL> begin
insert into plch_test values (1, 0);
insert into plch_test values (2, 0);
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> declare
b number;
2 3 procedure xxx
4 is
5 pragma autonomous_transaction;
6 begin
7 set transaction isolation level read committed;
8 update plch_test
9 set b = 1
10 where a = 2;
11 commit;
12 select ORA_ROWSCN into b from plch_test
13 where a = 1;
14 dbms_output.put_line('ORA_ROWSCN_XXX = '||b);
15 end;
16 begin
17 select ORA_ROWSCN into b from plch_test
18 where a = 1;
19 dbms_output.put_line('ORA_ROWSCN_start = '||b);
20 set transaction isolation level serializable;
21 dbms_lock.sleep(10);
22 xxx;
23 select ORA_ROWSCN into b from plch_test
24 where a = 1 for update;
25 dbms_output.put_line('ORA_ROWSCN_end = '||b);
26 exception
27 when others then
28 dbms_output.put_line('Error');
29 end;
30 /
ORA_ROWSCN_start = 855358236
ORA_ROWSCN_XXX = 855358246
ORA_ROWSCN_end = 855358236
As you see, the SCN of the autonomous transaction is higher than the SCN from the select for update at the end. What is your explanation of this?
I have asked, _Nikotin, the author of the quiz to do some research and post his reply here.
Hello all,
ReplyDeleteI will post my comments in 3 separate posts,
because of the length limit
Before posting my other comments, I can just confirm that the issue raised by the above player is indeed true !
Though the test case shown above DOES NOT need the DBMS_LOCK.sleep call at all,
because it uses an autonomous transaction instead, that automatically causes the parent transaction TO WAIT until it can proceed, the results look indeed different in 10gR2 and 11gR1 :
------------
For 10gR2:
------------
SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Producti
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
-- the rows are located in the same block and have the same ORA_ROWSCN
select a,
ora_rowscn,
dbms_rowid.rowid_relative_fno(rowid) rec_file,
dbms_rowid.rowid_block_number(rowid) rec_block
from plch_test
/
A ORA_ROWSCN REC_FILE REC_BLOCK
-------------------- -------------------- -------------------- --------------------
1 265489529556 51 1205426
2 265489529556 51 1205426
2 rows selected.
declare
b number;
procedure xxx
is
pragma autonomous_transaction;
begin
-- set transaction isolation level read committed;
update plch_test
set b = 1
where a = 2;
commit;
select ORA_ROWSCN into b from plch_test
where a = 1;
dbms_output.put_line('ORA_ROWSCN_XXX = '||b);
end;
begin
select ORA_ROWSCN into b from plch_test
where a = 1;
dbms_output.put_line('ORA_ROWSCN_start = '||b);
set transaction isolation level serializable;
-- dbms_lock.sleep(10);
xxx;
select ORA_ROWSCN into b from plch_test
where a = 1 for update;
dbms_output.put_line('ORA_ROWSCN_end = '||b);
exception
when others then
dbms_output.put_line('Error');
dbms_output.put_line(SQLERRM);
end;
/
ORA_ROWSCN_start = 265489529556
ORA_ROWSCN_XXX = 265489783314
ORA_ROWSCN_end = 265489529556
PL/SQL procedure successfully completed.
( end of PART1 - to be continued )
( continued from PART1 )
ReplyDelete------------
For 11gR1:
------------
SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Producti
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
-- the rows are located in the same block and have the same ORA_ROWSCN
select a,
ora_rowscn,
dbms_rowid.rowid_relative_fno(rowid) rec_file,
dbms_rowid.rowid_block_number(rowid) rec_block
from plch_test
/
A ORA_ROWSCN REC_FILE REC_BLOCK
-------------------- -------------------- -------------------- --------------------
1 265465560219 4 5238
2 265465560219 4 5238
2 rows selected.
declare
b number;
procedure xxx
is
pragma autonomous_transaction;
begin
-- set transaction isolation level read committed;
update plch_test
set b = 1
where a = 2;
commit;
select ORA_ROWSCN into b from plch_test
where a = 1;
dbms_output.put_line('ORA_ROWSCN_XXX = '||b);
end;
begin
select ORA_ROWSCN into b from plch_test
where a = 1;
dbms_output.put_line('ORA_ROWSCN_start = '||b);
set transaction isolation level serializable;
-- dbms_lock.sleep(10);
xxx;
select ORA_ROWSCN into b from plch_test
where a = 1 for update;
dbms_output.put_line('ORA_ROWSCN_end = '||b);
exception
when others then
dbms_output.put_line('Error');
dbms_output.put_line(SQLERRM);
end;
/
ORA_ROWSCN_start = 265465560219
ORA_ROWSCN_XXX = 265465560483
Error
ORA-08177: can't serialize access for this transaction
PL/SQL procedure successfully completed.
-----------------------------------
This result is amazing !!!
-----------------------------------
I also checked in 11gR2 ( 11.2.0.1.0 ) and it behaves the same as 11gR1 .
Prior to this, I did not think about checking in 10gR2 at all !
Does this mean that we should also set a MAXIMUM Oracle version for the PL/SQL Challenge quizzes ?
SHOW PARAMETER compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.1.0.7.0
It would be interesting to check this in 11g by setting the above parameter to '10.2.0'
( database restart required, therefore I cannot check it at this moment ).
Now comes the BIGGEST question of all:
Is this a FEATURE of 11g or simply a BUG introduced in 11g, in comparison with 10gR2 ?
I ask this because the Oracle documentation for both 10gR2 and 11g
seems to be more compatible with the behavior observed in 10gR2, than with the one observed in 11g.
-- end of PART2 - to be continued
( continued from PART2 )
ReplyDeleteI will reproduce below a part of my initial feedback, as I posted it immediately after taking the quiz:
-------------------------------------------------------------------------------------------------------
The documentation is a little bit ambiguous when explains the meaning of a serializable transaction
in general and the ORA-08177 error in particular:
The Oracle10gR2 concepts quide
http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm#sthref1981
says the following:
"Oracle generates an error when a serializable transaction tries to update or delete data
modified by a transaction that commits after the serializable transaction began:
ORA-08177: Cannot serialize access for this transaction"
Two ambiguities here:
It speaks of DATA MODIFIED.
From today's quiz we can learn that "DATA MODIFIED" means:
1. also a row that was just locked and released (committed), without being effectively modified.
2. also a row that is located IN THE SAME BLOCK with another row that was modified or even just
locked and released (committed).
In fact, it explains the error in terms of DATA MODIFIED (without even mentioning that "data"
here means BLOCK and not just ROW), and "modified" means also LOCKED and COMMITTED and not just
CHANGED (UPDATED), instead of explaining it in terms of ROW SCN modified.
What is amzing is that the 11gR2 documentation goes even further from the observed behavior,
in the wrong direction:
The 11gR2 Concepts guide
http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABCJIDI
says:
"Oracle Database permits a serializable transaction to modify a row only if changes to
the row made by other transactions were already committed when the serializable
transaction began. The database generates an error when a serializable transaction
tries to update or delete data changed by a different transaction that committed after
the serializable transaction began:
ORA-08177: Cannot serialize access for this transaction
"
That is, it clearly speaks here about "modifying a row",
even less ambiguously than the 10g documentation above !!!
Using ROWDEPENDENCIES (as mentioned in the Quiz lesson) is one of the methods that can avoid
the ORA-08177, though, as far as I know, it causes some processing overhead.
By the way, the SQL Language only explains that ROWDEPENDENCIES allow SCN tracking at row level,
but not a word here relating this feature to the serializable isolation level,
neither in the 10gR2 nor in the 11gR2 documentation ...
Consequently, once you LEARN that the "issue" of the ORA-08177 is the ROW's SCN,
it is also clear that if the two rows in the quiz were located in DIFFERENT BLOCKS,
then the two choices related to changing or locking a DIFFERENT row would have become correct.
Unfortunately, for the small table in the quiz, this was not the case ...
-----------------------------------------------------------------------------------------------------
Now let's wait for the others to comment, of course, the quiz author above all !
Thanks a lot & Best Regards,
Iudith
The problem probably is that 11g uses less precisely upper bound SCN for serializable transaction.
ReplyDeleteSo it seems that ORA_ROWSCN and this value are same in 11g.
However, 10g uses a little bit more precisely method.
As wee see, Oracle doesn't do cleanout for the block and can deduct that needed row doesn't have lock byte set and so
can use Scn from 0x01 Slot as upper bound SCN.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0012.020.0001870b 0x01c0007b.dac6.19 C--- 0 scn 0x0000.561263aa
0x02 0x000c.00b.00017162 0x01c0001d.6346.04 --U- 1 fsc 0x0000.5612644a
So far, so good, we can reuse both of this slots by doing 2 transactions so that Oracle can not get appropriate upper bound SCN and
raises ORA-08177:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table plch_test (a number, b number);
Table created.
SQL>
SQL> begin
2 insert into plch_test values (1, 0);
3 insert into plch_test values (2, 0);
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> declare
2 b number;
3 procedure p_2_sfu is
4 pragma autonomous_transaction;
5 begin
6 select b into b from plch_test where a = 2 for update;
7 commit;
8 select b into b from plch_test where a = 2 for update;
9 commit;
10 end;
11 begin
12 set transaction isolation level serializable;
13 p_2_sfu;
14 select b into b from plch_test where a = 1 for update;
15 end;
16 /
declare
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
ORA-06512: at line 14
If we take a look at dump after two SFU it will be:
after first:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000f.02d.00018747 0x01c00169.efdf.25 C--- 0 scn 0x0000.56126c04
0x02 0x000c.001.00017163 0x01c0001e.6346.35 --U- 1 fsc 0x0000.56126c07
and after second:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0012.014.0001870c 0x01c0007d.dac6.28 --U- 1 fsc 0x0000.56126c15
0x02 0x000c.001.00017163 0x01c0001e.6346.35 C--- 0 scn 0x0000.56126c07
Thus, Oracle can get only SCN 0x0000.56126c07 from this block that is greater than start SCN of serialized transaction.
I have confirmation from _Nikotin: we will give all players credit for 9005 and 9007. We will make changes to the quiz so that future players will not encounter the issue raised by the players on 10 January.
ReplyDelete>Does this mean that we should also set a MAXIMUM Oracle version for the PL/SQL Challenge quizzes ?
ReplyDeleteI think it is simplification (and increase performance as well) for the code that used to get properly upper bound SCN for the serializable transaction.
It completely differs from the serialization on the statement level where compared consistent read and current versions of block.
Since the lock sleeps for 10 seconds and all the options conclude in 5 seconds (or less in reality), the ORA-08177 error is bound to occur. I thought that this was the trick to the quiz and have rejected all the options as wrong. I have also verified the result afterwards using Oracle 11gR2. Is this the expected behavior - ignoring the oracle docs. Should Oracle 11g be trusted on that?
ReplyDeleteSince the lock slept for 10 secs and all the options concluded in 5 secs (or less in reality) I thought that error ORA-08177: can't serialize access for this transaction, was bound to occur. I thought that this was the trick to the quiz and have answered that all options was wrong. I have also verified my selection afterwards using Oracle 11gR2. I wanted to ask if this is normal behavior independent of oracle docs and oracle version. If it is then credit for 9005 and 9007 should not be awarded or time to answer the quiz should not influence the result.
ReplyDeleteHello All,
ReplyDeleteIt looks to me that all the explanation based on dumping the block content, a.s.o. goes a little bit "too far" in explaining the result, in comparison with the appearingly clear
explanation given in the Oracle Documentation for the serializable transaction.
As by the documentation, it seems topmost clear that the ORA-08177 should occur ONLY when the serializable transaction attempts to change (or lock) a row that was UPDATED/DELETED and COMMITTED AFTER the start point of the serializable transaction, neither more nor less.
And this is indeed the behavior observed in 10gR2.
If this behavior was DELIBERATELY changed in 11g, and it doesn't matter for what reasons, then Oracle should have made this change CLEAR in the documentation as well, by saying that:
1. ORA-08177 will ALWAYS occur in case that the serializable transaction attempts to change or lock
a ROW whose SCN is LATER than the SCN at the start of the serializable transaction.
2. A row's SCN changes (forward) also when the row is locked and committed, even if the row was NOT updated
3. A row's SCN changes when a DIFFERENT ROW in the SAME BLOCK is changed or locked and committed, except if ROWDEPENDENCIES is used for the table, in which case each row does have its own SCN ( by the way, in this case I expect the SCN to be 100% precise, and not just a "conservative upper limit, as is the case without using ROWDEPENDNECIES ).
This would have avoided the need to resort to a DBA-level explanation for an issue that should be clearly
understood by the developers that consider using the serializable transaction feature.
In case that the SCN of a row is still an approximative one, depending on whether Oracle is or is not doing cleanup of blocks and WHEN exactly it is doing it, a.s.o. then the appearance of ORA-08177 is not a 100% precise occurrence, it will probably happen but it also may NOT happen,
and this is NOT related only to WHAT the applications do (data locks, updates, commits, a.s.o.) but (also) to WHAT AND WHEN Oracle is performing its kernel processing, then I'm afraid that the whole issue of answering YES/NO for a question that asks whether ORA-08177 will happen or not is probably only precise in a "vanilla" environment, where no other database activity happens.
In such a case, the Oracle documentation would have had to just mention that, under the same conditions, an ORA-08177 "MIGHT happen", instead of the categoric "WILL happen".
In my opinion, considering:
1. the (better ?) 10g observed behavior
2. the version specified in the Quiz
3. the Oracle Documentation, that only (and highly incompletely) relates the ORA-08177 to ROW CHANGES
and not to the Oracle kernel workings and NOT AT ALL to the SCN, the ORA_ROWSCN or ROWDEPENDENCIES feature,
I think that the two problematic choices should be better rescored as correct to all the players.
It is interesting, by the way, that the explanation given by Stelios above, as to WHY he has considered all the choices to be incorrect is by itself not precise, though it led to 100% correctness.
This consideration did not take into account the issue of updating/locking a DIFFERENT ROW, versus THE SAME ROW,
which is, I bet, the reason for which most of the players were "only" 50% correct, in spite of having made the correct consideration as per the Oracle documentation.
Thanks a lot & Best Regards,
Iudith
>And this is indeed the behavior observed in 10gR2.
ReplyDeleteNot at all, try following example in 10g:
create table plch_test (a number, b number);
begin
insert into plch_test values (1, 0);
insert into plch_test values (2, 0);
commit;
end;
/
declare
b number;
procedure xxx is
pragma autonomous_transaction;
begin
select b into b from plch_test where a = 2 for update;
commit;
end;
begin
set transaction isolation level serializable;
xxx;
xxx;
select b into b from plch_test where a = 1 for update;
end;
/
Hello All,
ReplyDeleteThat's right, using 2 autonomous transactions the ORA-08177 shows up even in Oracle10g.
Just to summarize, and from the Overall Answer given, we can conclude that when Oracle is checking whether a row was CHANGED or not "on behalf" of a "serializable transaction"
then it can encounter some "false positives", namely, for the cases where it CANNOT derive exactly the SCN for a row, and then is forced to use a higher one instead.
The example in the quiz was "ingeniously crafted" so that two of the choices "have the chance" to encounter such a false positive.
This happened immediately in 11g, using the original quiz case, but for 10g it required "additional crafting" to "work-around the ITL behavior" and still encounter the two "false positives".
By the way, reporting a row that was only locked and released without being changed is also a kind of a false positive, though somewhat more consistent, because for that row
the error always happens, even if SCN's are tracked at row level.
If ROWDEPENDENCIES solves indeed the issue, that is, it removes completely the false positives, than it is indeed a welcome solution to avoid the problem for tables that are used by serializable transactions.
But, if not, then we should still continue to live with the knowledge that "an ORA-08177" MIGHT be encountered even if a row WAS NOT changed, but another one in the same block was.
I say MIGHT and not WILL, because it highly depends on how Oracle's kernel is managing the ITL and block cleanup.
Yes, Nikotin is probably right about the reason for which Oracle changed the behavior between the two different versions (namely internal algorithms performance), but, back to our applicative issue, I still think that the players who marked the two problematic choices as correct were "more right" than those who didn't, so, especially in the light of the
two different behaviors in the two versions, I think all of them deserve at least the same credit.
Regardless of this, maybe a good invitation "to dive into Oracle internals" for those who are interested or whose curiosity (or, why not ? whose temper ! ) was arisen by this quiz.
Thanks a lot & Best Regards,
Iudith
The assertion that the use of autonomous transactions in the validation script is equivalent to execution in separate sessions is incorrect. The validation script will execute each statement in a determinable order whereas the relative order of execution between statements in multiple sessions is indeterminate. Although extremely unlikely, there is a possibility that the statements in the second session might complete before the "SET TRANSACTION" statement in the first is executed. This probability increases as the difference in start times of the two PL/SQL blocks is reduced. The question stated that the second session was started "within one second of executing the above block," so there is no minimum delay between the start of the two blocks (it is presumed that executing means initiating execution).
ReplyDeleteQuestions that involve multiple sessions with specified timings are likely to have issues with determinacy and should be closely examined by the reviewers. The last two sentences of the question could be replaced with the following phrasing which eliminates the indeterminacy and is consistent with the assumptions incorporated into the validation script:
"Which of the following choices, when executed in a second session during the sleep period of the first session, will result in the display of 'b = 0' when the PL/SQL block in the first session completes."
I scored poorly on this quiz because I knew little about serialized transactions (I’ve not needed to use that facility). Thanks go to Valentin Nikotin for providing a learning opportunity.
Firstly, on the 'lock/release' acting in the same way as a row change. Since the lock is held on the record, locking the record will require a physical change to the block (as would the lock release). Rollbacks and online table redefinitions probably would as well. These will all advance the block SCN.
ReplyDeleteThere are also situations where a row might cross multiple blocks and I'd guess this is why the row SCN gets advanced when a block is locked (because you may need to reflect the data item changed was for a part of that row on another block).
I think the upshot is that the ORA-08177 is sometimes a false alarm due to the granularity at which Oracle tracks data changes (block vs row and time precision). I've spoken to experienced DBAs who don't recommend serializable transactions, and I think I now realize why.
Hello All,
ReplyDeleteJust one addition:
NO, ROLLBACK is NOT the same as COMMIT.
If a ROLLBACK is performed after a row change or row lock, the SCN is NOT changed and the ORA-08177
is NOT raised.
SCN is only advanced when a transaction COMMITS (any session, not just user sessions, but also Oracle's kernel activity itself).
JHall is right about the issue of the timing coordination between the two sessions.
Looks like the same autonomous transaction scenario that was used in the Verification code could have been equally well used in the quiz itself, without causing less players "to be tricked"
by the Oracle Documentation's lack of precision ...
The SCN cannot be precisely managed because of the block cleanout feature, that sometimes may happen AFTER the precise SCN information is lost due to the UNDO space being reused.
As we have seen with the 10g case, if the cleanout is done (called a Fast-commit cleanout) then the block has the correct information about the SCN and a "false positive" is avoided.
You are right, good lesson (or, not so much a lesson, but rather awareness to the Oracle Internals),
but not the happiest scenario for a good quiz ...
The last sentence in the quiz was a little bit unclear:
"Which of the choices will cause "b = 0" to be displayed after the block in the first session completes execution?"
This could have been easily interpreted as "b=0" being displayed by the choices,
( it says: "the choice will cause "b=0" to be displayed" ! )
but after the block in the first session completes execution.
A much better wording could have been:
"Which of the following choices code can be executed by a second session while the first session is sleeping, so that "b=0" will be displayed after executing
the following block" ...
Gary is right, serializable transactions should be used very cautiously, first of all because they may raise serious data consistency issues in case that they perform transactions requiring complex business rules checking, which cannot be implemented using pure declarative constraints.
Even if locks are managed correctly, such a transaction by definition cannot see the entire database, so it should use some clever tricks for causing it to correctly fail when a business rule is violated.
I think that this isolation level was introduced as a "slight release" of the too restrictive read only isolation level.
For example, in cases when a session should perform several read consistent queries, ( for which the read only transactions were in fact introduced ), but should also execute some log recording, a.s.o.
Or, otherwise put, using it for "query sessions", not for "business data entry" sessions.
Thanks a lot & Best Regards,
Iudith
I've probably found the cause of difference between 10g and 11g. In the head of data block Oracle stores "csc" that is last cleanout SCN. And 11g discusses this SCN equally with ITL SCNs. So if we clean block before the start of the serializable transaction an exception will not be raised:
ReplyDeletecreate table plch_test (a number);
begin
insert into plch_test values (1);
insert into plch_test values (2);
commit;
end;
/
select * from plch_test where a = 2 for update;
rollback;
set transaction isolation level serializable;
declare
pragma autonomous_transaction;
begin
execute immediate 'select * from plch_test where a = 2 for update';
commit;
end;
/
select * from plch_test where a = 1 for update;
Hello Valentin, All,
ReplyDeleteIn 11g the ORA-08177 will not be raised in the above example
if performing the autonomous transaction block only once,
but it will be raised if the same block is repeated twice.
Anyway, any application using serializable transactions should
always be prepared to encounter the ORA-08177.
In some scenarios it happens for sure, namely when the SAME row
is updated or locked/committed, but if this is done with a different row, then the error may happen or not, depending on how Oracle manages the block cleanout.
I wonder whether the ROWDEPENDENCIES does ensure a 100% precise
ORA_ROWSCN for each row.
As far as I understand, it still can generate some false positives, but much less, because each row has its own ORA_ROWSCN which is NOT touched if other rows in the block are updated or locked/committed, but still may be slightly higher than the real one for the row itself when Oracle cannot determine it precisely at cleanup time.
Thanks a lot for your deep insights & Best Regards,
Iudith
Iudith, you may take a look at very old "Bug 440366: SERIALIZABLE GIVES ORA 8177 WITH DML ON SAME BLOCK" which is the root of the problem. It contains also several words about the documentation bug.
ReplyDelete> but it will be raised if the same block is repeated twice.
Yes, because of both ITL slots in the table will be reused.
>but still may be slightly higher than the real one for the row itself when Oracle cannot determine it precisely at cleanup time.
ReplyDeleteYes, here is the simple example of the delayed block cleanout impact:
drop table plch_test;
drop table plch_test2;
create table plch_test (a number, tilte varchar2(100), val number) rowdependencies;
begin
insert into plch_test values (1, 'create and commit at step 1', 0);
insert into plch_test values (2, 'update and commit at step 2', 0);
commit;
end;
/
update plch_test set val = 1 where a = 2;
alter system flush buffer_cache;
commit;
create table plch_test2 (a number, tilte varchar2(100), val number) rowdependencies;
begin
insert into plch_test2 values (3, 'create and commit at step 3', 0);
insert into plch_test2 values (4, 'update and commit 1e5 times at step 4', 0);
commit;
end;
/
begin
for i in 1 .. 1e5 loop
update plch_test2 set val = val + 1 where a = 4;
commit;
end loop;
end;
/
select t.*, ora_rowscn scn from plch_test2 t
union all
select t.*, ora_rowscn from plch_test t
order by scn;
After that you can see that 2 and 3 steps are reversed in the result query:
SQL> select t.*, ora_rowscn scn from plch_test2 t
2 union all
3 select t.*, ora_rowscn from plch_test t
4 order by scn;
A TILTE VAL SCN
---------- -------------------------------------------- ---------- ----------
1 created and commit at step 1 0 1998971227
3 created and commit at step 3 0 1998971254
2 updated and commit at step 2 1 1999009794
4 updated and commit 1e5 times at step 4 100000 1999224125
Hello Valentin, All,
ReplyDeleteThanks a lot again for your so valuable input.
I am very grateful for your example which proved me that my understanding of ROWDEPENDENCIES is indeed correct.
Yes, the BUG you indicated puts it very correctly, I wonder however that after so much time Oracle did not add this to the documentation of the serializable feature.
What is interesting is that the BUG thread speaks about a parameter named "delayed_logging_block_cleanouts".
I looked after it and it does not exist any more in 11g.
Instead, I found another hidden parameter that I suppose should do the same, named "_log_committime_block_cleanout", with a default value of FALSE.
It is settable by ALTER SYSTEM, so I tried to set it to TRUE, but it did not seem to have any influence,
at least on one single test ( the one from the quiz Verification code, without using ROWDEPENDENCIES ).
Also, I did not perform an ALTER SYSTEM FLUSH BUFFER_CACHE before or after setting the parameter, because the database is also used by others and I did not want to impact current work,
This also could have an influence.
Maybe, rather than cancelling at all the delayed cleanout feature at the entire database level ( if this is indeed what the hidden parameter is supposed to do ), another option would be to perform a full commit-time cleanout ONLY for the blocks of tables defined with ROWDEPENDENCIES.
In addition, maybe not to do this always, but to have the above parameter settable AT THE SESSION level as well, with a value of "ROWDEPENDENCIES", in addition to TRUE or FALSE,
so that an individual session could change it at least for its own changes ).
I wonder whether Oracle is able to work simultaneously in both the ways, that is, using commit time cleanout for some tables and delayed cleanout for others.
Another way would be NOT to reuse the ITL entries for a table with ROWDEPENDENCIES prior to making sure that the respective block is completely cleaned out.
All this, with the purpose of achieving a 100% precise commit time information for each row, which could be very useful for many applications, not only specifically for avoiding
the ORA-08177, for which "reparing steps" should be taken anyway at the application level, at least for the case when really the same rows are involved.
Well ... this is maybe digging to far and most of us are really "too small" for teaching Oracle what do do ...
All that I object is the same as the user that opened that bug, namely, to have the observed behavior completely and correctly documented, especially if it is a built-in feature and not a bug.
I think this thread was very useful, it contributed a lot to improve our understanding.
Thank you again very much for all this information
& Best Regards,
Iudith