tag:blogger.com,1999:blog-5124641802818980374.post8134932284539925778..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Read Consistency, "ORA-01555 snapshot too old" errors and the SCN_ASCENDING hintUnknownnoreply@blogger.comBlogger12125tag:blogger.com,1999:blog-5124641802818980374.post-52383543399970135142009-11-23T18:30:02.395+01:002009-11-23T18:30:02.395+01:00If I put all the columns in the cursor and within ...If I put all the columns in the cursor and within the loop I process the row and then do an UPDATE conditional on all the columns in the table being the same as in the cursor, I'd only have to do SELECT FOR UPDATE and process the new version of row if the SQL%ROWCOUNT for the UPDATE was zero. If I move all the processing to a private procedure, that would be straightforward. Odds are against the data having been changed, so having to process the same row a second time (to process the updated data) would be rare. <br /><br />Cool idea. Thanks!Mark Freeman (@m60freeman)https://www.blogger.com/profile/11475488917130342878noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-21374114542572940062009-11-20T22:15:17.574+01:002009-11-20T22:15:17.574+01:00Mark,
> I could read all fields and
> comp...Mark,<br /><br />> I could read all fields and <br />> compare them in the UPDATE, but <br />> that would cause the UPDATE to be <br />> skipped if the record had been <br />> changed. I don't want to skip <br />> processing the row, but be sure I <br />> am processing the latest version <br />> of it<br /><br />probably my explanation of my intended approach wasn't clear enough:<br /><br />- You use all the fields in the WHERE clause of the UPDATE statement to make sure that you only update the row if it hasn't been modified<br />- Should the update return 0 affected rows, you know the row has been modified (or deleted)<br />- In this case you don't skip the row, but start a special processing for this particular row, that is either a SELECT FOR UPDATE to make sure that no one else can modify the row, or another nested loop that reads the row content and attempts to update again with all fields in the WHERE clause until the update returns one affected row (or the row is no longer available).<br /><br />This way you save the overhead of querying each and every record.<br /><br />If you expect only few records to be updated in the meanwhile this might save you some overhead - if you think that the majority of rows will be updated anyway, you can go for your approach of querying each and every record.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-46611684017609326572009-11-20T16:45:27.389+01:002009-11-20T16:45:27.389+01:00Randolph,
Thanks for the detailed response. It so...Randolph,<br /><br />Thanks for the detailed response. It sounds like the hint will be useful in my situation without causing any problematic side-effects. I have no control or influence over the UNDO settings in the production environment so I just have to make do as best I can. Processing in smaller chunks would be a reasonable approach (fire off multiple jobs, each processing a specific range of PK values), but if the hint will solve the problem, that seems far simpler.<br /><br />As for the off-topic issues I raised, I am sure that ROWDEPENDENCIES has not been enabled on the table. I could read all fields and compare them in the UPDATE, but that would cause the UPDATE to be skipped if the record had been changed. I don't want to skip processing the row, but be sure I am processing the latest version of it. So I think I am better off with doing the extra read at the top of the loop to read and lock the one record and then COMMIT after each UPDATE. I sacrifice some speed, but I gain both thoroughness (all records get updated if needed) and safety (I won't accidentally overwrite unprocessed user-updated data with processed older data).Mark Freeman (@m60freeman)https://www.blogger.com/profile/11475488917130342878noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-13246497412283074972009-11-20T01:38:35.080+01:002009-11-20T01:38:35.080+01:00Mark,
I'm not going to raise here the questio...Mark,<br /><br />I'm not going to raise here the question why it takes such a long time to process the data.<br /><br />With your approach you're basically facing different issues:<br /><br />- The long running query might run into ORA-01555 errors if the undo required to re-construct the read consistent version of blocks accessed gets overridden. The SCN_ASCENDING hint might help here, yes. Since you're not interested in the read consistent view of data as you say, this might be an option to avoid the error.<br /><br />Another option might be to switch your UNDO to retention guarantee while performing this batch run, requiring potentially a huge UNDO tablespace and the risk of failing DML activities in case of UNDO size issues.<br /><br />Another potential option would require you to be able to identify rows that have already been reconciled - in that case you could process your data in smaller chunks, lowering the risk of running into ORA-01555 errors - but with the potential issue of increased run time and I/O if you don't find an efficient way of reading the chunks / identifying already reconciled data.<br /><br />So much about the CURSOR FOR loop you use to drive your batch.<br /><br />- What you're trying to achieve regarding updating only the latest version of the record: What has been described in this post doesn't help you in that matter.<br /><br />Usually you should attempt to go for an "optimistic" locking approach. That means you can either use a timestamp value (or from 10g on the ORA_ROWSCN pseudo-column but that required you to create the table with ROWDEPENDENCIES enabled which you probably won't have) or fetch all fields, and while attempting to update an particular row you would compare that timestamp or all fields to the current row content, something like:<br /><br />UPDATE TABLE SET ...<br />WHERE PK = fetched_ID<br />AND ORA_ROWSCN = fetched_ORA_ROWSCN<br /><br />-- or using all fields approach<br />(<br />AND field1 = fetched_field1<br />AND field2 = fetched_field2<br />AND field3 = fetched_field3<br />(taking NULLs into account etc.)...<br />)<br /><br />If the update modifies 0 rows, you know that the row has been modified by someone else in the meantime.<br /><br />Then you can try to re-query that particular row, if you like with FOR UPDATE to prevent anyone else from updating it while you do, and perform then your UPDATE, or you keep the optimistic approach for that row and loop until the row is left unmodified.<br /><br />This approach avoids to re-query every row before performing the update, which would be the other option, as you mention.<br /><br />By using the optimistic locking you will update all those rows which have not been modified without the need to requery it, which should help to speed up that part of the job.<br /><br />How often you attempt to COMMIT has nothing to do with how to get the latest version of the data, but simply controls how other users are able to modify the data modified by your batch job - concurrency so to say.<br /><br />It also influences to a certain degree the speed of your batch run, the amount of redo generated and the load caused by your batch run.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-35025919548489015402009-11-19T17:59:48.564+01:002009-11-19T17:59:48.564+01:00Rudolph,
I want to be sure I understand the conce...Rudolph,<br /><br />I want to be sure I understand the concept in your article as it applies to a real-world situation.<br /><br />I have to run a one-time validation script on an table of mailing addresses. There is a cursor that does a full table scan on this table. The cursor is accessed via a FOR loop. For each record, the script does various look-ups using other tables and based upon what it finds it will either update the address record or insert a record in an exception table. The script does a COMMIT after every 20,000 iterations of the loop. This script takes 12 - 24 hours to run. <br /><br />While the script is running, application users are free to update addresses in the table. We do not want to block them from making these updates, nor do we want to ignore these updates. <br /><br />When this script processes each record, it needs to see the most recently committed version of that record, regardless of what that record looked like when the cursor was first opened. In other words, if a user updated an address after the script started but before the loop gets to that record, the new version of the record is what should be processed by the script. <br /><br />Would use of the /*+ scn_ascending */ hint help me achieve the goal? <br />Would I be better off to COMMIT after processing each record?<br />Would doing a "SELECT ... INTO ... FROM address WHERE pk = cursor_rec.pk FOR UPDATE" for each record before processing the record help (or would it still see the original version of the record)?Mark Freeman (@m60freeman)https://www.blogger.com/profile/11475488917130342878noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-46304305986283530552009-04-11T23:53:00.000+02:002009-04-11T23:53:00.000+02:00@Madhu:I definitely would think more than twice be...@Madhu:<BR/><BR/>I definitely would think more than twice before using this scary and questionable hint, even if you say "we don't care about consistency at all" which sounds to me a bit odd (it sounds a bit like "we don't care about our content at all").<BR/><BR/>Using complex PL/SQL functions on such a data volume is for sure not a good idea in terms of performance. You probably could benefit most from replacing the PL/SQL logic with a similar plain SQL solution which is usually much, much faster.<BR/><BR/>@Daniel:<BR/>I think that it's for sure possible to read the same row twice even when using a full table scan and the SCN_ASCENDING hint. Consider the following testcase, which is a modified version of the first one from the post:<BR/><BR/>-- code start<BR/><BR/>drop table t purge;<BR/><BR/>drop table t_2 purge;<BR/><BR/>create table t<BR/>as<BR/>select<BR/> a.*, 1 as my_id<BR/>from<BR/> all_objects a<BR/>order by<BR/> dbms_random.random;<BR/><BR/>alter table t add constraint t_pk primary key (object_id);<BR/><BR/>exec dbms_stats.gather_table_stats(null, 'T', cascade=>true)<BR/><BR/>create table t_2<BR/>as<BR/>select * from t<BR/>where 1 = 2;<BR/><BR/>create or replace function wait_sec(<BR/> in_row_identifier in number<BR/>, in_wait_sec in number default 1<BR/>, in_dbms_output in boolean default false<BR/>) return number<BR/>as<BR/>begin<BR/> if in_dbms_output then<BR/> dbms_output.put_line('Row: ' || in_row_identifier);<BR/> end if;<BR/> dbms_lock.sleep(in_wait_sec);<BR/> return in_row_identifier;<BR/>end;<BR/>/<BR/><BR/>-- session 1<BR/>declare<BR/> snapshot_too_old exception;<BR/> pragma exception_init(snapshot_too_old, -1555);<BR/> no_space_left exception;<BR/> pragma exception_init(no_space_left, -30036);<BR/>begin<BR/> for i in 1..100 loop<BR/> begin<BR/> execute immediate 'truncate table t_2';<BR/> insert /*+ append */ into t_2<BR/> select<BR/> *<BR/> from<BR/> t<BR/> where<BR/> rownum <= 2000;<BR/> commit;<BR/> delete<BR/> from<BR/> t<BR/> where<BR/> object_id in (<BR/> select<BR/> object_id<BR/> from<BR/> t_2<BR/> );<BR/> commit;<BR/> insert into t<BR/> select<BR/> a.*<BR/> from<BR/> t_2 a<BR/> order by<BR/> dbms_random.random;<BR/> commit;<BR/> exception<BR/> when snapshot_too_old or no_space_left then<BR/> commit;<BR/> end;<BR/> dbms_lock.sleep(0.5);<BR/> end loop;<BR/>end;<BR/>/<BR/><BR/>-- session 2<BR/>select /*+ scn_ascending */<BR/> count(*) as cnt<BR/> , object_id<BR/> , max(substr(object_name, 1, 5)) as dummy1<BR/> , count(distinct fetch_slow) as dummy2<BR/>from (<BR/> select<BR/> object_name<BR/> , object_id<BR/> , wait_sec(object_id, 0.01) as fetch_slow<BR/> from<BR/> t<BR/>)<BR/>group by object_id<BR/>having count(*) > 1;<BR/><BR/>-- code end<BR/><BR/>The "session 1" replaces the table content in chunks (due to the limited undo space in the "UNDO_SMALL" tablespace available), and "session 2" slows down the processing artificially using a simple PL/SQL function while looking for duplicates of the primary key. Guess what it's going to return? It ought to be no rows, but it does return multiple duplicates.<BR/><BR/>But I think your point is heading in the right direction:<BR/>Consider a index access traversing the index root block -> branch blocks -> leaf blocks. It probably will be a mess if some of these index blocks are consistent and others are not.<BR/><BR/>If an index is used to filter data and the table block where the index entry points to is inconsistent with the index leaf block, wrong data might be returned.<BR/><BR/>Likewise, consider an execution plan that depends on the sort order of an index (no SORT ORDER BY operation, e.g. a FIRST_ROWS_n plan using the index to return data in a guaranteed order).<BR/><BR/>Now if some of the index leaf blocks are inconsistent, or the table blocks that the index points to are inconsistent then this potentially returns the data in the wrong order (or wrong data anyway), and therefore would lead to a wrong result set.<BR/><BR/>So, yes, there seem to be good reasons why this can't be used with indexes.<BR/><BR/>This basically begs for more tests, e.g. what happens if an IOT is involved, may be having an OVERFLOW segment? A index fast full scan might be supported, but what happens to the overflow lookup? Hopefully IOTs are not supported at all, but I haven't tested it yet.<BR/><BR/>@Hemant:<BR/>I don't have a clue who initially requested this "enhancement", but as I've mentioned in the blog post I agree that it is a scary feature and I can't think of a reasonable justification why it should be used.<BR/><BR/>Do you think I should make this point clearer in the post?<BR/><BR/>It would be interesting to see which point release initially knows about it. In 9.2.0.8 "SCN_ASCENDING" is not mentioned in V$RESERVED_WORDS, but it does apparently in 10.2.0.4. I don't have at present a pre-10.2.0.4 point release at hand to check previous 10g releases for the keyword.<BR/><BR/>I was thinking more than twice if I should publish the post at all, but it has already been reported multiple times and it is officially documented on MetaLink, so I thought it's better to inform than to keep quiet about it.<BR/><BR/>But I agree that probably there is the possibility of mis-use, simply because some people don't understand the reason for the "snapshot too old" error and are happy to have found a way to avoid it...<BR/><BR/>Best Regards,<BR/>RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-46303853557445412682009-04-11T09:15:00.000+02:002009-04-11T09:15:00.000+02:00Do we know which Oracle customer raised the Enhanc...Do we know which Oracle customer raised the Enhancement Request and how it was justified ?<BR/>Letting the Hint loose in the public domain means giving licence to developers to write code that might break the much revered Read Consistency model, doesn't it ?Hemant K Chitalehttps://www.blogger.com/profile/07369112096230549250noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-86797647518454746212009-04-11T05:15:00.000+02:002009-04-11T05:15:00.000+02:00Randolf, Thanks for pointing out, I think you are ...Randolf, <BR/><BR/>Thanks for pointing out, I think you are right - Oracle will ignore the snapshot too old and proceed further instead of erroring. So I guess, I have less to be excited about :-(<BR/><BR/>Part of the long query problems that we have are due to the way the tables are designed, and some are due to queries hitting complex/convoluted plsql code (packages procedures returning collections, for example). But these are long term fixes, that I don't have control on.<BR/><BR/>-MadhuMadhuhttps://www.blogger.com/profile/12156102855609997940noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-58379745767449145472009-04-10T23:31:00.000+02:002009-04-10T23:31:00.000+02:00Randolf,Thanks a lot for this interesting informat...Randolf,<BR/><BR/>Thanks a lot for this interesting information.<BR/><BR/>In my opinion not allowing the combination of index usage and SCN_ASCENDING hint makes perfectly sense. Otherwise the same row could get read multiple times.<BR/><BR/>Let's say we run a index range scan. Row A gets read, it's value in the index is 2. Now an update is running over row A, in parallel to our query. It sets it's value to 100 and commits. Meanwhile we scanned until value 99. Now we will reread row A with value 100. Of course the opposite can happen too.<BR/><BR/>This can not happen when using a full table scan.<BR/><BR/>Best Regards,<BR/>DanielDaniel Reyhttp://www.trivadis.comnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-30091754772439676422009-04-10T22:55:00.000+02:002009-04-10T22:55:00.000+02:00Madhu,are you sure that the queries you've mention...Madhu,<BR/><BR/>are you sure that the queries you've mentioned are efficient? 200 million rows doesn't sound like a volume that should require multiple hours to process, although of course I don't have a clue what your queries are performing, what else in running simultaneously and what hardware you're using and therefore your mileage might vary. <BR/><BR/>If you could speed up the queries you might have less trouble, but as you say, may be the main reason for the queries being slow is that they're permanently hunting down the undo data to provide a read-consistent view of the data.<BR/><BR/>If this is the case you might want to reconsider your current approach how the data is loaded into the tables.<BR/><BR/>By the way, I don't think that simply using the SCN_ASCENDING hint would make your queries much faster, because I assume that Oracle still tries its best to re-construct the blocks as required and only breaks the consistency if the undo data is not available.<BR/><BR/>So in your particular case to benefit from the SCN_ASCENDING hint you might need to use an undo tablespace that is as small as possible to support the transactions, but overwrites the undo data as soon as possible so that Oracle can't re-construct the blocks anymore.<BR/><BR/>It sounds however like an odd approach I have to admit.<BR/><BR/>Best Regards,<BR/>RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-82498991655296653672009-04-10T22:18:00.000+02:002009-04-10T22:18:00.000+02:00Thanks for the breaking news. This is a great feat...Thanks for the breaking news. This is a great feature in our situation, except for the limitation of full table access. <BR/>Our system has queries that run 4 to 6hrs on tables (~200million recs) that change constantly, and sometimes they run like 12hrs. We don't care about consistency at all (it's not financial system!) so this is valuable "feature" for us, as most of the query time is wasted on constructing a read consistent block.Madhuhttps://www.blogger.com/profile/12156102855609997940noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-61275571971821645012009-04-10T08:44:00.000+02:002009-04-10T08:44:00.000+02:00Randolf. It's an amazing and weird new feature. :)...Randolf. <BR/><BR/>It's an amazing and weird new feature. :)<BR/><BR/>As far as I know, this is the first(not sure it would be last) exception to Oracle's proud read consistency model.Dion_Chohttps://www.blogger.com/profile/17799718037719118540noreply@blogger.com