Expert Oracle SQL: optimization, deployment, and statistics

Description

Expert Oracle SQL: Optimization, Deployment, and Statistics is about optimizing individual SQL statements, especially on production database systems. This Oracle-specific book begins by assuming you have already identified a particular SQL statement and are considering taking steps to improve its performance. The book describes a systematic process by which to diagnose a problem statement, identify a fix, and to implement that fix safely in a production system. You'll learn not only to improve performance when it is too slow, but also to stabilize performance when it is too variable. You'll learn about system statistics and how the Cost-Based Optimizer uses them to determine a suitable execution plan for a given statement. That knowledge provides the foundation from which to identify the root cause, and to stabilize and improve performance. Next after identifying a problem and the underlying root cause is to put in place a solution. Expert Oracle SQL: Optimization, Deployment, and Statistics explains how to apply various remedies such as changing the SQL statement, adding hints, changing the physical design, and more, and how they can be brought to bear on fixing a problem once and for all. Rolling a change out smoothly is an important topic when dealing with tuning and optimization. Expert Oracle SQL: Optimization, Deployment, and Statistics does not let you down in this critical area. The book contains two chapters of good information from an experienced professional on how to safely deploy changes into production so that your systems remaining running and available throughout the deployment process. Describes a systematic approach to tuning Oracle SQL Explains how things are supposed to work, what can go wrong, and how to fix it Takes you through the steps needed to stabilize performance in your production systems.;At a Glance; About IOUG Press; Contents; About the Author; About the Technical Reviewers; Acknowledgments; Foreword; Introduction; Part 1: Basic Concepts; Chapter 1: SQL Features; SQL and Declarative Programming Languages; Statements and SQL_IDs; Cross-Referencing Statement and SQL_ID; Array Interface; Subquery Factoring; The Concept of Subquery Factoring; Improving Readability; Using Factored Subqueries Multiple Times; Avoiding the Use of Temporary Tables; Recursive Factored Subqueries; Joins; Inner Joins and Traditional Join Syntax; A Simple Two Table Join; A Four Table Inner Join. At a Glance About IOUG Press Contents About the Author About the Technical Reviewers Acknowledgments Foreword Introduction Part 1: Basic Concepts Chapter 1: SQL Features SQL and Declarative Programming Languages Statements and SQL_IDs Cross-Referencing Statement and SQL_ID Array Interface Subquery Factoring The Concept of Subquery Factoring Improving Readability Using Factored Subqueries Multiple Times Avoiding the Use of Temporary Tables Recursive Factored Subqueries Joins Inner Joins and Traditional Join Syntax A Simple Two Table Join A Four Table Inner Join. Outer Joins and ANSI Join SyntaxLeft Outer Joins Right Outer Joins Full Outer Joins Partitioned Outer Joins Summary Chapter 2: The Cost-Based Optimizer The Optimal Execution Plan The Definition of Cost The CBO's Cost-Estimating Algorithm Calculating Cost The Quality of the CBO's Plan Selection The Accuracy of the CBO's Cost Estimates Suboptimal Execution Plans The Optimization Process Parallelism Query Transformation Final State Query Optimization Summary Chapter 3: Basic Execution Plan Concepts Displaying Execution Plans Displaying the Results of EXPLAIN PLAN. EXPLAIN PLAN May Be MisleadingDisplaying Output from the Cursor Cache Displaying Execution Plans from the AWR Understanding Operations What an Operation Does How Operations Interact Operation 0: SELECT STATEMENT Operation 1: SORT AGGREGATE Operation 2: TABLE ACCESS FULL Operation 3: HASH JOIN Operations 4 and 5: TABLE FULL SCANs How Operations Interact Wrap Up How Long Do Operations Take? Summary Chapter 4: The Runtime Engine Collecting Operation Level Runtime Data The GATHER_PLAN_STATISTICS Hint Setting STATISTICS_LEVEL=ALL Enabling SQL Tracing. Displaying Operational Level DataDisplaying Runtime Engine Statistics with DBMS_XPLAN. DISPLAY_CURSOR Displaying Runtime Engine Statistics with VSQL_PLAN_STATISTICS_ALL Displaying Session Level Statistics with Snapper The SQL Performance Monitor Workareas Operations Needing a Workarea Allocating Memory to a Workarea Calculating Memory Allocation When WORKAREA_SIZE_POLICY Is Set to AUTO Calculating Memory Allocation When WORKAREA_SIZE_POLICY Is Set to MANUAL Optimal, One-Pass, and Multipass Operations Shortcuts Scalar Subquery Caching Join Shortcuts Result and OCI Caches. Function Result CacheSummary Chapter 5: Introduction to Tuning Understanding the Problem Understanding the Business Problem Understanding the Technical Problem Understanding the SQL Statement Understanding the Data Understanding the Problem Wrap Up Analysis Running the Statement to Completion Analyzing Elapsed Time When the Elapsed Times Doesn't Add Up When the Time Does Add Up Fixing the Problem Check the Statistics Changing the Code Adding Information Transforming the Query Adding Hints Making Physical Changes to the Database Making Changes to the Environment.