Oracle Database 11gR2 Performance Tuning Cookbook

Oracle Database 11gR2 Performance Tuning Cookbook

by Ciro Fiorillo
Released January 2012
Publisher(s): Packt Publishing
ISBN: 9781849682602

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Shifting your Oracle Database into top gear takes a lot of know-how and fine-tuning ability. The 80+ recipes in this Cookbook will give you those skills along with the ability to troubleshoot if things starts running slowly.

  • 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

Table of contents

  1. Oracle Database 11gR2 Performance Tuning Cookbook
    1. Table of Contents
    2. Oracle Database 11gR2 Performance Tuning Cookbook
    3. Credits
    4. About the Author
    5. Acknowledgement
    6. About the Reviewers
    7. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    8. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    9. 1. Starting with Performance Tuning
      1. Introduction
        1. Incorrect session management
        2. Poorly designed cursor management
        3. Inadequate relational design
        4. Improper use of storage structures
      2. Reviewing the performance tuning process
        1. How to do it...
        2. How it works...
        3. There's more…
        4. See also
      3. Exploring the example database
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Acquiring data using a data dictionary and dynamic performance views
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      5. Analyzing data using Statspack reports
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Collecting different amounts of data
          2. Producing a report on a specific SQL
          3. Automating snapshot generation
          4. Statspack maintenance
      6. Diagnosing performance issues using the alert log
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Analyzing data using Automatic Workload Repository (AWR)
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Analyzing data using Automatic Database Diagnostic Monitor (ADDM)
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      9. A working example
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    10. 2. Optimizing Application Design
      1. Introduction
      2. Optimizing connection management
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Dedicated server versus shared server
          2. Web applications
          3. Client-server Online Transaction Processing
          4. Batch processing
        5. See also
      3. Improving performance sharing reusable code
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. PL/SQL and parsing
          2. Diagnosing soft and hard parsing
        5. See also
      4. Reducing the number of requests to the database using stored procedures
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      5. Reducing the number of requests to the database using sequences
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Correct definition of a sequence
        4. See also
      6. Reducing the number of requests to the database using materialized views
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Materialized views in depth
          2. Materialized views and grants
          3. Database parameters to use query rewrite
          4. Can I use materialized views in an OLTP environment?
      7. Optimizing performance with schema denormalization
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Not 1NF structures
      8. Avoiding dynamic SQL
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
    11. 3. Optimizing Storage Structures
      1. Introduction
      2. Avoiding row chaining
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Avoiding row migration
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Estimating table size with different PCTFREE parameter
      4. Using LOBs
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Using index clusters
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Cluster size
          2. Cluster index
          3. Clustering and truncating
      6. Using hash clusters
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Sorted hash clusters
          2. Custom hash function
          3. Single-table hash clusters
      7. Indexing the correct way
        1. How to do it...
        2. How it works...
        3. There's more...
          1. What is the "small percentage" of the data which assures we can improve performances using B-tree indexes?
        4. See also
      8. Rebuilding index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Index rebuild and statistics
        5. See also
      9. Compressing indexes
        1. How to do it…
        2. How it works...
        3. There's more...
      10. Using reverse key indexes
        1. How to do it...
        2. How it works...
        3. There's more...
      11. Using bitmap indexes
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Bitmap join index
        4. See also
      12. Migrating to index organized tables
        1. How to do it...
        2. How it works...
        3. There's more...
          1. INCLUDING, OVERFLOW, PCTTHRESHOLD
          2. Logical ROWID
        4. See also
      13. Using partitioning
        1. How to do it...
        2. How it works...
        3. There's more...
          1. List partitioning
          2. Hash partitioning
          3. Composite partitioning
    12. 4. Optimizing SQL Code
      1. Introduction
      2. Using bind variables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Concurrency and scalability
          2. Security issues
        5. See also
      3. Avoiding full table scans
        1. How to do it...
        2. How it works...
        3. There's more...
          1. The High-Water Mark
          2. PctFree, PctUsed, and FREELISTs
        4. See also
      4. Exploring index lookup
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      5. Exploring index skip-scan and index range-scan
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      6. Introducing arrays and bulk operations
        1. How to do it...
        2. How it works...
        3. There's more...
          1. When to use direct path load
        4. See also
      7. Optimizing joins
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      8. Using subqueries
        1. How to do it...
        2. How it works...
        3. There's more...
      9. Tracing SQL activity with SQL Trace and TKPROF
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    13. 5. Optimizing Sort Operations
      1. Introduction
      2. Sorting—in-memory and on-disk
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Sorting and indexing
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      4. Writing top n queries and ranking
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      5. Using count, min/max, and group-by
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      6. Avoiding sorting in set operations: union, minus, and intersect
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      7. Troubleshooting temporary tablespaces
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Optimal storage parameters for temporary tablespaces
        4. See also
    14. 6. Optimizing PL/SQL Code
      1. Introduction
      2. Using bind variables and parsing
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      3. Array processing and bulk-collect
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      4. Passing values with NOCOPY (or not)
        1. How to do it...
        2. How it works...
        3. There's more...
      5. Using short-circuit IF statements
        1. How to do it...
        2. How it works...
        3. There's more...
      6. Avoiding recursion
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      7. Using native compilation
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      8. Taking advantage of function result cache
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      9. Inlining PL/SQL code
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      10. Using triggers and virtual columns
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Using WHEN and OF in trigger definition
          2. Avoid FOR EACH ROW in triggers, when possible
        4. See also
    15. 7. Improving the Oracle Optimizer
      1. Introduction
      2. Exploring optimizer hints
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Errors in hints
        4. See also
      3. Collecting statistics
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Lock table statistics for load or highly volatile tables
          2. Other procedures in DBMS_STATS
        4. See also
      4. Using histograms
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Height-based and value-based (frequency) histograms
        4. See also
      5. Managing stored outlines
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Private and public stored outlines
        5. See also
      6. Introducing Adaptive Cursor Sharing for bind variable peeking
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      7. Creating SQL Tuning Sets
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      8. Using the SQL Tuning Advisor
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      9. Configuring and using SQL Baselines
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    16. 8. Other Optimizations
      1. Introduction
      2. Caching results with the client-side result cache
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Configuring the client-side result cache
        5. See also
      3. Enabling parallel SQL
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Parallel query and I/O
          2. When to use parallel SQL
        5. See also
      4. Direct path inserting
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      5. Using create table as select
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      6. Inspecting indexes and triggers overhead
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      7. Loading data with SQL*Loader and Data Pump
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    17. 9. Tuning Memory
      1. Introduction
      2. Tuning memory to avoid Operating System paging
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      3. Tuning the Library Cache
        1. How to do it...
        2. How it works...
        3. There's more...
          1. How to minimize misses
        4. See also
      4. Tuning the Shared Pool
        1. How to do it...
        2. How it works...
        3. There's more...
          1. Tuning the Dictionary Cache
        4. See also
      5. Tuning the Program Global Area and the User Global Area
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      6. Tuning the Buffer Cache
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
    18. 10. Tuning I/O
      1. Introduction
      2. Tuning at the disk level and strategies to distribute Oracle files
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Striping objects across multiple disks
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      4. Choosing different RAID levels for different Oracle files
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. RAID level 0
          2. RAID level 1
          3. RAID level 5
          4. RAID level 0+1
        5. See also
      5. Using asynchronous I/O
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      6. Tuning checkpoints
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      7. Tuning redo logs
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also

    19. 11. Tuning Contention
      1. Introduction
      2. Detecting and preventing lock contention
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      3. Investigating transactions and concurrency
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      4. Tuning latches
        1. How to do it...
        2. How it works...
        3. There's more...
        4. See also
      5. Tuning resources to minimize latch contention
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
      6. Minimizing latches using bind variables
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
        5. See also
    20. A. Dynamic Performance Views
      1. ALL_OBJECTS
        1. Fields
      2. DBA_BLOCKERS
        1. Fields
        2. See also
      3. DBA_DATA_FILES
        1. Fields
        2. See also
      4. DBA_EXTENTS
        1. Fields
        2. See also
      5. DBA_INDEXES
        1. Fields
      6. DBA_SQL_PLAN_BASELINES
        1. Fields
      7. DBA_TABLES
        1. Fields
      8. DBA_TEMP_FILES
        1. Fields
        2. See also
      9. DBA_VIEWS
        1. Fields
      10. DBA_WAITERS
        1. Fields
        2. See also
      11. INDEX_STATS
        1. Fields
        2. See also
      12. DBA_SEQUENCES
        1. Fields
      13. DBA_TABLESPACES
        1. Fields
      14. DBA_TAB_HISTOGRAMS
        1. Fields
      15. V$ADVISOR_PROGRESS
        1. Fields
      16. V$BUFFER_POOL_STATISTICS
        1. Fields
        2. See also
      17. V$CONTROLFILE
        1. Fields
        2. See also
      18. V$DATAFILE
        1. Fields
        2. See also
      19. V$DB_CACHE_ADVICE
        1. Fields
        2. See also
      20. V$DB_OBJECT_CACHE
        1. Fields
        2. See also
      21. V$ENQUEUE_LOCK
        1. Fields
        2. See also
      22. V$FILESTAT
        1. Fields
        2. See also
      23. V$FIXED_TABLE
        1. Fields
      24. V$INSTANCE_RECOVERY
        1. Fields
      25. V$LATCH
        1. Fields
        2. See also
      26. V$LATCH_CHILDREN
        1. Fields
        2. See also
      27. V$LIBRARYCACHE
        1. Fields
      28. V$LOCK
        1. Fields
        2. See also
      29. V$LOCKED_OBJECT
        1. Fields
        2. See also
      30. V$LOG
        1. Fields
        2. See also
      31. V$LOG_HISTORY
        1. Fields
        2. See also
      32. V$LOGFILE
        1. Fields
        2. See also
      33. V$MYSTAT
        1. Fields
        2. See also
      34. V$PROCESS
        1. Fields
        2. See also
      35. V$ROLLSTAT
        1. Fields
      36. V$ROWCACHE
        1. Fields
      37. V$SESSION
        1. Fields
        2. See also
      38. V$SESSION_EVENT
        1. Fields
        2. See also
      39. V$SESSTAT
        1. Fields
        2. See also
      40. V$SGA
        1. Fields
        2. See also
      41. V$SGAINFO
        1. Fields
        2. See also
      42. V$SHARED_POOL_RESERVED
        1. Fields
      43. V$SORT_SEGMENT
        1. Fields
      44. V$SQL
        1. Fields
        2. See also
      45. V$SQL_PLAN
        1. Fields
        2. See also
      46. V$SQLAREA
        1. Fields
        2. See also
      47. V$STATNAME
        1. Fields
        2. See also
      48. V$SYSSTAT
        1. Fields
        2. See also
      49. V$SYSTEM_EVENT
        1. Fields
      50. V$TEMPFILE
        1. Fields
      51. V$TEMPSTAT
        1. Fields
        2. See also
      52. V$WAITSTAT
        1. Fields
        2. See also
      53. X$BH
        1. Fields
    21. B. A Summary of Oracle Packages Used for Performance Tuning
      1. DBMS_ADDM
        1. Procedures
      2. DBMS_ADVISOR
        1. Procedures
      3. DBMS_JOB
        1. Procedures
      4. DBMS_LOB
        1. Procedures
      5. DBMS_MVIEW
        1. Procedures
      6. DBMS_OUTLN
        1. Procedures
      7. DBMS_OUTLN_EDIT
        1. Procedures
      8. DBMS_SHARED_POOL
        1. Procedures
      9. DBMS_SPACE
        1. Procedures
      10. DBMS_SPM
        1. Procedures
      11. DBMS_SQL
        1. Procedures
      12. DBMS_SQLTUNE
        1. Procedures
      13. DBMS_STATS
        1. Procedures
      14. DBMS_UTILITY
        1. Procedures
      15. DBMS_WORKLOAD_REPOSITORY
        1. Procedures
    22. Index

Product information

  • Title: Oracle Database 11gR2 Performance Tuning Cookbook
  • Author(s): Ciro Fiorillo
  • Release date: January 2012
  • Publisher(s): Packt Publishing
  • ISBN: 9781849682602