February 20, 2012
My copy of the “Oracle Database 11gR2 Performance Tuning Cookbook” arrived from Amazon, and I will say that I like the seven steps for solving performance problems that is found on page 12, although the diagram of the process on page 14 may lead to a condition known as Compulsive Tuning Disorder.
I am delighted to see that the book makes use of test case scripts, which often allow the reader to demonstrate that the book’s suggestion works in the reader’s Oracle environment. One such test case is provided on pages 54-55. I will not reproduce the test case script here, but the script may be found in the download library for the book, in chapter 2’s 2602_02_StoredProcedure.sql file. The download library for the book may be found by selecting the book from this page, and entering your email address.
After connecting to the database:
sqlplus /@TESTDB AS SYSDBA
The test case script compares the performance of this SQL statement that is directly executed in SQL*Plus:
SELECT PROD_ID, SUM(AMOUNT_SOLD) FROM SH.SALES GROUP BY PROD_ID;
With the performance of returning a SYS_REFCURSOR to a SQL*Plus variable that is declared as a REFCURSOR datatype. The book shows that the SQL statement executed directly in SQL*Plus required 1.26 seconds, while the method using the REFCURSOR required just 0.45 seconds, with the obvious extension being that the second method is more efficient. I tried the test case, and found that the normal SQL statement executed in SQL*Plus required 0.47 seconds, and the REFCURSOR method required just 0.26 seconds, so my results are consistent with those from the book – the REFCURSOR method shows less Elapsed time just as stated in the book.
What, if anything, is wrong with the above quote (test case) from the book?
The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.
Charles Hooper's Oracle Notes 
Well, when a PL/SQL returns an object of the SYS_REFCURSOR type, the underlying query is parsed and authorized, which eliminates the need to do it again. That was done during the execution of the PL/SQL procedure. There are several stages in the query execution:
1) Check the syntax of the statement.
2) Authorize the statement and check whether the session trying to execute the statement has the necessary
privileges to do so.
3) Search the baselines and see whether there is a usable baseline. If there is, the next two steps are skipped.
4) Search the SGA (V$SQL) for the same statement. If found, compare the execution environments for every
copy of the SQL found and see if there is a match. If there is, reach into V$SQL_PLAN and use the existing
plan. This gets a bit more complex than that, if the cursor is bind aware and cardinality feedback is used.
5) Determine the execution plan for the statement, possibly by merging it with the next step. This is a really
complex step.
6) Bind the variables.
7) Execute the statement.
8) Fetch the results.
When a SQL_REFCURSOR object is returned, all the steps prior to the step 6 have already been done, within the procedure itself. It is easily conceivable that for a simple statement like “select * from emp”, more than 50% of the execution time will actually be spent on the phases 1 – 5.
Furthermore, it is not logical that using SYS_REFCURSOR would be faster than executing it directly. The steps that Oracle needs to take are the same, regardless of whether the SQL is executed directly or is handed to Oracle through a PL/SQL wrapper. In the latter situation, Oracle has to deal with PL/SQL as well, which is a separate parser and added complexity. PL/SQL wrapper can hide the first few phases, but I doubt that any speed is gained. Lastly, there is a methodology problem, too. The execution time of the procedure should have been added to the comparison. I doubt that the results would still be the same.
In other words, the quote is as accurate as they get.
Without downloading the script, I can’t comment in detail, but a few things:
1) There is always the possibility that some things are done once and for all the first time you execute a query, so whatever runs first will incur that overhead. As a sanity check, run the REF CURSOR test first and see if the results are similar.
2) I can think of a few “once and for all” possibilities: parsing the statement (if it is really the same to Oracle); physical I/O.
3) I don’t know how the fetching is done in this script, but different fetch sizes can cause different run times.
4) If the results of the query are actually displayed on the screen, most of the time is spent displaying outside the database. Measuring “DB time” would be a more accurate indicator.
Hi.
Not clear from the blog post (probably intentionally). Did the ref cursor test case do all of the following, or just step 1?
1) Return the ref cursor.
2) Fetch the data using the ref cursor.
3) Close the ref cursor.
If it only did step 1 it is a pretty unfair comparison.
Cheers
Tim…
Lots of good answers so far, having not seen the script. I willl share my review comment of this section in the book either today or tomorrow (my answers will be a little vague until then).
SQL*Plus output the Elapsed Time immediately after returning the ref cursor to the SQL*Plus variable, just as it did after displaying all of the rows from the SELECT statement in the first test.
That sounds very much like it is just step (1) to me… I’ll wait with baited breath… 🙂
For not having an opportunity to see the script, Mladen, Stew, and Tim all came very close to the same conclusion that I reached *after* studying the script and enabling a 10046 trace for the execution. I forgot to mention that the buffer cache and shared pool were flushed before each segment of the test run – Stew covered that as a potential problem that the second test would be provided an unfair advantage.
As I was studying the script, my thoughts were similar to those shared by Mladen… doing the same amount of work, introducing a context switch, and still executing faster. I also thought about the first part of Stew’s #4 item. The 10046 trace revealed what was happening – as Tim hinted, the second test only performed step #1 in his list.
Below is the book review summary of the problem as I wrote it up last night:
The elapsed time comparison between the directly executed SELECT statement, and the REFCURSOR that is returned by the SH.SALES_BY_PRODUCT procedure is not valid for a couple of reasons:
1) The script is run by the internal user rather than a normal user, which can lead to unexpected performance differences.
2) The SELECT statement method displays its rows to the screen, so it is subject to delays caused by formatting the output for the SQL*Plus window (SET AUTOTRACE TRACEONLY STATISTICS may be used to reduce the impact of the formatting delays, but that change had little effect).
3) The REFCURSOR method, because it involves PL/SQL, will be subject to a context switch while the normal SELECT will not be subject to the context switch – the associated delay is operating system dependent and should suggest that something is wrong with the test result.
4) While the normal SELECT statement test actually fetches the rows, the REFCURSOR method does not, as can be seen within an enabled 10046 trace (the normal SELECT will show a FETCH line that is preceded by WAIT lines, while the REFCURSOR method will not show a FETCH line in the trace file).
With all due respect to the book authors, there is something fundamentally wrong with writing a “performance tuning cookbook”. The “cookbook” approach introduces more problems than it solves. Performance is, at least the way I understand it, a way of thinking. It starts from the application design and the database design, using the proper tools and the proper monitoring. I have recently read K. Gopalakrishnan’s 11G RAC handbook which has pleasantly surprised me, by suggesting functional partitioning of the applications and moving all I/O intensive sessions to a single node. That is thinking from the ground up, starting with the application design. Using so called “Jonathan parameter” like “_make_things_go_faster” or “_silver_bullet=on” usually doesn’t resolve the performance problems.
When I see a question like “my query is slow, will you help me, puhleeeease!” I usually suggest carefully looking into the data model, questioning statistics, looking into the use of the table and, as the last and the least desirable option, use hints and/or change parameters.
Performance tuning is a reactive process, there is no “being proactive”, a favourite management phrase these days. I might be able to spot the problem before the users do, but that’s as fas as “being proactive” goes. The reason lies in the question “what is tuning”?. The answer to this question is “tuning is a reaction to a performance problem”. We have all been in the situation where an end user calls the DBA service and claims that “the database is slow today”, in the same tone of voice as if the claim was that “there a great disturbance in the Force is”, to put it in Yoda’s words. Being a DBA and not a Jedi knight, I start from looking into the application and seeing where the time is spent. More often than not, the problem is in application. Putting the database squarely at the centre of attention is usually wrong and even counter-productive. A “cookbook approach” claiming that “in this situation, such recipe should be used” actually deters people from thinking properly and engage in the performance thinking when there is still time, while the application is being designed. Performance is a way of life. Having a book like this is akin to gorging yourself with burgers and French fries and then using pills to lower cholesterol and get rid of the heartburn. Ask your DBA about the performance tuning book. The side effects are wrong design, running out of resources, low customer satisfaction and increased outages. Ask your DBA if the performance cookbook is right for you.
Performance is a way of life, like bushido, not a fast food issue.
Mladen: Although I agree with most of what you said, I still think there is a need for the “cookbook” approach. Us old timers have a very focused view of the database world and how someone should progress to becoming a DBA. The reality is many people are just given the job even though they are vastly under qualified. It’s stupid, but that’s the way it seems to be going these days. Anything that will give them a quick win and keep them in the job long enough for them to become a good DBA is probably a good thing from their perspective.
Just a thought.
Tim… 🙂
Tim, I agree, the road to becoming a DBA is long and covered with hardships. There is a Latin proverb, “per aspera ad astra”, which describes it adequately. However, I doubt that a “performance cookbok” will trigger a transformation for a junior DBA on the road to Damascus, into a senior DBA. There are two Oracle books that caused me to have an epiphany: Cary Millsap’s and Tome Kyte’s “architecture” book. Judging by the title, I wouldn’t put my money on this book to have the same effect on anybody. It might be a good learning tool, though. Now that I discussed this book so much on Charles’s blog, I feel compelled to buy it and actually read it. By the way, speaking of great books, it would not be right to skip the latest book from the furniture store:
Expert Oracle Practices: Oracle Database Administration from the Oak Table
That’s an excellent book. I also think that Jonathan’s latest “Core” book is phenomenal, but it is kind of hard to read. I needed more than a week to read it, study dumps and understand it. I probably haven’t understood it properly yet, I plan to re-read it in May (6 months after the initial reading). I wouldn’t recommend that one to a newbie.
[…] is Slower than Returning a REFCURSOR from a PL/SQL Procedure – What is Wrong with this Quote? Charles Hooper […]