January 4, 2011
(Forward to the Next Post in the Series)
While performing a Google search I encountered a couple of Oracle multiple choice quizzes that also supplied justifications for the answer that is described on the page as being correct. The web pages I found reminded me a bit of some of the quizzes that I put together for this blog, all of which appeared in my blog’s category “Quiz – Whose Answer is it Anyway”. I thought that it might be helpful to let you read the quizzes, allow you to mention which of the supplied possible answers is correct (with justification of course), and then allow you to indicate whether or not you agree with the correct answer that is listed on the web page.
Oracle Database 11g Optimizer Mode Quiz:
Questions (with the currently supplied answers):
- D
- C, D
- A
- B
————-
Oracle Database 11g Rule Optimizer Mode Quiz:
Questions (with the currently supplied answers):
- C, D
- A
- A, B, C
————-
I think that I might have failed both quizzes if they were handed to me as a test of proficiency, how about you?
Charles Hooper's Oracle Notes 
1 quiz:
1. D this is not good question, I think. SQL does not change access paths, CBO does decisions.. but from listed options I would also choose D.. SQL profiles (also sets of hints) are better way to ensure plan stability, I think.
2. C, D – C is wrong optimizer_goal is obsolete even in 10g..
SQL> alter session set optimizer_goal=choose;
alter session set optimizer_goal=choose
*
ERROR at line 1:
ORA-01986: OPTIMIZER_GOAL is obsolete
D is OK, we can use first_rows(n), all_rows hints
3.
correct answer is not there: Because the choose is not valid for 11g and even for 10g
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams161.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams145.htm#sthref453
OPTIMIZER_MODE = { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
it was valid for 9i
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#38218
4. B first_rows is obsolete… first_rows(n) should be used
But I think most OLTP queries don’t need hints..
I guess sometimes you have to look at a quiz and say “which answer is the best fit?” or perhaps, as is probably the case in those quizzes and some of the answers/explanations, “which is the least wrong?”
Pointing out the things I saw worth pointing out:
Optimizer Mode Quiz
1. “Place a hint” – a single hint is usually inadequate and in a reasonably complex piece of SQL as likely to cause as many problems as it was originally designed to relieve.
2. OPTIMIZER_GOAL? Obsolete in 11g I believe. And this is an 11g quiz apparently
But as we see in the examples on this page http://www.dba-oracle.com/oracle_tips_ault_rbo_10g.htm that it’s easy to get mixed up between the OPTIMIZER_GOAL and OPTIMIZER_MODE and indeed what error messages are saying.
I had thought that that obsolescence was what the answer would be pointing at.
3. Listed? Listed where? On that website perhaps. I’ve got so many issues with all the explanations I had better just move on.
4. I’d hope that most OLTP queries would not need to be hinted – hints being the exception rather than the rule – no pun intended 🙂
Rule-based Quiz
A blanket comment – why does a quiz with the phrases Oracle 11g and rule-based optimizer even exist?
The technical imprecisions and grammatical inaccuracies are not a great advert for the related books so heavily promoted on those pages.
Cheers,
Dominic
Quite a few page views of this article within the last 7.5 hours, and so far 36 people have attempted the first quiz, but only 12 have attempted the second quiz. I will restrain the temptation to supply my answers for at least 24 hours.
Radoslav,
Nice idea to provide references to the documentation.
DomBrooks,
It is interesting that the link that you provided links directly to a book that I recently reviewed. It is also somewhat interesting that the optimizer goal that was attempted in that article would have ever worked. In short, it is a business decision that determines what material is presented as a representation of the quality of one’s products. That business decision has been made in this case, and Google search results find the result of such business decisions every day. On a positive note, the errata for that book now lists two of the errors that visitors of this blog mentioned in comments; oddly, the hard-hitting portions of the comments never made it into the errata, just two missing commas were mentioned.
Quiz one.
1: – Make sure your application is well written and you understand how often you need to gather statistics and which objects need aditional stats or “massaged” statistics to allow the optimizer to generate a stable plan, if all else fails stored outlines (or as its an 11g quiz SQL plan management) is a better way to go than hinting the query.
2: – I would use an alter session set opimtimiser_mode or optimizer_features_enable or a hint inthe sql if i really had too, (have done this to work around a poor plan after an upgrade from 9.2 to 10.2 then re-written the query to use analytics)
3: – Choose isn’t even an option in 11.1 or 11.2, but even in previous version A makes no sense the cost based optimiser will sample tables that have missing stats in any mode.
4: – None, best not to use a hint at all, anyway would it not depend on the query in an OLTP system most queries should only be returning a small set of rows so all_rows would be entirely appropriate in this case anyway.
Quiz two.
1: – The rule based optimiser is depricated so it should be avoided, it may still work but any new access paths are inaccessable and any bugs will not be investigated/fixed. While it may be faster in some cases the effort spent re-writting the query and re-ordering joins etc. would be better spent getting the statistics rigth on the objects to allow the cost based optimiser to find the best plan.
2: – Black magic and vodoo may work as well, hand me the chicken and a knife.
3: – Option E: use the cost based optimiser as its supported and use DBMS_STATS to gather statistics rather than analyze, Option A doesn’t actually seem to fit the question, option C i have no idea if it would work or not as its been a long time since I used the rule based optimiser. Option B would work but you should feel bad for using it.
Man, those are two messed up pages. I like the hit counter down at the bottom, every hit raises the google rank, you think?
Poor chickens.
The visitors to this blog are responsible for roughly half of the hit counts on the page containing the first quiz. Directly accessing that site from this page should not increase the Google rank for those pages. However, my link to those pages probably will promote those pages in the Google rank. I guess this is a double-edged sword for letting people take an online version of the quiz.
As of right now, roughly 60 people have attempted the first quiz, and 25 have attempted the second quiz. None of the questions in these two quizzes should ever appear on a competency test for DBA job candidates. However, I fear that questions much like these have in fact been used to unfairly judge a job candidate’s qualifications.
I am impressed with the quality of the answers provided so far. My answers to the questions follow, and my answers are quite similar to those already provided.
First quiz:
1. The wrong question is being asked for the supplied possible answers. First of all, a person really need to ask themselves why the access path should not change for a SQL statement. Assuming that bind variables are being used, it is quite possible that with accurate statistics, adaptive cursor sharing, and cardinality feedback, the optimizer will correctly change the execution plan as needed (after of course it fails to execute efficiently once). If you want to ensure that the access paths do not change, then you either need to heavily hint the SQL statement (every join method, every access method, and prevent query transformations), use stored outlines – hacked if necessary (http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA285), or if you are using the Enterprise Edition create a SQL profile (this actually might not lock the execution plan as it adds hints to the SQL statement to alter cardinality estimates, among other things), or … I will have to think of something else.
A. is not a reasonable answer for a test that is written for Oracle Database 10.1 or above. Yes, it is still possible to set the OPTIMIZER_MODE to CHOOSE in Oracle Database 11.1 and above, but it is also possible to hit your thumb while driving a screw with a hammer.
B. doesn’t make sense as an answer. ALTER SESSION SET “__FAST”=TRUE; is a SQL statement. If that SQL statement were to appear in production SQL, would you then need to construct the SQL statement like this:
C. might work, or might not. If you were previously changing the value of that parameter on a scheduled weekly, daily, or hourly basis, and then you stop doing that – sure the access paths in the execution plan may stop changing.
D. I have read enough articles stating that a single hint is insufficient to lock the execution plan and seen the behavior first hand, so D. is not a good enough/specific enough answer. What if there are multiple SELECT blocks (inline views, WITH blocks, scalar subqueries, EXISTS/IN clauses)?
If I had to pick an answer, I would pick C. as the correct answer, and then ask the examiner to stop changing the value of that parameter on a scheduled basis. 🙂
2. In Oracle Database 10.1 and above the default OPTIMIZER_MODE is ALL_ROWS. Therefore, if you modify the spfile or init.ora value for OPTIMIZER_MODE to something other than ALL_ROWS, you have then over-ridden the default OPTIMIZER_MODE. The value can also be changed in memory at the instance level (SCOPE=MEMORY) or session level. You could very well override the default OPTIMIZER_MODE by using SQL*Plus for all of your queries, and that is simplified if you use a configuration file that you run every time SQL*Plus is started. Changing the session level parameter OPTIMIZER_GOAL stopped working a long time ago (attempting to change it should result in an ORA-01986). If you need to override the default optimizer mode for a single query, it certainly is possible to do so with a hint.
If I had to pick an answer, I would pick A., and then ask the quiz examiner why a spfile is not being used.
3. Something is missing with this question. There should be a RULE to CHOOSE not to use deprecated parameters, even if nothing was listed above that which was questioned. As of Oracle Database 10.1 ALL_ROWS is the default OPTIMIZER_MODE. Flipping between the RULE based optimizer and the cost-based optimizer, on a query-by-query basis, is one reason not to use the deprecated OPTIMIZER_MODE of CHOOSE. A second reason is that if CHOOSE is the default OPTIMIZER_MODE for your Oracle Database release version, you are running a database version that is no longer supported by Oracle Corp.
A. Sure, missing statistics could cause dynamic sampling to take effect during a hard parse (assuming that at least one object referenced by the query has statistics), but I would not state that estimating statistics happens at run time (on a side note, what is run time – is it an EXEC call as would appear in a 10046 trace file, a PARSE call in a 10046 trace file, or something else?).
B. The value of the OPTIMIZER_MODE parameter controls the OPTIMIZER_MODE! Setting the value of that parameter to CHOOSE does not allow it (the parameter) to choose a different OPTIMIZER_MODE. This must be a trick answer.
C. The OPTIMIZER_MODE parameter does not select whether or not a particular query performs full table scans. Of course if the cost based optimizer is used, it may select to perform full table scans or use index access paths, assuming that indexes are present. This must be another trick answer.
D. Well, it is obsolete (or better termed deprecated), however that did not happen when Oracle 8.0 was released.
If I had to pick an answer, I would pick D and then ask the quiz examiner if “7” is a typo.
4. This is a tough question. A cost-based OLTP query could use any of the 4 hints listed – the hints would be handled like any other comment; it is not the “query” that *uses* the hint. The problem is that it is the cost-based optimizer that uses the hints, and it might handle some of the hints as simple comments – especially if all four hints are included in a single SQL statement.
SELECT NAME, INVERSE, SQL_FEATURE, VERSION FROM V$SQL_HINT WHERE NAME IN ('ALL_ROWS','FIRST_ROWS','CHOOSE','RULE') ORDER BY NAME; NAME INVERSE SQL_FEATURE VERSION ---------- ---------- ------------------ ------- ALL_ROWS QKSFM_ALL_ROWS 8.1.0 CHOOSE QKSFM_CHOOSE 8.1.0 FIRST_ROWS QKSFM_FIRST_ROWS 8.1.0 RULE QKSFM_RBO 8.1.0Oracle Database 11.2.0.1 recognizes all 4 hints as valid.
If I had to pick an answer, I would pick E., none of the above. I am thinking that *none* of the hints are most likely to be used by the query. A hint is just a comment to the query.
I am beginning to wonder if I even need to answer the questions in the second quiz – I think that I have already failed this quiz. 🙂