| Get 1 Year of unlimited Apress for $199 | |
| Instant access to all available titles and new releases |  |
Related Titles
- Full Description
-
Troubleshooting Oracle Performance, 2nd Edition is your systematic guide to diagnosing and resolving performance problems in database-backed applications involving Oracle's database engine. Christian Antognini brings a decade and a half experience to his topic. His first edition is one of the most well-respected books in its field. This second edition has been rigorously updated to cover the latest developments in Oracle Database 11g Release 2 through Oracle Database 12c.
What do you do when your database application isn’t running fast enough? You troubleshoot, of course. Finding the slow part of an application is often the easy part of the battle. It’s finding a solution that’s difficult. Troubleshooting Oracle Performance, 2nd Edition helps by providing a systematic approach to addressing the underlying causes of poor database application performance. The author freely shares his experience while explaining the underlying foundations of how SQL statements are executed by the Oracle database engine. You'll be able to draw a solid foundation of theory and shared experience as you face head-on the performance challenges in your daily work.
- Written for developers by an application developer who has learned by doing
- Gives a systematic approach to solving database application performance problems
- Helps you plan for performance as you would for any other application requirement
What you’ll learn
- Systematically identify performance problems
- Configure the query optimizer to meet performance goals
- Read and recognize inefficient execution plans
- Minimize the impact of parsing upon performance
- Optimize data access, joins, and the physical design of your database
- Improve performance through parallel processing, materialized views, and result caching
- Apply SQL tuning techniques such as hints, SQL profiles, stored outlines, and SQL plan baselines
- Reduce inefficiencies from too much procedural code
Who this book is for
Troubleshooting Oracle Performance, Second Edition is targeted at application developers and database administrators involved in troubleshooting performance problems in Oracle-based applications.
- Table of Contents
-
Table of Contents
Part I: Foundations
1. Performance Problems
2. Key Concepts
Part II: Identification
3. Analysis of Reproducible Problems
4. Real-Time Analysis of Irreproducible Problems
5. Postmortem Analysis of Irreproducible Problems
Part III: Query Optimizer
6. Introducing the Query Optimizer
7. System Statistics
8. Object Statistics
9. Configuring the Query Optimizer
10. Execution Plans
Part IV: Optimization
11. SQL Tuning Techniques
12. Parsing
13. Optimizing Data Access
14. Optimizing Joins
15. Beyond Data Access and Join Optimization
16. Optimizing the Physical Design
Appendix A
- Source Code/Downloads
- Errata
-
If you think that you've found an error in this book, please let us know by emailing to editorial@apress.com . You will find any confirmed erratum below, so you can check if your concern has already been addressed.
On page 19:Original sentence: The selectivity is a value between 0 and 1 representing the fraction of rows filtered by an operation.Author Comment:
On page 24:In Figure 2-3 two “boxes” contain the same text (Store parent cursor in library cache). The one at the top is right, the one at the bottom is wrong. It should be “Store child cursor in library cache”.
Reported by Charles Hooper.
On page 43:Original sentence: Because of its success, having been ported to other programming languages like C++, .NET, Perl, and PHP.Author Comment:
On page 45/46:The session_info.sql script was renamed. As written at page 46, its new name is session_attributes.sql.
On page 62:Original sentence: The ALTER SESSION privilege required to execute the previous trigger can’t be granted through a role. Instead, it has to be granted directly to the user executing the trigger.Author Comment:
On page 73:Original sentence: The number of executions itself is provided by the “Number of plan statistics captured” value.Author Comment:
On page 106:The query executed against v$osstat contains a wrong WHERE clause. Actually, no where clause should be present.
Reported by Winston Zhang
On page 111:Original sentence: The following query, which was executed for the same session as the previous one, illustrates how produce a detailed resource usage profile for the processing carried out by a sessionAuthor Comment:
On page 123:Original sentence: Load map showing the top 5 SQL statement for the time period selected in Figure 4-5Author Comment:
On page 126:Original sentence: Whereas the dynamic performance views described in the last section provide only cumulated statistics about cursors, real-time monitoring provides information that describe how, over time, cursors are executed.Author Comment:
On page 149:Original sentence: Can you find out what happened and, as a result, prevent it from happenning again?Author Comment:
On page 169:Original sentence: With the search_space.sql script I was able to reproduce 122 of them.Author Comment:
On page 185:Original sentence: In fact, without this query transformation, all component queries would be executed idependently before applying the set operator.Author Comment:
On page 186:Original sentence: Without this query transformation, both component queries would be executed idependently, and both tables would be accessed twice:Author Comment:
On page 215:Original sentence: This is especially true for multibyte character sets where each character might take up to three bytes.Author Comment:
On page 215:Original sentence: A row in available for each bucket in a view like user_tab_histograms.Author Comment:
On page 221:Original sentence: For this reason, as of version 12.1, top frequency histograms and hybrid histograms replace height-balanced histograms.Author Comment:
On page 226/227:It is not possible to create more than 20 extensions. If you try to create more than 20 extensions, the following error is raised:
ORA-20008: Number of extensions in table <table> already reaches the upper limit (20)
On page 239:Original sentence: Also note that some features (top frequency histograms, hybrid histograms, and incremental
statistics) only work when dbms_stats.auto_sample_size is specified.Author Comment:
On page 244:There is a sidebar (even though it doesn’t look like a sidebar, but it’s how Apress formats them) entitled CONCURRENT STATISTICS GATHERING that starts at page 243 and ends at page 244. Unfortunately, it doesn’t end at the right place. In addition, the bullet point that follows it is also (partially) wrongly formatted. To avoid misunderstandings, here are the published version and the corrected version of that part of the book.
On page 282:Original sentence: This level is available as of version 12.1 only.Author Comment:
On page 313:Original sentence: The number of logical reads performed during the execution.Author Comment:
On page 313:Original sentence: The number of physical reads performed during the execution.Author Comment:
On page 313:Original sentence: The number of physical writes performed during the execution.Author Comment:
On page 340:Original sentence: With the value in the mgr column, operation 6 does a scan of the emp_mgr_i index by applying the “MGR”=PRIOR “EMPNO” access predicate (shown as “connect$_by$_pump$_002″.”PRIOR empno”=”MGR”), applies the filter predicate “MGR” IS NOT NULL, extracts the rowids, and returns them to its parent (5).Author Comment:
On page 365:Original sentence: Syntactical errors in hints don’t raise errors.Author Comment:
On page 371:Original sentence: While processing a SQL statement, the parser checks the syntax of the hints. In spite of this, no error is raised when a hint is found with invalid syntax.Author Comment:
On page 384:The second UPDATE ol$hints SET ol_name = 'P_OUTLINE_EDITING' WHERE ol_name 'P_OUTLINE_EDITING_HINTED' statement has to be replaced by the following query: SELECT hint_text FROM ol$hints WHERE ol_name = 'P_OUTLINE_EDITING'
Reported by Sangwoo Ryu
On page 407:The “SELECT /*+ full(t) */ count(pad) FROM t WHERE n = 42″ query execute between the two “ALTER SESSION” statements has to be executed twice.
Reported by Koltai Róbert.
On page 428:Original sentence: The aim of using a prepared statement is to share a single cursor for all SQL statements and, consequently, to avoid unnecessary hard parses by turning them into soft parses.Author Comment:
On page 460:Original sentence: After the reorganization, the query performs only 24 logical reads in order to return 40 rows:Author Comment:
On page 478:Original sentence: Inequality (!= or <>), NOT IN, IS NOT NULL conditions, and restrictions based on expressions and functions don’t lead to partition pruning.Author Comment:
On page 490:Original sentence: That’s because the time needed to build the list of rowids is much smaller, both for B-tree and for bitmap indexes, than the time needed to access the table with them in such situationsAuthor Comment:
On page 540:Original sentence: As discussed in the the previous section, if the selectivity of the inner loop is strong, using an index scan for the inner loop makes sense.Author Comment:
On page 548:Original sentence: As a result, idependently of the access paths used to get data, no SORT JOIN operations are required.Author Comment:
On page 556:Original sentence: The probe input is executed at most once. In the event the build input doesn’t return any row, the probe input isn’t executed at all.Author Comment:
On page 559:Original sentence: Although the operation_id column is used to relate the work areas to an operation in the execution plan, the actual_mem_used column shows the size (in bytes), and the tempseg_size columns and tablespace give information about the utilization of temporary space:Author Comment:
On page 569:Original sentence: Therefore, I strongly recommend that you use use join conditions and, consequently, foreign keys and bitmap indexes based on a single column.Author Comment:
On page 589:Original sentence: This method, called out-of-place refresh, makes sure that the impact on concurrent queries accessing the materialized is minimized.Author Comment:
On page 611:From 12.1 onward, the default value of the parallel_min_servers initialization parameter is “cpu_count * parallel_threads_per_cpu * 2″, and not 0 as written.
On page 665:Original sentence: This way, if you have determined that you’re are experiencing row migration, you should increase the current values of PCTFREE.Author Comment:
On page 671:Original sentence: The following example, based on the reserve_index.sql script, shows that the query optimizer no longer uses the index after rebuilding it with the REVERSE optionAuthor Comment:
Best-Sellers
Why Shop on Apress.com?
Reported by Charles Hooper.