SQL tuning begins with a question: which database operation is failing to meet the application's performance target, and what evidence explains the delay? In Oracle AI Database 26ai, answering that question involves SQL execution plans, optimizer statistics, and measurements from a representative workload. A shorter statement or a different index is useful only when it preserves correct results and improves the behavior that matters to users.
Objective: Distinguish response time, throughput, and resource use; explain the cost-based optimizer's role; and outline a repeatable process for investigating a slow SQL statement.
This module introduces the concepts used throughout the SQL tuning course. You will examine Oracle SQL features, statement execution, diagnostic tools, execution plans, optimizer goals, and hints. The older lessons on rule-based optimization and access-path rankings provide historical context. Modern tuning uses the cost-based optimizer; a fixed ranking of access methods is not a current tuning strategy.
A statement is not slow in isolation. Its performance must be judged against a specific workload and an agreed service target. Identify the operation, expected result, input values, data volume, and number of concurrent users before choosing a remedy.
These measures are related, but they are not interchangeable. A parallel report can finish sooner while consuming more resources and slowing other requests. A frequently executed statement may deserve attention even when each execution is short. Measure both individual executions and their cumulative impact.
SQL describes the required result. The optimizer evaluates ways to obtain it, including access paths, join orders, join methods, and query transformations. It uses statistics and other available information to estimate the cost of candidate plans. Statistics about table size and value distribution help it estimate how many rows each operation will process.
A full table scan is not automatically a problem, and an index scan is not automatically an improvement. Retrieving a large part of a table can favor a scan; retrieving a small, selective subset may favor an index. Poor row-count estimates can lead to an unsuitable join method or access path. Inspect the evidence before proposing changes to statistics, SQL, or indexes.
Optimizer cost is a relative estimate, not a stopwatch reading. Do not treat a reduction in displayed cost as proof that elapsed time improved. The chosen plan and observed performance can also change with data distribution, bind values, optimizer settings, and database versions.
EXPLAIN PLAN shows a plan generated without executing the target statement. It is useful for understanding possible operations, but it may differ from the plan used by an application's actual cursor. It also does not supply observed row counts or execution times for that statement.
DBMS_XPLAN.DISPLAY_CURSOR can display the plan of a cursor in the cursor cache. Where execution statistics have been collected, it can also show runtime information. This lets you investigate differences between estimated and observed rows. Displaying a plan does not itself collect missing statistics, and the required catalog privileges must be available.
SQL trace and TKPROF provide another route to understanding statement activity. More advanced facilities, including AWR, ADDM, SQL Tuning Advisor, and SQL Monitoring, have edition, service, or management-pack requirements. Check your environment's entitlements before using them. Later lessons introduce the tools in more detail.
Suppose a bookstore's order-history page responds slowly. First establish whether the user needs the latest few orders or the entire history. Record the SQL calls for one request and inspect their execution plans. Many small repeated queries require a different investigation from one query processing an unnecessarily large result set.
If the evidence suggests an index could help, compare representative customers with small and large histories. Preserve the same filtering and ordering semantics. Include the index's storage and write-maintenance costs when deciding whether the improvement is worthwhile. This is an investigation scenario, not a benchmark or a claim that an index has been tested.
The next lesson examines Oracle extensions to standard SQL. Subsequent lessons introduce the tuning tool set, execution plans, and cost-based optimization. Keep statement semantics, data types, and NULL behavior correct when evaluating any rewrite.
You will also study optimizer goals: ALL_ROWS favors efficient processing of the complete result, while FIRST_ROWS_n favors early retrieval of a specified number of rows. Hints can influence optimizer decisions, but they should follow a diagnosed problem and measured testing rather than substitute for either.