So, what I think the author of that answer might have meant was "when you execute a query, Oracle looks at the current SCN and ensures that the data returned/touched by that query is as of that SCN - you get a read consistent result set"Ī) open a cursor as 9am when the SCN is 1234Ĭ) come back and see that the SCN is now 1000 (700+ transactions later)ĭ) start fetching the data - any block NOT COMMITTED as of SCN 1234 will be rolled back until we have the committed version of the block as of SCN 1234 Oracle does this thing called mutliversioning and read-consistency. See, now if you COMMIT 1,000 times, the SCN does jump by 1,000 (the other jumps are the background processes, they are always doing stuff - smon, mmom, pmon, etc - they do SQL all of the time - the database never rests :) ) New 1: select dbms_flashback.get_system_change_number - 79178271 from dualĭBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178271 The SCN is incremented upon commit - and the SCN might jump large increments in a distributed environment as the instances "align" themselves with each other. The SCN is not assigned to a SQL statement (more on this later, there is a NUGGET OF TRUTH hidden in that statement, but it got a little twisted here). It only advanced by 5 - but we did over 1,000 dml statements (our plsql block did some dml to parse itself.). New 1: select dbms_flashback.get_system_change_number - 79178265 from dualĭBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178265 Old 1: select dbms_flashback.get_system_change_number - &SCN from dual Ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number - &SCN from dual Ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number scn from dual Ops$tkyte%ORA9IR2> column scn new_val scn Ops$tkyte%ORA9IR2> create table t ( x int ) We would presume that if an SCN was assigned to a sql statement (and it must be unique by statement if we were to use it to 'order' the sql) than the difference would be greater than or equal to the number of DML statements. Well, by reading out the SCN, doing a ton of DML, rereading the current SCN and outputing the difference between the two.
How would or could we show that is probably true? See, this is exactly why I like to provide a little "evidence that what I'm about to say is probably true"
0 kommentar(er)
