book

Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

Name: Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach
ISBN: 9781430261872

by Sam R. Alapati, Darl Kuhn, Bill Padfield

December 2013

Intermediate to advanced

636 pages

17h 42m

English

Apress

Read now

Unlock full access

Title Page
Dedication
Contents at a Glance
Contents
About the Authors
About the Technical Reviewers
Acknowledgments
Introduction
Who This Book Is ForHow This Book Is StructuredDownloading the Code
CHAPTER 1: Optimizing Table Performance
1-1. Building a Database That Maximizes Performance1-2. Creating Tablespaces to Maximize Performance1-3. Matching Table Types to Business Requirements1-4. Choosing Table Features for Performance1-5. Selecting Data Types Appropriately1-6. Avoiding Extent Allocation Delays When Creating Tables1-7. Maximizing Data-Loading Speeds1-8. Efficiently Removing Table Data1-9. Displaying Automated Segment Advisor Advice1-10. Manually Generating Segment Advisor Advice1-11. Automatically E-mailing Segment Advisor Output1-12. Rebuilding Rows Spanning Multiple Blocks1-13. Detecting Row Chaining and Row Migration1-14. Differentiating Between Row Migration and Row Chaining1-15. Proactively Preventing Row Migration/Chaining1-16. Detecting Unused Space in a Table1-17. Tracing to Detect Space Below the High-Water Mark1-18. Using DBMS_SPACE to Detect Space Below the High-Water Mark1-19. Freeing Unused Table Space1-20. Compressing Data for Direct Path Loading1-21. Compressing Data for All DML1-22. Compressing Data at the Column Level
CHAPTER 2: Choosing and Optimizing Indexes
2-1. Understanding B-tree Indexes2-2. Deciding Which Columns to Index2-3. Creating a Primary Key Constraint and Index2-4. Ensuring Unique Column Values2-5. Indexing Foreign Key Columns2-6. Deciding When to Use a Concatenated Index2-7. Reducing Index Size Through Compression2-8. Implementing a Function-Based Index2-9. Indexing a Virtual Column2-10. Limiting Index Contention when Several Processes Insert in Parallel2-11. Toggling the Visibility of an Index to the Optimizer2-12. Creating a Bitmap Index in Support of a Star Schema2-13. Creating a Bitmap Join Index2-14. Creating an Index-Organized Table2-15. Monitoring Index Usage2-16. Maximizing Index Creation Speed2-17. Reclaiming Unused Index Space

