Mastering Oracle SQL Optimization: Practical Techniques and Real-World Cases
This article compiles a DBAplus online session that presents a systematic SQL‑optimization methodology, rewrite tricks, common pitfalls, and a series of concrete Oracle examples—including CASE WHEN, ROWNUM pagination, hint usage, column selection, function‑call reduction, trigger avoidance, and ROWID access—plus a detailed Q&A that clarifies practical tuning questions.
1. Reduce Access Paths
SQL optimization fundamentally aims to shorten the execution path, for example by adding indexes to turn full‑table scans into index‑range scans or by partitioning tables to limit scans to relevant partitions. When such structural changes are insufficient, equivalent rewrites such as CASE WHEN transformations or ROWNUM pagination are employed.
Case 1: CASE WHEN Rewrite
The original script accessed table T2 six times, resulting in 300,904 logical reads.
The execution plan confirmed the multiple accesses.
After consolidating the logic with a CASE WHEN rewrite, the script accessed T2 only once and reduced logical reads to 2,040.
Case 2: ROWNUM Pagination Rewrite
The naïve pagination script scanned the entire table, consuming 1,723 buffers.
The optimized version limited the scan to 10 rows, using only 5 buffers.
Case 3: Hint Direct‑Path Rewrite
By applying a direct‑path hint, the insert into table T2 bypassed the SGA and achieved dramatically higher throughput.
Case 4: Selecting Only Required Columns
When a view v_t1_join_t2 is queried with SELECT *, both tables T1 and T2 are accessed. By selecting only object_id and object_name, which reside in T2, the optimizer accesses only T2, cutting unnecessary I/O.
When the needed columns are covered by an index, the query can avoid a table‑row fetch entirely.
Case 5: Reducing Function Calls
Frequent function calls can dominate execution time. By moving function evaluation after aggregation or by using function‑based indexes, the number of recursive calls drops dramatically.
Two equivalent queries—one calling the function before aggregation, the other after—show a stark performance gap.
Creating a function‑based index eliminates the recursive calls entirely.
Case 6: Avoiding Triggers
When a table has an INSERT trigger that performs a statistical query per row, the insert time balloons (e.g., 46 seconds for 1 000 rows). Disabling the trigger reduces the time to just over 1 second.
Case 7: ROWID Access
ROWID is a pseudo‑column that uniquely identifies a row. Accessing rows via ROWID yields the lowest consistent‑gets count (1) compared with full‑table scans (1 724) and index scans (4).
When a row is located via an index, appending ROWID = :rid to the UPDATE statement can further speed up the modification.
2. Avoid External Influences
External factors such as unstable execution plans, insufficient resources, or mis‑written hints can degrade performance. Ensuring that hints are applied correctly, avoiding Cartesian products, and keeping statistics up‑to‑date help maintain stable plans.
Hint Rewrite Example
Proper hint placement, fixed outline, and refreshed statistics can force the optimizer to choose the intended plan.
Eliminating Cartesian Products
Incorrect sub‑query ordering caused a Cartesian join between two tache tables, inflating cost. Re‑ordering the joins so that inner tables are linked before the outer reference resolves the issue.
3. Resource Constraints
Performance problems often stem from three resource‑related scenarios: (1) low‑end hardware (CPU, memory, I/O), (2) external applications exhausting host resources, and (3) excessive parallelism causing contention.
Q&A
Q: Oracle row‑to‑column conversion – CASE WHEN vs. PIVOT? A: Both rely on the execution plan; PIVOT often benefits from internal optimizations.
Q: How to optimise DBlink statements? A: Network latency is the primary factor.
Q: Is using ROWID always faster? A: When the ROWID is available, it can locate a row directly, avoiding index look‑ups.
Q: Why do some queries produce two execution plans? A: Different predicate selectivities (e.g., ID=1 vs. ID=8) can lead to index vs. full‑table scans.
Q: When should hints be fixed versus left dynamic? A: Fixed plans are risky because statistics change; they should be used sparingly.
Q: How to handle heavy‑weight SQL that runs for hours? A: Usually address it at the application level rather than enabling 10053 tracing.
Q: Does IN get transformed to OR internally? A: Oracle rewrites them to the same plan; developers need not worry.
Q: Risks of ROWID‑based UPDATE when concurrent sessions modify the same row? A: Use SELECT … FOR UPDATE to lock the row if consistency is required.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
dbaplus Community
Enterprise-level professional community for Database, BigData, and AIOps. Daily original articles, weekly online tech talks, monthly offline salons, and quarterly XCOPS&DAMS conferences—delivered by industry experts.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.