Reading Material On Order 2

29052011

May 29, 2011

A year ago I wrote an article that had a couple of topics, one of which described three books that I put on order – ordered with the intention of writing reviews for all three books. Two of the books arrived in a timely fashion, while the third book has yet to arrive despite being ordered twice from Amazon (I had to rely on the Google Books viewer for the review).

I tested the PDF and ePUB (apparently used by the Nook) versions of this book. While the PDF version of the book accurately reproduces the book contents, I recommend staying away from the ePUB version, if possible. You can read about my adventures with the PDF version and ePUB version of that book at the end of one of my previous articles. It has been almost three years since my first read through of this book, and while I found a couple of minor errors (yet somewhat obvious errors that the author quickly addressed before I had a chance to read those sections of the book), I did not find anything negative worth mentioning in the original review. About a year ago I started re-reading the book, but only made it about 100 to 120 pages into the book before I had to break-away to something else. I do not recall taking any notes in the first 100 to 120 pages, but I do recall taking fairly extensive notes in later parts of the book during the initial read.

—

Beginning Oracle SQL

This book is intended as an update to the “Mastering Oracle SQL and SQL*Plus” book that was originally written by Lex DeHaan, and was apparently intended both and as an introduction to SQL as well as a brief demonstration of more advanced techniques. I was impressed with the quality and accuracy of the original book, and I was excited to see a follow-up book titled “Pro Oracle SQL“. The original “Mastering Oracle SQL and SQL*Plus” book did have a couple of problems: most of the pages lacked page numbers, various formatting problems that resulted in dropped characters, and a missing Oracle Database 10.1.0.2 CD that was promised on the book’s cover. I have not had a lot of time to examine the “Beginning Oracle SQL” book. However, I did notice that every page that should have a page number now has a page number, I have not noticed any formatting problems that resulted in dropped characters, and the front cover no longer advertises that the book includes Oracle Database on CD. I initially thought that all mention of analytic queries had been dropped from the “Beginning Oracle SQL” book due to the extended discussion of this topic in the “Pro Oracle SQL” book, and a search for the word analytic finds only a single page containing that word. Thankfully, it does appear that a couple of the analytic functions are mentioned in the updated book. It does not appear that this is just a simple reprint of the original book – the new book includes descriptions of Oracle Database 11.1 and possibly 11.2 features. More information will follow if I have a chance to read the entire book. The Google Books viewer application crashes when attempting to show book pages 33 and 34 side-by-side (pages 56 and 57 as displayed in Google Books), but the problem is not present when the Xoom is held in portrait orientation.

This book is set to be released in the middle of August 2011, so I bought the alpha copy of the book that currently includes chapters 3, 5, 6, 7, and 14. I bought this book because I was curious to see how the recipe format works for a book written on the topic of performance tuning. That format worked very well for one of the books that I reviewed, and mostly/almost worked for another book that I reviewed (with the notable exception of the SQL statements that were Oracle Database performance related). How well will the format work for a book that is specifically written about Oracle Database 11g performance tuning? Let’s just say that I hope that there is a good technical reviewer involved in this book project, that the authors listen to the technical reviewer, and that the alpha copy of the chapters were captured before the technical reviewer had a chance to examine the chapters. If the alpha copy of the book chapters actually shows the results after the technical reviewers provided recommendations, this will very likely be the first Apress title that I have read which will receive a 3 star, or very likely lower, rating on a 5 star scale when I write a review of the book. I do not want to go into a lot of specifics after a very quick examination of a couple of alpha chapters of a book, but I will mention a couple of examples of problems that I identified:

The introduction to chapter 3 states that the results of SQL queries and PL/SQL functions may be stored in the shared pool when a results cache is used. It might be a better idea to be much more specific, and state that Oracle Database is using the Result Cache Memory in the shared pool as indicated in the Oracle Database documentation, but maybe this is explained later.

In recipe 3-1 the authors mention that they would set the MEMORY_MAX_TARGET to 1500M, and then show a SQL statement that sets that parameter to 2G. This particular inconsistency will likely be caught in a later review of the book material. This recipe seems to be mirroring a page from the Oracle documentation library, only that the order of a couple commands were specified incorrectly in the book. The comment regarding the _TARGET parameters does not seem to apply to the SGA_TARGET parameter. This recipe states that “automatic memory management is the recommended approach to managing Oracle’s memory allocation,” but does not describe any of the potential pitfalls or operating system specific problem areas/limitations (reference1reference2reference3reference4reference5reference6). Beyond telling the reader to simply add the current PGA_AGGREGATE_TARGET and SGA_TARGET values to determine a starting point for MEMORY_MAX_TARGET, the recipe gives little insight into the “best” value for this parameter. For now I will ignore the numerous spelling mistakes, because the book editors will likely find and address those issues.

