| Understanding The Tuning Issues |
SQL Statement Execution |
 |
OLTP vs. Data Warehouses |
|
Database Tuning |
|
Infrastructure & network issues |
|
|
Parse Phase |
|
Execution Phase |
|
Fetch Phase |
|
Dedicated server processes |
|
Multi-threaded server processes |
|
Parallel Query Processes |
|
| Explain Plan Utility |
Cost Based Optimiser (CBO) |
|
Generating an execution plan |
|
Viewing the execution plan |
|
Interpreting the execution plan |
|
|
Optimisation methods |
|
Collecting statistics |
|
ANALYSE |
|
DBMS_STATS() |
|
Chained rows |
|
| Collecting Statistics |
Optimiser Operations |
|
Statistics Collection Methods |
|
Monitoring Object Modifications |
|
Using the DBMS_STATS() Package |
|
Using the ANALYZE Command |
|
CREATE INDEX … COMPUTE STATISTICS |
|
|
Table scans |
|
Join operations |
|
Hash operations |
|
| Indexes & The Execution Plan |
Optimiser Hints |
|
B-tree indexes |
|
Bitmap indexes |
|
Function-based indexes |
|
|
| Using TKPROF & AUTOTRACE |
Histograms |
|
|
|
Creating histograms |
|
Data Dictionary Storage |
|
| Using Plan Stability |
Editing Private Outlines |
|
Preparing for Stored Outlines |
|
Creating & Using Stored Outlines |
|
Data Dictionary Storage |
|
Managing Stored Outlines |
|
|
Preparing for Private Outlines |
|
Creating Private Outlines |
|
Editing Private Outlines |
|
Utilizing Private Outlines |
|
| Collecting System Statistics |
Exploiting & Managing Cursor Sharing |
|
Managing System Statistics |
|
Developing a Tuning Strategy |
|
|
Bind Variables & Cursor Peaking |
|
Using the CURSOR_SHARING Parameter |
|
| Understanding The Rule Based Optimizer |
Understanding The Rule Based Optimizer |
|
About the RBO |
|
Activating the RBO Explicitly |
|
How the RBO works |
|
The Access Paths |
|
RBO Join Execution Plan Selection |
|
|
Correctly building Indexes |
|
Understanding when Indexes will be ignored |
|
Rewriting Queries to avoid Index suppression |
|
Rewriting Queries to force Index suppression |
|
