How does an organization create a plan of attack for SQL tuning?
What are the tricks for changing its execution plans?
How can an organization make Oracle SQL statements perform quickly and efficiently?
How can an organizaiton uncover the internal execution plan for Oracle SQL?
How can an organization learn how to extract top performance from the application?
- high - medium - low
Application Development
Architecture and Infrastructure
Business Applications
Data Management
eBusiness
Independent Oracle database expert and author Donald Burleson cuts through the complexities of this challenging application and details a plan of attack for SQL tuning. Oracle SQL is arguably the most popular database interface language in the world, and IT professionals need to understand the secret tricks for changing its execution plans. In this program, Burleson reveals his secrets - based on years of experience - for making Oracle SQL statements perform quickly and efficiently. The presentation also shows how to uncover the internal execution plan for Oracle SQL, and how to adjust the execution plan for optimal performance. If you write Oracle queries, this program is indispensable for learning how to extract top performance from the application.
For those with access to the CD, Internet and intranet versions, this program provides a selection of Web links, including articles such as, 'Getting the Most From Oracle's Cost-Based Optimizer' from Akadia Information Technology, which explore how DBAs can tune their SQL statements to make the most of the Oracle database's complex features. White papers include 'Tuning Packaged Software and Production Applications' which illustrates how to identify and modify the SQL statements that are slowing down Oracle processes.
PROGRAM TOPICS:
INTRODUCTION
AGENDA
ORACLE TUNING OVERVIEW
THE GOALS OF SQL TUNING
Tuning Techniques
The Tuning Professional
THE ORACLE SQL OPTIMIZERS
Rule-Based Optimization
Cost-Based Optimization
Choose Optimization
Cost-Based Optimization Requires the Analyze Command
PLAN OF ATTACK FOR SQL TUNING
Check Externals Before You Begin SQL Tuning
Fishing Out the SQL
Finding High-Impact SQL Statements
Determine the Execution Plan for the SQL
Tune the SQL Statement
USING HINTS TO TUNE ORACLE SQL
Hints Serve a Dual Purpose
Adding Hints to a SQL Statement
Three Categories of SQL Statement Hints
Compatibility Between Hints and Table Access Methods
The Number and Complexity of Hints Is Increasing
The Rule Hint
The First_Rows Hint
Formulating SQL Queries
LOCATING AND TUNING FULL TABLE SCANS
Searching for SQL Tuning Opportunities
Using an access.sql Script
Evaluating the SQL Statement
Full Table Scans vs. Index Range Scans
Determining the Threshold for a Full Table Scan
Test the Execution Speed by Timing the Query
OPTIMIZER PLAN STABILITY
Can You Use Stored Outlines?
Benefits of Creating a Stable Execution Plan for Oracle SQL
CURSOR_SHARING
CURSOR_SHARING and SQL Performance
DATA WAREHOUSE SQL
Data Warehouse SQL: Large Table Join Tuning
The Parse Phase of Data Warehouse SQL
The ORDERED Hint
Main Points to Remember About Data Warehouse SQL
ORACLE PARALLEL QUERY
Invoking Parallelism
The Alter Table Command Can Be Dangerous
Be Careful When Setting Parallelism
Determining the Optimal Degree of Parallelism
Factors That Affect Parallelism Settings
Setting the Optimal Degree of Parallelism
SUMMARY
Choose the Proper OPTIMIZER_MODE
Evaluate All Large Table Full Table Scans
Use ORDERED Hints
Try Using Nonstandard Indexes
Set the Driving Table
CURSOR_SHARING, Optimizer Plan Stability and Oracle Parallel Query
- high
- medium
- low
