You are previewing Oracle Database 11gR2 Performance Tuning Cookbook.
O'Reilly logo
Oracle Database 11gR2 Performance Tuning Cookbook

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. Oracle Database 11gR2 Performance Tuning Cookbook
      2. Credits
      3. About the Author
      4. Acknowledgement
      5. About the Reviewers
      6. 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
      7. 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
      8. 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
      9. 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
      10. 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
      11. 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
      12. 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
      13. 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
      14. 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
      15. 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
      16. 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
      17. 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
      18. 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
      19. 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
      20. 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