🚀

Oracle Performance Tuning Lecture Notes

Jul 4, 2024

Oracle Performance Tuning Lecture Notes

Introduction

  • Performance Tuning: Crucial when handling large volumes of data in Oracle or any other databases (e.g., SQL, DB2, Teradata).
  • ETL Environment: Fact tables often handle terabytes of data, making performance tuning essential.

Key Methods of Performance Tuning

Explain Plan

  • Purpose: To analyze how SQL queries are executed and identify areas for optimization.
  • Usage: Provides information on query execution, such as CPU usage and scanning methods.

Collect Statistics (Collect Stats)

  • Purpose: Improves performance by creating metadata that Oracle can use for optimization.
  • Usage: Applied to large tables to gather statistics.

Optimizer

  • Definition: Software that determines the most efficient way to execute a query.
  • Execution Plan: Similar to planning a route, the optimizer provides the best execution path based on past data.

Partitioning

  • Importance: Splits large tables into smaller, manageable pieces, each treated as an independent object.
  • Types: Available in Oracle Enterprise Edition (not in Express or Standard Editions).
  • Strategies: Include methods like range partitioning, hash partitioning, list partitioning, and composite partitioning.

Best Practices for Query Writing

Avoiding Full Table Scans

  • Do not use: SELECT * FROM table
  • Use: Specific column names and filters to minimize the data scanned.*

Use of Indexes

  • General Rule: More appropriate for tables with frequent SELECT operations, not frequent inserts or updates.
  • Pro Tip: Drop indexes during bulk insert/update and recreate them afterward.

Temporary Tables

  • Usage: Combine and store data from multiple tables into temporary tables before performing joins.

Join Conditions

  • Preference: Use efficient join conditions and consider replacing IN keyword with EXISTS for faster performance.

Advanced Techniques

Partitioning Methods

  • Range Partitioning: Splits data into specified ranges.
  • List Partitioning: Uses discrete values for partitioning.
  • Hash Partitioning: Uses a hash algorithm for even distribution.
  • Composite Partitioning: Combines two or more methods (range + hash, range + list, etc).

Hints and Optimizer

  • Purpose: Provide guidance to the optimizer on how to execute queries.
  • Example: PARALLEL hints to run operations in parallel.

Performance Tuning Process

  • Identify Issues: Use EXPLAIN PLAN to understand execution paths and costs.
  • Collection of Stats: Run COLLECT STATS periodically to update metadata.
  • Adjust Indexes and Queries: Using insights gained, adjust indexes and refine queries.

Practical Examples

Explain Plan Execution

  1. Explain Query: EXPLAIN PLAN FOR SELECT ...
  2. Review Plan: Using results from DBMS_XPLAN.DISPLAY
  3. Understand Steps: Identify full table scans, index usage, etc.

Index Creation and Impact

  1. Create Index: CREATE INDEX idx ON table(column)
  2. Compare Plans: Check the impact on the EXPLAIN PLAN results.

Partitioning Example

  1. Create Range Partition:
CREATE TABLE sales ( product_id NUMBER, ... ) PARTITION BY RANGE (created_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2020', 'DD-MM-YYYY')), ... );
  1. Enable Row Movement: ENABLE ROW MOVEMENT
  2. Collect Stats: For updated partitions
  3. Select From Partitions: Using specific PARTITION keywords or filter conditions.

Using Parallel Hint

  • Query Example:
SELECT /*+ PARALLEL(8) */ * FROM employees WHERE department_id = 10;
  • Explanation: Runs in 8 parallel threads, improving performance for large datasets.

Summary

  • Ongoing Process: Performance tuning is continuous, adapting to data volume growth and changing requirements.
  • Multi-faceted Approach: Combining query optimization, indexing, statistics collection, hint usage, and partitioning for best results.

Full transcript