SQL Query tuning in Oracle database

 

Here are practical SQL query tuning and rewriting examples for Oracle Database. These cover common small improvements that can significantly boost performance.

---

🔸 1. Avoid SELECT *

Bad:

SELECT * FROM employees WHERE department_id = 10;

Better:

SELECT first_name, last_name, salary FROM employees WHERE department_id = 10;

✅ Reduces I/O and memory usage by fetching only needed columns.

---

🔸 2. Use EXISTS Instead of IN (for subqueries)

Bad:

SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

Better:

SELECT * FROM employees e 
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location_id = 1700);

✅ EXISTS stops at first match, IN evaluates all rows.

---

🔸 3. Use JOIN Instead of Subqueries (where appropriate)

Bad:

SELECT employee_id, (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) 
FROM employees e;

Better:

SELECT e.employee_id, d.department_name 
FROM employees e 
JOIN departments d ON e.department_id = d.department_id;

✅ Joins are usually faster and optimizer-friendly.

---

🔸 4. Use BIND Variables

Bad:

SELECT * FROM employees WHERE department_id = 10;

Better (PL/SQL or apps):

SELECT * FROM employees WHERE department_id = :dept_id;

✅ Enables SQL reuse and avoids hard parsing.

---

🔸 5. Create Indexes on Filter Columns

CREATE INDEX emp_dept_idx ON employees(department_id);

✅ Improves performance for queries filtering on department_id.

---

🔸 6. Use ANALYZE or DBMS_STATS to Keep Stats Updated

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

✅ Helps optimizer choose the best plan.

---

🔸 7. Use WHERE Clause to Limit Rows

Bad:

SELECT first_name FROM employees;

SELECT first_name FROM employees WHERE department_id = 10;

✅ Reduces full table scans.

---

🔸 8. Avoid Functions on Indexed Columns

Bad:

SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2020';

Better:

SELECT * FROM employees 
WHERE hire_date BETWEEN TO_DATE('2020-01-01','YYYY-MM-DD') AND TO_DATE('2020-12-31','YYYY-MM-DD');

✅ Allows use of index on hire_date.

---

🔸 9. Use UNION ALL Instead of UNION (if duplicates don’t matter)

SELECT first_name FROM employees 
UNION 
SELECT first_name FROM contractors;

Better:

SELECT first_name FROM employees 
UNION ALL 
SELECT first_name FROM contractors;

✅ UNION sorts and removes duplicates, UNION ALL is faster.

---

🔸 10. Use WITH Clause for Reused Subqueries

WITH dept_data AS (
  SELECT department_id FROM departments WHERE location_id = 1700
)
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM dept_data);

✅ Improves readability and can boost performance for complex queries.

---

---

🔹 11. Avoid Starting with Wildcard in LIKE

Bad (causes full table scan):

SELECT * FROM employees WHERE first_name LIKE '%john';

Better:

SELECT * FROM employees WHERE first_name LIKE 'john%';

✅ Index can be used if the pattern doesn’t start with %.

---

🔹 12. Use IN for Static Filters Instead of Multiple ORs

Bad:

SELECT * FROM employees WHERE department_id = 10 OR department_id = 20 OR department_id = 30;

Better:

SELECT * FROM employees WHERE department_id IN (10, 20, 30);

✅ Simplifies plan and improves performance.

---

🔹 13. Avoid Sorting Unless Needed (Reduce ORDER BY)

Bad:

SELECT * FROM employees ORDER BY hire_date;

Better (only if sorting is not required):

SELECT * FROM employees;

✅ ORDER BY requires sorting and extra CPU/Temp space.

---

🔹 14. Avoid Full Table Scans by Using Indexes

Bad (no filter):

SELECT * FROM orders;

Better:

SELECT * FROM orders WHERE order_status = 'OPEN';

✅ Use filters to take advantage of indexes and reduce I/O.

---

🔹 15. Use GROUP BY with Selective Filtering

Bad:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

Better:

SELECT department_id, COUNT(*) 
FROM employees 
WHERE status = 'ACTIVE' 
GROUP BY department_id;

