tag:blogger.com,1999:blog-77778500657283493152024-03-23T06:14:40.670-04:00I wonder...It is simply impossible to stop questioning the Universe!Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.comBlogger63125tag:blogger.com,1999:blog-7777850065728349315.post-65703703854972371802022-08-31T13:24:00.002-04:002022-08-31T13:24:34.178-04:00End of August Thoughts<p>For the last couple of year the end of August means one extra thing (other than sign of relief that soon kids will be back to school :) ): it is time to start thinking about conferences abstracts! Because sooner than later various user groups would start to collect them - and having to come up with something at the very last minute usually a <i>really</i> bad idea.</p><p>And this year is a really strange one - the first post-COVID conferences brought mixed feelings: it was great to see a lot of familiar faces, but... I started to ask myself - what is my audience? whom to I need to talk? You know, I have multiple "hats" - kind-of-DBA, architect, developer, performance tuning specialist... How do I position myself nowadays?</p><p>And you know... I looked back at my kids - and realized something: there will always be a new batch of kids just coming to IT industry! "Buzzwords" may change, "best practices" may mutate... But overall ways of building systems efficiently, being frank and open with yourself and your colleagues - stay the same.</p><p>So, back to basics... I will try to reexamine what I know and what I don't know - and will start sharing here various snippets and ideas. Maybe it will be useful to anybody else - maybe it will just help me thinking clearly... Who knows?!<br /><br />P.s. Today is a deadline for <a href="https://rmoug.org/" target="_blank">RMOUG-2023</a> :) - please, don't miss it! That's a really great event!</p>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-91623876276972447602022-05-31T18:03:00.005-04:002022-05-31T18:03:57.326-04:00Generic Search Screen (and thoughts about blogs vs slides)<p>Talking about things that I missed here while not blogging... Years ago I wrote the whole series of posts about ways we can implement the most common part of any application - generic search screen (I even got some of those examples into my <a href="https://www.amazon.com/Oracle-Performance-Tuning-Tips-Techniques-ebook/dp/B00LI363FE/" target="_blank">"PL/SQL Performance Tuning"</a> book). That was a lot of fun, because it involved a lot of advanced features - object types, Dynamic SQL, XML, pipelined function etc.</p><p>Years later I finally decided to make a single-point-of-reference, where I aggregate all of the thoughts. And you know, I understand why people like PowerPoint - in presentation format it feels much more understandable. At least that's what I've heard from a number of my listeners. </p><p>To be fair, I was surprised - personally I can much quicker go through textual format (book/article) comparing to various visually-enhanced content sources. I can still understand live webinar where you have a chance to ask the author direct questions... But listening to webcasts?! I even tried to do it on higher speed - still not my format. Summary: people are REALLY different :-)</p><p>Anyways, here is a link (if you still care):</p><p><a href="https://www.slideshare.net/MishaRosenblum/building-a-generic-search-screen-using-dynamic-sql" target="_blank">https://www.slideshare.net/MishaRosenblum/building-a-generic-search-screen-using-dynamic-sql</a></p>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-87800604360626154022022-05-30T17:28:00.004-04:002022-05-30T17:28:22.704-04:00Long-term IT survivability - educational impact?Even though Memorial day is a big holiday here in the USA, I am still doing my tech-editing (quiet! I didn't name the book :) ) - got the new chapter a couple of days ago, and the publishing process should run no matter what is going on around the world. And of course, got a couple of thoughts that I wouldn't mind sharing ;)<div><br /></div><div>When you talk about Oracle as a development platform, you can cleanly see two major patterns - either people are covering extremely advanced topics that only other gurus would understand, or people provide very light walk-throughs without providing real depth. As a result, in the IT you often see either over-utilization of Oracle platform or its under-utilization (depending on the type of available workforce).</div><div><br /></div><div>Being educator myself for the last 20 years I often ask myself: how much knowledge is really needed to build systems efficiently? The current trend is that lots of mediocre developers is better, because they are predictable, and because if needed you can get more of them. As a result, wide knowledge of various platforms/tools is valued more than in-depth knowledge of smaller number of tools. I.e. IT education should be focusing on widening of the base + ease of switching from one environment to the other - but IMHO, that's a trap!</div><div><br /></div><div>You cannot constantly jump from one solution to another - eventually you need to be able to provide-long term support and maintenance of existing systems (yes, rewrite is an option, but how often you can find THAT level of budgets?!) And you, know - recently I made a presentation covering that long-term survivability of IT platforms, and I completely forgot to add it to this blog.<br /></div><div><br /></div><div>Here it is:<br /><a href="https://www.slideshare.net/MishaRosenblum/managing-the-earthquake-surviving-major-database-architecture-changes-rev2022">https://www.slideshare.net/MishaRosenblum/managing-the-earthquake-surviving-major-database-architecture-changes-rev2022</a></div><div><br /></div>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-38933419483696681942022-05-25T21:43:00.001-04:002022-05-27T22:09:50.572-04:00Back to live conferences<p>One of the reasons I decided to resurrect this blog is, to be fair, refresh my professional story-telling skills in English. As you may know, my native languages are Russian and Ukrainian, same for my family - and for the last two years I've been working from home. So, my English started to noticeably degrade. </p><p>If you work online - everybody is more or less on their own schedule, so you would rather send email instead of calling, there is no coffee breaks etc. As a result, I started to write a bit more eloquently, but technology conversations lag what I would call "the natural flow": I have to search for correct words instead of searching for the correct intonations.</p><p>And here comes the key topic: why would I care, considering that nowadays significant number of IT people just work from whatever they call their base (home/hotel/summer house/winter house etc)? My answer - because I learn from inter-person communication significantly more than I learn from written text! The way people tell their stories is much more than "just content" - it is all of their feelings/struggles/discoveries. It is a lot of extras that you cannot just dig our from the article or a blog post.</p><p>I've been speaking at various IT conferences for the last 20 years - and it always amazes me how much people undervalue live contact. I understand convenience of your own chair - but stepping out of it could bring you so much more!</p><p>In about a week I will be flying to Las Vegas for the first in-person conference since the beginning of COVID pandemia - <a href="https://questoraclecommunity.org/events/conferences/blueprint4d/" target="_blank">BLUEPRINT 4D</a> (June 6/June 9). I will have my own presentation there on Wednesday at 9 am - "Through the Looking-Glass: Database Views". As you can guess from the title, it will be SQL and PL/SQL-heavy - well, that's my normal style. </p><p>Hopefully, I will not stumble the same way I did at my first Oracle OpenWorld presentation (I had to simulate mic malfunction to buy some time and get back my voice :-) ). Two decades of experience are still two decades! And I really miss conversations with people, I really miss that great feeling of one big community that I had (and to be fair, that kept me within Oracle technology space).</p><p>Summary: person-to-person conversions are fun! Live events forever! :-)</p>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-30777332651034305672022-05-20T20:24:00.065-04:002022-05-25T21:00:17.899-04:00Joys and Nightmares of a Tech EditorObviously, there was a lot of interesting things I did during all of those years since I more or less abandoned this blog. And filling in all holes would be more or less useless (since time had definitely changed). But there are some exceptions (as usual :-) ) - and one of those exceptions is that in 2019 I was pulled back to the book-publishing process! I am still to lazy to write something on my own (although, I have a couple of good drafts, so, well, "never say never") - but I don't mind looking over somebody's shoulder, especially if I care about the topic.<div><br /></div><div>Frankly speaking, over the years I had mixed experiences - people are different, and not all authors take comments/critique in a friendly manner. But this time I lucked out - up to the point that I feel proud that I participated in the first book by <a href="https://www.linkedin.com/in/jon-heller">Joh Heller</a> (I bet, you may recognize this name from various extremely useful posts on <a href="https://stackoverflow.com/users/409172/jon-heller">StackOverflow</a>)</div><div><br /></div><div>But why I decided to write a post about my experience... Because working as a tech editor in Oracle technology space is a very tricky job, and lessons that I learned may be useful to other people. Let's do bullet points (prepping up for conferences!):</div><div><br /></div><div>1. Anybody who've spent at least some time with Oracle tech stack knows that the most common answer on any question - "It depends!" I.e. there are different answers that could be valid within specific ranges of applicability. So, one of the main tasks for the tech editor is to keep the author from providing "universal" answers - only answers+conditions. <br /><br />2. Tech editor must always remember that it is <b>not his book</b>! The author has full rights to have his/her own opinion/style/favorites. As long as they are not factually incorrect, <b>it is OK</b><i style="font-weight: bold;"> </i>to not agree with the author - your role is to keep the author honest in front of the audience. I.e. you can point that something doesn't add up (or in some cases there are other approaches), but whether to follow this route - not your choice!</div><div><br /></div><div>3. Even the best authors are sometimes very sensitive. So, treat them the same way you would talk to your teenagers :) - no direct orders, no harsh comments! Only suggestions, only very polite hints that this-and-that could be better. </div><div><br /></div><div>4. As you all know, teenagers can be VERY irritating from time to time - in that case you speak directly with the publisher (luckily, in this project I didn't have to use that "big hammer", but I had different experience too). Tech editors are hired by the publishing house, not by the author - so, your role is to be a safety check against bad quality books with technical errors.</div><div><br /></div><div>Anyways, that was definitely fun - and here is a link on the current edition (yes, it is a hint :) ) </div><div><br /></div><div><a href="https://www.amazon.com/Pro-Oracle-SQL-Development-Practices-ebook-dp-B07RSK45BH/dp/B07RSK45BH/" target="_blank">Jon Heller, Pro Oracle SQL Development: Best Practices for Writing Advanced Queries</a></div><div><br /></div><div><table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjE0Jn7Ne7vO_zeOiV8whycZDWkttBvJEbyuTMtCI_qidpjrDjj3mnAaPw3nAV4w87fH98dqe_f0LM6PO8jOsQU6_xFHxq6pUjJtKz3L8vAtFt0_ShZ2pls_KVibIH3oUQBwKTF_Akr9m4YGsfNFGhCqm_b8r1YfNymXqcLPYbk4nyyny5v2da_hIBc6Q" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="" data-original-height="698" data-original-width="483" height="347" src="https://blogger.googleusercontent.com/img/a/AVvXsEjE0Jn7Ne7vO_zeOiV8whycZDWkttBvJEbyuTMtCI_qidpjrDjj3mnAaPw3nAV4w87fH98dqe_f0LM6PO8jOsQU6_xFHxq6pUjJtKz3L8vAtFt0_ShZ2pls_KVibIH3oUQBwKTF_Akr9m4YGsfNFGhCqm_b8r1YfNymXqcLPYbk4nyyny5v2da_hIBc6Q=w240-h347" width="240" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"></td></tr></tbody></table><br /><br /><br /><br /><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div><div><br /></div>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-40212461000667222112022-05-16T15:27:00.001-04:002022-05-16T15:27:18.633-04:00Return of a blogger (plus a bit of ORA-7445 [LpxMemFree()+200] )<p>Considering that "conference life" is picking up (two <i>in-person</i> events in June!), it is time to start writing something meaningful (yes, I am back to blogging after years of ignorance)! </p><p>To cover event previews I need a bit more prep time, while today I would like to share a small story (narrow, but very practical!)</p><p>At Dulcian we have a couple of systems still on Oracle 11g (yeah, I understand that it is outdated). And today one of my support people started to complain about ORA-07445. To be precise - ORA-7445 [LpxMemFree()+200]. And you know how much fun is to sort out <i>those</i> errors, especially considering my gut feeling ("I bet, XMLtype is misbehaving!")</p><p>Obviously, first two steps were obvious: Google and MOS. </p><p>Oook... Google gave me a direct hit - and I indeed was right, it was XML parsing issue - <a href="https://support.oracle.com/knowledge/Oracle%20Database%20Products/2220757_1.html" target="_blank">Doc ID 2220757.1</a> </p><p>And in case you don't have MOS access - here is a code snippet to replicate (it should blow up with "end of communication channel" plus corresponding trace file):</p><p> <span style="font-family: courier;">declare<br /></span><span style="font-family: courier;"> lv_text varchar2(6000);<br /></span><span style="font-family: courier;"> lv_temp_xml xmltype;<br /></span><span style="font-family: courier;"> lv_len number := 0;<br /></span><span style="font-family: courier;">begin<br /></span><span style="font-family: courier;"> lv_text := '<LPCNotes Subject="Comments for LPC " Text="'<br /></span><span style="font-family: courier;"> || to_char(sysdate, 'dd-Mon-yy hh24:mi')<br /></span><span style="font-family: courier;"> || ' '<br /></span><span style="font-family: courier;"> ||lpad('A',4033,'A')<br /></span><span style="font-family: courier;"> || '" />';<br /></span><span style="font-family: courier;"> lv_len := length(lv_text);<br /></span><span style="font-family: courier;"> dbms_output.put_line('Length of the 3rd run is '||lv_len);<br /></span><span style="font-family: courier;"> lv_temp_xml := sys.xmltype(lv_text);<br /></span><span style="font-family: courier;"> end;</span><br /></p><p>The proposed solution was to enable some kind of "old parser" - you can do it on either session of system level. Considering that everything else in our system worked fine, I added it via Dynamic SQL just before the problematic code, so it will be less overall system impact:<br /><br /> <span style="font-family: courier;">declare<br /></span><span style="font-family: courier;"> lv_text varchar2(6000);<br /></span><span style="font-family: courier;"> lv_temp_xml xmltype;<br /></span><span style="font-family: courier;"> lv_len number := 0;<br /></span><span style="font-family: courier;">begin</span><br /><span style="font-family: courier;"><b> execute immediate 'alter session set events ''31156 trace name context forever, level 1024''';</b></span><br /><span style="font-family: courier;"> lv_text := '<LPCNotes Subject="Comments for LPC " Text="'<br /></span><span style="font-family: courier;"> || to_char(sysdate, 'dd-Mon-yy hh24:mi')<br /></span><span style="font-family: courier;"> || ' '<br /></span><span style="font-family: courier;"> ||lpad('A',4033,'A')<br /></span><span style="font-family: courier;"> || '" />';<br /></span><span style="font-family: courier;"> lv_len := length(lv_text);<br /></span><span style="font-family: courier;"> dbms_output.put_line('Length of the 3rd run is '||lv_len);<br /></span><span style="font-family: courier;"> lv_temp_xml := sys.xmltype(lv_text);<br /></span><span style="font-family: courier;"> end;</span><br /><br /></p><p>And the whole problem was gone! After a bit more digging I found that this kind of approach could help with a number of other similar ORA-07445 errors if you still on 11.2.0.4 (here is a doc from MOS: <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=140262814885311&id=14822091.8" target="_blank">Bug 14822091</a>)</p><p>Hope, it helps!</p><p>P.s. Footprint: in the doc they use hexadecimal level 0x400, but I prefer to use decimals (1024) for readability. What is funny, in the bug description whoever wrote it also used decimal - so, don't worry, it is the same stuff written differently.</p>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-931119311432870452021-02-11T12:30:00.004-05:002021-02-11T12:36:14.351-05:00RMOUG-2021 Day#4<p>My today's presentation - "Managing Unstructured Data: Lobs in the World of JSON"<br /><a href="https://www.slideshare.net/MishaRosenblum/managing-unstructured-data-lobs-in-the-world-of-json">https://www.slideshare.net/MishaRosenblum/managing-unstructured-data-lobs-in-the-world-of-json</a></p>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-57995323318033678112021-02-09T13:39:00.001-05:002021-02-09T13:39:15.474-05:00RMOUG-2021 Day #2<p> If anybody needs - here is my most recent presentation: "Hidden Gems of Performance Tuning":</p><p><a href="https://www.slideshare.net/MishaRosenblum/hidden-gems-of-performance-tuning-hierarchical-profiler-and-dml-trigger-optimization">https://www.slideshare.net/MishaRosenblum/hidden-gems-of-performance-tuning-hierarchical-profiler-and-dml-trigger-optimization</a><br /><br />P.s. It was a pleasure to get back to RMOUG - even virtually!</p>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-14721336523593676452017-04-05T14:30:00.002-04:002017-04-05T14:30:35.606-04:00Collaborate'17: Slides are availableFor some reasons, slides from one of my presentations are not available via the conference agenda tool. No problem, here is a direct link:<br />
<a href="https://www.slideshare.net/MishaRosenblum/developers-approach-to-code-management">https://www.slideshare.net/MishaRosenblum/developers-approach-to-code-management</a><br />
<br />
More thoughts/comments about the conference are coming! To be continued...Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-65518731670412800032016-04-20T11:29:00.000-04:002016-04-20T11:29:15.399-04:00Collaborate'16: Slides are availableFirst, of all, I would like to thank everybody who came to my presentations on Collab'16! It was a pleasure talking FOR you :-) A lot of nice questions/comments/follow-ups.<br />
<br />
Second, some SlideShare uploads (as promised):<br />
- my talk about user-defined functions - that's where I was talking a lot about execution counts and the impact of Cost-Based Optimizer on them:<br /><a href="http://www.slideshare.net/MishaRosenblum/plsql-userdefined-functions-in-the-read-world">http://www.slideshare.net/MishaRosenblum/plsql-userdefined-functions-in-the-read-world</a><br />
<br />
- updated version of my "Printing without Printers" talk. As usual, I was reminded that "the best live demo is dead demo". Now you've got everything covered explicitly:<br />
<a href="http://www.slideshare.net/MishaRosenblum/printing-without-printers">http://www.slideshare.net/MishaRosenblum/printing-without-printers</a><br />
<br />
<br />Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-84846982428909108362015-04-13T19:01:00.001-04:002015-04-13T19:01:19.426-04:00Collaborate'15 A New View of Database ViewsThanks to everybody who visited my presentation! Here is a link to view and/or download the slide set: <a href="http://www.slideshare.net/MishaRosenblum/2015-458-rosenblumpptfinal">http://www.slideshare.net/MishaRosenblum/2015-458-rosenblumpptfinal</a><br />
<br />
Feel free to ask questions!Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-91364613736399869332014-10-07T14:10:00.001-04:002014-10-07T14:48:57.086-04:00NYOUG Training Event: "High Performance PL/SQL" on November 7, 2014<span style="background-color: white; color: #333333; line-height: 17px;"><span style="font-family: inherit;">If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:</span></span><br />
<span style="background-color: white; color: #333333; line-height: 17px;"><span style="font-family: inherit;"><br /></span></span>
<span style="background-color: white; line-height: 17px;"><span style="color: #333333; font-family: inherit;"><a href="http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258">http://nyoug.org/event/nyoug-fall-2014-training-event-high-performance-plsql/?instance_id=258</a></span></span><br />
<span style="background-color: white; line-height: 17px;"><span style="font-family: inherit;"><br /></span></span>
I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to move the business logic somewhere else (front-end/ middle-tier/other database technologies/you name it). But my strong believe that by properly utilizing available Oracle technologies you can achieve much better results without compromising data security and manageability.<br />
<br />
I've put together more than a decade of my experience to a full-day seminar that can really help you. Here is an outline:<br />
<br />
1. <b>Know your Environment</b> – This section introduces the available tools and features related to<br />
PL/SQL performance tuning including: data dictionary views, logging, tracing, profiling etc. It<br />
also discusses the proper ways to instrument code deal with and exception handling.<br />
<br />
2. <b>User-Defined PL/SQL Functions in the SQL Context</b> – For most developers, knowledge of<br />
PL/SQL starts from writing user-defined functions. As a result, even if this code is functionally<br />
correct, the program units are fired significantly more often than needed, impact CBO<br />
decisions, and cause execution plan degradation. This section addresses these issues and<br />
includes a number of examples of how PL/SQL can extend basic SQL functionality.<br />
<br />
3. <b>SQL Sets Within PL/SQL</b> – The goal of PL/SQL is to efficiently communicate with SQL sets<br />
using cursors. This key feature of database cursors is often missed by assuming that they are<br />
nothing more than pointers to SQL sets. The most efficient way of working with SQL sets is to<br />
access data using set-based operators. This section reviews the results of two case studies<br />
focused on comparing different access approaches. The first one looks for the most efficient<br />
ways of moving significant volumes of data between remote locations, while the second tests<br />
the impact of MULTISET operations on resource utilization.<br />
<br />
4. <b>Triggers are NOT Evil</b> - Both table triggers and INSTEAD-OF triggers are examined from a<br />
global system optimization point of view that includes not only the aspect of functional<br />
correctness, but also the tradeoffs between multiple goals. For example, depending upon the<br />
available hardware, developers can select either CPU-intense or I/O-intense solutions. This<br />
section also covers some of the most common performance problems related to different kinds<br />
of DML triggers and the proper ways of resolving them.<br />
<br />
5. <b>Keeping the Cache</b> - One of the most important performance tuning strategies is to ensure<br />
that you are not doing exactly the same thing multiple times. However, often you cannot avoid<br />
this repetition. In those cases, the best option is to minimize the impact by utilizing existing<br />
caching techniques, either manual or built-in. Currently in Oracle, there are different caching<br />
mechanisms, each with its own strengths, drawbacks, and side-effects. This section introduces<br />
all of them and details the selection criteria for matching the appropriate mechanism to specific<br />
situations.3<br />
<br />
6. <b>WHEN OTHERS THEN…</b> – The topic of PL/SQL performance tuning is too large to cover<br />
in a 6-hour seminar. However, some tips and tricks can be very useful to attendees. This last<br />
section describes some one-off cases that overlap multiple areas. It also includes an extended<br />
question & answer period where attendees will get a chance to share their experiences and ask<br />
questions.<br />
<div>
<br /></div>
<div>
<br /></div>
<br />Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-44309701770030041622014-09-25T18:27:00.001-04:002014-09-25T18:27:12.018-04:00Oracle OpenWorld'14 Guide!I think, the biggest challenge for people coming to such huge events as Oracle OpenWorld is to navigate through all of the events that simultaneously happen at various locations. You are always under stress that you may miss something, or that you may hit a bad talk, or that you may not even know that there was a talk directly linked to your key production problems.<br />
<br />
Finally there is a real guide to OpenWorld - and I am very pleased have one of my presentations listed there:<br />
<br />
<a href="http://www.otechmag.com/2014/otech-magazine-fall-2014/">http://www.otechmag.com/2014/otech-magazine-fall-2014/</a><br />
<br />
Looking forward to see everybody in San Fran!Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-2396878939557663222014-09-19T15:10:00.001-04:002014-10-07T14:52:37.833-04:00My new bookIt seems to me that I talked about my most recent book everywhere except for my own blog... So, let me introduce you to the one and only<a href="http://www.amazon.com/gp/product/0071824820/"> performance tuning book dedicated to PL/SQL</a>:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6d8gUIoDseY2lYa9uB8rPevftttZxU3T9CWRko3bVJocqx7EwBRw5g54h3LsMRN7x3Z6ir_Ss9aXqOfg_EjTnWf7-r0It9QODhmh2nwY_YRHzxJS_eD3Tgck6tswrlgb7qQR7BhO26l7X/s1600/FrontPage.tif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6d8gUIoDseY2lYa9uB8rPevftttZxU3T9CWRko3bVJocqx7EwBRw5g54h3LsMRN7x3Z6ir_Ss9aXqOfg_EjTnWf7-r0It9QODhmh2nwY_YRHzxJS_eD3Tgck6tswrlgb7qQR7BhO26l7X/s1600/FrontPage.tif" height="400" width="312" /></a></div>
<br />
This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - I finally got a chance to create a digest of my own knowledge and put it into any format I choose to. Of course, some shaping from my esteemed guru and co-author Dr. Paul Dorsey was definitely needed, but still it was as free-form as you get. Publishers trusted us so much that they didn't attach any strings to the contract - we were reshaping the structure of the book up to the very end.<br />
<br />
I think, I did my best and reached the goal I was shooting at. I really hope, this book can help a lot of people to build better software. Now it's your turn to tell me whether I was right or wrong.<br />
<br />
P.s. Here is a review from Steven Feuerstein - <a href="http://stevenfeuersteinonplsql.blogspot.com/2014/08/new-plsql-book-oracle-plsql-performance.html">link</a>.Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-59186756061204756992014-09-15T21:23:00.000-04:002014-09-15T23:48:49.336-04:00Good sources of Oracle informationPretty often I am being asked about various online and printed source of Oracle-related information. Finally I decided to create a blog post here where I will start to accumulate all the goodies that I just "happen" to find. Maybe I will eventually create even some kind of a guide... Let's see.<br />
<br />
Before we start - a couple of disclaimers:<br />
* It is my <u>personal</u> list of favorite resources, so all of the following statements assumed to be prefixed with IMHO :-)<br />
* The list represents my own interests, which are somewhat between DBA and Developer lands (plus a bit of Architect).<br />
* I highly value printed books, because while working on them authors structure (and check!) the material much better comparing to blogs. So, if I mention a name below, you also should check his/her books - they will be worth your money. Even blogs have the advantage of being more interactive (you may get immediate feedback), the quality of books should not be underestimated.<br />
<br />
Ok, let's start:<br />
<br />
1. My gurus (overall)<br />
- Tom Kyte <a href="http://asktom.oracle.com/">asktom.oracle.com</a> - no extra explanations needed, I hope ;-)<br />
- Steven Feuerstein <a href="http://www.stevenfeuerstein.com/">http://www.stevenfeuerstein.com/</a> - one of the main faces behind PL/SQL for the last 20 years<br />
- Jonathan Lewis <a href="http://jonathanlewis.wordpress.com/">http://jonathanlewis.wordpress.com/</a> - after his explanations I finally started to <u>get</u> the Cost-Based Optimizer<br />
- Daniel Morgan <a href="http://www.morganslibrary.org/">http://www.morganslibrary.org/</a> - huge collection of useful articles and examples! Often more understandable than original Oracle docs.<br />
<br />
2. My teachers in the field of performance tuning<br />
- Tanel Poder <a href="http://blog.tanelpoder.com/">http://blog.tanelpoder.com/</a> - I've seen him reading raw trace files! He can decipher X$-objects too.<br />
- Cary Millsap <a href="http://carymillsap.blogspot.com/">http://carymillsap.blogspot.com/</a> - extremely rare case of holistic approach to performance tuning (<a href="http://method-r.com/">Method-R</a>!)<br />
- Craig Shallahamer <a href="http://shallahamer-orapub.blogspot.com/">http://shallahamer-orapub.blogspot.com/</a> - another example of very scientific approach to tuning<br />
- Chris Antognini <a href="http://antognini.ch/blog/">http://antognini.ch/blog/</a> - a lot of depth in understanding of how Oracle internals work together.<br />
<br />
3. My fellows. They do the same kind of job as I do (only sometimes better!)<br />
- Tim Hall <a href="http://www.oracle-base.com/">http://www.oracle-base.com/</a><br />
- Adrian Billington <a href="http://www.oracle-developer.net/">http://www.oracle-developer.net/</a><br />
- Alex Nuijten <a href="http://nuijten.blogspot.com/">http://nuijten.blogspot.com/</a><br />
- Rob van Wijk <a href="http://rwijk.blogspot.com/">http://rwijk.blogspot.com/</a><br />
- Marco Gralike <a href="http://www.liberidu.com/blog/">http://www.liberidu.com/blog/</a><br />
<br />
4. DBAs land<br />
- Arup Nanda <a href="http://arup.blogspot.com/">http://arup.blogspot.com/</a>.<br />
- Alex Faktulin <a href="http://afatkulin.blogspot.com/">http://afatkulin.blogspot.com/</a><br />
- Company blog (a lot of smart people up there!): <a href="http://www.pythian.com/blog/">http://www.pythian.com/blog/</a> Just to name a few: Alex Gorbachev, Michael Abbey, Christo Kutrovski, Jared Still, etc.Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-79760229481283424872014-09-12T11:10:00.002-04:002014-09-15T21:37:36.340-04:00Analytic functions in real lifeYesterday while working on a real production report I found a requirement that forced me to come up with a bit nontrivial usage of analytic functions. Here is the problem (translated to SCOTT-level terms):<br />
<br />
- I have a table that contains a DATE column (HIREDATE)<br />
- Input parameter is also a date (IN_DT)<br />
- For each department I need to show all rows that have HIREDATE after IN_DT plus the most recent row from the past (only one!).<br />
<br />
Obviously, if you split the last condition into two, the solution is reasonably clear:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SELECT empno, ename, deptno, hiredate</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM scott.emp</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE hiredate>:IN_DT</span><br />
<span style="font-family: Courier New, Courier, monospace;"><b>UNION ALL</b></span><br />
<span style="font-family: Courier New, Courier, monospace;">SELECT empno, ename, deptno, hiredate</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM (</span><br />
<span style="font-family: Courier New, Courier, monospace;"> SELECT e.*,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> row_number() </span><br />
<span style="font-family: Courier New, Courier, monospace;"> over(PARTITION BY deptno <br /> ORDER BY hiredate desc, </span><br />
<span style="font-family: Courier New, Courier, monospace;"> empno desc</span><br />
<span style="font-family: Courier New, Courier, monospace;"> ) rank_nr</span><br />
<span style="font-family: Courier New, Courier, monospace;"> FROM scott.emp e</span><br />
<span style="font-family: Courier New, Courier, monospace;"> WHERE hiredate<=</span><span style="font-family: 'Courier New', Courier, monospace;">:IN_DT</span><br />
<span style="font-family: Courier New, Courier, monospace;"> )</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE rank_nr = 1</span><br />
<br />
The only problem of this solution - it needs two passed over EMP table. But in my production case that table is huge. Also, just to make life interesting, the result of the specified query has to be joined to 8 other tables to provide the required output. It is understandable, that my first optimization idea was to somehow do a single-pass query. To my own surprise, Oracle analytic functions are flexible enough to help me out:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SELECT </span><span style="font-family: 'Courier New', Courier, monospace;">empno, ename, deptno, hiredate, rank_nr</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM</span><br />
<span style="font-family: Courier New, Courier, monospace;">(</span><br />
<span style="font-family: Courier New, Courier, monospace;">SELECT e.*,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> <b>dense_rank() </b> </span><br />
<span style="font-family: Courier New, Courier, monospace;"> over(PARTITION BY deptno</span><br />
<span style="font-family: Courier New, Courier, monospace;"> ORDER BY </span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> CASE WHEN hiredate > :IN_DT THEN <b>'9999'</b></span><br />
<span style="font-family: Courier New, Courier, monospace;"> ELSE to_char(hiredate,'YYYYMMDDHH24MISS')</span><span style="font-family: 'Courier New', Courier, monospace;">||</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> lpad(empno,10,'0')</span><br />
<span style="font-family: Courier New, Courier, monospace;"> END desc </span><br />
<span style="font-family: Courier New, Courier, monospace;"> ) rank_nr,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> <b>max</b>(hiredate) </span><br />
<span style="font-family: Courier New, Courier, monospace;"> over(</span><br />
<span style="font-family: Courier New, Courier, monospace;"> PARTITION BY deptno) max_dt </span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM scott.emp e</span><br />
<span style="font-family: Courier New, Courier, monospace;">)</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE max_dt > :IN_DT AND rank_nr IN (1,2)</span><br />
<span style="font-family: Courier New, Courier, monospace;"> OR</span><br />
<span style="font-family: Courier New, Courier, monospace;"> max_dt < = </span><span style="font-family: 'Courier New', Courier, monospace;">:IN_DT</span><span style="font-family: Courier New, Courier, monospace;"> AND rank_nr = 1</span><br />
<div>
<br /></div>
<div>
In this solution I two analytical functions<br />
1. DENSE_RANK is forced to order in the following way:<br />
- bring all future dates to the top by assigning them <u><b>exactly the same value</b></u> '9999'</div>
<div>
- order all other dates by converting them to 'YYYMMDDHH24MISS' format. In that format DESC would bring the most recent date up anyways.</div>
<div>
- to resolve conflicts of multiple entries associated with the same date concatenate past dates with padded EMPNO. This way textual sort would correctly work even if the length of EMPNO may differ.</div>
<div>
2. MAX() OVER() brings the highest hire date for each department.</div>
<div>
<br /></div>
<div>
Now the outer SELECT has enough information to filter out correct rows: if the highest date if after the parameter, you need to bring ranks 1 and 2 (all future dates + the last past date). Otherwise you need only rank 1 (last past date).</div>
<div>
<br /></div>
<div>
I understand, that this solution may take a couple of seconds to wrap your head around :-). Still - I was able to do exactly what I needed in a single pass!<br />
<br />
<u>Summary:</u> if you are working on reports, analytic function is the MUST. That's the only safe way to write efficient code that would consistently work over large data sets.</div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-90360452522133534022014-09-09T15:26:00.001-04:002014-09-09T15:26:22.071-04:00Oracle OpenWorld 2014Less than 3 weeks left before the biggest Oracle event of the year kicks in - and I bet, Oracle OpenWorld'14 will impress anybody (as usual). I am honored to be selected as one of speakers there. Here are my two talks:<br />
<br />
- <a href="https://oracleus.activeevents.com/2014/connect/sessionDetail.ww?SESSION_ID=4428">UGF4428</a> "Expanding SQL Horizons: PL/SQL User-Defined Functions in the Real World"<br />
Sunday, Sep 28, 1:30 PM - 2:15 PM<br />
Moscone South - 310<br />
<br />
- <a href="https://oracleus.activeevents.com/2014/connect/sessionDetail.ww?SESSION_ID=4429">CON4429</a> "Calling SQL from PL/SQL the Right Way"<br />
Thursday, Oct 2, 2:30 PM - 3:15 PM<br />
Moscone South - 308<br />
<br />
Everybody is welcome! I really think that these presentations can improve you day-to-day PL/SQL.<br />
<br />
P.s. I will do my best to provide a daily update from the ground! There should be a lot of interesting things to share.Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com2tag:blogger.com,1999:blog-7777850065728349315.post-88925333998267089392014-08-20T16:42:00.000-04:002014-08-20T16:42:02.884-04:00Time to resurrect blogging!Sorry for staying quiet for some time, but for some good reasons. Some of these reasons will be explained in later posts, but for now I would like to share the story I wrote upon request of <a href="http://stevenfeuersteinonplsql.blogspot.com/2014/08/yessql-celebration-of-sql-and-plsql.html">Steven Feuerstein</a>, who asked to tell how PL/SQL impacted our life. Here is my story:<div>
<br /></div>
<div>
-----------------</div>
<div>
<div>
I started to work with databases already in college, but more from the data modelling/business analysis angle. When I moved to the USA in 2000 (just after graduation) I quickly recognized that my language skills are too limited to work as BA, so I went to evening Oracle courses in Philadelphia to deepen by technical skills – SQL, PL/SQL, SQL*Plus, into to administration etc. </div>
<div>
<br /></div>
<div>
And suddenly I realized that I like it even more than playing with ERDs! Also, a bit of a lucky break – one of my teachers recommended me to his former boss. To be fair, I have no idea how I survived that interview – somewhere in the middle of it I realized that this boss is no less than Dr. Paul Dorsey, whose book I was recently quoting in my Master Degree Thesis… Anyways, on February 26th, 2001 Dulcian Inc got a brand new 21-year-old employee with the focus on database development. </div>
<div>
<br /></div>
<div>
And couple of months later I’ve got a task that really shaped my specialization: I needed to write a module that would check for invalid objects in the schema and compile them in the appropriate order of dependencies. So, the set of tools is obvious (now), but at that time I had to discover: data dictionary views, object collections, recursion, and, finally, Dynamic SQL. For my reader/listeners – that list sounds really familiar, isn’t? :-) Looks like Oracle versions change, but favorite toys stay the same!</div>
<div>
<br /></div>
<div>
After that module started to work (I think, even Dr. Paul got surprised!) my field was settled: solving strange/unsolvable database problems using SQL and PL/SQL. That’s pretty much what I still do now – even 14 years later. Bad news – there are lots of such problems. Good news – it’s fun to solve them (and that’s the way I get new stories for books/presentations!)</div>
</div>
<div>
---------------------</div>
<div>
<br /></div>
<div>
Not exactly a fairy tale, but it definitely shows that you never know how your career will move forward, especially just after college. By the way, I brought with me to the USA that Russian-translated book by Dr. Paul! It stays right now in the office together with other memorabilia. Here is the scan:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh_RMQDFkx-D_BFlnAwya-KeSjgZZURzOLbg_Xq6FZ4UuElvbMcXg9UlItn6Vjzzx5iDOz4RqmOBbDKCS3Ig8ER-qDs_Gkff4kHZwCw-dmRy4iBP43UbjwoS-_xAJ91VcdE1U1v0xND69u/s1600/Designer_Russian.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh_RMQDFkx-D_BFlnAwya-KeSjgZZURzOLbg_Xq6FZ4UuElvbMcXg9UlItn6Vjzzx5iDOz4RqmOBbDKCS3Ig8ER-qDs_Gkff4kHZwCw-dmRy4iBP43UbjwoS-_xAJ91VcdE1U1v0xND69u/s1600/Designer_Russian.jpg" height="320" width="221" /></a></div>
<div>
<br /></div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-12518532637509380802013-09-03T13:52:00.000-04:002013-09-03T13:56:26.070-04:00Unknown DATAPUMP functionality: getting catalog from the fileTo be fair, I am not spending all of my days writing the new PL/SQL book :-) I even work sometimes! And while solving real problems I reasonably often find interesting thing.<br />
<div>
<br /></div>
<div>
Today I was asked by one of my colleagues: is it possible to look inside of the existing DATAPUMP file. This file was a FULL import of client's database, and we needed to check for the existence of a very specific public synonym.</div>
<div>
<br /></div>
<div>
The question was perfectly valid and I started the research. First, I found that IMPDP has parameter SQLFILE=<filename>, that generates all DDL from the dump. It could work out, but the perspective of digging through megabytes of textual data was not very promising, so I continued my research.</div>
<div>
<br /></div>
<div>
Eventually I found a <a href="http://stackoverflow.com/a/14450325">comment</a> in of Oracle-related forums mentioning another IMPDP parameter: MASTER_ONLY=YES. We all know that every time you run IMPDP, it creates a special table that contains the catalog of objects to be processed (names like SYS_IMPORT_TABLE_##, SYS_IMPORT_SCHEMA_##, SYS_IMPORT_FULL_##). As it appears to be, this catalog is human-readable (mostly)! The parameter mentioned above just loads this table - and stops at this point:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">C:\>impdp system/system@testDB dumpfile=StrangeFile.dmp lo</span><span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">gfile=imp_master.log <b>master_only=yes full=y</b></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Import: Release 11.2.0.3.0 - Production on Tue Sep 3 13:09:15 2013</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Production</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">With the Partitioning, OLAP, Data Mining and Real Application Testing options</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><b>Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded</b></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 13:09:32</span></div>
<div>
<span style="font-family: 'Courier New', Courier, monospace;"><span style="font-size: x-small;">C:\></span></span></div>
</div>
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<div>
Now the table is ready to be examined! The following query shows the most important data:</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">select distinct</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> object_schema,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> object_name,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> object_type,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> object_tablespace,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> process_order,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> duplicate,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> processing_status,</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> processing_state</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">from system.SYS_IMPORT_FULL_01</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">where process_order > 0</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">and object_name is not null</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">order by object_schema, object_name</span></div>
</div>
<div>
<br /></div>
<div>
Just to make sure that everybody gets limitations here: this method <u>does not</u> provide you DDLs of objects. It allows to find what objects exist in the dump file. So, there will be no guessing anymore, which is always a good thing!</div>
<div>
<br /></div>
<div>
By the way, getting DDLs not is also trivial now:<br />
* take required object names and put it into INCLUDE clause<br />
* add SQLFILE clause to generate DDLs instead of firing them</div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-49765126385491267352013-08-26T17:03:00.003-04:002013-09-04T10:14:35.265-04:00Oracle 12c OFFSET...FETCH.. bug?Working on the chapter that covers best ways of manipulating with object collection. Of course, if you talk about pagination of the rowset, it is very tempting to test Oracle 12c new feature - OFFSET…FETCH.<br />
<br />
Unfortunately, immediately got an issue. It seems to me that passing a variable to FETCH is not supported:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> declare</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2 type table_tt is table of employees%rowtype;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3 v_tt table_tt;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 5 v_limit_nr number:=10;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 6 v_counter_nr number:=0;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 7 begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 8 select *</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 9 bulk collect into v_tt</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 10 from employees</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 11 offset v_counter_nr*v_limit_nr rows</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 12 fetch next v_limit_nr rows only; -- variable </span><br />
<span style="font-family: Courier New, Courier, monospace;"> 13 end;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 14 /</span><br />
<span style="font-family: Courier New, Courier, monospace;"> type table_tt is table of employees%rowtype;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> *</span><br />
<span style="font-family: Courier New, Courier, monospace;">ERROR at line 2:</span><br />
<span style="font-family: Courier New, Courier, monospace;">ORA-03113: end-of-file on communication channel</span><br />
<span style="font-family: Courier New, Courier, monospace;">Process ID: 3060</span><br />
<span style="font-family: Courier New, Courier, monospace;">Session ID: 20 Serial number: 35307</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL></span><br />
<br />
Doing the same with hard-coded value works fine:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;"> ... </span><br />
<span style="font-family: Courier New, Courier, monospace;"> 12 fetch next 10 rows only; -- hard-code</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 13 end;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 14 /</span><br />
<span style="font-family: Courier New, Courier, monospace;">PL/SQL procedure successfully completed.</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL></span><br />
<br />
Couldn’t find any reference to this anywhere, so it looks like a bug. Before I log it, could anybody confirm/deny the issue for different environment?<br />
<br />
I am running:<br />
- the official release of Oracle 12c<br />
- on MS Windows 2012 (64-bit)<br />
- on the top of VMWare ESXi 5.1<br />
- VMX-9 version of the file<br />
<br />
<span style="font-size: large;"><u>Update:</u> got that issue confirmed as a bug #17404511. Thanks a lot to everybody who provided their test-cases. Will keep you posted.</span>Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com4tag:blogger.com,1999:blog-7777850065728349315.post-50796024084058493622013-08-26T16:59:00.000-04:002013-08-27T09:10:18.030-04:00Staying quiet... For a good reason :-)Please, don't worry - I didn't suddenly disappear from the surface of the world of databases. It just happened that after some years of staying outside of publishing business I've been recruited to make a new book.The tentative name/dates are: <i>PL/SQL Performance Tuning Guide: Tips and Techniques</i> (Oracle Press, ETA late spring 2014).<br />
<div>
<br /></div>
<div>
Good news: my publisher authorized me to use interesting findings as spoilers. So, prepare yourselves for new discoveries in the land of PL/SQL!</div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-66005277542236093632013-04-30T14:06:00.001-04:002013-04-30T14:22:07.818-04:00Database roles - 2. Big...Mistakes?Looks like my <a href="http://wonderingmisha.blogspot.com/2013/04/database-roles-bigdata-bigusers.html">Friday post</a> about BigData got some attention. In that case - let's continue! While looking at tons of articles about <i>Big-anything</i> I've noticed a very constant pattern - everybody is talking about multi-node clusters of database servers: you constantly hear about horizontal scalability, sharding, node failure tolerance etc... But let's ask the question - is it only me who feels that we are over-complicating our development patterns? Maybe we just don't know how to properly use available hardware resources?<br />
<br />
Before going any further - let me tell you a "war story": couple of years ago a system built by us at Dulcian was compared to a similar system (both by the scope and functionality). And at one of the tender kick-off meetings our competitors loudly accused us of lying in the proposal! The reasoning was very simple - we claimed that our current required hardware was one database server (16 cores total) utilized by 15% and two application servers (4 core each) utilized by 5%. For competitors such a footprint was plainly impossible - to do exactly the same job they've been burning to the ground a couple of racks of servers!<br />
Yeah, sure - by request our servers were counted and matched what was in the proposal!<br />
<br />
That's the price to pay for <u>architectural</u> mistakes: for "them" it was a "new norm" to require a separate application server for every 100 users - and for years nobody questioned that ratio! So, let me ask - did our IT industry fall in the same trap and became accustomed to bad performance? Did we forget to wonder WHY do we need that extra app server for every 100 users?<br />
<br />
As far as I see while attending different conferences current software/database architects prefer to have the same problems as their neighbor (pretty good "out-of-jail" card to show to management, isn't it?) rather than doing proper tuning on their systems. It is very rare for a company to do a real honest-to-God performance review - and event if it happens, it is usually in the wrong time and against the will of the current IT staff<br />
<br />
Recently I talked to a number of top specialists who make living out of fixing somebody's systems - and all of them repeat the same story:<br />
<ul>
<li>Once upon a time there was a current IT system that performed badly</li>
<li>"Contemporary architect" proposed complete rewrite of the whole system using "the new way"</li>
<li>Management looked at the cost of rewrite and went ballistic!</li>
<li>Somebody on IT staff finally proposed to hire a performance tuning experts/guru-DBAs. At least to be able to say <i>next time</i> that they followed all possible ways!</li>
<li>"Hired gun" in a couple weeks found a lot of "interesting things" (wasting about 3/4 of time fighting uncooperative locals). Some of those issue were fixable - some of them were not. But still the system started working faster (and often faster than anybody expected).</li>
<li>"Hired gun" got a big check (and curses behind the back from IT staff)</li>
<li>"Contemporary architect" got a pink slip (and also curses behind the back from IT management)</li>
</ul>
As you see, nobody is happy at the end! But the whole situation started when local IT specialists were not able to correctly evaluate existing resources and solutions. Yes, we all would like to be trend-compliant, but jumping to the system overhaul without a good reason is a dangerous as falling behind the technology curve.<br />
<div>
<br /></div>
<div>
<u>Summary:</u> your IT may not be as big as you think!<br />
<div>
<br /></div>
<div>
<br /></div>
</div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-92095539116870062232013-04-29T09:40:00.000-04:002013-04-29T09:40:25.830-04:00Dynamic SQL and RESULT_CACHEOver the weekend one of my friends asked me for a clarification on <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/subprograms.htm#BABFHACJ">RESULT_CACHE</a> - a really interesting functionality that allows Oracle to cache in SGA results of PL/SQL function calls.<br />
<br />
The point of confusion was the fact that between 11gR1 and 11gR2 Oracle development team significantly re-thought this feature - if in R1 developers needed to explicitly specify objects that should be monitored for cache invalidation (via RELIES_ON clause), starting R2 Oracle took care of it directly (RELIES_ON clause is completely ignored).<br />
<br />
Interestingly enough, Oracle will monitor not only direct dependencies! If inside of the function there are calls via Dynamic SQL to other objects - those will be monitored too. Last year I had the whole section on this topic at ODTUG KScope'12 ("Dynamic SQL in 11g World")! Here is a snippet from my white-paper:<br />
<br />
<i>Integration of Dynamic SQL and RESULT_CACHE</i><br />
<br />
Another good thing about the current implementation of Dynamic SQL is that Oracle’s PL/SQL team actively integrates it with other advanced features. For example, the “result cache” introduced in 11gR1 (and significantly rewritten in 11gR2) is efficient enough to not only auto-detect hard-coded dependencies, but also recognize and record on-the-fly calls made via Dynamic SQL. The following example includes a function that can get current row counts for a given table:<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">create or replace function f_getCount_nr (i_tab_tx varchar2)</span><br />
<span style="font-family: Courier New, Courier, monospace;">return number</span><br />
<span style="font-family: Courier New, Courier, monospace;">result_cache</span><br />
<span style="font-family: Courier New, Courier, monospace;">is</span><br />
<span style="font-family: Courier New, Courier, monospace;"> v_sql_tx varchar2(256);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> v_out_nr number;</span><br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> execute immediate </span><br />
<span style="font-family: Courier New, Courier, monospace;"> 'select count(*) from '||i_tab_tx into v_out_nr;</span><br />
<span style="font-family: Courier New, Courier, monospace;"> return v_out_nr;</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<br />
Step #1 would be to confirm that (a) result cache actually works and (b) it recognized on-the-fly dependency.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> select f_getCount_nr('EMP') from dual;</span><br />
<span style="font-family: Courier New, Courier, monospace;">F_GETCOUNT_NR('EMP')</span><br />
<span style="font-family: Courier New, Courier, monospace;">--------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 14</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SQL> select ro.id, ro.name, do.object_name</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2 from v$result_cache_objects ro,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3 v$result_cache_dependency rd,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4 dba_objects do</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 5 where ro.id = rd.result_id</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 6 and rd.object_no = do.object_id;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ID NAME OBJECT_NAME</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">-- ----------------------------------------------------- ---- </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">1 "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440... #1 EMP</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">1 "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440... #1 F_GETCOUNT_NR</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SQL>select f_getCount_nr('EMP') from dual;</span><br />
<span style="font-family: Courier New, Courier, monospace;">F_GETCOUNT_NR('EMP')</span><br />
<span style="font-family: Courier New, Courier, monospace;">--------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 14</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SQL> select *</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2 from v$result_cache_statistics</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3 where name in ('Create Count Success','Find Count');</span><br />
<span style="font-family: Courier New, Courier, monospace;">ID NAME VALUE</span><br />
<span style="font-family: Courier New, Courier, monospace;">--- -------------------- ------</span><br />
<span style="font-family: Courier New, Courier, monospace;">5 Create Count Success 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">7 Find Count 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL></span><br />
<br />
Oracle successfully recognized the EMP table as cache dependency and was able to return a value from the cache when the function was called a second time. Now to test cache invalidation, I will insert a new row to EMP table and re-fire the function F_GETCOUNT_NR<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> insert into emp(empno) values (100);</span><br />
<span style="font-family: Courier New, Courier, monospace;">1 row created.</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL> commit;</span><br />
<span style="font-family: Courier New, Courier, monospace;">Commit complete.</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SQL> select f_getCount_nr('EMP') from dual;</span><br />
<span style="font-family: Courier New, Courier, monospace;">F_GETCOUNT_NR('EMP')</span><br />
<span style="font-family: Courier New, Courier, monospace;">--------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 15</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SQL> select id, name, value</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2 from v$result_cache_statistics</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3 where name in ('Create Count Success',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4 'Find Count','Invalidation Count');</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">ID NAME VALUE</span><br />
<span style="font-family: Courier New, Courier, monospace;">--- -------------------- ------</span><br />
<span style="font-family: Courier New, Courier, monospace;">5 Create Count Success 2</span><br />
<span style="font-family: Courier New, Courier, monospace;">7 Find Count 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">8 Invalidation Count 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL></span><br />
<br />
This time, Oracle successfully detected data changes and invalidated the previously cached information. Now let’s introduce a new dynamic dependency (to DEPT table) and see whether the resulting cache would successfully recognize the difference.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SQL> select f_getCount_nr('DEPT') from dual;</span><br />
<span style="font-family: Courier New, Courier, monospace;">F_GETCOUNT_NR('DEPT')</span><br />
<span style="font-family: Courier New, Courier, monospace;">---------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL> select id, name, object_name</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2 from v$result_cache_objects ro,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3 v$result_cache_dependency rd,</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4 dba_objects do</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 5 where ro.id = rd.result_id</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 6 and rd.object_no = do.object_id;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">ID NAME OBJECT_NAME</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">--- --------------------------------------------------------------- -------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">3 "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1 EMP</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">3 "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1 F_GETCOUNT_NR</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">4 "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1 DEPT</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">4 "SCOTT"."F_GETCOUNT_NR"::8."F_GETCOUNT_NR"#8440831613f0f5d3 #1 F_GETCOUNT_NR</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SQL> select id, name, value</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 2 from v$result_cache_statistics</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3 where name in ('Create Count Success',</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 4 'Find Count','Invalidation Count');</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">ID NAME VALUE</span><br />
<span style="font-family: Courier New, Courier, monospace;">--- -------------------- ------</span><br />
<span style="font-family: Courier New, Courier, monospace;">5 Create Count Success 3</span><br />
<span style="font-family: Courier New, Courier, monospace;">7 Find Count 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">8 Invalidation Count 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">SQL></span><br />
<br />
As you can see, a variation of the resulting cache with the dependency on DEPT (rather than EMP) was immediately recognized. This means that Dynamic SQL is indeed fully integrated into the overall on-the-fly caching mechanism.<br />
<div>
<br /></div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com0tag:blogger.com,1999:blog-7777850065728349315.post-29248441861850574632013-04-26T16:29:00.001-04:002013-04-30T14:07:07.282-04:00Database roles: BigData, BigUsers, Big...Problems?Today at lunch we had an interesting discussion about the role of databases in the contemporary IT development - and there was a lot of buzz-words thrown across the table: Hadoop, NoSQL, BigData, BigUsers (reasonably small data+high number of concurrent users), ACID-compliance etc. To be fair, a bit too many buzz-words - I have a very bad feeling that even contemporary architects stopped understanding core concepts behind their work!<br />
<br />
Let's start from the key point - we have databases to manage DATA. And one of the key elements of this tasks is to make sure that data is reliably stored and retrieved. And here is a catch - what do we mean by <i>reliable</i>? Or to be precise - what happens to you/your company/your customers if some piece of the data is lost forever/unrecoverable? And the answer on this question drives the whole technology stack! For example, if you work with medical/legal/official data - a small chunk of lost information (if noticed) could mean litigation at best and people's life at worst!<br />
<br />
Let's be clear - majority of current NoSQL DB solutions are explicitly not ACID-compliant (or at least not 100% ACID compliant). For example, I found a pretty good analysis of <a href="http://css.dzone.com/articles/how-acid-mongodb">MongoDB</a> and <a href="http://stackoverflow.com/questions/2608103/is-there-any-nosql-that-is-acid-compliant">CouchDB</a> - and it is clear that even its proponents say that there are always trade-offs between performance and data reliability. In some articles there are even suggestions to have double-environment implementation, where you have NoSQL-database for non-critical data plus RDBMS for critical data.<br />
<br />
Just to clarify - what do I mean by <a href="http://en.wikipedia.org/wiki/ACID">ACID-compliance</a>:<br />
<ul>
<li><b>Atomicity</b> requires that each transaction is executed in its entirety, or fail without any change being applied.</li>
<ul>
<li>I.e. if you have successful INSERT and successful DELETE in the same transaction - you will have both/none of them committed.</li>
</ul>
<li><b>Consistency</b> requires that the database only passes from a valid state to the next one, without intermediate points.</li>
<ul>
<li>I.e. it is impossible to catch the database in the state when for the stored data some rules (for example, PK) are not yet enforced</li>
</ul>
<li><b>Isolation</b> requires that if transactions are executed concurrently, the result is equivalent to their serial execution. A transaction cannot see the partial result of the application of another one.</li>
<ul>
<li>I.e. each transaction works in its own realm until it tries to commit the data.</li>
</ul>
<li><b>Durability</b> means that the the result of a committed transaction is permanent, even if the database crashes immediately or in the event of a power loss.</li>
<ul>
<li>I.e. it is impossible to have a situation when the application/user <u>thinks</u> the data is committed but after the power failure it is gone. </li>
</ul>
</ul>
As we can see from that list, all of these listed requirements are technically very challenging to implement, especially with the high number of concurrent users and significant data volumes - that why Oracle went extreme with its UNDO/REDO/LOG mechanisms. But that's the price to pay for being sure that if you saved the data - it would NEVER disappear.<br />
<br />
I understand that there are environments where that small chance of data loss can be if not ignored, but at least tolerated: we all know that Craiglist is being run by MongoDB - so, what's the impact by one lost add? Somebody might get annoyed, but that's all! <br />
<br />
Although when I start hearing about medical systems being built via NoSQL solutions - I start to get nervous. Maybe, in a couple of years before going to the doctor I will first check what kind of software they use! Just to feel safer...<br />
<br />Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com1tag:blogger.com,1999:blog-7777850065728349315.post-41343287381753027072013-04-24T11:46:00.000-04:002013-04-24T12:33:54.294-04:00Performance tuning. Spending time is NOT OK (if you do not know exactly why)Yet another <a href="http://dulcian.com/if-you-dont-ask-you-will-never-find-out-why-your-system-is-performing-poorly/">performance tuning story</a>, similar to one that happened about <a href="http://wonderingmisha.blogspot.com/2013/03/explaining-miracle.html">a month ago</a>. Sad thing - something was was coded years ago we never questioned the time spent... Just to keep it short:<br />
<ul>
<li>once upon a time there was a very time-consuming module.</li>
<li>eventually we were forced to take much closer look - WHY is it so time-consuming</li>
<li>we found in our own view a function call that was completely unnecessary.</li>
<li>this function was very light that initially we ignored it altogether, but later we realized that we've been calling it 80000 times - and in that case even the lightest cost adds up.</li>
<li>removing this function (all needed data was already available via joined tables) took the cost of a class from 40 seconds down to 3.</li>
<li>Profit! :-)</li>
</ul>
We knew that the module is extremely complicated and that a lot of database operations was involved - so we <u>assumed</u> that it is absolutely fine! Of course, we've hit a case when assumptions are the worst enemies of a real knowledge... Especially if we are talking about performance tuning - because unless we've <u>proven</u> where exactly we are losing N seconds in the module X, we cannot say that module X is cannot take less than N seconds. Because those N seconds could be related to the different module/outdated coding technique/structural change etc. And only <u>knowing</u> what exactly is going on we can make a decision whether it is OK or not.<br />
<br />
<u>Summary:</u> A couple of lessons learned:<br />
<ol>
<li>If something does not perform well in the existing system - check it (unless it is something known and documented)! You may think that that time loss is inevitable, but who knows? </li>
<li>Function calls in SELECT statements could cause a lot of issues even if each call is light - you must think not only about the cost of a function call, but about how many times this function will be fired</li>
</ol>
By the way, at the recent IOUG Collab'13 in the presentation "Top 5 Issues that Cannot be Resolved by DBAs (other than
missed bind variables)" I covered the second topic (number of calls) a bit deeper. Below is a snippet from my white-paper:<br />
<br />
<div class="Subhead3">
<i>Number of calls in SELECT clause</i><o:p></o:p></div>
<div class="Subhead3">
<br /></div>
<div class="BodyCopy">
There are multiple ways of ensuring that if a function is
referenced in the SELECT clause, it is not fired more often than needed.
Unfortunately, few developers are even aware of this problem. My recommendation
is to include the following set of examples in any PL/SQL class. This explicitly
illustrates the difference between real understanding and guessing.<o:p></o:p></div>
<div class="BodyCopy">
<br /></div>
<div class="BodyCopy">
First, set up a basic environment to count total number of
calls: a package variable to store the counter, a simple function, and a checker
to display/reset the counter:<o:p></o:p></div>
<div class="ComputerCode" style="margin-left: .5in;">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">create package misha_pkg is<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> v_nr number:=0; <o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">end;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">create or replace function
f_change_tx (i_tx varchar2)<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">return varchar2 is<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">begin<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> misha_pkg.v_nr:=misha_pkg.v_nr+1; <o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> return lower(i_tx);<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">end;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">Create or replace procedure
p_check is<br />
begin<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">
dbms_output.put_line('Fired:'||misha_pkg.v_nr);<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> misha_pkg.v_nr:=0;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">end;</span><o:p></o:p></div>
<div class="BodyCopy">
<br /></div>
<div class="BodyCopy">
Second, run a very simple query against table EMP, where the function above will be applied
against EMP.JOB. And let us keep in mind that there are 14 total rows in the
table EMP:<o:p></o:p></div>
<div class="ComputerCode">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> select empno, ename,
f_change_tx(job) job_change_tx<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">
2 from emp;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> ... <o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"><b>14 </b>rows selected.<br />
SQL> exec p_check<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">Fired:<b>14</b><o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">PL/SQL procedure successfully
completed.</span><o:p></o:p></div>
<div class="ComputerCode">
<br /></div>
<div class="BodyCopy">
If you just use the function, it will be fired for every row.
But we know that there are only 5 distinct JOB values, so we should try to
decrease the number of calls. In Oracle 11gR2, there is a very interesting
internal operation called “scalar sub-query caching” being used while
processing SQL queries. It allows Oracle to internally reuse previously calculated
results on SELECT statements if they are called multiple times in the same
query. The following example tests to see if using this operation helps: <o:p></o:p></div>
<div class="ComputerCode">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> select empno, ename, <b>(select f_change_tx(job) from dual)</b><o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">
2 from emp;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">
... <o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">14 rows selected.<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> exec p_check <br />
<b>Fired:5</b><o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">PL/SQL procedure successfully
completed.<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL></span><o:p></o:p></div>
<div class="ComputerCode">
<br /></div>
<div class="BodyCopy">
The result shows that it did help. Now, only five distinct
calls are registered, which isexactly as needed. Although, since we are
discussing cache, why not use it explicitly? There is another very powerful
feature called “PL/SQL function result cache.” The following example enables it
on the function while the same query is run two times:<o:p></o:p></div>
<div class="ComputerCode">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">create or replace function
f_change_tx (i_tx varchar2)<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">return varchar2 <b>result_cache</b> is<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">begin<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> misha_pkg.v_nr:=misha_pkg.v_nr+1; <o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;"> return lower(i_tx);<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">end;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<br /></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> select empno, ename,
f_change_tx(job) from emp;<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">... <o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">14 rows selected.<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> exec p_check<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<b><span style="font-family: Courier New, Courier, monospace;">Fired:5<o:p></o:p></span></b></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> select empno, ename,
f_change_tx(job) from emp; <br />
...<br />
14 rows selected.<o:p></o:p></span></div>
<div class="ComputerCode" style="margin-left: .5in;">
<span style="font-family: Courier New, Courier, monospace;">SQL> exec p_check <br />
<b>Fired:0</b></span><o:p></o:p></div>
<div class="BodyCopy">
<br /></div>
<div class="BodyCopy">
The result is impressive! If the first call matches the
sub-query caching, the second call is a fantastic example of great performance
tuning – everything works as needed, but nothing is being done (actually, this
is not 100% true, since the cache should be retrieved anyway, but for practical
purposes it is a very simple PK lookup).<o:p></o:p></div>
Michael "Misha" Rosenblumhttp://www.blogger.com/profile/05568300284769247997noreply@blogger.com1