Troubleshooting Oracle Performance

2nd Edition

By Christian Antognini

Troubleshooting Oracle Performance, 2nd Edition provides a systematic approach to addressing the underlying causes of poor database application performance.

Full Description

  • ISBN13: 978-1-4302-5758-5
  • 740 Pages
  • User Level: Intermediate to Advanced
  • Publication Date: June 5, 2014
  • Available eBook Formats: EPUB, MOBI, PDF
  • Print Book Price: $59.99
  • eBook Price: $41.99
Buy eBook Buy Print Book Add to Wishlist

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

Downloads are available to accompany this book.

Your operating system can likely extract zipped downloads automatically, but you may require software such as WinZip for PC, or StuffIt on a Mac.

Errata

Please Login to submit errata.

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:
Corrected sentence: The selectivity is a value between 0 and 1 representing the fraction of rows that an operation returns.
Reported by Charles Hooper.

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:
Corrected sentence: Because of its success, it has been ported to other programming languages like C++, .NET, Perl, and PHP.
Reported by Ted Persky.

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:
Corrected 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 creating the trigger.
Reported by Charles Hooper.

On page 73:
Original sentence: The number of executions itself is provided by the “Number of plan statistics captured” value.
Author Comment:
Corrected sentence: The number of execution plans itself is provided by the “Number of plan statistics captured” value.
Reported by Charles Hooper.

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 session
Author Comment:
Corrected sentence: The following query, which was executed for the same session as the previous one, illustrates how to produce a detailed resource usage profile for the processing carried out by a session
Reported by Philipp Salvisberg

On page 123:
Original sentence: Load map showing the top 5 SQL statement for the time period selected in Figure 4-5
Author Comment:
Corrected sentence: Load map showing the top 5 SQL statements for the time period selected in Figure 4-5
Reported by Ted Persky.

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:
Corrected sentence: Whereas the dynamic performance views described in the last section provide only cumulated statistics about cursors, real-time monitoring provides information that describes how, over time, cursors are executed.
Reported by Ted Persky.

On page 149:
Original sentence: Can you find out what happened and, as a result, prevent it from happenning again?
Author Comment:
Corrected sentence: Can you find out what happened and, as a result, prevent it from happening again?
Reported by Charles Hooper.

On page 169:
Original sentence: With the search_space.sql script I was able to reproduce 122 of them.
Author Comment:
Corrected sentence: With the search_space.sql script I was able to reproduce 157 of them.

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:
Corrected sentence: In fact, without this query transformation, all component queries would be executed independently before applying the set operator.
Reported by Na Jangkeun.

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:
Corrected sentence: Without this query transformation, both component queries would be executed independently, and both tables would be accessed twice:
Reported by Na Jangkeun.

On page 215:
Original sentence: This is especially true for multibyte character sets where each character might take up to three bytes.
Author Comment:
Corrected sentence: This is especially true for multibyte character sets where each character might take up to four bytes.
Reported by Philipp Salvisberg.

On page 215:
Original sentence: A row in available for each bucket in a view like user_tab_histograms.
Author Comment:
Corrected sentence: A row is available for each bucket in a view like user_tab_histograms.
Reported by Philipp Salvisberg.

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:
Corrected sentence: To avoid using them, as of version 12.1, top frequency histograms and hybrid histograms are available .
Reported by Charles Hooper.

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:
Corrected sentence: Also note that some features (top frequency histograms, hybrid histograms, and incremental
statistics) require that dbms_stats.auto_sample_size is specified.
Reported by Charles Hooper.

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:
Corrected sentence: This level is available as of version 11.2.0.4 only.
Reported by Charles Hooper.

On page 313:
Original sentence: The number of logical reads performed during the execution.
Author Comment:
Corrected sentence: The number of blocks accessed through logical reads during the execution.
Reported by Charles Hooper.

On page 313:
Original sentence: The number of physical reads performed during the execution.
Author Comment:
Corrected sentence: The number of blocks accessed through physical reads during the execution.
Reported by Charles Hooper.

On page 313:
Original sentence: The number of physical writes performed during the execution.
Author Comment:
Corrected sentence: The number of blocks accessed through physical writes during the execution.
Reported by Charles Hooper.

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:
Corrected sentence: With this value, 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).
Reported by Sigrid Keydana.

On page 365:
Original sentence: Syntactical errors in hints don’t raise errors.
Author Comment:
Corrected sentence: In general, syntactical errors in hints don’t raise errors.
Reported by Charles Hooper.

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:
Corrected sentence: While processing a SQL statement, the parser checks the syntax of the hints. In spite of this, except for the oddly behaving change_dupkey_error_index, ignore_row_on_dupkey_index and retry_on_row_change hints, no error is raised when a hint is found with invalid syntax.
Reported by Charles Hooper.

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:
Corrected sentence: The aim of using a prepared statement is to share a single cursor for all similar SQL statements (those that would have differed only by literals if bind variables weren’t used) and, consequently, to avoid unnecessary hard parses by turning them into soft parses.
Reported by Charles Hooper.

On page 460:
Original sentence: After the reorganization, the query performs only 24 logical reads in order to return 40 rows:
Author Comment:
Corrected sentence: After the reorganization, the query performs only 23 logical reads in order to return 40 rows:
Reported by Sangwoo Ryu.

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:
Corrected sentence: Inequality (for example, != or <>), NOT IN, IS NOT NULL conditions, and restrictions based on expressions and functions don’t lead to partition pruning.
Reported by Philipp Salvisberg.

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 situations
Author Comment:
Corrected sentence: That’s because the number of logical reads needed to build the list of rowids is much smaller, both for B-tree and for bitmap indexes, than the number of logical reads needed to access the table with them in such situations
Reported by Philipp Salvisberg.

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:
Corrected sentence: As discussed in the previous section, if the selectivity of the inner loop is strong, using an index scan for the inner loop makes sense.
Reported by Na Jangkeun.

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:
Corrected sentence: As a result, independently of the access paths used to get data, no SORT JOIN operations are required.

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:
Corrected sentence: The probe input is executed at most once. In the event the build input doesn’t return any row and that no right outer or full outer join is used, the probe input isn’t executed at all.
The hash_join_empty_build_input.sql script provides examples about this behavior.
Reported by Na Jangkeun.

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:
Corrected 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 and tablespace columns give information about the utilization of temporary space:
Reported by Na Jangkeun.

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:
Corrected sentence: Therefore, I strongly recommend that you use join conditions and, consequently, foreign keys and bitmap indexes based on a single column.
Reported by Philipp Salvisberg.

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:
Corrected sentence: This method, called out-of-place refresh, makes sure that the impact on concurrent queries accessing the materialized view is minimized.
Reported by Na Jangkeun.

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:
Corrected sentence: This way, if you have determined that you’re experiencing row migration, you should increase the current values of PCTFREE.
Reported by Na Jangkeun.

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 option
Author Comment:
Corrected sentence: The following example, based on the reverse_index.sql script, shows that the query optimizer no longer uses the index after rebuilding it with the REVERSE option
Reported by Charles Hooper.