Book Review: Practical Oracle8i: Building Efficient Databases

I bought this book a couple weeks ago, after reading a couple reviews from different sources stating that while this book is a bit old, much of the information contained within is still useful. But, why spend the money buying a book that is eight years old, when the last database maintained in-house was moved to Oracle 10g R2 three years ago, and Oracle 11g R2 is likely set to be released in just a couple months? As fast as computer technology, and computer software for that matter, changes, is there much value in eight year old information? I was very pleased with the author’s “Cost-Based Oracle Fundamentals” book, so I thought that it was wise to buy a copy of this book before it disappears from the retail market. Was it a waste of money? After all, there is no coverage of ASSM tablespaces, MEMORY_TARGET parameter, SGA_TARGET parameter, PGA_AGGREGATE_TARGET parameter, AWR, Enterprise Manager Database Control, or any other feature introduced after Oracle 8.1.6.

If one ignores what likely amounted to ten pages of bug descriptions related to problems which must be avoided in Oracle 8.1.5 and 8.1.6, 95% of the book information is still relevant to people administering Oracle 9i through Oracle 11g. If one ignores this quote from page 28, “Of course the buyers may decide that they can get a really good deal by buying 4 35GB disks instead of the 16 9GB disks you requested… and maybe 4 600-MHz CPUs would be cheaper than the 12 220-MHz CPUs you wanted,” the relevance and accuracy of the advice dispensed in the book does not diminish with time, and that cannot be said for many Oracle related books of the same vintage. It might even be an interesting mental exercise to read the book with a critical eye, attempting to identify limitations outlined within the text which no longer apply to more recent releases of Oracle – one would hope that the bug related limitations have long since been exterminated.

Despite having read a couple books by Thomas Kyte a couple years ago, as well as several others written by various respected authors, I still managed to record eight pages of typewritten notes while reading the Practical Oracle 8i book. Topics of the notes seemed to fall into the categories of “I remember that, kind of”, “*There* is the missing link that connects the facts of the seemingly unrelated events”, “Wow, I never thought of that as a possibility”, and “That fact, or a question related to that fact, was just mentioned on one of the Oracle forums, or on a technical Oracle blog”.

Paraphrased sections of the book, the case against rebuilding indexes on a scheduled basis, circa 2001 (and probably just as accurate today): “When an index block becomes completely empty, it is placed in the index’s freelist, but is not removed from the structure until the block is reused. The index block, when reused, may be used in a different part of the index structure.” Formulas are floating about on the Internet purporting to indicate when an index should be rebuilt based on criteria which is only updated after analyzing an index. “Analyzing an index will take out a mode 4 (shared) lock on a table, in the process preventing inserts, updates, and deletes within the index. An online rebuild of an index requires two periods in which the parent table is locked – once at the start of the rebuild and once at the end of the rebuild. Waits caused by the locks will not show in V$LOCK, and V$SESSION_WAIT will show waits on a NULL event. It is not possible to perform an online rebuild of bitmap indexes, reversed indexes, LOB indexes, secondary indexes on index organized tables, or on function based indexes. During an online index rebuild, space will be needed for the original index, the rebuilt index, and a logging segment named SYS.JOURNAL_nnnnn. Unlike the rebuild command, coalesce does not demand extra space to maintain two copies of the index, nor does it need to lock the table, but using coalesce does increase the chances of other sessions hitting a snapshot too old error.”

Paraphrased from the book, the case against and for using the SYS user for an export (CONISTENT=Y cannot be used when the SYS user is performing an export, per the “Oracle Database Utilities 10g Release 2, Original Export and Import” documentation): “Oracle 8i introduces row level security, which uses a PL/SQL function to apply an additional WHERE clause predicate to a table – row level security does not apply to the SYS user. It is important to use CONSISTENT=Y when exporting partitioned tables. When CONSISTENT=N is specified, the export of each partition in a table is treated as a separate transaction, and may be exported at a different SCN number (incremented when any session commits). When tables are exported which contain nested tables, the two physical segments are exported in separate transactions, potentially resulting in inconsistent data during the import if the export was performed with the default CONSISTENT=N.” So, what to do if the database is using partitioning, row level security, and nested tables? (Read the book to find the answer).

The book is written in a style which seems to mimic the type of conversation one might expect if an Oracle guru casually pulls up a chair in the library and starts telling the story of the little Oracle database engine that grew up to become the big database engine with occasional sharp teeth. The author’s casual language in the text is not what one would expect from a technical computer book, but the writing style does flow well. When I reached the last page of the final appendix, I thought to myself “is that all there is to the book, what happened next?” Reviewing my notes, I think that I am able to answer that question.

Struggling to Find a Couple Negatives:
* A couple scripts are missing obvious characters, such as a closing parenthesis.
* Features requiring an Enterprise Edition license were not identified as such until Appendix A (an important section of the book which briefly describes various features mentioned in the book, along with associated benefits and negatives).
* Appendix A failed to mention that analytic functions and function based indexes were not available to users on the Standard Edition of Oracle 8.1.x

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: