tag:blogger.com,1999:blog-8677649049588007585.post5748738569502134150..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: "Could" Changes Really Improve Performance? (7755)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-8677649049588007585.post-88349022206790694142011-09-29T16:57:33.413+01:002011-09-29T16:57:33.413+01:00Well put, Sean, and thanks for the insightful anal...Well put, Sean, and thanks for the insightful analyses by the other posters. <br /><br />Due to sloppiness in the way I set up the question, there is no avoiding the fact that any improvement in performance could change which rows are updated. So as it stands, all choices are incorrect.<br /><br />What's a quizmaster to do? Here's the plan:<br /><br />1. I will mark this quiz as invalid for purposes of ranking. It will not be used to determine ranking in the quarter, participation in the playoff, etc.<br /><br />2. I will change the question text so that it is clear that the procedure always finishes in the morning. It is the simplest way to keep as much of the quiz (and your answers) intact.<br /><br />3. I will give all players credit for the HH24 choice, which will be set to CORRECT.<br /><br />4. If any player marked all choices as incorrect (thus assuming that they applied the analysis laid out in this blog), they should submit a request for a score adjustment and they will be given 100% credit for the quiz. Only 16 players submitted a "No choices are correct" answer. You know who you are. :-) <br /><br />Regards, StevenSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-69780810806645637872011-09-29T14:21:01.301+01:002011-09-29T14:21:01.301+01:00I agree that the code as presented is a poor desig...I agree that the code as presented is a poor design for a "real" program. However, many of the quizzes involve practices that wouldn't be recommended but still present valid learning material. Similarly some quizzes have answers that are poor design but still valid within the context of the quiz constraints.<br /><br />The quiz didn't ask us to determine the validity of the scenario. The requirements of the quiz are very clear (make it faster, don't change results) and as such, the requirements of the answers are pretty clear too. Either the answers do NOT affect performance OR they DO and hence affect the results. Therefore, none of them are valid solutions.<br /><br />The fact that the original scenario was ambiguous and the code was not a good model is, I think, immaterial.<br /><br />If people object to having all answers be wrong, then just call it a wash. Simply give everyone full credit or give everyone 0 credit and then adjust the question/scenario so there are no ambiguities. I enjoy playing the game of the quiz, but I'd much rather have the quiz/answers be consistent and correct for future use.Seanhttps://www.blogger.com/profile/15790298349995376048noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-75646714424313289452011-09-29T07:34:24.353+01:002011-09-29T07:34:24.353+01:00Hello All,
When it comes to BUSINESS data process...Hello All,<br /><br />When it comes to BUSINESS data processing, probably the most realistic scenario that may come close to the one presented in this quiz is to have a requirement that, depending on the time of the day, either performs ALL of the updates or none of them.<br />In such a case, of course, the check should be made OUTSIDE THE LOOP.<br /><br />I can also think of a scenario that is even closer to that of the quiz, for example, <br />a daily batch process that collects tables statistics by some special criteria, having a restricted time window (range of hours) in which it can be run.<br />That is, in each run, only part of the tables will be analyzed in the given time frame, <br />specifically, starting always with those whose statistics are the oldest collected.<br /><br />In such a case it DOES make sense to check the current time BEFORE EACH ITERATION of the loop<br />and stop when the time frame is exceeded.<br />But, of course, the outcome of a single run will be each time and ON PURPOSE a different one.<br /><br />All in all, back to the quiz, if we incline to consider all the choices as wrong<br />because of the quiz requirement of "preserving the external effect", then I think that we <br />can and probably should rather consider the question scenario itself as being incorrect per se for such a requirement.<br /><br />Basic logic says that a FALSE can imply anything, so, if we are at the point of questioning the question itself, it does not make much sense to discuss the answers correctness at all ...<br /><br />Just a few more thoughts ... <br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-5413274568260340062011-09-28T13:12:32.528+01:002011-09-28T13:12:32.528+01:00Unfortunately the whole purpose of this quiz, tes...Unfortunately the whole purpose of this quiz, testing knowledge of "invariants" has been superceded by the performance issue, and fallout from that. I thought of another exception. Just the flipside of the example I gave originally. Instead of 11am, what if the process ran at 11pm? Again, if the procedure runs for 2-3 hours, it will skip updates for the first hour because they are after noon, but it will do updates after midnight when the process rolls into the next day because sysdate will be before noon. If the procedure becomes faster it's possible that all of the updates will be skipped. Ironically, the more it skips, the faster it will become thus exacerbating the symptom.<br /><br />The explanation for 8122 has already identified the issue. The problem then is that explanation isn't being applied to all of the answers even though the same logic holds. I still vote for a rescore (even though mine will get worse) and changing the text of the quiz so that changes in performance won't affect the external behavior.Seanhttps://www.blogger.com/profile/15790298349995376048noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-10537802769144865862011-09-27T10:38:04.515+01:002011-09-27T10:38:04.515+01:00I ran some brief tests, checking V$SQL's CPU_T...I ran some brief tests, checking V$SQL's CPU_TIME<br />and ELAPSED_TIME. There were benefits in only doing those conversions once but nothing worth the trouble.<br />The repeated calls to SYSDATE (and the context switch to SQL to obtain it) have a bigger impact. I once tuned a routine by grabbing SYSDATE once and using dbms_utility.get_time to keep the value 'fresh' rather than requery it from the database....but that wasn't one of the options here.sydoraclehttps://www.blogger.com/profile/10404756950638119562noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-72532588700485339012011-09-27T02:28:35.683+01:002011-09-27T02:28:35.683+01:00Using the logic of "could" improve perfo...Using the logic of "could" improve performance, then capturing l_hour "could" be an improvement if the process was launched before 8am.<br /><br />I thought this answer would be one of the better improvements to the code, and should have had a good chance of not affecting the outcome.<br /><br />I have not tested it yet, but I thought perhaps even more-so than referring to plch_config.min_salary<br /><br />ScottScott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-27894266183108654552011-09-26T18:01:51.319+01:002011-09-26T18:01:51.319+01:00Hello All,
I just tried to test the sample cases ...Hello All,<br /><br />I just tried to test the sample cases submitted above by Chris Saxon, and my results on <br />11.1.0.7.0 are as follows:<br /><br /> -- first test<br /> Run1 -- Char <= Char: 180<br /> Run2 -- Char <= number: 174<br /> Run3 -- Char <= Char: 334<br /><br /> -- second test<br /> Run1 -- Char <= Char: 171<br /> Run2 -- Char <= number: 178<br /> Run3 -- Char <= Char: 156<br /><br />So, while the first two blocks had consistent result, the 3-rd one took much more time <br />"to warm up" ...<br /><br />Thanks & Best Regards,<br />IudithAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-49694133885239440422011-09-26T07:20:17.621+01:002011-09-26T07:20:17.621+01:00Don't change the question. It was valid enough...Don't change the question. It was valid enough to demonstrate the effect of taking out code of a loop. But I lake the comment of iudith. The fact that faster code changes the outcome which should not be the case.Wim de Langehttps://www.blogger.com/profile/05505341375827859005noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-939099951740968412011-09-26T00:03:44.047+01:002011-09-26T00:03:44.047+01:00Default installation of 11g comes with plsql_optim...Default installation of 11g comes with plsql_optimize_level 2.<br /><br />As per documentation - Level 1 itself "Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions"<br /><br />Most of the answers discussed here are falling under "elimination of unnecessary computation" and this is automatically eliminated.<br /><br />Hence both version of program would achieve the same result in terms of performance.Spoonhttps://www.blogger.com/profile/04277621011196124328noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-2414594715556862952011-09-24T02:48:51.223+01:002011-09-24T02:48:51.223+01:00I had emailed Steven earlier about he same issue.
...I had emailed Steven earlier about he same issue.<br />Even though it will give me a score of 50% I think I have to say all answers to this quiz are invalid.<br /><br />The results of the procedure are SYSDATE dependent (the 12 noon mark Iudith pointed out above) so anything that changes the execution time of the procedure can potentially impact the results which breaks the "(what it displays or which rows it updates)" rule.<br /><br />For example, if the procedure normally runs at 11am and takes 2-3 hours to complete.<br />The first hour of execution will update rows because of this clause because it's before noon and the rest will be skipped. (This is poor design but the quiz doesn't ask about this.)<br /><br />If we now make the procedure so fast that it completes in 50 minutes, then all rows will be updated. This, we have changed the rows updated.<br /><br />That's an extreme example but simply altering the execution time by 1 second is sufficient to potentially change results.<br /><br />Thus... NONE of the answers are correct.<br /><br />Either they don't help performance or they do help performance and thus, potentially change the functionality and hence disqualify themselves.Seanhttps://www.blogger.com/profile/15790298349995376048noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-17585674422398871762011-09-23T18:05:57.634+01:002011-09-23T18:05:57.634+01:00Hello All,
I would just add two more remarks, in ...Hello All,<br /><br />I would just add two more remarks, in addition to the implicit conversion one that I also thought about a little bit longer while answering the quiz:<br /><br /><br />1. In Choice 8120, defining the local l_name as having the anchored type<br />plch_employees.last_name%type<br />might raise a VALUE_ERROR in the declaration section if the NAME_IN value passed in happens<br />to be longer than the declared 100 bytes of plch_employees.last_name ...<br /><br />This was probably not "the stone to stumble upon" ... so I treated it like something to be ignored in the context of this quiz.<br /><br />2. Regarding the overall logic of updating employees based on the current hour,<br />yes, it has probably caused many of us "to raise eyebrows" regarding the requirement itself...<br /><br />In fact, if the 4 hours of running the procedure happen to encompass hour 12 at noon,<br />then any of the accepted performance emprovers ( the 3 correct choices ) are likely to influence the number of the rows updated.<br />Put otherwise, if the procedure runs faster due to any of the other improvements, then more employees "get the chance" to earn their 1$ salary raise before the current time hits <br />hour 12 ... which in fact changes what was called "the external effect of the procedure",<br />here the number of records updated.<br /><br />Putting it this way, strictly speaking, each of the correct improvements "carries the potential" to turn its own choice into being incorrect.<br /> <br />Yes, the quiz style was somewhat different from what we are used to have in the daily quizzes,<br />but, however, interesting in its own way.<br /><br />All the performance improvements suggested are in fact best practices, worth to be remembered<br />and used naturally in current development.<br /><br />Thanks a lot & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-86284069416425744122011-09-23T16:32:48.519+01:002011-09-23T16:32:48.519+01:00I had selected option 4 as incorrect because I tho...I had selected option 4 as incorrect because I thought the implicit conversion of l_year would add to the processing. On testing however, I'm not sure this is the case... <br /><br />I made the following test cases to see what difference comparing the (number) l_year to the character vs. to_char-ing the date:<br /><br />-- Char <= Char<br />declare<br /> l_emp_date date := sysdate;<br /> l_comp_date date := sysdate;<br /> l_number number := to_char(l_comp_date, 'yyyy');<br /> l_start number;<br /> l_varchar2 varchar2(4) := to_char(l_comp_date, 'yyyy'); <br />begin<br /> l_start := dbms_utility.get_time();<br /> for i in 1 .. 100000 loop<br /> for j in 1 .. 1000 loop<br /> if to_char(l_emp_date, 'yyyy') <= to_char(l_comp_date, 'yyyy') then<br /> null;<br /> end if;<br /> end loop;<br /> end loop;<br /> dbms_output.put_line((dbms_utility.get_time() - l_start));<br />end;<br />/<br />-- Char <= number<br />declare<br /> l_emp_date date := sysdate;<br /> l_comp_date date := sysdate;<br /> l_number number := to_char(l_comp_date, 'yyyy');<br /> l_start number;<br /> l_varchar2 varchar2(4) := to_char(l_comp_date, 'yyyy'); <br />begin<br /> l_start := dbms_utility.get_time();<br /> for i in 1 .. 100000 loop<br /> for j in 1 .. 1000 loop<br /> if to_char(l_emp_date, 'yyyy') <= l_number then<br /> null;<br /> end if;<br /> end loop;<br /> end loop;<br /> dbms_output.put_line((dbms_utility.get_time() - l_start));<br />end;<br />/<br />-- Char <= Char, but convert the date outside the loop<br />declare<br /> l_emp_date date := sysdate;<br /> l_comp_date date := sysdate;<br /> l_number number := to_char(l_comp_date, 'yyyy');<br /> l_start number;<br /> l_varchar2 varchar2(4) := to_char(l_comp_date, 'yyyy'); <br />begin<br /> l_start := dbms_utility.get_time();<br /> for i in 1 .. 100000 loop<br /> for j in 1 .. 1000 loop<br /> if to_char(l_emp_date, 'yyyy') <= l_varchar2 then<br /> null;<br /> end if;<br /> end loop;<br /> end loop;<br /> dbms_output.put_line((dbms_utility.get_time() - l_start));<br />end;<br />/<br /><br />Running on 10.2.0.3, I get both the first and last cases (both char comparisons) as around 17s but the middle case (char compared to number) as around 14s - about 3s faster. This has held up over multiple rounds of tests.<br /><br />I'm not sure why the last case about the same amount of time as the first; the second to_char() isn't invoked in the loop. So the improvement could be due to the fact that number comparisons are more efficent than varchar; I'm interested if any others can reproduce my results and have a more solid explanation!Chris Saxonhttps://www.blogger.com/profile/09485645419920814104noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-19846036219421903862011-09-23T15:47:00.000+01:002011-09-23T15:47:00.000+01:00Although response 8123 replaces an explicit date-t...Although response 8123 replaces an explicit date-to-character conversion with an implicit character-to-number conversion, the change could result in improved performance since the implicit conversion might be less computationally expensive than the explicit conversion. Using <i>EXTRACT</i> to get the year from hire date may be more efficient than the implicit conversion. Without knowing the internal details of each of the conversion routines, it is not possible to determine if any of these changes is likely to result in improved performance. None of this invalidates the scoring: the quiz was about the <i>possibility</i>, not the <i>probability</i>, of improving performance without altering the results of the procedure.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.com