tag:blogger.com,1999:blog-5309604992043123290.post3567624408664639469..comments2024-03-24T11:22:17.354+01:00Comments on berxblog: Doing it better ... (Part 1)Martin Bergerhttp://www.blogger.com/profile/16504572924713610305noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-5309604992043123290.post-47452967671932541972008-12-28T21:42:00.000+01:002008-12-28T21:42:00.000+01:00flado,we had no serializing problems, as the appli...flado,<BR/>we had no serializing problems, as the application had initial only one (!) connection, at some stress-tests, there where only 4 concurrent connections. So serialization problems came never to our mind an where not visible during our testcases. <BR/>My goal was to avoid any unnecessary switch between SQL and PL/SQL engine, and also to keep (soft)parsing short.<BR/>I never thought about SKIP LOCKED, and I'm glad I hadn't to do, as it looks complex in my environment.<BR/>Maybe you just want to summarise your learings somewhere, as I'm really searching for additional informations, anywhere ;-)Martin Bergerhttps://www.blogger.com/profile/16504572924713610305noreply@blogger.comtag:blogger.com,1999:blog-5309604992043123290.post-91394679740116517972008-12-23T15:16:00.000+01:002008-12-23T15:16:00.000+01:00I made some tests and came up with this:"Skip...I made some tests and came up with this:<BR/>"Skip locked" does not work as intuitively expected with rownum and is undocumented.<BR/>The single-statement update serializes on the pool level, which means your transactions must be very short to decrease contention.<BR/><BR/>This serializes on the individual resource and can therefore work for longer transactions as well (sorry, no code tags allowed):<BR/><BR/> for x in (<BR/> SELECT "RESOURCE"<BR/> FROM RESOURCE_POOL <BR/> WHERE v_time>FREED_TIME <BR/> AND STATUS='FREE' <BR/> AND POOL=v_pool for update skip locked) <BR/> loop<BR/> v_resource:=x."RESOURCE";<BR/> exit;<BR/> end loop;<BR/> UPDATE RESOURCE_POOL set <BR/> STATUS='INUSE', <BR/> FREED_TIME=null, <BR/> START_TIME=sysdate<BR/> WHERE "RESOURCE"=v_resource;<BR/><BR/>Depending on how the app uses this "resource allocator", it may be feasible to just keep a resource's row locked while it is in use - and get rid of the STATUS column altogether...<BR/><BR/>Thanks for the post, I learned quite a bit from it :-) !<BR/><BR/>Schöne Feiertage!Fladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.comtag:blogger.com,1999:blog-5309604992043123290.post-8117319723165358122008-12-23T14:33:00.000+01:002008-12-23T14:33:00.000+01:00Dieser Kommentar wurde vom Autor entfernt.Fladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.comtag:blogger.com,1999:blog-5309604992043123290.post-30702710780271938962008-12-23T10:48:00.000+01:002008-12-23T10:48:00.000+01:00Aren't you in fact serializing at the POOL level i...Aren't you in fact serializing at the POOL level instead of at individual RESOURCE level? I mean, all your sessions will try to reserve the same resource (they don't see each other's changes until committed, and they use the same plan) - the one that happens to be selected first (rownum=1). <BR/>If that's the case, the first one will come back in 10ms, but what about the 10th session?<BR/>That's where the SKIP LOCKED would prove handy, methinks. Have you checked that it does in fact become "the perf bottleneck"?<BR/>Failing that, you could design some kind of scheme that would allow you to select different eligible resources in each session (maybe sub-dividing pools into ranges and hashing the SID to determine a range).Fladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.com