tag:blogger.com,1999:blog-7395977411859619892.post3375155891102694659..comments2015-03-27T07:48:17.164+01:00Comments on About Oracle: enq: JI - contentionRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-7395977411859619892.post-46453698609947182382010-02-15T17:15:04.586+01:002010-02-15T17:15:04.586+01:00Hello Rob,
What about partitioning the percentage...Hello Rob,<br /><br />What about partitioning the percentages_per_year table and the materialized view in order to have PCT. Would that relieve contention?HansPhttp://www.blogger.com/profile/09820861226969525729noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-3417625192350430852010-01-17T10:06:59.983+01:002010-01-17T10:06:59.983+01:00Hi Toon,
I just checked it to be sure. In the cas...Hi Toon,<br /><br />I just checked it to be sure. In the case of four MV&#39;s, one for each year, the four processes are each updating (MERGE INTO &quot;RWIJK&quot;.&quot;MV200n&quot; ...) all MV&#39;s. With even more contention.<br /><br />Having an MV per parent row, wouldn&#39;t be a practical solution anyway.<br /><br />Regards,<br />Rob.Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-78805647311902788692010-01-16T19:53:03.482+01:002010-01-16T19:53:03.482+01:00Hi Rob,
Interesting experiment.
Of course if tw...Hi Rob,<br /><br />Interesting experiment.<br /><br /><br />Of course if two TX&#39;s affect the same year, serialization needs to be done.<br />But, as you probably are aware of, implementation of this business rule need not cause any serialization between your transactions, as these transactions all affect *different* years, and the rule is such that different transactiond affecting different Toon Koppelaarshttp://www.blogger.com/profile/08214478545420378529noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-87790590709088070802010-01-16T19:32:26.878+01:002010-01-16T19:32:26.878+01:00Hi Narendra,
Yes you always need to serialize som...Hi Narendra,<br /><br />Yes you always need to serialize somehow. But in this case every table access needs to be serialized, whereas a trigger or api approach needs to be serialized only along some parent row, which is MUCH more scalable. In the case above serializing per year is already sufficient. So four transactions, each for a different year, do not need to be serialized.<br /><br />BecauseRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-37366823218178978602010-01-16T18:41:47.832+01:002010-01-16T18:41:47.832+01:00Rob,
Thanks. Learned something new today. But you...Rob,<br /><br />Thanks. Learned something new today. But your last few statements confuse me:<br /><i>But it can be a big problem when applied to a table that modifies a lot concurrently. <br /><br />So be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed. Or be prepared to be Narendrahttp://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-75981686159254743232010-01-16T09:55:02.342+01:002010-01-16T09:55:02.342+01:00Hi Randolf,
It&#39;s only the commit that is seri...Hi Randolf,<br /><br />It&#39;s only the commit that is serialized. But in OLTP databases, the update is typically immediately followed by the commit, so the end user will report that the update is slow.<br /><br />I have edited the text, because the original text was indeed not clear enough.<br /><br />Thanks for your comment!<br /><br />Regards,<br />Rob.Rob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-2828851929227837322010-01-15T23:20:45.989+01:002010-01-15T23:20:45.989+01:00Rob,
just for clarification: Are the UDDATE DMLs ...Rob,<br /><br />just for clarification: Are the UDDATE DMLs themselves serialized?<br /><br />I wonder, since this is a on commit MV - the contention should be caused by the COMMIT, not the by the update itself?<br /><br />So the question is: Do multiple DMLs within one transaction serialize, or does only the COMMIT serialize on the refresh?<br /><br />Of course this doesn&#39;t make the Randolfhttp://www.blogger.com/profile/13463198440639982695noreply@blogger.com