Useful Book Even without a Lot of Real Undocumented Secrets, July 1, 2009
The “Secrets of the Oracle Database” book with the subtitle of “Advanced administration, tuning, and troubleshooting using undocumented features” follows the same pattern as most of the other Apress books, with very well researched and verified information with careful references to well regarded books and external resources. Many code samples and demonstrations are provided throughout the book in the format of: tell me about something, and then show me/prove to me that it actually works. The topics in this book reminded me a bit of the topics discussed in the book “Oracle 10g Insider Solutions,” except for the fact that the “Secrets of the Oracle Database” book actually makes an effort (a very thorough effort) at indicating which features are available with each version of Oracle (through 188.8.131.52), which features require additional cost licenses (such as the use of AWR), and provides a good enough example of the features so that the feature may be utilized with an understanding of why the feature should be used. The “Secrets of the Oracle Database” book even does a thorough job indicating the permissions and/or roles needed to leverage the various features. Specific items that I found to be helpful:
- In most cases, commands are provided for Linux/Unix and the equivalents for Windows.
- Good description of SYS.AUX_STATS and the various functions to view and populate the CPU stats are described in the book.
- Very detailed description of raw 10046 trace files.
- Detailed listing of the purpose of the various database tables related to Statspack.
- Good summary of ORADEBUG functionality.
- Provides a warning not to adjust the hidden (underscore) parameters unless under the supervision of Oracle support.
With the above in mind, why not give the book a 5 out of 5 rating?
Several Oracle features/behavior which are described as undocumented are in fact fairly well documented and/or discussed in Metalink notes, on various Oracle related blogs, and various Internet forums:
- Page 143 describes ALTER USER IDENTIFIED BY VALUES as being undocumented while it is documented in Metalink (279355.1 last modified 27-OCT-2008 and 1051962.101 last modified 16-OCT-2008) and on several websites.
- Page 136 states that the TRACE=TRUE parameter for the exp/imp and expdp/impdp utilities is undocumented while it is documented in a couple Metalink notes (271782.1 last modified 17-JAN-2005 and 286496.1 last modified 21-APR-2009) .
- Page 337 states that DBMS_SYSTEM is undocumented while it is documented in a couple Metalink notes (286496.1 last modified 21-APR-2009 and 103267.1 last modified 20-NOV-2002 and 436036.1 last modified 09-MAR-2009 and DBMS_SUPPORT is described in 62294.1 last modified 25-OCT-2002), several books, and several websites.
- Pages 271 and 371 state that the 10046 trace file format is undocumented while it is documented in a couple Metalink notes (39817.1 last modified 09-DEC-2008 and 376442.1 last modified 25-JUN-2009), two books referenced by this book (“Optimizing Oracle Performance” and “Troubleshooting Oracle Performance“), and several websites.
- Page 299 states that “it is undocumented which parameter changes force the optimizer to consider a new plan,” after showing how changing OPTIMIZER_INDEX_COST_ADJ forced a change in the execution plan – but the book never went on to suggest checking V$SES_OPTIMIZER_ENV, V$SQL.OPTIMIZER_ENV_HASH_VALUE/V$SQL.OPTIMIZER_ENV, or a 10053 trace file.
There also appear to be a couple errors, or at least exceptions to some of the broad rules discussed in the book:
- Page 29 states that “V$PARAMETER is built in such a way that it lists documented (non-hidden) parameters only,” with documented parameters being those which do not begin with one or two underscore characters. This is a correct statement, until one of the hidden parameters is modified, with a command such as the following: ALTER SYSTEM SET “_OPTIMIZER_UNDO_COST_CHANGE”=’10.1.0.4′; (_OPTIMIZER_UNDO_COST_CHANGE is one of those parameters which are adjusted automatically when OPTIMIZER_FEATURES_ENABLE is set to a different value). Once the _OPTIMIZER_UNDO_COST_CHANGE parameter (or likely any _ parameter) is modified, it will then be listed along with the documented parameters in V$PARAMETER (tested on Oracle 10.2.0.2 and 184.108.40.206).
- Page 45 states “Since SELECT statements don’t benefit from unused indexes… it may be worthwhile to drop unused indexes.” Richard Foote’s blog provides evidence that those indexes which appear to be unused indexes may actually provide the cost based optimizer statistical information that it would not otherwise have. Also, not every use of an index is recorded as a use of that index.
Given the title and subtitle of the book, I expected much more insight into the internals of Oracle databases. For instance on page 277 when describing the content of a 10046 trace file, the book stated that cr is defined simply as “consistent reads”, while cu is defined simply as “current blocks processed”. Page 357 gives a little more detail on the cu statistic “call parameter cu (current read) corresponds to the statistic db block gets.” This limited description is disappointing as a book described as providing advanced administration, tuning, and troubleshooting techniques should be able to tell much more about the cr and cu statistics. The same might also be stated about the coverage of V$SYS_TIME_MODEL, V$LOCK, V$SQL_SHARED_CURSOR and several other features discussed in the book.
Several of the chapters (5, 6, 8, 13, 14, 15, etc.) are very short, ranging from two to five pages in length. It might have been a better idea to combine several of these small chapters and/or provide more knowledge of Oracle’s internal behavior in those chapters. The author seems to favor the word “contrary” as it appears many times throughout the book. The back cover of the book indicates that this book should be read after “Troubleshooting Oracle Performance” and/or “Expert Oracle Database 11g Administration”. Reading either of these two books before reading the book “Secrets of the Oracle Database” might make some of the secrets disclosed in the book seem quite ordinary.
In summary, this book’s contents would likely seem as “secrets” for those DBA who have worked with Oracle for only a couple years and for those DBAs whose last book read was an Oracle 8.0.5 administrator’s guide. This book contains a great collection of disconnected information about Oracle (no building process from one item to the next), but it sometimes stops short of providing undocumented secrets to those DBAs who have read a couple good books recently, read a couple good blogs, and followed along on the discussions of a couple Oracle related forums. Much of the information may be obtained from other sources, but it is helpful that so much of the information is contained in a single book.