Oracle SQL tuning begins with a practical problem: a database request does not meet the application's performance requirement. A search might take too long to display useful results, an overnight report might miss its processing window, or many small queries might consume more resources than expected. This course introduces a disciplined way to investigate those problems in Oracle AI Database 26ai.
The goal is to connect correct SQL results with measurable execution behavior. You will learn to ask what work a statement performs, why the optimizer selected its operations, and whether a proposed change improves the workload that matters. An index, hint, or shorter expression is a candidate to evaluate, not an automatic solution.
Objective: Understand the course's purpose, learning outcomes, preparation, and relationship to current Oracle training. Begin with a repeatable approach to SQL performance rather than a collection of fixes to apply indiscriminately.
This course is intended for developers and database practitioners who can write basic SQL and want to understand its performance. You should be comfortable reading SELECT statements, joins, predicates, and expressions. Familiarity with tables, indexes, transactions, and database connections will help you interpret the examples. The next lesson describes the course prerequisites in more detail.
You do not need to begin by memorizing every optimizer parameter or diagnostic view. You do need to distinguish a question from a proposed answer. Asking why a customer search reads so much data starts an investigation. Asking which hint to add assumes a remedy before the cause is understood.
Developers contribute knowledge of application semantics and call patterns. Database administrators contribute knowledge of object definitions, statistics, resources, and operational constraints. Effective tuning combines those perspectives. A database plan cannot explain an application requirement that has never been stated, and an application timing alone may not identify the database work responsible.
A statement is not simply fast or slow without context. Record what the request must return, how often it runs, which inputs are representative, and what completion means. An interactive page might need the first useful rows quickly. An export might need every row efficiently. Those are different tests even when they use similar SQL.
| Measure | Question | Example |
|---|---|---|
| Response time | How long does the user wait for the required result? | Displaying the first page of order history. |
| Throughput | How much useful work finishes in an interval? | Completing the required batch of reports. |
| Resource use | What work does the request impose on the system? | CPU, logical reads, physical reads, memory, and temporary space. |
Improvement in one measure does not guarantee improvement in every other measure. A report can finish sooner while consuming resources needed by concurrent requests. A statement that looks inexpensive in isolation can account for substantial total work if the application executes it repeatedly. Include frequency and concurrency in the investigation.
Use an explicit target when one is available, and record the baseline before testing a candidate. Keep input values, data volume, client behavior, and the amount of fetching comparable. Without that context, two elapsed times can describe different work rather than a performance improvement.
These are skills to develop through practice. Completing the lessons does not guarantee that every SQL problem will have an obvious solution or that one plan will remain best indefinitely. Data distributions, application behavior, object definitions, and database environments change. A useful tuning method remains valuable when an earlier workaround no longer does.
SQL is declarative: you express the result you need, while the optimizer considers ways to produce it. The same request may be implemented with different access paths, join orders, join methods, or transformations. Oracle's cost-based optimizer uses available information to estimate candidate work and choose a plan.
A full table scan can be appropriate, especially when the required work involves much of the data. An index can be useful for a selective request, but may require additional table visits for columns it does not contain. Do not classify a plan as good merely because an index appears or bad merely because a scan appears.
Estimated cost is not a stopwatch reading. It helps explain choices within the optimizer's model, while measured execution shows what happened under particular conditions. Statistics can be incomplete or unrepresentative, and application inputs can vary. Use plans to form hypotheses and runtime evidence to evaluate them.
The current teaching focus is cost-based optimization. Rule-based optimization belongs to historical discussions of older Oracle systems, not a supported Oracle 26ai configuration to enable as a routine tuning technique. You will examine modern optimization goals without treating them as a choice between two currently supported optimizer engines.
EXPLAIN PLAN records a proposed plan without executing the target statement. DBMS_XPLAN.DISPLAY formats those plan-table records. This is useful for exploration and learning, but it does not measure the target query or prove which plan an application execution used.
DBMS_XPLAN.DISPLAY_CURSOR inspects a cached cursor and can display available collected execution statistics. Identifying the intended SQL ID and child cursor matters. So does collecting the relevant statistics during execution: requesting a display format later cannot reconstruct measurements that were never gathered.
SQL Trace and TKPROF provide another view of statement activity. Trace collection records processing, while TKPROF formats an existing trace file. This can help distinguish repeated calls and work during parse, execute, and fetch. Client elapsed timing adds context but is not, by itself, a server-only resource measurement.
Other facilities, including AWR, advisors, and SQL Monitoring, can support appropriate investigations. Their availability and entitlement depend on the database offering and licensing. Start with a question that justifies collection, use the necessary access, and select a suitably narrow diagnostic scope. Advanced tools are not substitutes for knowing which request is being investigated.
Capture the statement, relevant input values, expected result, and application behavior. Determine whether the complaint concerns initial response, full completion, repeated execution, or another measurable outcome. Include the environment and observation period so that later comparisons refer to the same problem.
Check correctness and record representative performance. Preserve the relevant plan and available statistics. Note whether all rows were fetched and whether concurrent work was present. A baseline is a description of the original behavior, not simply one number copied from a worksheet.
Use the evidence to explain what might be wasteful. A query may retrieve rows the application discards, make repeated calls for related data, or process a very different number of rows from the estimate. Choose a hypothesis that can be tested instead of changing unrelated settings until a run happens to improve.
Evaluate the change in a suitable practice or test environment. Keep result requirements constant and make the comparison understandable. When considering an index, include storage and write-maintenance costs. When considering a statistics change, recognize that other statements may also receive different plans.
Compare results as well as timing and resource work. Repeat representative tests, look for regressions, and document the decision. Keep a way to reverse the change. After adoption, monitor the outcome rather than assuming successful testing guarantees identical behavior in every future workload.
Imagine that a bookstore's order-history page becomes slower as customer data grows. This is a hypothetical learning scenario, not a reported benchmark. Before adding an index, establish what the page needs: recent orders, a complete historical export, or a count followed by several detail requests.
Observe the SQL calls for one representative request. If the application retrieves every past order and discards most rows before displaying a page, a more focused request may be worth investigating. If it makes a separate query for each displayed order, call frequency may matter more than the duration of any individual statement.
Next, inspect the relevant execution evidence. Compare customers with small and large histories instead of testing only one convenient account. Confirm ordering and duplicate behavior. A change that accelerates a narrow lookup may be unsuitable for a customer with a much larger result or for a complete export.
Suppose an index becomes a plausible candidate. Test whether it reduces the work of the actual request, including any required table access. Evaluate its effect on inserts and updates as well. The outcome could be to keep the index, choose a different query shape, or retain the original design because the measured benefit is insufficient.
The value of the exercise is the reasoning. You should be able to explain what was tested and why the conclusion follows. Saying that a new query uses an index describes a plan change; it does not establish that the customer's experience improved.
SQL expressions are part of the application's business rules. A built-in function can make a requirement clear, but changing where it is applied can change results or access opportunities. Review datatype conversions, date formats, character comparisons, and NULL behavior before replacing an expression with one that looks more portable or more concise.
Bind variables allow applications to supply changing data values while keeping SQL text consistent. Prepared-statement reuse can reduce unnecessary parsing when the interface and cursor validity permit it. Neither technique guarantees one execution plan or solves all expensive database work. The processing lessons distinguish reducing preparation from improving execution.
Similarly, an optimizer goal does not limit the rows a statement returns. A first-rows goal prioritizes early delivery; a complete-result goal considers a different objective. Preserve the application's actual result requirements when choosing a test and deciding which outcome matters.
Hints offer a way to investigate selected optimizer decisions. Their syntax, target, and applicability must be checked, and the result still needs measurement. Avoid adopting a hint simply because it appears in an example. The course introduces controlled comparisons so that you can distinguish an honored instruction from a useful improvement.
Oracle AI Database 26ai includes capabilities that automate parts of database management and analysis. That does not justify a blanket claim that the database autonomously fixes every query or removes the need for performance investigation. Evaluate a named feature in the context of its documented purpose and the deployment where it is used.
AI Vector Search addresses vector-search workloads; it is not a general explanation for why an ordinary relational query should become faster. Likewise, SQL plan management helps control plan evolution rather than guaranteeing the fastest possible plan for every execution condition. This course centers on evidence that you can explain and review.
An advisor recommendation is a candidate action. Ask whether its assumptions match the application, whether required features are available, and what broader effects the change could have. Automation is most useful when the problem and acceptance criteria are clear enough to assess its output.
Use Module 1 to establish preparation and resources. Then begin Module 2's SQL tuning concepts, which connect tools, processing, plans, optimizer goals, statistics, and hints. Work through examples with their explanations instead of copying an isolated command as a production recommendation.
Diagrams help explain processing relationships, while SQL examples make a particular question concrete. Their object names and privileges are prerequisites to check. An optional sample schema is not installed in every database, and a differently configured environment may produce a different plan without anything being wrong.
Keep a small experiment record. Include the question, relevant SQL, required results, environment, baseline, candidate, observations, and conclusion. Record an unsuccessful test too: evidence that a candidate does not help prevents repeated guesses and suggests what to investigate next.
Practice explaining the result to another person. Can you identify the exact statement and what its plan proposes? Can you distinguish estimated rows from observed rows? Can you describe the amount of fetching included in the timing? These questions are more useful than memorizing a preferred operation name.
This is independent educational material for developing practical SQL tuning skills. Completing it does not award an Oracle credential, establish completion of Oracle-authorized training, or guarantee exam readiness. The historical description of this course as part of an Oracle Performance Tuning Certification Series is not the basis of the current course.
As checked on November 12, 2026, Oracle's Administration Professional learning path includes Performance Management and Tuning alongside broader administration subjects. Oracle Japan's 26ai administration exam checklist includes SQL processing, optimization, and performance management. These establish topic overlap, not a promise that this course covers an entire exam.
If certification is your goal, consult Oracle University's current objectives and eligibility rules for the exam and region you intend to use. Exam details can change. Treat practical study here as complementary learning rather than assuming that a legacy certification title identifies a current qualification or that regional requirements apply worldwide.
Before beginning hands-on work, choose a suitable learning database, establish permitted access, and understand which operations inspect information and which change state. A plan display, index creation, statistics collection, and instance setting do not have the same operational effect. The examples should support a deliberate investigation with a clear scope.
Your starting goal is to become a more precise investigator: define the requirement, preserve meaning, select relevant evidence, test a focused change, and evaluate its effect. Continue to Oracle SQL tuning prerequisites to review the background and preparation needed for that work.