| Lesson 2 | DBA Responsibilities for SQL Tuning in Oracle 26ai |
| Objective | Define the responsibilities of the Oracle DBA regarding SQL when using Oracle AI Database 26ai. |
A database administrator helps an application meet its performance goals while protecting the database services that other applications depend on. In Oracle AI Database 26ai, this means identifying important SQL workloads, interpreting execution evidence, maintaining useful optimizer statistics, and coordinating changes with developers. It does not mean personally writing every query or guaranteeing that every statement always uses the fastest possible plan.
Consider a hypothetical order-search operation that becomes slow during the afternoon peak. A developer knows what the search must return and how the application constructs its parameters. The DBA can investigate the database workload, execution plans, resource pressure, and concurrent activity. Together, they determine whether the problem is excessive database work, blocking, repeated application calls, or time spent elsewhere. Their first deliverable is a supported explanation of the delay, followed by a change that can be tested.
SQL performance is a shared responsibility. Developers own application behavior: result correctness, SQL construction, parameter types, transaction boundaries, and the number of database calls made for a request. DBAs contribute knowledge of the database environment, shared resources, access paths, statistics, privileges, and operational constraints. A service owner helps define acceptable response times, throughput, and availability.
The division of work varies by organization. A small team may combine these roles; a managed database service may handle some infrastructure tasks. Neither arrangement removes the need to understand application SQL. Establish a named owner for each important operation and agree who can approve, implement, and reverse a change. This makes responsibility actionable without requiring the DBA to become the sole author of every stored procedure.
| Activity | DBA contribution | Developer contribution | Deliverable |
|---|---|---|---|
| Prioritize work | Identify database load and competing demands. | Identify affected operations and user impact. | A measurable target and investigation scope. |
| Diagnose SQL | Examine plans, waits, and resource use. | Supply SQL, parameter context, and application timings. | A reproducible baseline and likely cause. |
| Change access paths | Assess statistics and index tradeoffs. | Review query semantics and representative inputs. | A tested change with regression evidence. |
| Govern access | Review grants, schemas, and operational access. | Use approved interfaces and safe parameter handling. | Least-privilege access and reviewable code. |
| Release improvements | Plan rollout, monitoring, and rollback. | Validate correctness and application behavior. | A recorded outcome under realistic load. |
The DBA must understand the sources of a workload, not just the tables it accesses. An interactive SQLcl or SQL*Plus session can submit statements directly. Applications can use embedded SQL through Pro*C/C++ or Pro*COBOL, an Oracle ODBC driver, or Oracle Data Provider for .NET (ODP.NET) from C# or VB.NET. Java and other supported application interfaces provide additional routes. The diagram illustrates representative sources rather than an exhaustive interface catalog.
Stored PL/SQL routines are database schema objects. They execute procedural logic and can issue SQL; they are not an independent SQL engine inside the data dictionary. Direct SQL requests do not have to pass through a stored procedure. Both paths still operate within the database's authentication and privilege model. A successfully authenticated application may submit SQL that nobody reviewed individually, but it does not thereby acquire unrestricted access to every table.
A statement's SQL text alone does not reliably identify the business operation or team responsible for it. The same statement may serve several application paths, and one request may execute many statements. Correlate the database service, session context, module and action labels, SQL identifier (SQL_ID), and relevant child cursor with the observed request.
Developers can set module and action names with DBMS_APPLICATION_INFO or supported driver facilities. The DBA can then use those labels alongside database evidence when investigating an operation. The following SQL*Plus or SQLcl illustration sets a label and later clears it. It is not a complete application or a recorded execution result.
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(
module_name => 'OrderService',
action_name => 'LookupOrder');
END;
/
-- Perform the application operation between setup and cleanup.
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;
/
In application code, ensure cleanup happens on success and failure. This is particularly important with pooled connections: a later borrower must not inherit misleading request labels. The slash is a SQL*Plus/SQLcl command that executes the buffered PL/SQL block; it is not part of the block sent through a programming API. Module and action labels support diagnosis, not authentication or authorization.
Agree on a naming convention that remains stable across releases and identifies useful operations without exposing sensitive data. Put customer identities, secrets, and unrestricted parameter dumps outside routine diagnostic labels. If bind values are needed to reproduce a problem, collect and retain them according to the application's data-handling rules.
There are usually more statements than a team can investigate individually. Start with business impact and measurable database demand. A slow checkout operation may deserve attention even when it is infrequent. Conversely, a fast lookup repeated thousands of times can consume substantial resources in aggregate. Evaluate both the cost of one execution and the total cost across an appropriate interval.
Capture the workload period, execution count, elapsed time, CPU consumption, and relevant I/O or wait evidence. Cumulative cursor counters are not automatically comparable: cursors may have different lifetimes, and the workload may have changed. Use interval measurements where appropriate and record the scope of each measurement. An average can also conceal variation between parameter values or between quiet and busy periods.
Separate end-to-end application response time from database execution time. A request can wait for a connection, make repeated round trips, fetch a large result, or spend time formatting it after database processing. For a query, distinguish the time to obtain the first row from the time to fetch the complete result. Optimizing one database step does not prove that the user's whole request improved.
A useful investigation record contains the affected operation, expected result, performance target, SQL text and identifiers, parameter types and representative values, database context, observed plan, and timing evidence. Record recent application or data changes as well. This handoff prevents the DBA and developer from testing different statements or comparing different workloads.
EXPLAIN PLAN shows a proposed execution plan without running the target statement. It helps discuss access paths, but it does not prove which plan a particular application execution used or how much work that execution performed. Bind values, session settings, and other execution context can affect the comparison.
DBMS_XPLAN.DISPLAY_CURSOR can display a loaded cursor's plan. Identify the intended SQL_ID and child cursor rather than accidentally inspecting a diagnostic query. Runtime row-source statistics are available only when collected for the measured execution. Compare estimated and actual work where those statistics exist, but do not fabricate missing measurements or assume every displayed plan contains them.
Scoped SQL trace and TKPROF can help investigate parse, execute, and fetch activity. Collect only the needed evidence with appropriate privileges and an understanding of tracing overhead. Investigate waits and blocking alongside plans: a suitable access path does not prevent a session from waiting on another transaction. Avoid changing indexes when the primary delay is actually an application holding a lock too long.
Historical and advisor tools provide additional capabilities, subject to entitlement. AWR, ASH, and ADDM belong to Oracle Diagnostics Pack functionality. SQL Tuning Advisor, SQL Access Advisor, SQL profiles, and Real-Time SQL Monitoring are Tuning Pack features. Tuning Pack requires Diagnostics Pack where separately licensed; inclusions vary by edition and service. Check the actual deployment's licensing terms. An enabled setting or visible menu is not proof of entitlement, and access privileges are a separate requirement.
The cost-based optimizer uses statistics to estimate the work of candidate plans. A DBA should review automatic optimizer statistics collection and investigate missing, stale, or otherwise unsuitable statistics when evidence points to estimation problems. Use DBMS_STATS deliberately where additional collection is needed, for example after significant changes that the existing collection process has not addressed.
Do not replace this responsibility with a rule to gather every statistic on a fixed schedule. Collection itself consumes resources, and changed statistics can change plans. Consider table size, data distribution, maintenance windows, and existing collection preferences. A stale-statistics indicator is useful evidence, not a diagnosis by itself. Test the effect on representative SQL and preserve an appropriate recovery approach before material changes.
Indexes involve similar tradeoffs. An index that improves a selective lookup can increase storage and the cost of inserts, updates, or deletes. Review the wider workload and constraints before creating or dropping one. A short period with no observed use does not establish that an index is unnecessary for a monthly report or a critical infrequent operation. Rebuilding every index routinely is not a general SQL tuning strategy.
Work with developers to examine join conditions, predicates, unnecessary columns, repeated calls, and data conversions. A full table scan can be appropriate when much of a table is needed; an index scan can be expensive when it causes excessive scattered access. Judge plans by their actual work and business requirements, not by whether a preferred operation name appears.
SQL Tuning Advisor recommendations can suggest statistics work, SQL restructuring, indexes, or SQL profiles. Treat each recommendation as a proposal to validate against the application workload. SQL Access Advisor considers physical access structures for a workload; its recommendations also require assessment of storage, maintenance, and deployment effects. Automated advice does not replace business knowledge or correctness testing.
A SQL profile supplies supplemental information to the optimizer; it does not pin the statement to one execution plan. SQL Plan Management uses SQL plan baselines to manage accepted plans and help prevent regressions. These mechanisms address related but different problems. A baseline cannot guarantee identical performance under all loads or make a plan usable after a required index disappears. SQL Plan Management does not itself require Diagnostics or Tuning Pack, but edition and service feature limits still need checking.
Resource changes require broader judgment. Increasing parallelism may help one job while harming concurrent users. A memory adjustment or a system-wide optimizer setting can affect far more than the statement under investigation. Diagnose the bottleneck first, consider targeted remedies, and test shared workload effects. Record the setting, scope, expected benefit, and rollback procedure rather than changing several parameters until one test becomes faster.
A reviewed stored API can be a useful access boundary. With an appropriately designed definer-rights procedure, a caller may receive EXECUTE permission without direct access to the underlying tables. The routine runs using the applicable definer security context. Invoker-rights routines use a different privilege model; the same access assumptions do not automatically apply.
This design requires careful privilege management and implementation. Merely moving a query into a procedure does not make it efficient or protect unsafe dynamic SQL. Use safe parameter handling, validate inputs as required, and review the operations a routine exposes. Bind parameters represent data values, not arbitrary object names or SQL keywords. Do not grant broad administrative privileges simply to make an application routine run.
Stored APIs are one governance tool, not the only way to enforce SQL standards. Combine least-privilege grants, application roles, controlled schemas, code review, versioned changes, and representative performance tests. Security controls determine what an account may do; engineering standards determine how reviewed code should behave. A query can comply with access restrictions and still perform poorly, so both kinds of control matter.
Before release, agree on what constitutes success: correct results, acceptable response time, and sustainable resource use under representative concurrency. Repeat the baseline test with comparable data, parameters, fetch behavior, and workload conditions. Include both typical inputs and important edge cases. Check whether an improvement for one parameter range causes regressions elsewhere.
For the order-search example, the developer verifies matching orders, ordering, pagination, and transaction behavior. The DBA checks plan and resource changes and tests interference with other important workloads. If the proposed index improves searches but makes peak order entry unacceptable, revise the proposal. A single faster demonstration is not sufficient evidence for a production change.
Record the approved change, implementer, deployment window, measurements, and rollback steps. After release, compare the expected outcome with production evidence and keep the application owner involved. The DBA's responsibility is an ongoing process of prioritization, diagnosis, coordination, and verification. The next lesson explains how SQL standards make that process repeatable across a development team.