Oracle SQL tuning connects application requirements with evidence about database work. In this module, you examined SQL expressions, diagnostic tools, statement execution, plans, optimizer goals, statistics, and hints. These topics form one investigation process: establish what the application needs, identify the relevant execution, explain its work, and test a focused change without changing the required result.
Objective: Bring together the Oracle AI Database 26ai tuning concepts from Lessons 1 through 8 and apply them to a repeatable, measurable investigation.
A successful conclusion is more specific than “the plan looks better.” You should be able to state what was slow, which evidence identified the problem, what changed, and whether representative requests improved. A different access path, lower estimated cost, or newer statistics timestamp is an observation to interpret, not a complete performance verdict.
The order matters. Starting with an index or hint before establishing the request and its baseline can solve the wrong problem. Conversely, collecting every available diagnostic report can create unnecessary work when a focused cursor inspection would answer the question. Move between these lessons according to the evidence you still need.
A user waiting for the first page of an order history and a nightly export consuming every order have different requirements. Both involve query execution, but they need different measurements. Record the required columns, filtering, ordering, input values, and amount of fetching before comparing alternatives.
Response time describes waiting for a result; throughput concerns completed work over an interval. Resource consumption helps explain the cost to the wider system. A statement executed thousands of times can deserve attention even when each execution seems brief. A faster individual report can also be a poor trade if it consumes resources needed by other requests.
Preserve correctness throughout the investigation. NULL handling, datatype conversions, duplicate rows, and ordering can change when SQL is rewritten. A query that returns fewer required records is not a successful optimization. Test exceptional values and realistic data distributions instead of validating only one convenient example.
The extensions lesson demonstrated why syntax alone is insufficient. Standard constructs and Oracle-specific functions can express related ideas while differing in details. Replacing DECODE with CASE, changing date conversions, or moving a function within a predicate requires checking meaning as well as performance. An expression index is useful only when it supports the application semantics you actually need.
Parsing prepares SQL for execution and includes checks and reuse decisions. A soft parse can reuse compatible executable code; a hard parse includes optimization and row source generation. An application that retains a valid prepared cursor can sometimes reexecute it without requesting another parse. These cases are related but not interchangeable.
Bind variables keep data values separate from statement text and help applications submit consistent SQL. A placeholder such as :p_last_name is not quoted as a string. Appropriate binding and prepared-statement reuse can reduce unnecessary preparation, but they do not automatically repair an expensive execution strategy.
One statement can have multiple child cursors, and a matching SQL identifier does not mean every execution has identical characteristics. Record the relevant child cursor when reviewing a plan. Also distinguish a cursor from the rows returned through it: the cursor holds processing state rather than simply naming a result collection.
For queries, fetching can trigger additional database work. The completion of an execute call does not necessarily mean the entire result has been produced. Include the application's actual fetch behavior in your measurements. Closing a cursor is also separate from committing a transaction; do not confuse statement processing with transaction completion.
When a request is slow, ask whether the evidence points to repeated preparation, excessive execution work, many small calls, or fetching and client behavior. An index investigation is unlikely to resolve a delay caused mainly by unnecessary network round trips. Trace evidence and application context can help distinguish these cases.
| Question | Starting point | Important limit |
|---|---|---|
| What operations are proposed? | EXPLAIN PLAN and DBMS_XPLAN.DISPLAY | The target statement has not been executed. |
| Which plan belongs to the application cursor? | DBMS_XPLAN.DISPLAY_CURSOR | Identify the intended SQL ID and child cursor. |
| What happened during execution? | Collected row-source statistics or scoped SQL trace | Collection must cover the relevant execution. |
| Where do repeated calls spend work? | SQL Trace and TKPROF | TKPROF formats a trace; it does not enable tracing. |
| Does the request meet its target? | Representative application measurements | Keep inputs, fetching, and workload conditions comparable. |
EXPLAIN PLAN is useful for learning and exploring a proposed change. It does not supply measured row counts or elapsed time for the target query. The explanation environment can also differ from the one that produced the application's cursor. A graphical Explain Plan button does not remove this distinction.
Where runtime statistics were collected, DISPLAY_CURSOR can expose observed work. ALLSTATS LAST selects available statistics for the last execution of the selected cursor; it does not collect missing information retrospectively. Check the displayed statement before accepting the output, especially when a client may have issued intervening SQL.
Use appropriate privileges and scoped diagnostics. Advanced tools such as AWR, ADDM, advisors, and SQL Monitoring have deployment-specific availability and entitlement requirements. A tool's presence in a menu does not establish permission to use every underlying feature. The tool-set lesson provides the detailed distinctions.
A plan is a tree of row sources, not a list of independently timed instructions. Parent operations consume child results; some inputs are revisited, while others must process substantial data before producing output. Operation identifiers associate details with nodes. They are not a simple chronological execution counter.
The following review example assumes the optional HR sample schema exists and that the learner can query its tables. It is illustrative SQL, not a benchmark or a captured result:
SELECT e.employee_id, e.last_name, e.salary
FROM hr.employees e
JOIN hr.departments d
ON d.department_id = e.department_id
WHERE d.department_name = 'Accounting';
The equality connecting department identifiers is a join condition. The department-name condition restricts the requested department rows. The plan shows how Oracle implements those requirements, including which predicates are associated with access and which are evaluated as filters at particular operations.
An access predicate helps identify qualifying data through an access path or match row sources. A filter predicate tests rows at an operation. The label alone does not tell you whether the plan is good. Ask how many rows reach that point and how much work precedes or follows it. A filter is not automatically a defect requiring an index.
Read the complete output, including predicate details and notes. If an index supplies identifiers but not all requested columns, table visits may still contribute substantial work. If the result must be ordered, inspect how that requirement is met. An index's existence does not guarantee the final output order; express required ordering in SQL.
The cost-based optimizer evaluates candidate ways to obtain a result. Transformation, estimation, and plan generation work together. The selected plan depends on available information and the optimization goal; it is not a promise that every possible plan was tested by executing it.
Keep three ideas separate. Selectivity is an estimated fraction matching a condition. Cardinality is an estimated number of rows produced by an operation. Cost is a comparative estimate of resource work. None is a substitute for measuring the user's request. In particular, displayed cost is not elapsed seconds.
Optimizer statistics describe data and access structures. Execution statistics describe observed processing. Table row-count statistics need not equal the current live count, and recent statistics do not guarantee accurate estimates for every correlated predicate or expression. Investigate the specific mismatch rather than repeatedly gathering statistics without a hypothesis.
Compare estimated and actual rows with attention to Starts. An operation called many times can accumulate actual rows across those calls. A raw difference between two displayed numbers is not automatically proof of an estimation error. Understand the repeated work and the row source's position in the tree.
Neither a full table scan nor an index access is universally preferable. The selected columns, matching rows, table organization, and subsequent work affect the choice. This module deliberately replaces fixed access-path rankings and percentage cutoffs with an investigation of the actual statement and data.
Oracle 26ai tuning uses cost-based optimization. Historical rule-based optimization explains older articles, but it is not a supported alternative to enable when a query is slow or statistics are missing. The current module's optimizer-modes lesson makes that historical distinction explicit.
ALL_ROWS targets efficient completion of the whole statement. Numbered FIRST_ROWS_n goals prioritize delivery of initial rows. Choosing between these goals requires knowing how the application consumes its result. A first-page response improvement can coexist with more work to complete a full export.
A goal is not a row limit. For example, FIRST_ROWS_10 does not restrict a result to ten rows. Keep the optimizer parameter spelling separate from the hint form FIRST_ROWS(10). Neither provides evidence that a particular execution improved.
When experimenting with a session setting, record its original value and restore it when finished. Keep the same SQL, input values, and fetch requirement across the comparison. If the plan does not change, investigate the evidence instead of assuming that the setting was ignored or that every goal must produce a different tree.
Lesson 8 compared an optimizer-chosen access path with FULL and INDEX alternatives. The purpose was to isolate one decision and observe its consequences. A valid hint can influence plan selection, but an inapplicable or unresolved instruction may not take effect. Successful SQL execution does not prove hint use.
Inspect the hint report and the resulting operations. Check alias matching and query-block context before adding more instructions. A plan could already contain the desired operation without your hint; conversely, a changed plan could do more work. Keep applicability and performance as two separate questions.
Advanced names also require precise meanings. INDEX_JOIN combines indexes for one table rather than choosing how two tables join. INDEX_FFS does not guarantee ordered results. Prefer a small, explainable experiment to copying a collection of hints from an unrelated statement.
A retained hint needs a maintenance reason: what problem it addresses, where it helped, and when it should be reviewed. Changed data, indexes, or database versions can alter the tradeoff. A hint is one possible outcome of an investigation, not the required destination of every tuning exercise.
Suppose an order-history page becomes slow as customer records grow. Begin by defining what the page needs: perhaps the latest orders in a specified order, with complete histories handled separately. Capture the SQL calls and inputs for representative customers rather than assuming the request consists of one expensive query.
Next, establish a baseline. Record result correctness, execution counts, response time, fetching, and relevant database work. Inspect the intended cursor and collected statistics. If many small requests dominate, reducing unnecessary calls may be a more promising hypothesis than forcing an index in one statement.
If the evidence instead shows unexpected row processing, inspect predicates, estimates, and access structures. Test a focused candidate in a suitable environment. Compare customers with small and large histories, and account for any index storage or write-maintenance cost. Keep the required filtering and ordering unchanged.
Finally, decide from repeated evidence. Record an improvement only when it meets the target without unacceptable regressions. If the candidate fails, preserve that result too: it helps explain why the next investigation should take a different direction. This scenario illustrates a process and claims no measured speedup.
You should now be able to explain why a lower estimated cost is not a benchmark, why an index is not always preferable, and why first-row response differs from complete-result efficiency. You should also recognize when a result depends on unavailable statistics, a different cursor, or an incomplete fetch.
Before recommending a change, prepare a short record containing the statement and context, required results, baseline, hypothesis, candidate, comparison, and rollback approach. This turns “it seems faster” into a decision another person can review. Continue monitoring after adoption because a successful test describes the tested conditions, not every future workload.
Use the SQL Tuning Concepts - Quiz to review the module. Revisit the linked lessons when a distinction between estimates, measurements, goals, and access choices remains unclear.
The next module begins with Oracle SQL extensions. Carry forward the same discipline: preserve meaning, inspect the relevant work, and validate a change against a measurable requirement.