Directly Executing a Query is Slower than Returning a REFCURSOR from a PL/SQL Procedure – What is Wrong with this Quote?

20 02 2012

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.








Follow

Get every new post delivered to your Inbox.

Join 139 other followers