E-book details

Oracle Database 11gR2 Performance Tuning Cookbook. 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

Oracle Database 11gR2 Performance Tuning Cookbook. 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

Ciro Fiorillo

Ebook
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.
  • Oracle Database 11gR2 Performance Tuning Cookbook
    • Table of Contents
    • Oracle Database 11gR2 Performance Tuning Cookbook
    • Credits
    • About the Author
    • Acknowledgement
    • About the Reviewers
    • www.PacktPub.com
      • Support files, eBooks, discount offers and more
        • Why Subscribe?
        • Free Access for Packt account holders
        • Instant Updates on New Packt Books
    • Preface
      • What this book covers
      • What you need for this book
      • Who this book is for
      • Conventions
      • Reader feedback
      • Customer support
        • Downloading the example code
        • Errata
        • Piracy
        • Questions
    • 1. Starting with Performance Tuning
      • Introduction
        • Incorrect session management
        • Poorly designed cursor management
        • Inadequate relational design
        • Improper use of storage structures
      • Reviewing the performance tuning process
        • How to do it...
        • How it works...
        • Theres more
        • See also
      • Exploring the example database
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
      • Acquiring data using a data dictionary and dynamic performance views
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Analyzing data using Statspack reports
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Collecting different amounts of data
          • Producing a report on a specific SQL
          • Automating snapshot generation
          • Statspack maintenance
      • Diagnosing performance issues using the alert log
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Analyzing data using Automatic Workload Repository (AWR)
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
      • Analyzing data using Automatic Database Diagnostic Monitor (ADDM)
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • A working example
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 2. Optimizing Application Design
      • Introduction
      • Optimizing connection management
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Dedicated server versus shared server
          • Web applications
          • Client-server Online Transaction Processing
          • Batch processing
        • See also
      • Improving performance sharing reusable code
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • PL/SQL and parsing
          • Diagnosing soft and hard parsing
        • See also
      • Reducing the number of requests to the database using stored procedures
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Reducing the number of requests to the database using sequences
        • How to do it...
        • How it works...
        • There's more...
          • Correct definition of a sequence
        • See also
      • Reducing the number of requests to the database using materialized views
        • How to do it...
        • How it works...
        • There's more...
          • Materialized views in depth
          • Materialized views and grants
          • Database parameters to use query rewrite
          • Can I use materialized views in an OLTP environment?
      • Optimizing performance with schema denormalization
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Not 1NF structures
      • Avoiding dynamic SQL
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 3. Optimizing Storage Structures
      • Introduction
      • Avoiding row chaining
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Avoiding row migration
        • How to do it...
        • How it works...
        • There's more...
          • Estimating table size with different PCTFREE parameter
      • Using LOBs
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
      • Using index clusters
        • How to do it...
        • How it works...
        • There's more...
          • Cluster size
          • Cluster index
          • Clustering and truncating
      • Using hash clusters
        • How to do it...
        • How it works...
        • There's more...
          • Sorted hash clusters
          • Custom hash function
          • Single-table hash clusters
      • Indexing the correct way
        • How to do it...
        • How it works...
        • There's more...
          • What is the "small percentage" of the data which assures we can improve performances using B-tree indexes?
        • See also
      • Rebuilding index
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Index rebuild and statistics
        • See also
      • Compressing indexes
        • How to do it
        • How it works...
        • There's more...
      • Using reverse key indexes
        • How to do it...
        • How it works...
        • There's more...
      • Using bitmap indexes
        • How to do it...
        • How it works...
        • There's more...
          • Bitmap join index
        • See also
      • Migrating to index organized tables
        • How to do it...
        • How it works...
        • There's more...
          • INCLUDING, OVERFLOW, PCTTHRESHOLD
          • Logical ROWID
        • See also
      • Using partitioning
        • How to do it...
        • How it works...
        • There's more...
          • List partitioning
          • Hash partitioning
          • Composite partitioning
    • 4. Optimizing SQL Code
      • Introduction
      • Using bind variables
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Concurrency and scalability
          • Security issues
        • See also
      • Avoiding full table scans
        • How to do it...
        • How it works...
        • There's more...
          • The High-Water Mark
          • PctFree, PctUsed, and FREELISTs
        • See also
      • Exploring index lookup
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Exploring index skip-scan and index range-scan
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Introducing arrays and bulk operations
        • How to do it...
        • How it works...
        • There's more...
          • When to use direct path load
        • See also
      • Optimizing joins
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Using subqueries
        • How to do it...
        • How it works...
        • There's more...
      • Tracing SQL activity with SQL Trace and TKPROF
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 5. Optimizing Sort Operations
      • Introduction
      • Sortingin-memory and on-disk
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Sorting and indexing
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Writing top n queries and ranking
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Using count, min/max, and group-by
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Avoiding sorting in set operations: union, minus, and intersect
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Troubleshooting temporary tablespaces
        • How to do it...
        • How it works...
        • There's more...
          • Optimal storage parameters for temporary tablespaces
        • See also
    • 6. Optimizing PL/SQL Code
      • Introduction
      • Using bind variables and parsing
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Array processing and bulk-collect
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Passing values with NOCOPY (or not)
        • How to do it...
        • How it works...
        • There's more...
      • Using short-circuit IF statements
        • How to do it...
        • How it works...
        • There's more...
      • Avoiding recursion
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Using native compilation
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Taking advantage of function result cache
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Inlining PL/SQL code
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Using triggers and virtual columns
        • How to do it...
        • How it works...
        • There's more...
          • Using WHEN and OF in trigger definition
          • Avoid FOR EACH ROW in triggers, when possible
        • See also
    • 7. Improving the Oracle Optimizer
      • Introduction
      • Exploring optimizer hints
        • How to do it...
        • How it works...
        • There's more...
          • Errors in hints
        • See also
      • Collecting statistics
        • How to do it...
        • How it works...
        • There's more...
          • Lock table statistics for load or highly volatile tables
          • Other procedures in DBMS_STATS
        • See also
      • Using histograms
        • How to do it...
        • How it works...
        • There's more...
          • Height-based and value-based (frequency) histograms
        • See also
      • Managing stored outlines
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Private and public stored outlines
        • See also
      • Introducing Adaptive Cursor Sharing for bind variable peeking
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Creating SQL Tuning Sets
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Using the SQL Tuning Advisor
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Configuring and using SQL Baselines
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 8. Other Optimizations
      • Introduction
      • Caching results with the client-side result cache
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Configuring the client-side result cache
        • See also
      • Enabling parallel SQL
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • Parallel query and I/O
          • When to use parallel SQL
        • See also
      • Direct path inserting
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Using create table as select
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Inspecting indexes and triggers overhead
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Loading data with SQL*Loader and Data Pump
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 9. Tuning Memory
      • Introduction
      • Tuning memory to avoid Operating System paging
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Tuning the Library Cache
        • How to do it...
        • How it works...
        • There's more...
          • How to minimize misses
        • See also
      • Tuning the Shared Pool
        • How to do it...
        • How it works...
        • There's more...
          • Tuning the Dictionary Cache
        • See also
      • Tuning the Program Global Area and the User Global Area
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Tuning the Buffer Cache
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 10. Tuning I/O
      • Introduction
      • Tuning at the disk level and strategies to distribute Oracle files
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Striping objects across multiple disks
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Choosing different RAID levels for different Oracle files
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
          • RAID level 0
          • RAID level 1
          • RAID level 5
          • RAID level 0+1
        • See also
      • Using asynchronous I/O
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Tuning checkpoints
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Tuning redo logs
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • 11. Tuning Contention
      • Introduction
      • Detecting and preventing lock contention
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Investigating transactions and concurrency
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Tuning latches
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Tuning resources to minimize latch contention
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
      • Minimizing latches using bind variables
        • Getting ready
        • How to do it...
        • How it works...
        • There's more...
        • See also
    • A. Dynamic Performance Views
      • ALL_OBJECTS
        • Fields
      • DBA_BLOCKERS
        • Fields
        • See also
      • DBA_DATA_FILES
        • Fields
        • See also
      • DBA_EXTENTS
        • Fields
        • See also
      • DBA_INDEXES
        • Fields
      • DBA_SQL_PLAN_BASELINES
        • Fields
      • DBA_TABLES
        • Fields
      • DBA_TEMP_FILES
        • Fields
        • See also
      • DBA_VIEWS
        • Fields
      • DBA_WAITERS
        • Fields
        • See also
      • INDEX_STATS
        • Fields
        • See also
      • DBA_SEQUENCES
        • Fields
      • DBA_TABLESPACES
        • Fields
      • DBA_TAB_HISTOGRAMS
        • Fields
      • V$ADVISOR_PROGRESS
        • Fields
      • V$BUFFER_POOL_STATISTICS
        • Fields
        • See also
      • V$CONTROLFILE
        • Fields
        • See also
      • V$DATAFILE
        • Fields
        • See also
      • V$DB_CACHE_ADVICE
        • Fields
        • See also
      • V$DB_OBJECT_CACHE
        • Fields
        • See also
      • V$ENQUEUE_LOCK
        • Fields
        • See also
      • V$FILESTAT
        • Fields
        • See also
      • V$FIXED_TABLE
        • Fields
      • V$INSTANCE_RECOVERY
        • Fields
      • V$LATCH
        • Fields
        • See also
      • V$LATCH_CHILDREN
        • Fields
        • See also
      • V$LIBRARYCACHE
        • Fields
      • V$LOCK
        • Fields
        • See also
      • V$LOCKED_OBJECT
        • Fields
        • See also
      • V$LOG
        • Fields
        • See also
      • V$LOG_HISTORY
        • Fields
        • See also
      • V$LOGFILE
        • Fields
        • See also
      • V$MYSTAT
        • Fields
        • See also
      • V$PROCESS
        • Fields
        • See also
      • V$ROLLSTAT
        • Fields
      • V$ROWCACHE
        • Fields
      • V$SESSION
        • Fields
        • See also
      • V$SESSION_EVENT
        • Fields
        • See also
      • V$SESSTAT
        • Fields
        • See also
      • V$SGA
        • Fields
        • See also
      • V$SGAINFO
        • Fields
        • See also
      • V$SHARED_POOL_RESERVED
        • Fields
      • V$SORT_SEGMENT
        • Fields
      • V$SQL
        • Fields
        • See also
      • V$SQL_PLAN
        • Fields
        • See also
      • V$SQLAREA
        • Fields
        • See also
      • V$STATNAME
        • Fields
        • See also
      • V$SYSSTAT
        • Fields
        • See also
      • V$SYSTEM_EVENT
        • Fields
      • V$TEMPFILE
        • Fields
      • V$TEMPSTAT
        • Fields
        • See also
      • V$WAITSTAT
        • Fields
        • See also
      • X$BH
        • Fields
    • B. A Summary of Oracle Packages Used for Performance Tuning
      • DBMS_ADDM
        • Procedures
      • DBMS_ADVISOR
        • Procedures
      • DBMS_JOB
        • Procedures
      • DBMS_LOB
        • Procedures
      • DBMS_MVIEW
        • Procedures
      • DBMS_OUTLN
        • Procedures
      • DBMS_OUTLN_EDIT
        • Procedures
      • DBMS_SHARED_POOL
        • Procedures
      • DBMS_SPACE
        • Procedures
      • DBMS_SPM
        • Procedures
      • DBMS_SQL
        • Procedures
      • DBMS_SQLTUNE
        • Procedures
      • DBMS_STATS
        • Procedures
      • DBMS_UTILITY
        • Procedures
      • DBMS_WORKLOAD_REPOSITORY
        • Procedures
    • Index
  • Title: Oracle Database 11gR2 Performance Tuning Cookbook. 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
  • Author: Ciro Fiorillo
  • Original title: Oracle Database 11gR2 Performance Tuning Cookbook. 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.
  • ISBN: 9781849682619, 9781849682619
  • Date of issue: 2012-01-20
  • Format: Ebook
  • Item ID: e_3bjg
  • Publisher: Packt Publishing