Earlier, I ran an experiment to see if we got what I considered to be true read consistency on a table. Initially, admittedly, I didn’t think it was possible without first locking the entire table. And I still believe that’s the case with updates I envisioned them initially.
I was, however, thrown for a loop when I discovered the SCN of the update that was being performed was after the SCN of an update that had been started after the update, which made me devise the theory that if an update reaches a row that has been updated since the start of the statement, the read consistency of the row is foiled and the query has to start back at the beginning to achieve true read-consistency.
Today, I’ll prove that theory either true or false.
We’ll start with a table very similar to the table I created yesterday. This time, however, I’ve added a few different extra columns.
1 2 3 4 5 6 7 8 9 |
create table kaley.drop_me ( id number primary key , data varchar2(250) , index_update_ts timestamp , index_update_scn int , fts_update_ts timestamp , fts_update_scn int ); |
We’ll load it full of data just like before:
1 2 3 4 5 6 7 8 9 10 11 12 |
begin for v_index in 1 .. 2000000 loop insert into kaley.drop_me (id, data) values (v_index, rpad('X', 250, 'X')); if mod(v_index, 10000) = 0 then commit; end if; end loop; commit; end; / |
We’ll also have a table that will be updated frequently that will contain one row, which will have current timestamp.
1 2 3 4 |
create table kaley.updated_timestamp ( ts timestamp ); |
There will be 3 processes at play.
First, a process that will continually be updating the timestamp in the UPDATED_TIMESTAMP table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
begin -- Insert one record into the table, if one doesn't already exist. insert into kaley.updated_timestamp (ts) select systimestamp from dual where not exists (select * from kaley.updated_timestamp); for v_index in 1 .. 50000 loop update kaley.updated_timestamp set ts = systimestamp; commit; dbms_lock.sleep(0.1); end loop; end; / |
Second will be a full table scan that will record 3 different pieces of information. SYSTIMESTAMP- the time when the update kicked off. Also, the time listed in the KALEYC.UPDATED_TIMESTAMP table. Also, the CURRENT_SCN from the V$DATABASE view.
1 2 3 4 5 |
update kaley.drop_me set data = to_char(systimestamp, '"systimstamp: "YYYY-MM-DD HH24:MI:SS.FF') , fts_update_ts = (select ts from kaley.updated_timestamp) , fts_update_scn = (select current_scn from v$database) where index_update_ts is null; |
Lastly will be a PL/SQL block that will perform intermittent updates on the table using the index, starting about halfway down the table, continuing until the full table scan finally “catches up” with it. At that point, it’ll hang until the full table scan commits, then it will hurry through the rest of the process and not update anything.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
declare v_id number := 1000000; begin while v_id <= 2000000 loop update kaley.drop_me set data = to_char(systimestamp, '"systimstamp: "YYYY-MM-DD HH24:MI:SS.FF') , index_update_ts = (select ts from kaley.updated_timestamp) , index_update_scn = (select current_scn from v$database) where id = v_id and fts_update_ts is null; -- If we grabbed a row, then sleep for a bit. -- Otherwise, just hurry through the process and finish up. if sql%rowcount > 0 then dbms_lock.sleep(0.75); end if; commit; v_id := v_id + 10000; end loop; end; / |
The plan is to have the script that’s updating the timestamp be continually running the background while I kick off the full table scan update in one session, and almost immediately afterwards, kick off the pl/sql block in another session. My theory is this: Oracle starts the full table scan update. The index starts changing rows that would’ve been updated by full table scan update quickly, and goes down the line. Eventually, the full table scan will come to a row that has been updated by the index update and go “drat! my read-consistency is ruined!” since the row has already been updated since the start of the full table scan update. I’m expecting that the query will go through the rest of the table at that point and continue locking rows, until everything is finally locked, and then to go back and re-apply the update to all of the rows.
If this is true, then:
- I would expect there to be a large discrepancy between the timestamp recorded in the DATA column (systimestamp) vs the timestamp recorded in the FTS_UPDATE_TS column (which would come from KALEY.UPDATED_TIMESTAMP).
- I would expect there to be practically no discrepancy between the timestamp recorded in the DATA column and the INDEX_UPDATE_TS column.
- I would expect the full table scan update to have about twice as many logical reads as an uninterrupted update.
One additional piece. Last time, I cleared the buffer cache to make the full table scan slower. Which, I’ll do again here.
1 2 3 4 5 |
SQL> alter system flush buffer_cache; System FLUSH altered. Elapsed: 00:00:00.253 |
Something else that I didn’t think of, which would naturally slow down the full table scan update would be to decrease the multi block read count parameter in the session that’s going to perform the full table scan update. I put it at about 1/10th of what it was.
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> show parameter mult NAME TYPE VALUE ----------------------------- ------- ----- db_file_multiblock_read_count integer 128 parallel_adaptive_multi_user boolean TRUE SQL> alter session set db_file_multiblock_read_count = 12; Session altered. Elapsed: 001 Elapsed: 00:00:00.002 |
Let’s give it a whirl!
Here’s what the table looks like afterwards:
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select distinct data , to_char(fts_update_ts, 'YYYY-MM-DD HH24:MI:SS.FF') fts_update_ts , fts_update_scn from kaley.drop_me where fts_update_ts is not null; DATA FTS_UPDATE_TS FTS_UPDATE_SCN ---------------------------------------- ----------------------------- --------------------------------------- systimstamp: 2017-03-19 16:53:38.317000 2017-03-19 16:56:26.196000 13679923 Elapsed: 00:00:02.149 |
You can see there’s almost a 3 minute difference between the systimestamp (recorded in DATA) and the read-consistent timestamp stored in FTS_UPDATE_TS from KALEY.UPDATED_TIMESTAMP. So that’s completely as expected. The index information has thrown a bit of a curveball in my theory though.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
SQL> select id , data , to_char(index_update_ts, 'YYYY-MM-DD HH24:MI:SS.FF') index_update_ts , index_update_scn from kaley.drop_me where index_update_ts is not null order by index_update_scn; ID DATA INDEX_UPDATE_TS INDEX_UPDATE_SCN ---------- ---------------------------------------- ----------------------------- --------------------------------------- 1000000 systimstamp: 2017-03-19 16:53:38.854000 2017-03-19 16:53:38.801000 13680880 1010000 systimstamp: 2017-03-19 16:53:39.702000 2017-03-19 16:53:39.665000 13683826 1020000 systimstamp: 2017-03-19 16:53:40.501000 2017-03-19 16:53:40.440000 13685642 1030000 systimstamp: 2017-03-19 16:53:41.479000 2017-03-19 16:53:41.413000 13686338 1040000 systimstamp: 2017-03-19 16:53:43.455000 2017-03-19 16:53:43.441000 13686747 1050000 systimstamp: 2017-03-19 16:53:44.429000 2017-03-19 16:53:44.415000 13688859 1060000 systimstamp: 2017-03-19 16:53:45.270000 2017-03-19 16:53:45.260000 13690201 1070000 systimstamp: 2017-03-19 16:53:46.211000 2017-03-19 16:53:46.115000 13690978 1080000 systimstamp: 2017-03-19 16:53:47.121000 2017-03-19 16:53:47.081000 13692196 1090000 systimstamp: 2017-03-19 16:53:47.999000 2017-03-19 16:53:47.946000 13692580 1100000 systimstamp: 2017-03-19 16:53:49.110000 2017-03-19 16:53:48.912000 13694715 1110000 systimstamp: 2017-03-19 16:53:50.841000 2017-03-19 16:53:50.830000 13695530 1120000 systimstamp: 2017-03-19 16:53:51.799000 2017-03-19 16:53:51.797000 13697198 1130000 systimstamp: 2017-03-19 16:53:52.739000 2017-03-19 16:53:52.663000 13699478 1140000 systimstamp: 2017-03-19 16:53:53.632000 2017-03-19 16:53:53.616000 13699778 1150000 systimstamp: 2017-03-19 16:53:55.342000 2017-03-19 16:53:55.327000 13700918 1160000 systimstamp: 2017-03-19 16:53:56.514000 2017-03-19 16:53:56.512000 13701670 1170000 systimstamp: 2017-03-19 16:53:57.321000 2017-03-19 16:53:57.258000 13702013 1180000 systimstamp: 2017-03-19 16:53:58.566000 2017-03-19 16:53:58.437000 13703313 1190000 systimstamp: 2017-03-19 16:54:00.133000 2017-03-19 16:53:59.118000 13704824 1200000 systimstamp: 2017-03-19 16:54:00.886000 2017-03-19 16:54:00.867000 13705703 1210000 systimstamp: 2017-03-19 16:54:01.788000 2017-03-19 16:54:01.723000 13706053 1220000 systimstamp: 2017-03-19 16:54:03.126000 2017-03-19 16:54:03.110000 13706589 1230000 systimstamp: 2017-03-19 16:54:03.920000 2017-03-19 16:54:03.865000 13707854 1240000 systimstamp: 2017-03-19 16:54:04.842000 2017-03-19 16:54:04.831000 13708672 1250000 systimstamp: 2017-03-19 16:54:05.810000 2017-03-19 16:54:05.476000 13709932 1260000 systimstamp: 2017-03-19 16:54:07.097000 2017-03-19 16:54:07.078000 13710580 1270000 systimstamp: 2017-03-19 16:54:09.110000 2017-03-19 16:54:09.010000 13711655 1280000 systimstamp: 2017-03-19 16:54:09.943000 2017-03-19 16:54:09.864000 13713710 1290000 systimstamp: 2017-03-19 16:54:11.496000 2017-03-19 16:54:10.940000 13714700 1300000 systimstamp: 2017-03-19 16:54:13.263000 2017-03-19 16:54:13.164000 13715467 1310000 systimstamp: 2017-03-19 16:54:15.453000 2017-03-19 16:54:15.409000 13717361 1320000 systimstamp: 2017-03-19 16:54:16.233000 2017-03-19 16:54:16.162000 13718973 1330000 systimstamp: 2017-03-19 16:54:18.661000 2017-03-19 16:54:17.115000 13719566 1340000 systimstamp: 2017-03-19 16:54:19.447000 2017-03-19 16:54:19.415000 13720592 1350000 systimstamp: 2017-03-19 16:54:20.398000 2017-03-19 16:54:20.301000 13721208 1360000 systimstamp: 2017-03-19 16:54:21.193000 2017-03-19 16:54:21.166000 13722819 1370000 systimstamp: 2017-03-19 16:54:24.263000 2017-03-19 16:54:23.087000 13724464 1380000 systimstamp: 2017-03-19 16:54:25.022000 2017-03-19 16:54:25.013000 13725229 1390000 systimstamp: 2017-03-19 16:54:25.953000 2017-03-19 16:54:25.867000 13726248 1400000 systimstamp: 2017-03-19 16:54:27.312000 2017-03-19 16:54:27.259000 13728243 1410000 systimstamp: 2017-03-19 16:54:28.353000 2017-03-19 16:54:28.334000 13728530 1420000 systimstamp: 2017-03-19 16:54:30.327000 2017-03-19 16:54:29.298000 13729925 1430000 systimstamp: 2017-03-19 16:54:31.088000 2017-03-19 16:54:31.078000 13730835 1440000 systimstamp: 2017-03-19 16:54:32.056000 2017-03-19 16:54:32.054000 13731660 1450000 systimstamp: 2017-03-19 16:54:32.841000 2017-03-19 16:54:32.798000 13731860 1460000 systimstamp: 2017-03-19 16:54:34.096000 2017-03-19 16:54:34.086000 13733815 1470000 systimstamp: 2017-03-19 16:54:34.952000 2017-03-19 16:54:34.938000 13734925 1480000 systimstamp: 2017-03-19 16:54:36.869000 2017-03-19 16:54:35.471000 13735541 1490000 systimstamp: 2017-03-19 16:54:37.660000 2017-03-19 16:54:37.612000 13736510 1500000 systimstamp: 2017-03-19 16:54:38.664000 2017-03-19 16:54:38.578000 13737069 1510000 systimstamp: 2017-03-19 16:54:39.480000 2017-03-19 16:54:39.442000 13738092 1520000 systimstamp: 2017-03-19 16:54:40.997000 2017-03-19 16:54:40.943000 13740089 1530000 systimstamp: 2017-03-19 16:54:41.764000 2017-03-19 16:54:41.687000 13740445 1540000 systimstamp: 2017-03-19 16:54:42.649000 2017-03-19 16:54:42.552000 13740472 1550000 systimstamp: 2017-03-19 16:54:43.449000 2017-03-19 16:54:43.406000 13740720 1560000 systimstamp: 2017-03-19 16:54:44.229000 2017-03-19 16:54:44.161000 13741558 1570000 systimstamp: 2017-03-19 16:54:45.552000 2017-03-19 16:54:45.549000 13743112 1580000 systimstamp: 2017-03-19 16:54:46.351000 2017-03-19 16:54:46.303000 13744234 1590000 systimstamp: 2017-03-19 16:54:47.241000 2017-03-19 16:54:46.937000 13745738 1600000 systimstamp: 2017-03-19 16:54:49.950000 2017-03-19 16:54:49.941000 13746540 1610000 systimstamp: 2017-03-19 16:54:51.496000 2017-03-19 16:54:51.439000 13748099 1620000 systimstamp: 2017-03-19 16:54:52.329000 2017-03-19 16:54:52.305000 13749349 1630000 systimstamp: 2017-03-19 16:54:55.157000 2017-03-19 16:54:52.827000 13750536 1640000 systimstamp: 2017-03-19 16:54:55.919000 2017-03-19 16:54:55.909000 13751378 1650000 systimstamp: 2017-03-19 16:54:57.906000 2017-03-19 16:54:57.827000 13751929 1660000 systimstamp: 2017-03-19 16:54:59.429000 2017-03-19 16:54:59.333000 13754335 1670000 systimstamp: 2017-03-19 16:55:00.239000 2017-03-19 16:55:00.196000 13755241 1680000 systimstamp: 2017-03-19 16:55:01.243000 2017-03-19 16:55:00.830000 13755717 1690000 systimstamp: 2017-03-19 16:55:02.367000 2017-03-19 16:55:02.359000 13756285 1700000 systimstamp: 2017-03-19 16:55:03.619000 2017-03-19 16:55:03.547000 13756811 1710000 systimstamp: 2017-03-19 16:55:04.596000 2017-03-19 16:55:04.513000 13757864 1720000 systimstamp: 2017-03-19 16:55:06.122000 2017-03-19 16:55:06.113000 13760341 1730000 systimstamp: 2017-03-19 16:55:07.050000 2017-03-19 16:55:06.976000 13761067 1740000 systimstamp: 2017-03-19 16:55:07.823000 2017-03-19 16:55:07.719000 13761273 1750000 systimstamp: 2017-03-19 16:55:08.577000 2017-03-19 16:55:08.474000 13762131 1760000 systimstamp: 2017-03-19 16:55:09.683000 2017-03-19 16:55:09.660000 13763202 1770000 systimstamp: 2017-03-19 16:55:10.461000 2017-03-19 16:55:10.413000 13765247 1780000 systimstamp: 2017-03-19 16:55:12.061000 2017-03-19 16:55:12.010000 13766291 1790000 systimstamp: 2017-03-19 16:55:13.331000 2017-03-19 16:55:12.555000 13766338 1800000 systimstamp: 2017-03-19 16:55:14.095000 2017-03-19 16:55:14.013000 13766695 1810000 systimstamp: 2017-03-19 16:55:15.041000 2017-03-19 16:55:14.978000 13767787 1820000 systimstamp: 2017-03-19 16:55:16.128000 2017-03-19 16:55:16.054000 13768879 1830000 systimstamp: 2017-03-19 16:55:19.659000 2017-03-19 16:55:16.587000 13768911 1840000 systimstamp: 2017-03-19 16:55:20.432000 2017-03-19 16:55:20.337000 13768968 1850000 systimstamp: 2017-03-19 16:55:23.250000 2017-03-19 16:55:23.219000 13769020 1860000 systimstamp: 2017-03-19 16:55:24.195000 2017-03-19 16:55:24.192000 13769099 1870000 systimstamp: 2017-03-19 16:55:26.133000 2017-03-19 16:55:26.112000 13769137 1880000 systimstamp: 2017-03-19 16:55:27.942000 2017-03-19 16:55:27.832000 13769241 1890000 systimstamp: 2017-03-19 16:55:31.527000 2017-03-19 16:55:31.469000 13769272 1900000 systimstamp: 2017-03-19 16:55:32.301000 2017-03-19 16:55:32.222000 13769299 1910000 systimstamp: 2017-03-19 16:55:33.093000 2017-03-19 16:55:33.077000 13769342 1920000 systimstamp: 2017-03-19 16:55:33.888000 2017-03-19 16:55:33.863000 13769388 1930000 systimstamp: 2017-03-19 16:55:38.504000 2017-03-19 16:55:38.407000 13769493 1940000 systimstamp: 2017-03-19 16:55:42.842000 2017-03-19 16:55:42.799000 13769633 1950000 systimstamp: 2017-03-19 16:55:47.738000 2017-03-19 16:55:47.691000 13769705 1960000 systimstamp: 2017-03-19 16:55:49.271000 2017-03-19 16:55:49.190000 13769759 1970000 systimstamp: 2017-03-19 16:55:50.967000 2017-03-19 16:55:50.915000 13769785 1980000 systimstamp: 2017-03-19 16:55:51.767000 2017-03-19 16:55:51.759000 13769829 1990000 systimstamp: 2017-03-19 16:55:52.774000 2017-03-19 16:55:52.719000 13769877 2000000 systimstamp: 2017-03-19 16:55:58.731000 2017-03-19 16:55:58.721000 13769907 101 rows selected Elapsed: 00:00:00.241 |
You can see there’s practically no difference between the SYSTIMESTAMP recorded in DATA and the INDEX_UPDATE_TS column, so that’s as expected. However, notice that when comparing the FTS_UPDATE_SCN to INDEX_UPDATE_SCN that the FTS_UPDATE_SCN is before any of the index scans???
Update: 2017-05-04
The above piece where FTS_UPDATE_SCN was *before* any of the index scans really threw me for a loop there for a while! About a month after doing the initial experiment, though, I stumbled across a piece in the documentation.
So basically what happened, is Oracle started the update, got the information from the v$database view, then went on updating….the read consistency was foiled by the index scan….so Oracle kept “chugging,” continuing to lock rows…then when Oracle restarted the update, it said “peh, I’m not even going to bother trying to be read-consistent with v$database, because it’s not guaranteed to be read-consistent anyways.”
Apparently a great deal of overhead goes into making things read-consistent, and much of the data in v$views changes *very* rapidly, and in the interest of efficiency, Oracle says “we’ll omit all the locks and latches that would be needed to make sure these views are read-efficient.”
Mystery solved! Although I might do a third one in this series that explores other methods of getting the current SCN (such as dbms_flashback.get_system_change_number),