Achieve better performance from your Oracle Database applications
- Learn the right techniques to achieve best performance from the Oracle Database
- Avoid common myths and pitfalls that slow down the database
- Diagnose problems when they arise and employ tricks to prevent them
- Explore various aspects that affect performance, from application design to system tuning
In Detail
Oracle's
Database offers great performance, scalability, and many features for
DBAs and developers. Due to a wide choice of technologies, successful
applications are good candidates to run into performance issues and when
a problem arises it's very difficult to identify the cause and the
right solution to the problem.
The Oracle Database 11g R2
Performance Tuning Cookbook helps DBAs and developers to understand
every aspect of Oracle Database that can affect performance. You will be
guided through implementing the correct solution in a proactive way
before problems arise, and how to diagnose issues on your Oracle
database-based solutions.
This fast-paced book offers solutions
starting from application design and development, through the
implementation of well-performing applications, to the details of
deployment and delivering best-performance databases.
With this
book you will quickly learn to apply the right methodology to tune the
performance of an Oracle Database, and to optimize application design
and SQL and PL/SQL code. By following the real-world examples you will
see how to store your data in correct structures and access and
manipulate them at a lightning speed. You will learn to speed up sort
operations, hack the optimizer and the data loading process, and
diagnose and tune memory, I/O, and contention issues.
The
purpose of this cookbook is to provide concise recipes, which will help
you to build and maintain a very high-speed Oracle Database environment.
Effectively apply performance tuning principles with concise recipes
What you will learn from this book
- Design applications that run at lightning speed
- Implement fast and scalable SQL and PL/SQL code
- Choose the correct structures to store the data and access them
- Optimize sort operations, such as order-by, Top-N queries, ranking, and set operators
- Help the optimizer to choose the right access plan to retrieve data at the best available speed
- Load data in the database at a faster speed by using the correct tools and options
- Tune the database memory to obtain maximum performance using available resources
- Tune the I/O operations, by designing a database over the I/O system
- Tune and reduce contention issues on data and structures by using an optimal design
Approach
In this book you will find both examples and theoretical concepts covered.
Every
recipe is based on a script/procedure explained step-by-step, with
screenshots, while theoretical concepts are explained in the context of
the recipe, to explain why a solution performs better than another.
Who this book is written for
This
book is aimed at software developers, software and data architects, and
DBAs who are using or are planning to use the Oracle Database, who have
some experience and want to solve performance problems faster and in a
rigorous way.
If you are an architect who wants to design better
applications, a DBA who is keen to dig into the causes of performance
issues, or a developer who wants to learn why and where the application
is running slow, this is the book for you.
Basic knowledge of SQL language is required and general knowledge of the Oracle Database architecture is preferable.
Customer Reviews:
I ordered the "Oracle Database 11gR2 Performance Cookbook" book shortly
after it became available for purchase. I was very curious to see how
the book compared with the similarly titled "Oracle Database 11g
Performance Tuning Recipes" book, as well as some of the other Oracle
Database performance books that are on the market. Packt is a fairly
new book publisher, and this book marks the first Packt book in my
collection.
The author of this book does not appear to be widely
known in the international Oracle Database community, although it does
appear that the author is an active reviewer of SQL Server and
programming books on an Italian programming focused website. The
author's LinkedIn page indicates that he obtained OCA and OCP
certification in 2002 and 2003, respectively, has a variety of
programming experience, and currently is an IT Manager.
One
important characteristic of this book that is missing from some of the
other Oracle Database performance focused books on the market is the
extensive use of test case scripts throughout most of the book that
allow the reader to reproduce the performance changes mentioned in the
book, in the reader's Oracle Database environments. The test case
scripts, related screen captures, and author's explanations of the
results are both a blessing and a curse for this book. It appears that
the author used a single Amazon Elastic Compute Cloud hosted database
instance with only one set of instance parameters and system statistics
for the various test case results and the author's descriptions of the
expected outcome when the inputs in the test case script are provided.
Had the author re-executed the test case scripts in another Oracle
Database environment, the author probably would have written quite
differently the explanations that follow the test case scripts. It is
not uncommon for 80% of some of the book pages to be consumed by one or
two SQL*Plus screen captures; combined with the slightly larger font
sizes, double-spacing between paragraphs, and apparent one and a half
spacing between lines in code sections, the technical content in the
book is a bit more limited than the page count might suggest.
So,
how well did the book's contents meet the level of expectations
provided by the book's front cover and the publisher's description of
the book? One of the bullet pointed descriptions of the book reads,
"Avoid common myths and pitfalls that slow down the database."
Unfortunately, the book reintroduces several myths and inaccurate
conclusions about Oracle Database that have diminished in frequency
during the last 10+ years. Some of the information in the book is of
good quality. However, the significant number of inaccurate, vague,
misleading, and/or over-generalized facts in this book suggests that the
author of this book may have not received sufficient guidance from
Packt and the four technical reviewers of the book. The book
publisher's site currently lists no errata for the book, even though I
personally submitted 21 errata items to the publisher's errata reporting
site.
The author's native language is obviously not English, so
it is probably to be expected that some of the sentences in the book are
incomprehensible. Yet, there are also sentences in the book that use
completely different phrasing, close to that of a person who
double-majored in English and computer science with a focus on Oracle
Database. The consistent usage of the term "fields" in some sections of
the book, with the consistent usage of the term "columns" in other
sections of the book is but one example of the style shift that is
present in the book. Some of the sentences found in the book are oddly
familiar, and although I was not able to identify the original sources
of all of the oddly familiar sentences, I did manage to locate a few.
What constitutes plagiarism in an Oracle Database book, and how much
change is required to the original material to avoid the plagiarism
label? Would slightly reformatting a section of text to replace dashes
with colons be sufficient to avoid the label? Would changing the order
of some sentences and eliminating other sentences be sufficient to avoid
the label? Would performing simple word substitutions here and there,
or shortening sentences be sufficient to avoid the label? I am not
suggesting that there is rampant plagiarism in the book, but one does
need to question when that plateau is reached in a book about Oracle
Database.
While in some respects this book is more useful to the
reader than the "Oracle Database 11g Performance Tuning Recipes" book
due to the inclusion of test cases, both books seem to omit the
reasoning behind why and when someone might consider performing the 80
or so tasks/recipes mentioned in the books. Vague, inaccurate,
over-generalized, and out of date descriptions of Oracle Database
behavior are limiting factors of both books. This review is quite long,
and likely will not appear in full on Amazon - see my blog for the full
review.
Data Dictionary Views:
* DBA_VIEWS (page 20)
* V$FIXED_TABLE (page 21)
* V$LIBRARYCACHE (page 52)
* V$STATNAME, V$MYSTAT (page 53)
* SYS.SEQ$ (page 65)
* DBA_MVIEWS, USER_MVIEWS, ALL_MVIEWS (page 69)
* INDEX_STATS (pages 127, 128)
* V$SYSSTAT (page 160)
* V$SESSION (page 205)
Parameters:
* CURSOR_SHARING (pages 9, 38)
* TIMED_STATISTICS (pages 20, 201)
* LOG_CHECKPOINTS_TO_ALERT, BACKGROUND_DUMP_DEST (page 28)
* STATISTICS_LEVEL (pages 29, 32)
* CONTROL_MANAGEMENT_PACK_ACCESS (page 32)
* QUERY_REWRITE_ENABLED, QUERY_REWRITE_INTEGRITY (page 70)
* DB_16K_CACHE_SIZE (page 84)
* MAX_DUMP_FILE_SIZE, TRACEFILE_IDENTIFIER (page 201)
* SQL_TRACE (page 202)
Hints:
* APPEND (page 72)
* INDEX (page 121)
Comments, Corrections, and Problems:
*
The book states, "The first rule in writing applications which connect
to an Oracle Database is to always use bind variables, which means not
to include parameters in SQL statements as literals." The statement
should be clarified that this is a general recommendation. There are
times when literals should be used rather than bind variables, for
instance if there are very popular and unpopular values in a column, it
might be wise to prevent the sharing of execution plans when a very
popular or very unpopular value is used in the WHERE clause. A
correction/clarification is provided on page 51 (page 8).
* Steps
for creating a database with the Oracle Database Configuration
Assistant seem to be out of place in a performance tuning book (pages
17-19)
* Uses the term "fields" where the term "columns" should be used (page 21).
*
The book demonstrates the use of ANALYZE TABLE ... COMPUTE STATISTICS,
and DBMS_UTILITY.ANALYZE_SCHEMA to collect object statistics. The book
states that ANALYZE is retained for backward compatibility, but the book
provides no warning that using ANALYZE to collect statistics could be
problematic since the release of Oracle Database 8.1 (reference page
21).
* The book uses the word "elaborate" rather than "create" or "generate" (pages 24, 26, 27, 31, 37)
* The book demonstrates the use of AWR without first mentioning the licensing requirements of that feature (pages 30-31).
*
Word substitution error: "... and we experiment a lack of performance
in another period, we can elaborate two reports..." (page 31)
*
The book demonstrates the use of ADDM without first mentioning the
licensing requirements of that feature. The book also states, "ADDM is
enabled by default in Oracle Database 11g; it depends on two
configuration parameters..." Unlike with Oracle Database 10.1 and 10.2,
ADDM is not enabled by default in the Standard Edition of Oracle
Database 11.1 or 11.2, nor can it be legally enabled on the Standard
Edition. While ADDM is enabled by default in the Enterprise Edition
11.1 and 11.2, it cannot be legally used without a Diagnostic Pack
license (pages 32-35).
Read More About This Book..
* The book suggests the system-wide use of
the deprecated SIMILAR value for the CURSOR_SHARING parameter as one of
two solutions to address a hard parsing problem in a test case script
(page 38).
* The book states, "Now the Soft Parse is 97.84
percent." The output shown in the book actually indicates a Soft Parse
percent of 99.20. The instance efficiency numbers in the output are
identical to those found on page 40, so this might be an indication of a
copy-paste error (page 39).
* The book states, "If the
PreparedStatement is not closed, it can be executed multiple times -
changing the value assigned to bind variables - and only a `light'
soft-parse will occur, with no syntax and semantic check." If the SQL
statement is held open - there will NOT be a "light" soft-parse (session
cached cursors are not discussed in this section of the book, which
would allow a "light" soft-parse if the cursor is NOT held open) (page
52).
* 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 executed 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 the timing 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)
(pages 54-55).
* The output of the Java version of the SQL*Plus
test script found on pages 54-55 conflicts with the author's intended
result. Directly executing the SQL statement required 1.438 seconds,
while using the REFCURSOR in the Java code required 1.722 seconds. The
performance difference may be more significant than shown, because the
direct execution of the SQL statement test was performed first, and the
timing results include the time to flush the shared pool and the buffer
cache (the first call will almost certainly take longer than the second
call) (pages 56-58).
* The book uses a test case script to
demonstrate the negative effects of using a "COUNTER" table rather than
using a sequence to provide the same counter value. The test case
script uses a trigger on the table to populate the counter column in the
table, and the test case script does show that performance improves
with the use of the Oracle sequence. The test case script, however,
should have also included a test that completely eliminates the trigger
on the table, populating the TRAVELID column by including
TRAVEL_SEQ.NEXTVAL directly in the SQL statement that populates the
table. My timing results show that the counter trigger-table method
completes in 0.45 seconds, the trigger-sequence method completes in 0.14
seconds, and the select-sequence method completes in 0.03 seconds
(reference pages 60-62).
* Accidental word substitution, "... and
if the high watermark is reached, it caches other X numbers in the same
manner." "other" should be "another" (page 65).
* The author
incorrectly read the AUTOTRACE generated execution plan. The book
states "We can see that in the execution plan, there is full table
access to the SALES table examining 918K rows and reading 8075 KB." An
AUTOTRACE generated execution plan shows an estimated execution plan
that may differ from the actual execution plan in some situations, such
as cases where bind variables are involved. Additionally, an AUTOTRACE
generated execution plan shows the predicted number of rows that will be
returned (not examined), and the predicted volume of data that will be
returned (not read) based on the existing statistics for the objects
(page 67).
* The book states, "However, from the execution plan,
the number of rows processed is 72, and each row is 648 bytes long."
Once again it is important to stress that the execution plan is a
predicted execution plan generated by AUTOTRACE. The estimated 72 rows
returned by the operation in the execution plan does agree with the "72
rows processed" displayed in the actual statistics for the execution,
but that will not always be the case for an AUTOTRACE generated
execution plan (it happens to be the case because statistics were
collected for the materialized view with a 100% sample rate). The
statement that each row is 648 bytes long appears to be the result of
misreading the previous execution plan, which estimated that 72 rows
consuming 648 bytes total would be returned from operation 0 in the
execution plan. The AUTOTRACE generated execution plan for the
materialized view predicts that 72 rows consuming 1872 bytes will be
returned from operation 0 in the execution plan, which shows a predicted
row length of 1872/72 = 26 bytes per row (pages 67-68).
* The
book states, "In the latter case [after flushing the buffer cache], we
have 4047 consistent gets and 240 physical reads..." There are a couple
of issues with this test case, found in the source code library file
2602_02_Materialized Views.sql. First, the script in the source code
library uses "ANALYZE TABLE SH.MV_SALES_BY_PRODUCT COMPUTE STATISTICS"
to collect the statistics on the materialized view, while the book shows
the use of "EXEC DBMS_STATS.GATHER_TABLE_STATS" to collect the
statistics - the collected statistics from the ANALYZE table command
could very easily be different from the collected statistics from the
DBMS_STATS.GATHER_TABLE_STATS command. The screen capture shown after
flushing the buffer cache and re-executing the select from the
materialized view does show 4,047 consistent gets and 240 physical block
reads, as stated in the book, but it also shows 20,544 recursive calls
where 0 recursive calls were shown prior to flushing the buffer cache -
this recursive call count figure indicates that something else happened
beyond the author flushing the buffer cache. My test results with just
flushing the buffer cache show 8 consistent gets, 6 physical reads, and 0
recursive calls. The author also apparently flushed the shared pool,
which triggered the recursive calls and the majority of the consistent
gets and physical block reads (15,296, 2,978, and 177 respectively).
The author probably should mention that the test case and advice will
not work in a Standard Edition database, and should also state that the
decision whether or not the materialized view is used is a cost-based
optimizer decision (page 68).
* The book lists "QUERY REWRITE" as
a required privilege to create materialized views. The Oracle Database
11.2 (and 10.1) documentation state that the QUERY REWRITE privilege is
deprecated, and thus not needed (reference page 69).
* The book
states, "The same parameters [QUERY_REWRITE_ENABLED, and
QUERY_REWRITE_INTEGRITY] have to be enabled to use another
functionality, function-based indexes." QUERY_REWRITE_ENABLED must be
set to TRUE in Oracle Database 9.2 to use function-based indexes, but
that requirement disappeared in Oracle Database 10.1 (page 70).
*
The book states, "We encounter row chaining when the size of the row
data is larger than the size of the database block used to store it."
While this statement is correct, the book omits a secondary cause of
chained rows - Oracle database supports a maximum of 255 columns in a
row piece, so tables with more than 255 columns will necessarily have
chained rows (page 84).
* The book casually demonstrates setting
up a 16KB block size tablespace in a database that has a default 8KB
block size. The book provides a list of several advantages for
including smaller or larger than default block sizes in a single
database including, "Faster scans: tables and indexes that require full
scans can see faster performance when placed in a large block size."
This justification is incorrect for several reasons including the fact
that the DB_FILE_MULTIBLOCK_READ_COUNT parameter is scaled up for
tablespaces that use a smaller than database default block size, and
scales the parameter down for tablespaces that use a larger than
database default block size. All of the justifications found on page 88
appear to be copied verbatim from a commercial website page. The book
does not discuss the bugs and unexpected optimizer cost changes that
might result from using multiple block sizes in a single database
(reference reference2 pages 84-88).
* Step 5 contains two typos:
using angle brackets (less than and greater than signs) rather than
single quotes, and a spurious 3 after the semicolon (page 89).
* Step 7 and 9 contain typos: using angle brackets (less than and greater than signs) rather than single quotes (page 90).
* Steps 4 and 5 contain typos: using angle brackets (less than and greater than signs) rather than single quotes (page 97).
*
Step 14 contains a corrupted SQL statement: "CREATE.5* FROM HR.BIG_ROWS
WHERE 1=0;". Steps 15, 16, and 19 contain typos: using angle brackets
(less than and greater than signs) rather than single quotes. The
author should have mentioned at least one of the possible problems with
this approach, which might include triggers on the table, foreign keys
that point to the table, and the potential statistics problems caused by
the use of the ANALYZE TABLE command (page 92).
* The book
states about the DBMS_SPACE.CREATE_TABLE_COST example, "In this
procedure we have set the tablespace to use the average row size and the
row count..." The purpose of this function is to estimate space usage,
not to make changes to a tablespace (page 95).
* Step 1 contains an extraneous ".5" in the command.
* Pages 96-112 are present in the book, but omitted from this review.
* Steps 11 and 13 use angle brackets (less than and greater than signs) rather than single quotes (pages 116-117)
*
The book states, "We can also create a function-based descending
index." This is a strange statement - all descending indexes in Oracle
Database are function-based indexes (page 119).
* The book
states, "... this test allows us to dispel a myth. Oracle uses the
indexes even if the leading columns are not referenced in the WHERE
predicate of the query. We can see that in such a case, the operation
will be an INDEX FAST FULL SCAN." In this case, the author is
incorrectly attempting to generalize a special case into a general rule.
Firstly, there is no myth to dispel - Oracle's query optimizer has had
the ability to use INDEX SKIP SCAN operations when the leading column
of an index is not specified in the WHERE clause, since the release of
Oracle Database 9.0.1 a decade ago - but that access path is usually
only advisable when there are few distinct values in the leading column
of the index. The author's test case is a special case because all of
the columns selected from the table are present in the index structure
(page 119).
* The book states, "If we use a regular index to
access the data, Oracle is unable to do the sort in a mixed way, in a
query like this." The author then shows a SQL statement with the first
column in the ORDER BY clause sorted in descending order and the second
column in the ORDER BY clause sorted in ascending order. At this point
in the book, the author has not yet stated that Oracle Database is able
to read index entries in an ascending or descending order through a
normal (ascending sorted) b*tree index, so this sentence in the book is
confusing - almost to say that Oracle Database is not able to sort one
column in ascending sequence and a second column in descending sequence -
that concept is obviously false. It would have been more accurate for
the book to state that, "Oracle Database is unable to _avoid_ a sort
operation when accessing the rows through a concatenated index if both
of the columns in the index are sorted in ascending sequence, the ORDER
BY clause of the SQL statement specifies that one and only one column
contained in the index should be ordered in descending sequence, and the
second column in the concatenated index is included in the WHERE
clause." (page 120)
* A self-contradicting sentence, "In the
first case, we have a full table scan, because we cannot retrieve all of
the data from the index, so we have to do a TABLE ACCESS BY ROWID
operation for each row, which satisfies the predicate." Full table scan
probably does not belong in that sentence (page 121).
* The book
states, "In the next screenshot, we can see that Oracle knows (from the
table statistics) that only 43 rows satisfy the where condition." It
is important to stress that the autotrace generated execution plan only
shows the estimated number of rows that will be returned by an operation
- the author's query, in fact, retrieves a single row. The index that
the author specified in the index hint was created on the columns
CUST_LAST_NAME and CUST_YEAR_OF_BIRTH (in descending order), yet the
author's query only included the CUST_FIRST_NAME column in the WHERE
clause - it is ridiculous to force the optimizer to use this index with a
hint (page 121).
* The index's clustering factor was not
mentioned in the discussion of what determines the point at which it is
more efficient to access a table through an index access path, rather
than a full table scan - only the average row length was described as a
consideration and the percentage of the rows that need to be retrieved.
It could very well be the case that with a very poor clustering factor,
that it is more efficient to retrieve less than 1% of the table's rows
through a full table scan, rather than an index lookup (page 122).
*
The book should define "intra-block fragmentation" which is the benefit
that the book lists as resulting from rebuilding indexes (page 123).
*
The two session example of one session rebuilding an index while a
second session executes a SELECT and INSERT seems to be pointless. The
second session does not use the index that the first session attempts to
rebuild, instead a full table scan is performed on the BIG_CUSTOMERS
table, followed by an index unique scan of the CUSTOMERS_PK index. An
index named IX1_BIG_CUSTOMERS was created in the script, yet the script
attempts to rebuild a non-existent index named IX1_MYCUSTOMERS. The
test case only shows an example of efficiency gains due to blocks being
buffered in the buffer cache. The book should have mentioned that an
online rebuild and parallel rebuild are only possible in the Enterprise
Edition of Oracle Database (pages 123-125).
* Step 10 uses angle brackets (less than and greater than signs) rather than single quotes (page 126).
*
The book states, "We have used the PARALLEL option too, to speed up the
rebuild process." While specifying PARALLEL during an index rebuild
may speed up the rebuild, it is important to note that this results in
an index with a parallel degree that should be manually reset to the
original value, once the rebuild completed (page 127).
* The book
states, "However, when we have a table on which there are many INSERTs
and DELETEs, we could schedule an index rebuild, because when deleting
an index entry, the space is not freed in the index leaf, but just
marked as deleted. If we have massive DELETE and INSERT operations, we
could have a skewed index structure, which could slow performance due to
intra-block fragmentation." The book should have defined what is meant
by "skewed index structure" - does the book mean, for instance, that
one portion of the index could have a BLEVEL of 2 while another portion
of the index could have a BLEVEL of 3 - if that is the case, the book's
statement is incorrect. If the book's definition of "skewed index
structure" is that some leaf blocks of the index will be more densely
packed than other leaf blocks in the same index structure, then that
should be considered normal behavior for Oracle indexes - an occasional
coalesce might be used to combine index entries in logically adjacent
leaf blocks, but scheduling index rebuilds is neither required, nor
recommended. Depending on the order of the inserted values in relation
to the order of the entries in the index leaf blocks, an index leaf
block split operation could evenly divide the existing index entries
between two leaf blocks (a 50-50 split, resulting in both index blocks
being 50% utilized, if the inserted value is not the highest value that
would be inserted into the leaf block), or all of the existing entries
will remain in the existing leaf block and the new entry will be placed
by itself into a new leaf block (a 90-10 split). A deleted index entry
will remain in the block at least until that transaction is committed,
but any post-transaction insert into the block will clear out all
deleted index entries in the block. Deleting all table rows with index
entries at the low end of the index (the values were populated by a
sequence, for example, and are deleted in the same sequential order)
could leave many blocks in the index structure with nothing but deleted
index entries, but that situation should only result in a performance
problem if SQL statements attempt to determine the minimum value for the
indexed column, or to some extent, fast full index scans and full index
scans (reference reference2 page 127).
* The book states, "If
the value for DEL_LF_ROWS/LF_ROWS is greater than 2, or LF_ROWS is lower
than LF_BLKS, or HEIGHT is 4 then the index should be rebuilt." Some
of the advice found on the Internet suggests that if DEL_LF_ROWS is 20%
of LF_ROWS, then the index should be rebuilt - did the author of this
book intend to write "If the value for DEL_LF_ROWS/LF_ROWS is greater
than 0.2"? Why should the result of DEL_LF_ROWS/LF_ROWS be a
consideration of whether or not an index should be rebuilt - is it
supposed to measure the amount of wasted/unused space in the index leaf
blocks? The next INSERT/UPDATE DML operation in a given leaf block will
clear out the index rows that are flagged as deleted, but then does
that imply that the space is not wasted (or is the space wasted)? What
if there are many index blocks that are roughly 50% utilized due to a
large number of 50-50 leaf block splits, is that space not wasted (or is
the space wasted)? Since the formula DEL_LF_ROWS/LF_ROWS really does
not describe the percent of used space in the index, it is probably best
to just ignore the result of that formula. DEL_LF_ROWS/LF_ROWS can
never be greater than 1 because the statistic found in the LF_ROWS
column includes the DEL_LF_ROWS statistic. The second criteria suggests
comparing LF_ROWS to LF_BLKS, such that if on average there is less
than one index entry per leaf block, that the index should be rebuilt -
there can never be less than one index entry per leaf block, because the
leaf block will be detached from the index structure when all rows are
removed from that leaf block. The final criteria suggests rebuilding
the index when the height is exactly 4 - does that mean that an index
with a height of 5, 6, 7, etc. does not need to be rebuilt? What if
after rebuilding the index it still has a height of 4 - will it help to
rebuild a second time? (page 127)
* The book states, "When we
rebuild an index, we can add the COMPUTE STATISTICS option to that
statement." Since the release of Oracle Database 10.1, statistics are
automatically collected when indexes are created and/or rebuilt, so the
COMPUTE STATISTICS clause is unnecessary (page 127).
* Steps 6 and 9 uses angle brackets (less than and greater than signs) rather than single quotes (page 128-129).
* Steps 8 and 15 uses angle brackets (less than and greater than signs) rather than single quotes (page 131-132).
* The book should mention that bitmap indexes are not available in the Standard Edition of Oracle Database (page 136).
* Step 3 uses angle brackets (less than and greater than signs) rather than single quotes (page 137).
*
The author created a composite bitmap index with three columns to
demonstrate the use of bitmap indexes. Composite bitmap indexes are
rare - one of the strengths in using bitmap indexes is the ability to
create multiple single column bitmap indexes, and as needed the
optimizer will select to bitmap join two or more bitmap indexes in an
attempt to significantly reduce the number of rows visited in the table
(page 138).
* The book states, "This time the execution plan uses
the newly created bitmap index, ... using the INDEX RANGE SCAN or INDEX
FAST FULL SCAN operation, depending on whether we are filtering on the
first key column of the index - CUST_GENDER - or not. This result is
obtained thanks to the structure of bitmap indexes." With the index
definition found in the book, the operations that should be present in
the execution plan are BITMAP INDEX RANGE SCAN and BITMAP INDEX FAST
FULL SCAN, while you might expect to find INDEX RANGE SCAN or INDEX FAST
FULL SCAN operations associated with normal b*tree indexes. However,
it is a cost-based decision for the optimizer to use or not use an
index, so there is no guarantee that index will be used as indicated in
the book if the leading column in the index is either specified or not
specified. Additionally, it is not the structure of bitmap indexes that
permits INDEX RANGE SCAN or INDEX FAST FULL SCAN operation, depending
on whether we are filtering on the first key column of the index -
creating a normal b*tree index in the script rather than a composite
bitmap index could (will) actually allow the optimizer to take advantage
of INDEX RANGE SCAN or INDEX FAST FULL SCAN operations (page 139).
*
The book states, "Bitmap indexes offer very fast performance when we
have a low cardinality field indexed on a table containing many rows."
This statement could have several different interpretations, but I
believe that the author's intended meaning is "Bitmap indexes offer
significantly faster performance than b*tree indexes when columns with
few distinct values are indexed in tables containing a significant
number of rows." This fixed statement still requires additional
clarification - if the bitmap index does not help to further reduce the
number of table rows that are accessed through the index, the end result
may be performance that is roughly the same as that of an equivalent
b*tree index. One way to accomplish the task of further reducing the
number of table rows accessed is through the utilization of multiple
bitmap indexes with bitmap combine operations to significantly reduce
the number of rowids that are used to fetch table rows (page 139).
*
The book states, "When rows are frequently inserted, deleted, and
updated, there is a performance bottleneck if we use a bitmap index.
When the index is updated, all the bitmap segments are locked." This
statement requires a bit of clarification. I do not believe that the
author is stating that updating an entry in a bitmap index will lock all
of the bitmap indexes in the database (a segment could be a table,
table partition, index, etc.). Instead, I think that the author is
intending to state that updating an entry in a bitmap index will lock
all of the index entries in that index, effectively preventing any other
session from inserting, updating (the column covered by the index), or
deleting rows in the table. For very small bitmap indexes, this
statement could very well be true. However, for larger bitmap indexes,
built for tables with many rows, the number of index rows that will be
locked during an update is determined by the number of rows covered by
the index block(s) that update changed, possibly 20,000 to 50,000 rows
per index block. (page 139 reference slide 46, reference2 page 2,
reference3 comments section).
* The book states, "This [bitmap
join index] is a bitmap index which represents the join between two
tables, and can be used instead of a materialized view in certain
conditions." The book did not offer any suggestions or describe any
conditions that permit a bitmap join index to take the place of a
materialized view. The statement in the book needs additional
clarification (reference reference2 page 140).
* The book states about index organized tables, "If the row size exceeds the size indicated by this parameter [PCTTHR
