tag:blogger.com,1999:blog-755542794415995865.post5981992750501394981..comments2023-09-23T07:13:10.004-07:00Comments on DB Optimizer: Cursor_sharing : a picture is worth a 1000 wordsKyle Haileyhttp://www.blogger.com/profile/13586511268045480856noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-755542794415995865.post-73730728287777027962010-07-18T12:31:37.184-07:002010-07-18T12:31:37.184-07:00see also
http://blogs.oracle.com/optimizer/see also<br />http://blogs.oracle.com/optimizer/Kyle Haileyhttps://www.blogger.com/profile/13586511268045480856noreply@blogger.comtag:blogger.com,1999:blog-755542794415995865.post-5161225969187944832010-06-10T01:29:56.483-07:002010-06-10T01:29:56.483-07:00Kyle,
I think the main issue with the CURSOR_SHAR...Kyle,<br /><br />I think the main issue with the CURSOR_SHARING is that it creates the child cursors unconditionally if the bind is marked as unsafe - so for each unique literal value you end up with a separate child cursor, but this is independent from the execution plan generated - so you might end up with literally thousand(s) of child cursors all having the same execution plan.<br /><br />This is were Adaptive Cursor Sharing is different, since it tries to "group" the input values to minimize the number of child cursors.<br /><br />However the potential drawback is that it will need a few "bad" executions to have Adaptive Cursor Sharing kick in which might not be acceptable in all cases since these "bad" executions might bring your system already to a halt.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-755542794415995865.post-67001761136573771352010-06-09T20:24:01.542-07:002010-06-09T20:24:01.542-07:00Interesting to note that the same problem shows up...Interesting to note that the same problem shows up for equality<br />SELECT count(*) FROM t1 where c1=val1 and c2=val2<br />As Dion Cho pointed out in one of your links, maybe it has something as well to do with table stats missing, as I didn't create stats on the table.<br />Which ever the case, it was definitely considered a bug back when I was at Oracle in 2005. Surprised to see similar is still an issue in 11gR2<br />At one point if I recall correctly, a cursor was only allowed to have around 1000 children and after that it got errors, of the genre ORA-600. On 11gR2 I saw up to 2500 child cursors in the above example<br />Thanks for the note and linksKyle Haileyhttps://www.blogger.com/profile/13586511268045480856noreply@blogger.comtag:blogger.com,1999:blog-755542794415995865.post-43666462404205943182010-06-09T12:25:53.606-07:002010-06-09T12:25:53.606-07:00Kyle,
I don't think that this is a "bug&...Kyle,<br /><br />I don't think that this is a "bug" per se, but intended behaviour of CURSOR_SHARING=SIMILAR.<br /><br />There are more reasons why the child cursor won't be shared than histograms. Basically anything that could lead to a different execution plan might lead to a bind variable being marked as "unsafe" which can be seen from some flag in the 10046 trace file if I remember correctly.<br /><br />As far as I know this is the list of reasons that lead to "unsafe" binds:<br /><br />- predicates on columns with histograms<br />- range based predicates<br />- predicates on partition keys<br />- Dynamic Sampling<br /><br />For more details, see e.g.<br /><br /><a href="http://forums.oracle.com/forums/thread.jspa?messageID=3493934&#3493934" rel="nofollow">Oracle Forums thread 1</a><br /><br /><a href="http://forums.oracle.com/forums/thread.jspa?messageID=3826559&#3826559" rel="nofollow">Oracle Forums thread 2</a><br /><br />So your example above seems to fall into the category "range-based predicates".<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.com