The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
21 September 2010
20 September quiz: Whose Version Is It, Anyway? (1386)
The 20 September quiz on the Oracle Database 11g result cache feature generated several complaints regarding the Oracle version that applies to PL/SQL Challenge quizzes. Several people felt that the quiz violated the assumptions, which state that an Oracle Database 10g Release 2 instance should be assumed.
But as I have pointed out before, all those assumptions are valid "unless otherwise mentioned" - that is, unless I state otherwise in the quiz.
At the very top of the "Take the Quiz" page, you now see a line that says:
" Oracle Database 10g Release 2, unless noted otherwise in quiz"
And then there is the actual text for the quiz. In the 20 September quiz, the very first sentence of the quiz stated:
"I create and populate the following table on an Oracle Database 11g instance:"
So, I would think that it is fairly clear that this quiz established an exception to the assumptions: this was an Oracle Database 11g question.
As for the content of the quiz, several players also questioned what I show as the correct choice. I address these below.
1. "As far as I know, the result_cache implementation is different on 11g R1 and 11g R2, on which there exists no relies_on clause; result_cache structure is not mature enough yet, there are side effects, exceptions etc..."
Now, I am really unclear as to what point the player is making regarding the maturity level of the feature - so please, Bora, elaborate on this blog. As for the different implementations on 11.1 and 11.2 - you are absolutely right. In 11.2, the RELIES_ON clause is deprecated; it is ignored by the PL/SQL engine. But it is still valid syntax, and so is fine for both 11.1 and 11.2 - I did not need to specify a "sub-release" for the quiz.
2. "Query on the Quiz of 20 September. I would have expected the correct answer to be: 1 Mouse Keyboard 100 Keyboard that's why I replied that none of the answres were correct The first DBMS_OUTPUT.put_line (name_for_num (1)); will give 1 Mouse (cache not used, as it's the first call) Then DBMS_OUTPUT.put_line (name_for_num (100)); will output: Keyboard (No execution of the function here, cache is used, therefore DBMS_OUTPUT.put_line (num_in); within the function is not executed) And, you have explained, the last call will output 1 Mouse But maybe there is something I am missing?"
It is a bit hard to understand the above paragraph (Philippe, please clarify on the blog if you would like), but the main point to take away from this quiz is that if you make an uncommitted change to a table in your session, then any cache that relies on that table is not available to you until you commit or rollback. Thus, each call to the function in this quiz resulted in the body of the function being executed. The first two populate the cache since the function hadn't yet been called with those inputs, the third time because an update had occurred on plch_parts.
3. "Dear Mr. Feuerstein, I use Oracle 11.2.0.1 and I have tested my answer connected as SYSTEM. The result was: 1 Mouse 100 Keyboard Keyboard After I have seen at my score, the result was wrong, I tested the same on Oracle 11.1.0.7 connected as SYSTEM and I got an error in the function: PLS-00999: implementation restriction (may be temporary) RESULT_CACHE may not rely on system tables. As I have connected as another user than SYSTEM, I got the results you pretend on both systems: 1 Mouse 100 Keyboard 100 Keyboard I'm really sad about the outcome of the Qiuz :("
Markus, this is very interesting. I don't do development or try things in the SYSTEM schema, and I was not aware that Oracle had this restriction - so thanks for teaching me (and now us) something I didn't know. As for the behavior in 11.2 in SYSTEM, I cannot explain that. Can you please try the same thing in a non-SYSTEM account and see if your answer disagrees with mine?
Having said that, I don't think that your experience invalidates the quiz, though I suppose I could add as an assumption that the code of the quizzes is never run in SYS or SYSTEM. I just worry about making our list of assumptions longer and longer - it will begin to look like the fine print of a contract, written by a lawyer!
Regards from Oracle Develop / San Francisco,
Steven
Subscribe to:
Post Comments (Atom)
Hi Steven,
ReplyDeleteMy question was regarding the second call to the name_for_num function, i.e the first call with value 100
DBMS_OUTPUT.put_line (name_for_num (100));
Somehow I thought that the first execution (for input 1) would have cached the results for the other values in the underlying table incl. 100 and thus the cache would have been used.
But if I understand well (your comment: The first two populate the cache since the function hadn't yet been called with those inputs) function results are only cached for inputs for which the function has been already executed.
And it makes a lot of sense
Well I have learned something....
Thanks
Philippe
Dear Mr. Feuerstein,
ReplyDeleteFirst of all, I have to say that I never want this quiz to be re-scored. There is no reson for that. I just wanted to state that it was a really tough question. Because:
1. Database version related questions are very hard to focus on. You have to switch your mindset in a timely fashion and make an exception for the assumptions given, that are perceived as strong standarts in time.
2. The subject of the question, result cache feature of PL/SQL, is not mature enough yet. And ofcourse, it is my opinion as a professional. The reason underneath my judgement is: When I read the documentation about PL/SQL result cache feature (http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#i22204) what I see is a very fragile system with a lot of requirements. You have to notice nearly 20-30 rules including database initial settings, configurational issues, parameter and return types, parameter modes, session dependent issues, subprogram side effects such as dbms_output usage, sending e-mail, dynamic SQL usage, DML usage, cache bybass rules etc.
I am sure that Oracle developers are still working hard on this feature for making it more usable. As a software manager, it is a big question mark for me to use such a mechanism that does not sound so easy to manage, maintain and develop. But I know that it will be better in time and not using this feature would be irrelevant soon. On the other hand, in this very current moment, dealing with a question about result_cache is too difficult.
To conclude, my thoughts on this feature does not make the question invalid so I am not referring to any justifications.
Regards.
As I opened the 20 Sep. quiz, I immediately thought; Oh my god, here we go.... 11g. The anyways remote chances of a very cool prize just vaporized :-(
ReplyDeleteI am still a slave of 10g and didn't even have 11g documentation readily available. But (thanks Steven) I did know about the PL/SQL function result cache. Point here is that I think anyone who takes time to read the words of the quiz also reckognizes the legitimate framework, in this case 11g.
Having no practical knowledge on the topic and no documentation, I was left with little other choice than an attempt of applying logic. "My logic" relatively quickly had me center on the choice: "1 mouse, 100 keyboard, keyboard" and I was about to check in as an alarm went off.... Wait a minute... That would be so inconsistent and not possibly true. The result cache lives in the SGA for the benefit of all. To maintain read consistency, uncomitted changes in any session cannot possibly be visible there. But, that causes a dilemma within my own session if that is also using the cache. As we all know, the updates made in your own session are visible to that session (very cool) and also knowing that Bryn is a very smart guy, it would be rude to think that Oracle had not found a solution to this problem. I therefore changed my choice to: "1 mouse, 100 keyboard, 100 keyboard".
Having spent no thoughts on how Oracle did it, I appreciate Steven providing the explanation. Oracle took the easy way out and simply disable the cache. That allows everything within the session to work as adverticed, only without the benefit of the cache. Is that a bad or cheap solution from Oracle? No.. If you wish to perform quick updates on cached data, there are other and more performant ways. The PL/SQL function result cache is something to use for read-only, says the non-11g guy ;-)
Mike
I dare suggest to put assumptions that differs from default ones as the first line of the quiz and in bold. Something like to:
ReplyDeleteAttention: this quiz assumes Oracle 11.x!
Hi Steven,
ReplyDeleteI think the statement at the top of question "Oracle Version: Oracle Database 10g Release 2, unless noted otherwise in quiz" was confusing. It's quite new. But it had appeared few times before 20 Sep quiz.
Its purpose is not clear. It always displays 10g. But in a question 11g was specified.
First of all, we read this statement and this information keeps in mind and may override someting that is pointed in a question.
I beleive it's better to have this statement with real environment value, i.e. 11g for 20 Sep quiz. Another alternative is to remove totally.
Of course I will never get confused by this again.
Cheers,
Sergey Porokh
I had no clue about this question.
ReplyDeleteFirst by seeing 11g + Advanced 50% of the hopes were gone(as I work in 10g). Second by seeing "RESULT_CACHE" 100%hopes were gone(It seemed like an alien for me). I gave a try(guess) and it worked. After submitting the quiz, I spent about 2hrs to explore about the usage, conditions and all for this feature. [Satisfaction that comes with exploring is incredible.I should thank Steven fir this kind of questions]. The even more happiest thing is sharing the same across my team, which made 1-2mnths experianced ppl in my team know about "REUSLT_CACHE" usage. WOW :-) I am really happy about that.