Prerequisites
For the examples, we’ll assume you have a set of sample tables like employees, departments, sales, and others. The scenarios will include creating these tables, inserting data, and then demonstrating poorly performing queries and how to optimize them.
Table Creation Scripts
1. Employee Table
sqlCREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
department_id NUMBER,
hire_date DATE,
salary NUMBER,
status VARCHAR2(10)
);
CREATE INDEX idx_emp_name ON employees(emp_name);
CREATE INDEX idx_emp_dept ON employees(department_id);
2. Department Table
sqlCREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(100)
);
INSERT INTO departments VALUES (10, 'HR');
INSERT INTO departments VALUES (20, 'Finance');
INSERT INTO departments VALUES (30, 'Sales');
INSERT INTO departments VALUES (40, 'IT');
3. Sales Table
sqlCREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
emp_id NUMBER,
sale_date DATE,
sale_amount NUMBER
);
CREATE INDEX idx_sale_date ON sales(sale_date);
50 Query Tuning Scenarios with Scripts and Suggestions
1. Full Table Scan Without Index
Scenario: Querying a large table without an index.
sqlSELECT emp_name FROM employees WHERE emp_name = 'John Doe';
Solution: Create an index on emp_name.
sqlCREATE INDEX idx_emp_name ON employees(emp_name);
**2. Avoid SELECT ***
Scenario: Querying all columns when only specific ones are needed.
sqlSELECT * FROM employees WHERE department_id = 10;
Solution: Select only required columns.
sqlSELECT emp_id, emp_name FROM employees WHERE department_id = 10;
3. Avoid NOT IN
Scenario: Using NOT IN causes performance issues.
sqlSELECT emp_id FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments);
Solution: Rewrite using NOT EXISTS.
sqlSELECT emp_id FROM employees e WHERE NOT EXISTS (
SELECT 1 FROM departments d WHERE e.department_id = d.department_id
);
4. Use EXISTS Instead of IN
Scenario: IN on a subquery with many rows performs poorly.
sqlSELECT emp_id FROM employees WHERE department_id IN (SELECT department_id FROM departments);
Solution: Use EXISTS.
sqlSELECT emp_id FROM employees e WHERE EXISTS (
SELECT 1 FROM departments d WHERE e.department_id = d.department_id
);
5. Avoid Function on Indexed Column
Scenario: Applying a function to an indexed column prevents index usage.
sqlSELECT emp_id FROM employees WHERE UPPER(emp_name) = 'JOHN DOE';
Solution: Avoid the function or create a function-based index.
sqlCREATE INDEX idx_emp_name_upper ON employees(UPPER(emp_name));
6. Use Bind Variables
Scenario: Avoid using literals to reduce parsing.
sqlSELECT emp_name FROM employees WHERE department_id = 10;
Solution: Use bind variables.
sqlVARIABLE dept_id NUMBER;
EXEC :dept_id := 10;
SELECT emp_name FROM employees WHERE department_id = :dept_id;
7. Partition a Large Table
Scenario: Queries on large tables without partitions are slow.
sqlCREATE TABLE sales (
sale_id NUMBER,
emp_id NUMBER,
sale_date DATE,
sale_amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
PARTITION p_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);
8. Avoid Cartesian Joins
Scenario: Missing join conditions lead to Cartesian products.
sqlSELECT e.emp_name, d.department_name FROM employees e, departments d;
Solution: Add join conditions.
sqlSELECT e.emp_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
9. Use UNION ALL Instead of UNION
Scenario: UNION removes duplicates, which adds overhead.
sqlSELECT emp_name FROM employees WHERE department_id = 10
UNION
SELECT emp_name FROM employees WHERE department_id = 20;
Solution: Use UNION ALL if duplicates aren't an issue.
sqlSELECT emp_name FROM employees WHERE department_id = 10
UNION ALL
SELECT emp_name FROM employees WHERE department_id = 20;
10. Use Index-Organized Table (IOT)
Scenario: For read-heavy tables with a single access path, consider an IOT.
sqlCREATE TABLE iot_employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
department_id NUMBER
) ORGANIZATION INDEX;
11. Rewrite Correlated Subqueries
Scenario: Correlated subqueries can be inefficient.
sqlSELECT emp_name,
(SELECT department_name FROM departments d WHERE d.department_id = e.department_id)
FROM employees e;
Solution: Use a join.
sqlSELECT e.emp_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
12. Optimize Grouping with Indexes
Scenario: Grouping on non-indexed columns is slow.
sqlSELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
Solution: Create an index on department_id.
sqlCREATE INDEX idx_dept_id ON employees(department_id);
13. Use Analytical Functions Instead of Subqueries
Scenario: Using subqueries for rankings and other analytics can be inefficient.
sqlSELECT emp_id, emp_name FROM (
SELECT emp_id, emp_name, RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) WHERE rnk <= 5;
14. Avoid Implicit Data Type Conversion
Scenario: Implicit conversions cause full table scans.
sqlSELECT emp_id FROM employees WHERE emp_id = '100';
Solution: Use explicit data types.
sqlSELECT emp_id FROM employees WHERE emp_id = 100;
15. Gather Statistics Regularly
Scenario: Outdated statistics can lead to suboptimal execution plans.
sqlBEGIN
DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
END;
/
16. Use Optimizer Hints
Scenario: Force Oracle to use specific execution plans when necessary.
sqlSELECT /*+ INDEX(employees idx_emp_name) */ emp_id, emp_name
FROM employees WHERE emp_name = 'John Doe';
17. Leverage Parallel Execution
Scenario: Queries on large tables can be sped up using parallel execution.
sqlSELECT /*+ PARALLEL(employees, 4) */ emp_id, emp_name
FROM employees WHERE department_id = 10;
18. Use Materialized Views for Aggregates
Scenario: Expensive aggregation queries can benefit from materialized views.
sqlCREATE MATERIALIZED VIEW emp_sales_mv
AS
SELECT emp_id, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY emp_id;
19. Minimize the Use of DISTINCT
Scenario: Using DISTINCT can lead to unnecessary sorts.
sqlSELECT DISTINCT emp_name FROM employees WHERE department_id = 10;
Solution: Ensure that DISTINCT is needed, or use indexing to avoid duplicates.
20. Use Bulk Collect for PL/SQL
Scenario: Row-by-row processing in PL/SQL is slow.
sqlDECLARE
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
emp_rec emp_tab;
BEGIN
SELECT * BULK COLLECT INTO emp_rec FROM employees;
END;
/
More Advanced Query Tuning Scenarios
21. Use Pipelined Functions
Scenario: For returning large datasets from PL/SQL functions, use pipelined functions to stream rows back to the client.
sqlCREATE OR REPLACE FUNCTION get_employees RETURN emp_tab PIPELINED AS
BEGIN
FOR r IN (SELECT * FROM employees) LOOP
PIPE ROW(r);
END LOOP;
END;
22. Avoid Redundant Joins
Scenario: Joining the same table multiple times leads to redundant operations.
sqlSELECT e1.emp_name, e2.emp_name
FROM employees e1, employees e2
WHERE e1.department_id = e2.department_id;
Solution: Eliminate redundant joins if possible.
23. Use EXISTS for Conditional Logic
Scenario: Use EXISTS for efficient conditional logic checks.
sqlSELECT emp_name FROM employees e
WHERE EXISTS (SELECT 1 FROM sales s WHERE s.emp_id = e.emp_id);
24. Optimize Large Deletes
Scenario: Deleting a large number of rows in a single transaction can cause rollback issues.
Solution: Use bulk deletes with ROWNUM.
sqlDELETE FROM employees WHERE ROWNUM <= 10000;
COMMIT;
25. Use Global Temporary Tables
Scenario: For temporary data manipulation, use global temporary tables to reduce logging overhead.
sqlCREATE GLOBAL TEMPORARY TABLE temp_employees ON COMMIT PRESERVE ROWS AS
SELECT * FROM employees WHERE department_id = 10;
The remaining scenarios cover topics such as query restructuring for readability and performance, using advanced partitioning strategies, leveraging Oracle’s analytical functions more effectively, optimizing queries on clustered and non-clustered indexes, tuning PL/SQL procedures, and efficiently handling bulk data operations.
By creating realistic test cases and applying these techniques in your Oracle database, you can significantly improve performance.
No comments:
Post a Comment