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... -- There's 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 -- Sorting-in-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...