✅ Filtering before grouping reduces rows and resource usage.

---

🔹 16. Avoid Functions on Indexed Columns (Again)

Bad:

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

Better:

• Create a function-based index:

CREATE INDEX emp_lastname_upper_idx ON employees(UPPER(last_name));

Then:

SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

✅ Allows index usage on derived column.

---

🔹 17. Use Inline Views to Pre-Filter

SELECT dept_name, emp_count
FROM (
  SELECT d.department_name AS dept_name, COUNT(e.employee_id) AS emp_count
  FROM employees e 
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.status = 'ACTIVE'
  GROUP BY d.department_name
) WHERE emp_count > 5;

✅ Reduces processing in outer query.

---

🔹18. Use Optimizer Hints Where Needed

SELECT /*+ INDEX(employees emp_status_idx) */ * 
FROM employees WHERE status = 'ACTIVE';

✅ Forces use of desired index when optimizer chooses wrong path.

---

🔹 19. Avoid NOT IN with NULLs

Bad:

SELECT * FROM employees WHERE department_id NOT IN (10, 20, NULL);

Better:

SELECT * FROM employees WHERE department_id NOT IN (10, 20);

✅ NULL in NOT IN returns no rows due to unknown comparison.

---

🔹 20. Use Analytical Functions Instead of Correlated Subqueries

Bad:

SELECT e.*, 
       (SELECT MAX(salary) FROM employees WHERE department_id = e.department_id) AS max_sal
FROM employees e;

Better:

SELECT e.*, MAX(salary) OVER (PARTITION BY department_id) AS max_sal
FROM employees e;

✅ Reduces repetitive subquery execution using analytic window functions.

---

🔹 21. Use MERGE Instead of Separate INSERT and UPDATE

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name);

✅ Efficient for upserts.

---

🔹 22. Use FETCH FIRST Instead of ROWNUM (12c+)

Old way:

SELECT * FROM employees WHERE department_id = 10 AND ROWNUM <= 5;

New way:

SELECT * FROM employees WHERE department_id = 10 FETCH FIRST 5 ROWS ONLY;

✅ More readable and optimizer-aware.

---

🔹 23. Avoid DISTINCT Unless Needed

Bad:

SELECT DISTINCT department_id FROM employees;

Better:

SELECT department_id FROM employees GROUP BY department_id;

✅ GROUP BY can be more efficient in certain queries.

---

🔸 24. Avoid SELECT *, Specify Columns

Bad:

SELECT * FROM employees WHERE department_id = 10;

Better:

SELECT first_name, last_name FROM employees WHERE department_id = 10;

✅ Reduces I/O by fetching only needed columns.

---

🔸 25. Join on Indexed Columns

Bad (no index):

SELECT * FROM orders o JOIN customers c ON o.customer_name = c.customer_name;

Better:

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

✅ Use join keys with indexes for faster lookups.

---

🔸 26. Push Filters Inside Views

Bad:

SELECT * FROM (SELECT * FROM employees) WHERE department_id = 10;

Better:

SELECT * FROM (SELECT * FROM employees WHERE department_id = 10);

✅ Push predicates down into views or inline subqueries.

---

🔸 27. Use EXISTS Instead of IN for Subqueries

Bad:

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);

Better:

SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

✅ EXISTS stops scanning once a match is found, unlike IN.

---

🔸 28. Avoid Scalar Subqueries in SELECT

Bad:

SELECT e.name, 
       (SELECT COUNT(*) FROM tasks t WHERE t.employee_id = e.id) AS task_count
FROM employees e;

Better:

SELECT e.name, COUNT(t.task_id) AS task_count
FROM employees e LEFT JOIN tasks t ON e.id = t.employee_id
GROUP BY e.name;

✅ Avoids repeated subquery execution for each row.

---

🔸 29. Use WITH Clause for Repeated Subqueries

Without WITH:

SELECT COUNT(*) FROM (SELECT * FROM orders WHERE status = 'OPEN');
SELECT MAX(order_date) FROM (SELECT * FROM orders WHERE status = 'OPEN');

Better (WITH clause):

WITH open_orders AS (
  SELECT * FROM orders WHERE status = 'OPEN'
)
SELECT COUNT(*) FROM open_orders;
SELECT MAX(order_date) FROM open_orders;

✅ Prevents re-evaluation of subquery.

---

🔸 30. Use Bitmap Indexes for Low Cardinality Columns

If status has few values:

CREATE BITMAP INDEX emp_status_bix ON employees(status);

✅ Bitmap indexes work well for values like 'ACTIVE', 'INACTIVE'.

---

🔸 31. Avoid Implicit Data Type Conversion

Bad:

SELECT * FROM employees WHERE employee_id = '100'; -- employee_id is NUMBER

Better:

SELECT * FROM employees WHERE employee_id = 100;

✅ Avoids implicit conversion and allows index usage.

---

🔸 32. Use Partition Pruning

If sales is partitioned by month:

SELECT * FROM sales WHERE sale_month = 'JAN-2024';

✅ Ensures Oracle scans only that partition.

---

🔸 33. Avoid Nested Loops on Large Datasets

Check execution plan. If both tables are large, prefer hash join:

SELECT /*+ USE_HASH(e d) */ * 
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

✅ Hash joins scale better for large sets.

---

🔸 34. Use DECODE or CASE for Conditional Aggregation

SELECT department_id,
       COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
       COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count
FROM employees
GROUP BY department_id;

✅ Efficiently replaces multiple queries with a single grouped result.

---

🔸 35. Use TRUNC() Smartly for Date Comparisons

Bad:

SELECT * FROM sales WHERE TRUNC(sale_date) = TO_DATE('2024-01-01', 'YYYY-MM-DD');

Better:

SELECT * FROM sales WHERE sale_date BETWEEN TO_DATE('2024-01-01','YYYY-MM-DD') 
                                      AND TO_DATE('2024-01-01 23:59:59','YYYY-MM-DD HH24:MI:SS');

✅ Avoids function on column and allows index use.

Understanding CSSD Heartbeat Mechanisms in Oracle RAC

 

Understanding CSSD Heartbeat Mechanisms in Oracle RAC

The Cluster Services Synchronization Daemon (CSSD) is a critical process in Oracle RAC that continuously monitors the health of cluster nodes using two independent heartbeat mechanisms:

1. Network Heartbeat

2. Disk Heartbeat

---

🔹 Network Heartbeat

• Sent every 1 second over the interconnect (private network) using TCP.

• A sending thread of CSSD sends the heartbeat to all other nodes and itself.

• A receiving thread on each node listens for heartbeats from others.

✅ TCP handles error correction, but Oracle does not rely on TCP retransmissions for heartbeat monitoring. Heartbeat loss is interpreted at the Oracle level.

Heartbeat Loss Monitoring (Misscount Logic):

• If a node does not receive a heartbeat from another node:

• At 15 seconds (50% of misscount) → WARNING logged.

• At 22 seconds (75%) → Another WARNING logged.

• At 27 seconds (90%) → Additional warning.

• At 30 seconds (100%) [default misscount] → Node is evicted from the cluster.

---

🔹 Disk Heartbeat

• Occurs between each node and the voting disk.

• CSSD maintains a 1 OS block-sized heartbeat in a specific offset on the voting disk using pread / pwrite syscalls.

• CSSD:

  • Writes its own heartbeat (with a counter and node name in the block header).

  • Reads/Monitors the heartbeat blocks of all other nodes.

⚠️ If a node fails to write its heartbeat within the disk I/O timeout period, it is considered dead.
If its status is unknown and it's not part of the "survivor" node group, the node is evicted (via a "kill block" update in the voting disk).

 

---

🔸 Summary of Heartbeat Requirements

Heartbeat Type	Frequency	Timeout Condition	Consequence
Network	1 second	css_misscount (default: 30s)	Node eviction
Disk	1 second	disktimeout	Node eviction

---

🔸 Failure Matrix for Heartbeat Scenarios