In recipe 3-2 the authors state, “if you want a segment to be cached (or pinned) in the buffer pool, you must specify the KEEP buffer pool [when creating the segment].” Something just doesn’t seem to be right (or complete) about this statement – for one, I wonder what the CACHE and NOCACHE clauses might affect? Secondly, this statement might be implying that blocks belonging to an object will only remain in the buffer cache if those objects are assigned to the KEEP buffer cache. The recipe states that the approximate size of the KEEP buffer pool can be determined by “summing up the size of all candidate objects for this pool” – great, now what happens when Oracle Database creates up to six (and potentially many more) consistent read versions of the objects’ blocks (reference)? The last two paragraphs of this recipe seem to indicate that it is the segments themselves (apparently all blocks) that are aged out of the buffer cache when space is needed to cache other blocks, rather than the segments’ blocks that are aged out of the cache – additional clarity is needed in these two paragraphs. The second to last paragraph also makes a claim that I am not sure how to interpret: “Oracle figures that if a single segment takes up more than 10% of (nonsequential) physical reads, it’s a large segment, for the purpose of deciding if it needs to use the KEEP or RECYCLE buffer pools.”

(Skipping around a bit) In recipe 3-13 we learn how to tune the redo log buffer. The book states, “since there’s no cost whatsoever to increasing the LOG_BUFFER size, feel free to set it to higher than the suggested maximum of 1MB for this parameter.” Earlier the book showed how to set the LOG_BUFFER parameter to a value of 4,096,000. I think that I recall that Oracle Database 11.1 was released after Oracle Database 10.2 ( 🙂 ), where the LOG_BUFFER parameter started being auto-set to a value slightly smaller than the granule size – the most common granule size for Oracle Database 10.2 databases was likely 16MB, while only databases with a SGA size less then about 1GB saw granule sizes of 4MB. The granule size rules changed again with the release of Oracle Database 11.1 and again with the release of Oracle Database 11.2. If you check the comments section of this blog article, one reader commented about the LOG_BUFFER being auto-tuned to nearly 512MB in size. I wonder how useful the ratio of ‘redo entries’ statistic value divided by the the ‘redo log space requests’ statistic value might be when trying to find the ideal value for the LOG_BUFFER parameter?

In recipe 5-1, how does one see a “latch on a log file”? The recipe also describes a view that has a name that ends with the suffix _HISTORY, but the book failed to mention the licensing requirements to access this view. When describing the V$SESSION_EVENT view, the book states, “The data in this view are available only so long as a session is active.” – considering that one of the columns in V$SESSION is named ACTIVE, I can see where a statement like this one could lead to confusion. The last paragraph of the recipe states, “Note that you can query the V$WAITSTAT view for the same information as well.” – that view was not previously introduced and in actuality, the V$WAITSTAT view does not produce information similar to the V$SYSTEM_EVENT view that was described a sentence or two earlier.

Recipe 5-2 queries V$ACTIVE_SESSION_HISTORY without first providing a warning regarding the licensing requirements to access this view. I have not experimented enough with ASH data, but I wonder if SUM(WAIT_TIME + TIME_WAITED) when grouped by SQL_ID and USER_ID could lead to incorrect assumptions regarding the wait time per SQL statement for the last 30 minutes. Is there a risk of double-counting, or counting incidental CPU usage immediately after a wait event completes, as part of the time spent in wait events for a specific SQL statement? I am left wondering why the authors did not suggest checking the APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, and JAVA_EXEC_TIME columns of V$SQL (or V$SQLAREA) periodically, and calculating the delta (change) values for specific SQL statements – those columns have existed as long as ASH (since the release of Oracle Database 10.1).

I guess that the above is a bit more lengthy than I had originally intended. Best of luck to the technical reviewers of the book.

My reviews do not identify all mistakes in the books that I read, but I try to cover as many of the easily identified errors as possible, and do my best to cover all of the difficult to spot errors (yes, I miss several of these errors in a typical review that other members of the Oracle community could easily identify). I also make an effort to identify the positive points of the books that I review, and in some cases that is a challenging exercise.

There is still a possibility that the “Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach” book could be very good as a finished product. Right now, I would rate the alpha copy of the book chapters at 2 out of 5 stars based on a close look at recipes 3-1 through 3-6 and 3-12 through 5-7. Take, for example, the following quote from recipe 5-3: “For example, if you see the db file sequential reads event (indicates full table scans) at the top of the wait event list, you must look a bit further to see how the database is accumulating these read events.” The next 3 sentences in the book are just as logically accurate as this one. Another example from recipe 5-7 that describes how to resolve log file sync waits, “Instead of committing after each row, for example, you can specify that the commits occur after every 500 rows.” This is later followed up by the statement “The log file sync wait event can also be caused by too large a setting for the LOG_BUFFER initialization parameter. Too large a value for the LOG_BUFFER parameter will lead the LGWR process to write data less frequently to the redo log files.” – didn’t recipe 3-13 tell me that there is no risk in sizing the LOG_BUFFER parameter too large? Sure, go ahead and tell the readers to modify the hidden parameter _log_io_size (without providing any warnings about modifying hidden parameters), and fail to mention that excessive time spent in the log file sync wait event could be caused by a CPU starvation condition.

I know from personal experience that the Apress editors are very good at spotting logical errors where one portion of a chapter is inconsistent with another portion of a chapter. The Apress editors are also very good at looking at what is written and letting the author know that a section must be rewritten so that the content can be understood by the reader. I *hope* that the technical reviewers of this book are also recommending adjustments for content accuracy.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: