Written by the world's most widely-read Oracle experts, this eBook is a super-advanced guide to hypercharging Oracle SQL performance. Not for the beginner, this is a guru-level overview of Oracle SQL internals tuning and may be beyond the ability of the average Oracle DBA. You will learn the internal mechanisms for Oracle indexing and see how Oracle indexes can be used to fine-tune any Oracle database. You will also explore advanced Oracle SQL internals including bitmap join indexes, STAR transformations and SQL diagnostics and SQL tracing. This eBook is indispensable for anyone who must ensure that their Oracle SQL is optimally tuned.

* Learn the internal mechanisms of Oracle Cost Based SQL Optimizer. * Understand how to write efficient SQL statements. * Learn how to use Oracle Optimizer Plan Stability (Stored Outlines). * See how to monitor SQL parsing. * Learn when to use Oracle bitmap indexes. * Understand Oracle STAR index internals. * Explore SQL trees and hierarchical SQL queries. * See how bitmap join indexes work. * See how to embed SQL inside Java. * Understand advanced SQL matrix transformation. * Use SQL with keyword searches. * Learn how to trace Oracle SQL execution. * Utilize indexes to reduce disk I/O.

About the Authors:

With nearly a century of combined experience, this eBook is authored by some of the world's leading Oracle SQL and Oracle tuning experts.    Don Burleson   Don Burleson is one of the world’s top Oracle Database experts with more than 20 years of full-time DBA experience.  He specializes in creating database architectures for very large online databases and he has worked with some of the world’s most powerful and complex systems.   A former Adjunct Professor, Don Burleson has written 32 books, published more than 100 articles in National Magazines, and serves as Editor-in-Chief of Oracle Internals. Don is a popular lecturer and teacher and is a frequent speaker at Oracle Openworld and other international database conferences. Click here for Don Burleson's books Click here for Don Burleson articles       Jonathan Lewis   Jonathan Lewis, author of "Practical Oracle 8i - Building Efficient Databases". Jonathan has more than 17 years experience with the Oracle RDBMS, is a regular presenter at UKOUG SIGs, and is the designer and presenter of the seminar "Optimizing Oracle - performance by design".       Dave Moore   Dave Moore is a Product Architect at BMC Software, a leading supplier of database software tools.  As the inventor of BMC's DBXray product, he has developed many products that assist Oracle DBAs and Developers.  He has over 13 years of experience in Information Technology - as a DBA and as a developer specializing in Oracle, Java and PL/SQL.   Dave has presented at Oracle OpenWorld and is a frequent contributor to Oracle Professional and DBAZine.  He holds a BS in Information Systems from Western Carolina University.  Dave lives with his wife and three children in Austin, Texas.   John Weeg   John Weeg has over 20 years of experience in information technology, starting as an application developer and progressing to his current level as an expert Oracle DBA. His focus for the past three years has been on performance, reliability, stability, and high availability of Oracle databases. Prior to this, he spent four years designing and creating data warehouses in Oracle. John has a Bachelor of Science degree in Computer Science from the University of Iowa, and did post-Graduate work at Michigan State University.       Dave Ensor   Dave Ensor worked for Oracle for 7 years as a worldwide performance specialist and has been working with Oracle databases for 15 years. In total, he has over 35 years active programming and design experience. As an Oracle design and tuning specialist, Dave has built a global reputation for finding cost-effective solutions to Oracle performance problems and for his ability to explain performance issues to technical audiences with both clarity and humor.         Vadim Tropashko   Vadim Tropashko works for Real World Performance group at Oracle Corp. Prior to that he was application programmer and translated "The C++ Programming Language" by B. Stroustrup, 2nd edition into Russian. His current interests include SQL Optimization, Constraint Databases, and Computer Algebra Systems.

Table of Contents:

 

