SQL Tuning

SQL Tuning
SQL Tuning
English | MP4 | AVC 1376×768 | AAC 44KHz 2ch | 3 Hours | 648 MB

Learn how to formulate and tune SQL statements for optimal performance

This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems and will provide you with the skills necessary to write scalable, high performance SQL.

The SQL tuning methodology I used:

  • Identify a problem SQL statement
  • Determine how Oracle is executing SQL statement and why Oracle chose that way.
  • Investigate whether alternative executon plans would be better.
  • Update the SQL statement to push Oracle towards the better plan.
Table of Contents

SQL Tuning Introduction
1 Why SQL Tuning
2 Prerequisites for SQL Tuning
3 SQL Processing
4 SOFT parse vs HARD parse

Optimizer and its Execution Plan
5 Cost based Optimization
6 Gathering Statistics
7 Execution Plan
8 SQL Tuning Tools
9 Running Explain Plan
Optimizer statistics.html

Accessing Tables
10 What is my Address
11 Types of Table Accesses
12 Table Access FULL
13 Table Access by ROWID
14 Index Unique Scan
15 Index Range Scan
16 Choosing between FULL and INDEX scan
Access Paths.html

Explain Plan
17 Execution Plan
18 What should you look for
19 What is COST
20 Rules of Execution Plan Tree
21 Traversing through the Tree
22 Reading Execution Plan
23 Execution Plan Example 1
24 Execution Plan Example 2
25 Execution Plan Example 3
26 Execution Plan Example 4

Simple Tuning Rules
27 SELECT consideration
28 Using Table Aliases
29 Using WHERE rather than HAVING
Simple Rules.html

Index Suppression
30 Index Suppression reasons
31 Use of operator
32 Use of SUBSTR function
33 Use of Arithmetic operators
34 Use of TRUNC function on Date columns
35 Use of operator
36 Comparing a character column to a numeric value
37 Use of IS NULL and IS NOT NULL
Index Suppression SQL.html

Performance Improvement Considerations
39 Use UNION instead of OR
40 Use UNION ALL instead of UNION
41 Minimize Table lookups in a Query
43 Use EXISTS instead of DISTINCT
44 Reading same table multiple times
45 Use TRUNCATE instead of DELETE

Considerations while using SQL in programming
46 Reduce the number of Trips to the database
47 Issue frequent COMMIT statements

Join Methods
49 Join Methods
50 Nested Loop Join
51 Hash Join
52 Sort Merge Join

HINTS to the database
53 Why HINTS
54 Forcing a specific Join Method
55 HINTS list.pdf

House Keeping
56 Invalid Optimizer Statistics
57 Checking SQL statements which are performing BAD

Design Considerations
58 Effective Schema Design
59 Separate Tablespace for Data and Index
60 Index Organized Tables
61 Partitioned Tables
62 Bitmap Indexes