Network Ping	Disk Ping	Reboot?
Completes within misscount seconds	Completes within disktimeout	No
Completes within misscount seconds	Takes more than misscount but < disktimeout	No
Completes within misscount seconds	Takes more than disktimeout	Yes
Takes more than misscount seconds	Completes within disktimeout	Yes

---

🔧 Understanding Voting Disk and Its Role in Oracle RAC Clusterware

The Voting Disk is a vital component in Oracle RAC that helps determine node membership, resolve split-brain conditions, and enforce I/O fencing. It plays a key role alongside the CSSD process, which uses both network and disk heartbeats for node health monitoring.

---

🧠 What is Stored in the Voting Disk?

• Information about cluster node membership.

• Disk-based heartbeat blocks for each node.

• Kill blocks to mark evicted nodes.

Voting Disks are written using pwrite() and read using pread() system calls by the CSSD process.

Each node writes to a specific offset (its own heartbeat block) and reads others’ blocks to check their liveness.

Although the OCR and OLR also store node information, Voting Disk heartbeat plays a runtime role in eviction decisions. There’s no persistent user or application data, so if a Voting Disk is lost, it can be re-added without data loss—but only after stopping CRS.

---

🔁 Why Voting Disks Are Crucial

While it’s technically true that data in voting disks can be recreated, they’re instrumental in avoiding split-brain and enforcing evictions when:

• Heartbeat failures occur.

• Nodes lose contact with others.

• Shared storage needs to be protected (I/O fencing).

• 
  

---

💥 Split Brain Syndrome

A split-brain situation arises when cluster nodes lose communication via the private interconnect but continue running independently. Each node assumes others are down and may attempt to access and modify shared data blocks.

❌ Risk:

This leads to data integrity violations, such as concurrent conflicting updates to the same data block.

---

🧱 I/O Fencing

After a node failure or eviction, it’s possible that leftover I/O from that node reaches storage out of order, corrupting data. To prevent this:

• Oracle performs I/O fencing by removing failed nodes' access to shared storage.

• This ensures only surviving nodes can read/write to the disk.

---

⚖️ Simple Majority Rule

Oracle Clusterware requires a simple majority of voting disks to be accessible at all times:

"More than half" of the voting disks must be online for the cluster to operate.

📌 Formula:

To tolerate loss of N disks → Configure at least 2N+1 voting disks.

---

🔍 Examples – Voting Disk in Action

✅ Example 1: Network Heartbeat Failure in 3-node Cluster

• Setup: 3 nodes (Node 1, Node 2, Node 3) and 3 Voting Disks.

• Issue: Node 3 loses network heartbeat with Node 1 and Node 2, but disk heartbeat still working.

• Action: Node 1 and 2 can still see each other and determine via the Voting Disk that Node 3 is isolated.

• They mark Node 3’s kill block in Voting Disk.

• During next pread(), Node 3 sees the self-kill flag and evicts itself.

• I/O fencing ensures safe disk access. OHASD then gracefully shuts down and restarts the stack on Node 3.

---

✅ Example 2: Disk Heartbeat Split in 2-node Cluster

• Setup: 2 nodes and 3 Voting Disks.

• Issue: Node 1 sees 2 voting disks; Node 2 sees only 1.

• Based on Simple Majority Rule:

  • Node 1 (majority access) is the survivor.

  • CSSD of Node 1 marks Node 2’s kill block.

• Node 2 reads the kill flag and evicts itself.

• I/O fencing is applied, and OHASD restarts the stack on Node 2.

🧠 Without an odd number of disks, both nodes could think they're healthy, leading to potential split-brain.

 

---

📌 Summary

Component	Purpose
Voting Disk	Maintains disk heartbeats, kill blocks, and node membership info.
Network Heartbeat	Checks interconnect communication every second via TCP.
Disk Heartbeat	Checks I/O access health via shared storage every second.
Split-Brain	Scenario where isolated nodes continue operating independently.
I/O Fencing	Prevents failed nodes from sending stale writes to shared storage.
Simple Majority	Ensures more than half of voting disks are accessible to avoid eviction.

---