Expert Oracle SQL : Optimization, Deployment, and Statistics / by Tony Hasler.

Format:

Book

Author/Creator:

Maccoppi, Antonio, Author.

Series:

The expert's voice in Oracle

Language:

English

Subjects (All):

Oracle (Computer file).

Database management.

Database Management.

Local Subjects:

Database Management.

Physical Description:

1 online resource (599 p.)

Edition:

1st ed. 2014.

Place of Publication:

Berkeley, CA : Apress : Imprint: Apress, 2014.

Language Note:

English

System Details:

text file

Summary:

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.

Contents:

Contents 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

Running the SQL Tuning Advisor

Notes:

"The Expert's Voice in Oracle"--Cover.

"Covers Oracle 11gR2 and 12cR1"--Cover.

"Independent Oracle Users Group"--Cover.

Includes index.

ISBN:

9781430259787

1430259787

OCLC:

885593917