Oracle Database 11g R2 Performance Tuning Cookbook und über 1,5 Millionen weitere Bücher verfügbar für Amazon Kindle. Erfahren Sie mehr
EUR 46,00
  • Alle Preisangaben inkl. MwSt.
Auf Lager.
Verkauf und Versand durch Amazon.
Geschenkverpackung verfügbar.
Menge:1
Oracle Database 11gR2 Per... ist in Ihrem Einkaufwagen hinzugefügt worden
Ihren Artikel jetzt
eintauschen und
EUR 6,80 Gutschein erhalten.
Möchten Sie verkaufen?
Zur Rückseite klappen Zur Vorderseite klappen
Anhören Wird wiedergegeben... Angehalten   Sie hören eine Probe der Audible-Audioausgabe.
Weitere Informationen
Alle 2 Bilder anzeigen

Oracle Database 11gR2 Performance Tuning Cookbook (Englisch) Taschenbuch – 20. Januar 2012


Alle 4 Formate und Ausgaben anzeigen Andere Formate und Ausgaben ausblenden
Amazon-Preis Neu ab Gebraucht ab
Kindle Edition
"Bitte wiederholen"
Taschenbuch
"Bitte wiederholen"
EUR 46,00
EUR 46,00 EUR 142,28
9 neu ab EUR 46,00 1 gebraucht ab EUR 142,28

Hinweise und Aktionen

  • Große Hörbuch-Sommeraktion: Entdecken Sie unsere bunte Auswahl an reduzierten Hörbüchern für den Sommer. Hier klicken.

Jeder kann Kindle Bücher lesen — selbst ohne ein Kindle-Gerät — mit der KOSTENFREIEN Kindle App für Smartphones, Tablets und Computer.



Produktinformation


Mehr über den Autor

Entdecken Sie Bücher, lesen Sie über Autoren und mehr

Produktbeschreibungen

Über den Autor und weitere Mitwirkende

Ciro Fiorillo

Ciro Fiorillo is an IT professional and consultant with experience of more than a decade in different roles (developer, analyst, DBA, project manager, data and software architect) among software industries. He has worked on different technologies and architectures, such as Oracle, SQL Server, Delphi, C# and .NET Framework, C/C++, Java, PHP, COBOL, Fortran, and Tibco.

Ciro is currently employed as Lead Software and Data Architect with FinWin Srl, a software house specializing in banking and loans applications.

As a freelancer he writes articles for websites and printed magazines about software and computing, participates in workshops, and teaches C++ and Fortran parallel programming with Intel Software tools.

Ciro can be reached at ciro@cirofiorillo.com.


In diesem Buch

(Mehr dazu)
Ausgewählte Seiten ansehen
Buchdeckel | Copyright | Inhaltsverzeichnis | Auszug | Stichwortverzeichnis | Rückseite
Hier reinlesen und suchen:

Kundenrezensionen

Es gibt noch keine Kundenrezensionen auf Amazon.de
5 Sterne
4 Sterne
3 Sterne
2 Sterne
1 Sterne

Die hilfreichsten Kundenrezensionen auf Amazon.com (beta)

Amazon.com: 4 Rezensionen
16 von 17 Kunden fanden die folgende Rezension hilfreich
Oracle Database Performance Tuning Test Cases without Many "Why", "When", and "How Much" Filler Details 4. März 2012
Von Charles Hooper - Veröffentlicht auf Amazon.com
Format: Taschenbuch Verifizierter Kauf
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).

* 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
1 von 1 Kunden fanden die folgende Rezension hilfreich
useful guidelines 19. Februar 2012
Von W Boudville - Veröffentlicht auf Amazon.com
Format: Taschenbuch
This is a very detailed text by Fiorillo on how to improve the performance of your 11gR2 database. It presupposes that you are a database administrator already versed in the basics of 11g.

The first chapter is the simplest, and possibly offers the quickest improvements to your system. It has many guidelines. Like warning that an academic design of an abstract database could map badly into the relational structure of an actual Oracle database. Beware of over normalisation, we are told. Where too many table joins happen. This can sometimes be traced back to having too many tables. The latter can arise due to an elegant factoring of a problem, or to the mapping of an object oriented design to a relational database. In general, fewer tables can be better, even if this incurs extra columns as it usually will.

You should also look at and understand how the alert log works. A vital feedback if you want to adjust your table structures and measure actual performances.

The second chapter contains sections that can seem like heresy to those of you taught to observe or create the Normal Forms. The text warns that sometimes poor performance can result. Oracle lets you define tables that do not even follow the 1NF!

A very interesting section explains the differences between static and dynamic SQL. The former is hardcoded in application source code, while the latter is calculated (interpreted) at runtime. You are cautioned to only occasionally run dynamic SQL. It can be very slow.

Later chapters delve into other topics. As in how to optimise storage under Oracle. Or to optimise SQL code. Each of these warrants its own chapter, as an indication of their importance. While the chapters are certainly detailed enough, it may well be that some readers will wish for more examples in each.

You should also take a close look at the chapter on tuning memory. Avoid or minimise having the operating system page in and out of memory the Oracle runtime data. Very expensive because the disk accesses are several orders of magnitude slower than memory access. Just buying more memory for your machine might not be feasible. So pay attention to the chapter's remarks on how to tune memory usage.
Great book 15. Juni 2013
Von Joseph Opoku - Veröffentlicht auf Amazon.com
Format: Taschenbuch Verifizierter Kauf
This is one of the best Oracle Performance book I have read. The recipes are simple and easy to understand.
0 von 3 Kunden fanden die folgende Rezension hilfreich
Pretty good, but lacking the "why" 14. März 2012
Von S. Sutton - Veröffentlicht auf Amazon.com
Format: Taschenbuch Verifizierter Kauf
This will help you with the "what", as in "what should we do?", and it does a pretty good job at providing examples for tuning queries, indexes, and the database as a whole. Unfortunately for me, it's completely lacking the "why", as in "why did whatever they had me do just speed things up?".

It's great if you just want to bang out some performance tuning, it's not so great if you want to learn from that experience and pro-actively apply it to future applications.
Waren diese Rezensionen hilfreich? Wir wollen von Ihnen hören.