Scribd
Upload
41 views12 pages

Oracle SQL Tuning Essentials Guide

The workshop focuses on Oracle SQL tuning, highlighting the importance of the Cost-Based Optimizer (CBO) in determining the most efficient execution plans based on table and index statistics. Key concepts include understanding execution plans, the role of indexes, and the impact of selectivity and cardinality on query performance. Practical case studies demonstrate significant performance improvements through effective SQL tuning and indexing strategies.

Uploaded by

sdfaql94
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
41 views12 pages

Oracle SQL Tuning Essentials Guide

The workshop focuses on Oracle SQL tuning, highlighting the importance of the Cost-Based Optimizer (CBO) in determining the most efficient execution plans based on table and index statistics. Key concepts include understanding execution plans, the role of indexes, and the impact of selectivity and cardinality on query performance. Practical case studies demonstrate significant performance improvements through effective SQL tuning and indexing strategies.

Uploaded by

sdfaql94
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

Oracle Performance Tuning

Workshop – Day 2
SQL Tuning Essentials
Recap from Day 1 and Today's
Focus
• Recap:
• • Architecture: Database, Instance, Memory,
Processes
• • Using V$ views to observe real-time activity
• Today's Focus:
• • Understanding Oracle SQL Tuning
• • Optimizer behavior and execution plans
• • Indexes and their tuning impact
The Oracle Optimizer: The GPS for
Your Queries
• Optimizer = Oracle's decision-maker for SQL
execution
• Cost-Based Optimizer (CBO) chooses lowest-
cost plan
• CBO depends heavily on table/index statistics
• Old or missing stats = bad performance
Analogy: Optimizer as a GPS
• Query = Your destination
• Optimizer = GPS finding best route
• Execution Plan = Selected route
• Indexes/statistics = Map data for better routes
Understanding Execution Plans
• Execution Plan = Step-by-step roadmap of SQL
execution
• EXPLAIN PLAN shows plan without running the
query
• Use DBMS_XPLAN.DISPLAY to view the plan
output
Reading Execution Plans
• Read from bottom to top
• TABLE ACCESS FULL = Full table scan (slow)
• INDEX RANGE SCAN = Efficient use of index
• Use plans to compare before and after tuning
The Role of Indexes
• Index = Shortcut to find data faster
• Like book index: jump to relevant page quickly
• Independent of table but tightly related
Selectivity and Cardinality
• Cardinality = Number of unique values
• Selectivity = Unique values / total rows
• High selectivity = Index more effective
Index Usage and Common Pitfalls
• Use index when WHERE clause filters on high
selectivity columns
• Avoid functions in WHERE clause (e.g.,
UPPER(col))
• Optimizer can skip index if function hides
column
Case Study: Bad SQL vs Tuned SQL
• Original Query: Used UPPER(column), slow
10s
• Fixed: Removed function + added index
• Result: Query runs in 0.5s – huge
improvement
Demo Preview: Impact of Indexes
• Create large test table (employees_demo)
• Run query + check EXPLAIN PLAN before index
• Create index, gather stats, rerun query
• Check updated EXPLAIN PLAN
Key Takeaways
• Optimizer makes decisions based on cost and
stats
• Execution Plans help decode performance
issues
• Indexes can dramatically improve query time
• Always test changes with EXPLAIN PLAN

You might also like