CHAPTER 3: Optimizing Instance Memory
3-1. Automating Memory Management3-2. Managing Multiple Buffer Pools3-3. Setting Minimum Values for Memory3-4. Monitoring Memory Resizing Operations3-5. Optimizing Memory Usage3-6. Tuning PGA Memory Allocation3-7. Configuring the Server Query Cache3-8. Managing the Server Result Cache3-9. Caching SQL Query Results3-10. Caching Client Result Sets3-11. Caching PL/SQL Function Results3-12. Configuring the Oracle Database Smart Flash Cache3-13. Tuning the Redo Log Buffer3-14. Limiting PGA Memory Allocation
CHAPTER 4: Monitoring System Performance
4-1. Implementing Automatic Workload Repository (AWR)4-2. Modifying the Statistics Interval and Retention Periods4-3. Generating an AWR Report Manually4-4. Generating an AWR Report via Enterprise Manager4-5. Generating an AWR Report for a Single SQL Statement4-6. Creating a Statistical Baseline for Your Database4-7. Managing AWR Baselines via Enterprise Manager4-8. Managing AWR Statistics Repository4-9. Creating AWR Baselines Automatically4-10. Quickly Analyzing AWR Output4-11. Manually Getting Active Session Information4-12. Getting ASH Information from Enterprise Manager4-13. Getting ASH Information from the Data Dictionary
CHAPTER 5: Minimizing System Contention
5-1. Understanding Response Time5-2. Identifying SQL Statements with the Most Waits5-3. Analyzing Wait Events5-4. Understanding Wait Class Events5-5. Examining Session Waits5-6. Examining Wait Events by Class5-7. Resolving Buffer Busy Waits5-8. Resolving Log File Sync Waits5-9. Minimizing Read by Other Session Wait Events5-10. Reducing Direct Path Read Wait Events5-11. Minimizing Recovery Writer Waits5-12. Finding Out Who’s Holding a Blocking Lock5-13. Identifying Blocked and Blocking Sessions5-14. Dealing with a Blocking Lock5-15. Identifying a Locked Object5-16. Resolving enq: TM Lock Contention5-17. Identifying Recently Locked Sessions5-18. Analyzing Recent Wait Events in a Database5-19. Identifying Time Spent Waiting Because of Locking5-20. Minimizing Latch Contention
CHAPTER 6: Analyzing Operating System Performance
6-1. Detecting Disk Space Issues6-2. Identifying System Bottlenecks6-3. Determining Top System-Resource-Consuming Processes6-4. Detecting CPU Bottlenecks6-5. Identifying Processes Consuming CPU and Memory6-6. Determining I/O Bottlenecks6-7. Detecting Network-Intensive Processes6-8. Mapping a Resource-Intensive Process to a Database Process6-9. Terminating a Resource-Intensive Process
CHAPTER 7: Troubleshooting the Database
7-1. Determining the Optimal Undo Retention Period7-2. Finding What’s Consuming the Most Undo7-3. Resolving an ORA-01555 Error7-4. Monitoring Temporary Tablespace Usage7-5. Identifying Who Is Using the Temporary Tablespace7-6. Resolving the “Unable to Extend Temp Segment” Error7-7. Resolving Open Cursor Errors7-8. Resolving a Hung Database7-9. Invoking the Automatic Diagnostic Repository Command Interpreter7-10. Viewing an Alert Log from ADRCI7-11. Viewing Incidents with ADRCI7-12. Packaging Incidents for Oracle Support7-13. Running a Database Health Check7-14. Creating a SQL Test Case7-15. Generating an AWR Report7-16. Comparing Database Performance Between Two Periods7-17. Analyzing an AWR Report
CHAPTER 8: Creating Efficient SQL
8-1. Retrieving All Rows from a Table8-2. Retrieve a Subset of Rows from a Table8-3. Joining Tables with Corresponding Rows8-4. Joining Tables When Corresponding Rows May Be Missing8-5. Constructing Simple Subqueries8-6. Constructing Correlated Subqueries8-7. Comparing Two Tables to Find Missing Rows8-8. Comparing Two Tables to Find Matching Rows8-9. Combining Results from Similar SELECT Statements8-10. Searching for a Range of Values8-11. Handling Null Values8-12. Searching for Partial Column Values8-13. Re-using SQL Statements Within the Shared Pool8-14. Avoiding Accidental Full Table Scans8-15. Creating Efficient Temporary Views8-16. Avoiding the NOT Clause8-17. Controlling Transaction Sizes
CHAPTER 9: Manually Tuning SQL
9-1. Displaying an Execution Plan for a Query9-2. Customizing Execution Plan Output9-3. Graphically Displaying an Execution Plan9-4. Reading an Execution Plan9-5. Monitoring Long-Running SQL Statements9-6. Identifying Resource-Consuming SQL Statements That Are Currently Executing9-7. Seeing Execution Statistics for Currently Running SQL9-8. Monitoring Progress of a SQL Execution Plan9-9. Identifying Resource-Consuming SQL Statements That Have Executed in the PastComparing SQL Performance After a System Change
CHAPTER 10: Tracing SQL Execution
10-1. Preparing Your Environment10-2. Tracing a Specific SQL Statement10-3. Enabling Tracing in Your Own Session10-4. Finding the Trace Files10-5. Examining a Raw SQL Trace File10-6. Analyzing Oracle Trace Files10-7. Formatting Trace Files with TKPROF10-8. Analyzing TKPROF Output10-9. Analyzing Trace Files with Oracle Trace Analyzer10-10. Tracing a Parallel Query10-11. Tracing Specific Parallel Query Processes10-12. Tracing Parallel Queries in a RAC System10-13. Consolidating Multiple Trace Files10-14. Finding the Correct Session for Tracing10-15. Tracing a SQL Session10-16. Tracing a Session by Process ID10-17. Tracing Multiple Sessions10-18. Tracing an Instance or a Database10-19. Generating an Event 10046 Trace for a Session10-20. Generating an Event 10046 Trace for an Instance10-21. Setting a Trace in a Running Session10-22. Enabling Tracing in a Session After a Login10-23. Tracing the Optimizer’s Execution Path10-24. Generating Automatic Oracle Error Traces10-25. Tracing a Background Process10-26. Enabling Oracle Listener Tracing10-27. Setting Archive Tracing for Data Guard
CHAPTER 11: Automated SQL Tuning
11-1. Displaying Automatic SQL Tuning Job Details11-2. Displaying Automatic SQL Tuning Advisor Advice11-3. Generating a SQL Script to Implement Automatic Tuning Advice11-4. Modifying Automatic SQL Tuning Features11-5. Disabling and Enabling Automatic SQL Tuning11-6. Modifying Maintenance Window Attributes11-7. Creating a SQL Tuning Set Object11-8. Viewing Resource-Intensive SQL in the AWR11-9. Populating a SQL Tuning Set from High-Resource SQL in AWR11-10. Viewing Resource-Intensive SQL in Memory11-11. Populating a SQL Tuning Set from Resource-Consuming SQL in Memory11-12. Populating a SQL Tuning Set With All SQL in Memory11-13. Displaying the Contents of a SQL Tuning Set11-14. Selectively Deleting Statements from a SQL Tuning Set11-15. Transporting a SQL Tuning Set11-16. Creating a Tuning Task11-17. Running the SQL Tuning Advisor11-18. Generating SQL Tuning Advice from the Automatic Database Diagnostic Monitor
CHAPTER 12: Execution Plan Optimization and Consistency
BackgroundSeeing the Big Picture12-1. Creating and Accepting a SQL Profile12-2. Determining if a Query is Using a SQL Profile12-3. Automatically Accepting SQL Profiles12-4. Displaying SQL Profile Information12-5. Selectively Testing a SQL Profile12-6. Transporting a SQL Profile to a Different Database12-7. Disabling a SQL Profile12-8. Dropping a SQL Profile12-9. Creating a Plan Baseline for a SQL Statement in Memory12-10. Creating Plan Baselines for SQL Contained in SQL Tuning Set12-11. Automatically Adding Plan Baselines12-12. Altering a Plan Baseline12-13. Determining If Plan Baselines Exist12-14. Determining if a Query is Using a Plan Baseline12-15. Displaying Plan Baseline Execution Plans12-16. Manually Adding a New Execution Plan to Plan Baseline (Evolving)12-17. Toggling the Automatic Acceptance of New Low-Cost Execution Plans12-18. Disabling Plan Baselines12-19. Removing Plan Baseline Information12-20. Transporting Plan Baselines
CHAPTER 13: Configuring the Optimizer
13-1. Choosing an Optimizer Goal13-2. Enabling Automatic Statistics Gathering13-3. Setting Preferences for Statistics Collection13-4. Manually Generating Statistics13-5. Locking Statistics13-6. Handling Missing Statistics13-7. Exporting Statistics13-8. Restoring Previous Versions of Statistics13-9. Gathering System Statistics13-10. Validating New Statistics13-11. Forcing the Optimizer to Use an Index13-12. Enabling Query Optimizer Features13-13. Keeping the Database from Creating Histograms13-14. Improving Performance When Not Using Bind Variables13-15. Understanding Adaptive Cursor Sharing13-16. Creating Statistics on Expressions13-17. Creating Statistics for Related Columns13-18. Automatically Creating Column Groups13-19. Maintaining Statistics on Partitioned Tables13-20. Concurrent Statistics Collection for Large Tables13-21. Determining When Statistics Are Stale13-22. Previewing Statistics Gathering Targets
CHAPTER 14: Implementing Query Hints
14-1. Writing a Hint14-2. Changing the Access Path14-3. Changing the Join Order14-4. Changing the Join Method14-5. Changing the Optimizer Version14-6. Choosing Between a Fast Response and Overall Optimization14-7. Performing a Direct-Path Insert14-8. Placing Hints in Views14-9. Caching Query Results14-10. Directing a Distributed Query to a Specific Database14-11. Gathering Extended Query Execution Statistics14-12. Enabling Query Rewrite14-13. Improving Star Schema Query Performance
CHAPTER 15: Executing SQL in Parallel
15-1. Enabling Parallelism for a Specific Query15-2. Enabling Parallelism at Object Creation15-3. Enabling Parallelism for an Existing Object15-4. Implementing Parallel DML15-5. Creating Tables in Parallel15-6. Creating Indexes in Parallel15-7. Rebuilding Indexes in Parallel15-8. Moving Partitions in Parallel15-9. Splitting Partitions in Parallel15-10. Enabling Automatic Degree of Parallelism15-11. Examining Parallel Explain Plans15-12. Monitoring Parallel Operations15-13. Finding Bottlenecks in Parallel Processes15-14. Getting Detailed Information on Parallel Sessions
Index

Content preview from Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

CHAPTER 10

Tracing SQL Execution

Tracing session activity is at the heart of most SQL performance tuning exercises. Oracle provides a rich set of tools to trace SQL activity. This chapter introduces the Oracle SQL trace facility and shows you how to set up SQL tracing in your environment. Oracle provides numerous “events” that help you perform various types of traces.

Although there are several tracing methods available, Oracle now recommends that you use the DBMS_MONITOR package for most types of tracing. The chapter contains several recipes that explain how to use this package to generate traces. In addition, we show how to trace sessions by ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Oracle Database 11gR2 Performance Tuning Cookbook

Publisher Resources

ISBN: 9781430261872Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle Database 12c Performance Tuning Recipes: A Problem-Solution Approach

by Sam R. Alapati, Darl Kuhn, Bill Padfield

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.