Section One - SQL System Tuning Chapter 1 - Parsing in Oracle SQL Parsing in SQL by Vadim Tropashko Chapter 2 - Are We Parsing Too Much? Are We Parsing Too Much? by John Weeg What is Identical? How Much CPU are We Spending Parsing? Library Cache Hits Shared Pool Free Space Cursors Code Do What You Can Chapter 3 - Oracle SQL Optimizer Plan Stability Plan Stability in Oracle 8i/9i by Jonathan Lewis The Back Door to the Black Box Background / Overview Preliminary Setup What Does the Application Want to Do? What Do You Want the Application to Do? From Development to Production Oracle 9 Enhancements Caveats Conclusion Chapter 4 - SQL Tuning Using dbms_stats Query Tuning Using DBMS_STATS by Dave Ensor Introduction Test Environment Background Original Statement With Hash Join Hints Oracle's Cost-based Optimizer CPU Cost Key Statistics Other Factors Cursor Sharing Package DBMS_STATS Plan Stability Getting CBO to the Required Plan Localizing the Impact Ensuring Outline Use Postscript Section Two - SQL Statement Tuning Chapter 5 - Trees in SQL Trees in SQL: Nested Sets and Materialized Path by Vadim Tropashko Tropashko Adjacency List Materialized Path Nested Sets Nested Intervals Partial Order The Mapping Normalization Finding Parent Encoding and Sibling Number Calculating Materialized Path and Distance between node The Final Test Chapter 6 - SQL Tuning Improvements SQL Tuning Improvements in Oracle 92 by Vadim Tropashko Access and Filter Predicates V$SQL_PLAN_STATISTICS Chapter 7 - Oracle SQL Tuning Tips SQL tuning by Don Burleson Chapter 8 - Altering SQL Stored Outlines Faking Stored Outlines in Oracle 9 by Jonathan Lewis Review The Changes New Features Old Methods (1) Old Methods (2) The Safe Bet Conclusion References

Section Three - SQL Index Tuning Chapter 9 - Using Bitmap Indexes with Oracle Understanding Bitmap Indexes by Jonathan Lewis Everybody Knows … What Is a Bitmap Index? Do Bitmaps Lock Tables? Consequences of Bitmap Locks Problems with Bitmaps Low Cardinality Columns Sizing Conclusion References Chapter 10 - SQL Star Transformations Bitmap Indexes 2: Star Transformations by Jonathan Lewis The Bitmap Star Transformation Warnings Conclusion References Chapter 11 - Bitmap Join Indexes Bitmap Indexes 3 — Bitmap Join Indexes by Jonathan Lewis It's fantastic - What's the Problem What Is a Bitmap Join Index? Issues Conclusion References Section Four - SQL Diagnostics Chapter 12 - Tracing SQL Execution Oracle_trace - the Best Built-in Diagnostic Tool? by Jona Lewis How Do I … ? What is oracle_trace Uses for oracle_trace Putting it All Together Some Results Now What? The Future Conclusion Caveat References Chapter 13 - Embedding SQL in Java & PL/SQL Java vs PL/SQL: Where Do I Put the SQL? by Dave Moore The Power of a Package Table of Contents vii The Flexibility of Java Performance Benchmarks Environment The Tests Java: PL/SQL: Multiple Statements Java: PL/SQL: Truncate Java: PL/SQL: Benchmark Results Single Statement Results Multiple Statements Results Truncate Results Remote Results Conclusion Chapter 14 - Matrix Transposition in Oracle SQL Matrix Transposition in SQL by Vadim Tropashko Nesting and Unnesting Integer Enumeration for Aggregate Dismembering User Defined Aggregate Functions Section Five - Advanced SQL Chapter 15 - SQL with Keyword Searches Keyword Searches by Joe Celko Chapter 16 - Using SQL with Web Databases Web Databases by Joe Celko Chapter 17 - SQL and Calculated Columns Calculated Columns by Joe Celko Introduction Triggers INSERT INTO Statement UPDATE the Table Use a VIEW