Best Practices for Faster Queries: A SQL Performance Tuning Tutorial

Introduction

Effective SQL requests are essential for optimal performance in Oracle databases. This article focuses on key practices for optimizing requests, with practical examples adapted to Oracle environments.

1. effectively use indexes

Indexes are a powerful Oracle tool to accelerate data recovery. Oracle supports various index types, including B,, BitmapAnd Index based on the function.

Example: Creation of a B-Tree index

CREATE INDEX idx_employee_name ON Employees(Name);

Why it matters: Without index, Oracle performs a full table analysis, which is slower. The index allows Oracle to locate the lines more effectively.

2. Avoid using functions on indexed columns

The use of functions on indexed columns prevents Oracle from using the index, leading to a full table analysis.

Bad practice

SELECT * FROM Employees 
WHERE UPPER(Name) = 'ALICE';

Good practice

SELECT * FROM Employees 
WHERE Name = 'Alice';

Why it matters: Keep the unchanged indexed columns to allow the optimizer to effectively use the index.

3. Use Oracle execution plans

Oracle execution plans provide detailed information on how requests are executed, helping to identify ineffectiveness.

Example: display of the execution plan

EXPLAIN PLAN FOR 
SELECT e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > 50000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

To research::

• Fulle table scans: Indicate missing or ineffective clues.
• High cost operations: optimize joints, filters or aggregations.

4. Use liaison variables

The liaison variables improve performance by allowing Oracle to reuse the execution plans, reducing the hard analysis.

Example: using liaison variables

VARIABLE salary_threshold NUMBER;
EXEC :salary_threshold := 50000;

SELECT Name, Department 
FROM Employees 
WHERE Salary > :salary_threshold;

Why it matters: Reduces the use of the processor and memory by avoiding repeated analysis for similar queries.

5. Part of large tables

Partitioning shares a large table in smaller and manageable pieces, improving the performance and scalability of the request.

Example: Beach partitioning

CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATE,
    TotalAmount NUMBER
)
PARTITION BY RANGE (OrderDate) (
    PARTITION p2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD')),
    PARTITION p2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

Ask a partitioned table

SELECT * FROM Orders 
WHERE OrderDate BETWEEN TO_DATE('2021-01-01', 'YYYY-MM-DD') AND TO_DATE('2021-12-31', 'YYYY-MM-DD');

Why it matters: Oracle analyzes only the relevant partition instead of the whole table, reducing the E / S.

6. Use materialized views for complex requests

The materialized views store the results of the pre -cutted query, accelerating execution for repeated requests.

Example: Creation of a materialized view

CREATE MATERIALIZED VIEW EmployeeStats 
AS
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

Interrogate::

SELECT * FROM EmployeeStats;

Why it matters: Materialized views reduce calculation time for aggregations and complex joins.

7. Monitor the performance of the request with AWR

Oracle Automatic workload repository (AWR) Helps identify slow queries and bottlenecks.

Generate an AWR report

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

-- Query AWR data
SELECT * FROM DBA_HIST_SQLTEXT WHERE SQL_TEXT LIKE '%Employees%';

Why it matters: AWR provides detailed information on high intensity of resources and helps identify optimization opportunities.

Summary of best practices

Conclusion

By following these best practices specific to Oracle, you can optimize SQL queries, reduce the execution time and improve the overall performance of the database. Start implementing these tips in your Oracle environment to see significant improvements!

Thank you for taking the time to explore data related to me with me. I appreciate your commitment. If you find this information useful, I invite you to follow me or connect with me on LinkedIn. Happy to explore! 👋