This course is designed to teach the students how to prevent SQL performance problems and how to improve the performance of existing SQL.
Content
Introduction to SQL performance and tuning_x001a_ Performance issues_x001a_ Simple example_x001a_ Visualizing the problem_x001a_ SummaryPerformance analysis tools_x001a_ Components of response time_x001a_ Time estimates with VQUBE3_x001a_ SQL EXPLAIN_x001a_ The accounting trace_x001a_ The bubble chart_x001a_ Performance thresholdsIndex basics_x001a_ Indexes_x001a_ Index structure_x001a_ Estimating index I/Os_x001a_ Clustering index_x001a_ Index page splitsAccess paths_x001a_ Classification_x001a_ Matching versus Screening_x001a_ Variations_x001a_ Hash access_x001a_ Prefetch_x001a_ CaveatMore on indexes_x001a_ Include index_x001a_ Index on expression_x001a_ Random index_x001a_ Partitioned and partitioning, NPSI and DPSI_x001a_ Page range screening_x001a_ Features and limitationsTuning methodology and index cost_x001a_ Methodology_x001a_ Index cost: Disk space_x001a_ Index cost: Maintenance_x001a_ Utilities and indexes_x001a_ Modifying and creating indexes_x001a_ Avoiding sortsIndex design_x001a_ Approach_x001a_ Designing indexesAdvanced access paths_x001a_ Prefetch_x001a_ List prefetch_x001a_ Multiple index access_x001a_ Runtime adaptive indexMultiple table access_x001a_ Join methods_x001a_ Join types_x001a_ Designing indexes for joins_x001a_ Predicting table orderSubqueries_x001a_ Correlated subqueries_x001a_ Non-correlated subqueries_x001a_ ORDER BY and FETCH FIRST with subqueries_x001a_ Global query optimization_x001a_ Virtual tables_x001a_ Explain for subqueriesSet operations (optional)_x001a_ UNION, EXCEPT, and INTERSECT_x001a_ Rules_x001a_ More about the set operators_x001a_ UNION ALL performance improvementsTable design (optional)_x001a_ Number of tables_x001a_ Clustering sequence_x001a_ Denormalization_x001a_ Materialized query tables (MQTs)_x001a_ Temporal tables_x001a_ Archive enabled tablesWorking with the optimizer_x001a_ Indexable versus non-indexable predicates_x001a_ Boolean versus non-Boolean predicates_x001a_ Stage 1 versus stage 2_x001a_ Filter factors_x001a_ Helping the optimizer_x001a_ PaginationLocking issues_x001a_ The ACID test_x001a_ Reasons for serialization_x001a_ Serialization mechanisms_x001a_ Transaction locking_x001a_ Lock promotion, escalation, and avoidanceMore locking issues (optional)_x001a_ Skip locked data_x001a_ Currently committed data_x001a_ Optimistic locking_x001a_ Hot spots_x001a_ Application design_x001a_ Analyzing lock waitsMassive batch (optional)_x001a_ Batch performance issues_x001a_ Buffer pool operations_x001a_ Improving performance_x001a_ Benefit analysis_x001a_ Massive deletes