tag:blogger.com,1999:blog-4169710303065679169.post1931454229838943604..comments2024-03-18T21:07:31.080-07:00Comments on A Wider View : BLOG HAS MOVED *<a href="http://blog.orapub.com">HERE</a>*: CBC latches, CPU consumption, and wait timeCraig Shallahamer, President/Founder, OraPubhttp://www.blogger.com/profile/04109635337570098781noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-4169710303065679169.post-87065542843485747812018-05-09T02:35:50.797-07:002018-05-09T02:35:50.797-07:00Thanks for sharing these information. It’s a very ...Thanks for sharing these information. It’s a very nice topic. We are providing online training classes<br /><br /><a href="http://www.ciitnoida.com/best-bca-degree-course-college-in-noida.php" rel="nofollow">Best bca college in noida</a><br /><a href="http://www.ciitnoida.com/best-bca-degree-course-college-in-noida.php" rel="nofollow">Top bca colleges in noida</a><br />ciitnoidahttps://www.blogger.com/profile/13411560277382130436noreply@blogger.comtag:blogger.com,1999:blog-4169710303065679169.post-15111638781914643152011-07-26T02:36:59.384-07:002011-07-26T02:36:59.384-07:00Hi Craig,
Nice article ... again :-)
It gives a go...Hi Craig,<br />Nice article ... again :-)<br />It gives a good understanding of how to look at the CBS latching issue. Important to note that this is not a general way of tuning, but it's related to the latching issue.<br /><br />Lazar ...<br />I don't thing the performance gain you are referencing here is the same as Craig is discussing. You are decreasing the units of work (and yes that is the first thing I would consider facing a problem SQL). What Craig is showing is how to reduce the service time (CPU time) and queue time (wait time) by increasing CBC latches. This is probably not something I would do tuning one single SQL , but rather my system as a whole. Also I would perform a in-depth analysis of my latching contention (and consulting Oracle Support) before changing these hidden parameters.<br /><br />Thanks again Craig.<br />PS! Any news regarding your firefighting book on my kindle? :-)L. Jenssenhttps://www.blogger.com/profile/16945919361267764584noreply@blogger.comtag:blogger.com,1999:blog-4169710303065679169.post-38916738696519811102011-07-22T03:37:51.711-07:002011-07-22T03:37:51.711-07:00Hi Craig,
Awesome article! I will print to PDF no...Hi Craig,<br /><br />Awesome article! I will print to PDF now in case you move this page :)<br />Indeed, we can reduce the elapsed time if we reduce the CPU time per unit of work. <br />Last week I had a similar discussion with our backoffice support. There was one particular statement<br />being executed approximately 500.000 times with total elapsed time of 10 min in "row-by-row" fashion :(<br />Although the cost of the SQL was pretty low, I still thought that I can generate a better execution plan, but reducing the<br />number of operations (access paths remained the same). This SQL had a correlated subquery that was computing MAX() out of the same table ...<br /><br />- Original plan with 8 operations (Note operation 1 & 5 calling same access path)<br /><br />SELECT STATEMENT ALL_ROWS <br />Cost: 2 Bytes: 117 Cardinality: 1 <br /> 8 NESTED LOOPS <br /> 6 NESTED LOOPS Cost: 2 Bytes: 117 Cardinality: 1 <br /> 4 VIEW VIEW SYS.VW_SQ_1 Cost: 1 Bytes: 39 Cardinality: 1 <br /> 3 HASH GROUP BY Cost: 1 Bytes: 21 Cardinality: 1 <br /> 2 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA.MY_TABLE Cost: 1 Bytes: 21 Cardinality: 1 <br /> 1 INDEX RANGE SCAN INDEX SCHEMA.IX01_MY_TABLE Cost: 1 Cardinality: 1 <br /> 5 INDEX RANGE SCAN INDEX SCHEMA.IX01_MY_TABLE Cost: 1 Cardinality: 1 <br /> 7 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA.MY_TABLE Cost: 1 Bytes: 78 Cardinality: 1 <br /><br />consistent gets 9<br />sorts (memory) 0<br />sorts (disk) 0<br /><br />- Modified plan with 4 operations and only 1 access path with "INDEX RANGE SCAN"<br /><br />SELECT STATEMENT ALL_ROWS <br />Cost: 2 Bytes: 161 Cardinality: 1 <br />4 VIEW SCHEMA. Cost: 2 Bytes: 161 Cardinality: 1 <br /> 3 WINDOW BUFFER Cost: 2 Bytes: 78 Cardinality: 1 <br /> 2 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA.MY_TABLE Cost: 1 Bytes: 78 Cardinality: 1 <br /> 1 INDEX RANGE SCAN INDEX SCHEMA.IX01_MY_TABLE Cost: 1 Cardinality: 1 <br /> <br />consistent gets 4<br />sorts (memory) 1 <br />sorts (disk) 0 <br /><br />This means 44% less consistent gets; thus less CPU and decreased elapsed time by 40%; <br />We have lost like 3-4% in the in memory sort operation because of the analytical function.<br /><br />Now, if I manage to convince out backoffice to rewrite this part with BULK, I can go to a vacation with any worries on my mind :):):)<br /><br />Best regards,<br />LazarNakinovhttps://www.blogger.com/profile/03513167449171213014noreply@blogger.com