Cargando…
Expert Oracle SQL : optimization, deployment, and statistics /
This book is about optimizing individual SQL statements, especially on production database systems. 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. The book contains two chapters of good inform...
| Clasificación: | Libro Electrónico |
|---|---|
| Autor principal: | |
| Formato: | Electrónico eBook |
| Idioma: | Inglés |
| Publicado: |
Berkeley, CA :
Apress,
2014.
|
| Colección: | Expert's voice in Oracle.
|
| Temas: | |
| Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
Tabla de Contenidos:
- 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