tag:blogger.com,1999:blog-7395977411859619892.post1715074278381416279..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: Three new "hints"Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-7395977411859619892.post-67293831489876543712011-05-04T11:23:19.708+02:002011-05-04T11:23:19.708+02:00Hint: RETRY_ON_ROW_CHANGE was mentioned in 10g &q...Hint: RETRY_ON_ROW_CHANGE was mentioned in 10g "Bug 6082734 Dump (kdr9igtl) accessing a row from an IOT" for IOT. For update restart, I tried to compose a Blog: Update Restart and new Active Undo Extent (<a href="http://ksun-oracle.blogspot.com/2011/05/update-restart-and-new-undo-extent.html" rel="nofollow">http://ksun-oracle.blogspot.com/2011/05/ksunhttps://www.blogger.com/profile/02069522742393793364noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-51676427238050297412011-03-27T20:23:53.438+02:002011-03-27T20:23:53.438+02:00Thanks for these additions, ksun. I'm still no...Thanks for these additions, ksun. I'm still not convinced as to why you would really need this hint, though.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-18993373090221020122011-03-24T15:40:19.889+01:002011-03-24T15:40:19.889+01:00If session 1 updates as:
update /*+ retry_on_row_...If session 1 updates as:<br /><br />update /*+ retry_on_row_change */ t<br />set col = col * -1<br />where id = col and id != 40000;<br /><br />and session 2 updates before session 1 hits this data block (the blcok with id = 40000) by:<br /><br />update t<br />set col = 888888 <br />where id = 40000;<br /><br />then the update restarted, and performance is worse:<br /><br />Sqlplus> select * ksunhttps://www.blogger.com/profile/02069522742393793364noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-75614603012598886052011-03-23T11:18:29.560+01:002011-03-23T11:18:29.560+01:00It looks like that RETRY_ON_ROW_CHANGE is an optim...It looks like that RETRY_ON_ROW_CHANGE is an optimization of write consistency detection <br />in the block level, instead of traditional row level.<br /><br />Following query probably justify it. <br /><br />with block_stats as<br /> (select dbms_rowid.rowid_block_number (t.rowid) block#<br /> ,dbms_rowid.rowid_row_number (t.rowid) row#<br /> ,t.*<br /> from t)<br />selectksunhttps://www.blogger.com/profile/02069522742393793364noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-36910811216951055922009-12-22T09:12:51.532+01:002009-12-22T09:12:51.532+01:00The functionality of IGNORE_ROW_ON_DUPKEY_INDEX co...The functionality of IGNORE_ROW_ON_DUPKEY_INDEX could already be achieved in yeat another way - with MERGE:<br /><br />http://www.colloperate.com/2009/12/oracle-is-strange.html<br /><br />MERGE would seem a much more elegant way to do the same thing, since it makes the intent more explicit and much clearer.<br /><br />Oracle's process for including new features really leaves me scratching my Anthony Wilsonhttps://www.blogger.com/profile/17220918714910236998noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-32830745349216219312009-12-10T10:06:59.356+01:002009-12-10T10:06:59.356+01:00I also think RETRY_ON_ROW_CHANGE is useful in Edit...I also think RETRY_ON_ROW_CHANGE is useful in Edition-Based Upgrade.<br />They 3 are in the same category--Online Application Upgrade Hints.<br /><br />--viadeazhuOpenKBhttps://www.blogger.com/profile/02892129494774761942noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-9820019601179428372009-11-21T14:10:53.086+01:002009-11-21T14:10:53.086+01:00I'd bet the IGNORE_ROW_ON_DUPKEY_INDEX hint w...I'd bet the IGNORE_ROW_ON_DUPKEY_INDEX hint was added because (1) 10gR2's DML Error Logging doesn't log unique key violations and (2) the venerable EXCEPTIONS INTO clause allows the violations in. So the hint is allows Oracle to "take the first one" and reject the rest http://tkyte.blogspot.com/2005/04/understanding.htmlDukeGanotehttps://www.blogger.com/profile/01714681520904502051noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-26225150089571588442009-11-04T22:47:33.724+01:002009-11-04T22:47:33.724+01:00Yes, that's a good guess: it's true. But w...Yes, that's a good guess: it's true. But when would that behaviour be desirable?Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-46952458562098544262009-11-04T08:56:52.999+01:002009-11-04T08:56:52.999+01:00My guess - the standard write consistency mechanis...My guess - the standard write consistency mechanism triggers a restart if the columns referenced in the statement (or triggers)change, the RETRY_ON_ROW_CHANGE, being based on the ora_rowscn, restarts if ANY column has been modified.Alberto Dell'Erahttp://www.adellera.it/blognoreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-65300061402970194382009-10-31T00:21:49.935+01:002009-10-31T00:21:49.935+01:00Yes, the hint must have something to do with editi...Yes, the hint must have something to do with editions. The hint is mentioned in Bryn Llewellyn's white paper about editions. He writes in footnote 62:<br /><br />To do... explain the circumstances when the Retry_On_Row_Change hint is useful.<br /><br />(Did he publish the white paper unfinished?) I hope he will do his "to do" someday ...Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-31006553568432271292009-10-30T07:10:46.760+01:002009-10-30T07:10:46.760+01:00Back to the original question - a user case for RE...Back to the original question - a user case for RETRY_..., I'm guessing it will be related to editioning, seeing as the other two are also mentioned heavily in that context. <br /><br />I'm thinking something like a cross-edition trigger may need to get a "fuller" restart by using the hint rather than the default offered by standard write consistency.<br /><br />(Just a guess Connor McDonaldhttps://www.blogger.com/profile/06246356571229889735noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-52972178692585023862009-10-29T15:57:53.277+01:002009-10-29T15:57:53.277+01:00Good catch, Laurent.
Learnt something new. Thanks...Good catch, Laurent.<br /><br />Learnt something new. Thanks!Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-20312156613393121542009-10-29T15:31:42.027+01:002009-10-29T15:31:42.027+01:00In the same category, REWRITE_OR_ERROR appeared in...In the same category, REWRITE_OR_ERROR appeared in 10g<br />http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/qradv.htm#BABIAIHJLaurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-59796453347885533452009-10-29T14:54:42.414+01:002009-10-29T14:54:42.414+01:00Yeah, the mixing of the two types of hints is a ba...Yeah, the mixing of the two types of hints is a bad idea.<br /><br />Handling them is nice though. Well, the first one, not breaking, is very nice. When processing data from two different sources it is quite convenient to just INSERT the new stuff without having to write a NOT EXISTS for the very same TABLE which adds to the operation.Brian Tkatchhttps://www.blogger.com/profile/11320700842381820277noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-44452879868026353272009-10-29T10:23:18.890+01:002009-10-29T10:23:18.890+01:00All three seem, to some extent, puzzling with alte...All three seem, to some extent, puzzling with alternatives which would be clearer to the developer.<br /><br />It's hard to see how they passed any sort of prioritisation board.<br />I can only think that they helped meet some obscure internal dev requirement or to help beat some benchmark, etc.DomBrookshttps://www.blogger.com/profile/02598622186013843759noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-9334810734979718462009-10-29T02:56:00.789+01:002009-10-29T02:56:00.789+01:00Man! This mixing up of optimizer and semantic hint...Man! This mixing up of optimizer and semantic hints is gonna cause soooo many troubles...<br />Why oh why, didn't they just add a session parameter for each of these, to be set as needed?<br />One of the most bone-headed "evolutions", and I hope I'm being really optimistic...Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.com