Oracle DBA Lesson 103 Part 1 — Assess structures for a workload

An index or materialized view earns its place by improving the workload that matters. Faster execution of one query is useful evidence, but the decision also includes write cost, storage, refresh work, and operating requirements.

Oracle DBA Lesson 103 Part 1 — Assess structures for a workload

These commands target Oracle Database 19c. SQL Access Advisor requires the Oracle Tuning Pack, and Diagnostics Pack is a prerequisite. The existence of a package does not establish entitlement. Confirm the edition, offering, and contract. Partitioning and materialized view query rewrite have their own conditions. The workflow needs the ADVISOR privilege and access to the workload and objects. Managing an owned SQL tuning set generally requires ADMINISTER SQL TUNING SET. Another owner's sets require the broader privilege. Object creation, dictionary access, and runtime-statistics access are separate requirements.

Oracle 19c permits documented SQL tuning set interfaces in certain Enterprise Edition offerings without treating every such interface as Tuning Pack usage. Those interfaces do not cover SQL Access Advisor. Loading a workload from AWR invokes separate Diagnostics Pack conditions. Check the current licensing guide for the exact interface, source, and offering. Without SQL Access Advisor, use the same workload inventory and comparison with a manually chosen structure, and do not call the advisor. See Licensing Information.

How structures change the work

A B-tree index stores ordered keys and row locations. A selective customer search can follow a key to a small set of rows instead of examining many table rows. Whether that helps depends on the predicates, selectivity, table size, and optimizer estimates. A scan can remain the appropriate choice for a query that needs much of a table.

An index also has a segment on disk. Inserts, deletes, and changes to indexed values require index maintenance. An index on frequently changed order status therefore has a recurring cost. Measure that cost in the write workload, including its commit pattern and concurrency. See indexes and index-organized tables.

A materialized view stores a query result. A stored total by customer can avoid repeatedly aggregating the same order rows. Source changes create refresh work. Assess the chosen refresh method, duration, freshness requirement, storage, and any required materialized view logs. Some refresh methods need logs. Do not assume every materialized view has identical requirements. Refresh on commit can affect commit latency. Refresh on demand requires a schedule and an acceptable period of staleness. Query rewrite eligibility and configuration also matter when applications continue querying the base tables. See basic materialized views and refreshing materialized views.

Start with representative activity

Consider three query shapes against ORDERS:

SELECT order_id, amount
FROM orders
WHERE customer_id = :customer_id;

SELECT order_id, customer_id, amount
FROM orders
WHERE order_date >= :from_date;

SELECT customer_id, SUM(amount)
FROM orders
WHERE status = 'NEW'
GROUP BY customer_id;

Customer lookup, date range selection, and aggregation can favor different access structures. Include representative inserts and status updates as well. Record execution frequency, important bind values and their source, parsing schema, data distribution, object statistics, peak concurrency, and business importance. A rare expensive query and a frequent short transaction can have very different operational consequences. The bind values must represent the workload. A convenient single value can hide skew.

What SQL Access Advisor evaluates

SQL Access Advisor evaluates access structures for a supplied workload. Depending on its scope, it can advise on indexes, materialized views, materialized view logs, and partitioning. SQL Tuning Advisor focuses on statement tuning. Their reports answer different questions.

Recommendations depend on workload coverage and suitable object statistics. A partial sample cannot establish that an existing structure is unnecessary across the application. WORKLOAD_SCOPE defaults to PARTIAL. Use FULL only when the input contains all interesting application SQL for the target objects. Drop advice is available only for a full workload. Do not declare a small sample full merely to obtain more recommendations.

For write-maintenance costing, supply representative DML and set DML_VOLATILITY to TRUE. The setting allows index maintenance and materialized view refresh cost to be considered. It cannot compensate for omitted or unrepresentative write activity. Review other task parameters, including the materialized view refresh mode, against the proposed design. See SQL Access Advisor, DBMS_ADVISOR, and managing SQL tuning sets.

Create an index-only task

The SQL tuning set ORDERS_WORKLOAD_01, owned by APP_USER, must already contain representative queries, writes, frequencies, and context. The task below uses index-only analysis. Verify that the task name is unused.

DECLARE
  trial_task_id NUMBER;
  trial_task_name VARCHAR2(30) := 'ACCESS_ORDERS_01';
BEGIN
  DBMS_ADVISOR.CREATE_TASK(
    advisor_name => 'SQL Access Advisor',
    task_id => trial_task_id,
    task_name => trial_task_name);

  DBMS_ADVISOR.SET_TASK_PARAMETER(
    trial_task_name, 'ANALYSIS_SCOPE', 'INDEX');
  DBMS_ADVISOR.SET_TASK_PARAMETER(
    trial_task_name, 'WORKLOAD_SCOPE', 'PARTIAL');
  DBMS_ADVISOR.SET_TASK_PARAMETER(
    trial_task_name, 'DML_VOLATILITY', 'TRUE');
  DBMS_ADVISOR.SET_TASK_PARAMETER(
    trial_task_name, 'TIME_LIMIT', 5);

  DBMS_ADVISOR.ADD_STS_REF(
    trial_task_name, 'APP_USER', 'ORDERS_WORKLOAD_01');
END;
/

EXEC DBMS_ADVISOR.EXECUTE_TASK('ACCESS_ORDERS_01');

TIME_LIMIT is in minutes. This task allows five minutes of analysis. ANALYSIS_SCOPE set to INDEX keeps the analysis focused on indexes. Select a different scope deliberately when the experiment includes other structures and their feature conditions. Linking a tuning set does not make an incomplete sample representative. Coordinate changes to the linked set while the task uses it.

Inspect the actual task status and diagnostic messages before interpreting advice. The task identity and status can be checked with:

SELECT owner, task_name, status
FROM dba_advisor_tasks
WHERE owner = USER
  AND task_name = 'ACCESS_ORDERS_01';

A completed task may produce no useful recommendation. Investigate actual coverage, statistics, and task messages rather than inventing a recommended index. Inspect affected statements, recommended definitions, estimated space, and related actions in the actual report.

Export the implementation script for review. In SQL*Plus, set the CLOB display limits and spool the script:

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET PAGESIZE 0
SET LINESIZE 200
SET TRIMSPOOL ON
SPOOL access_orders_01_review.sql
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('ACCESS_ORDERS_01')
FROM dual;
SPOOL OFF

GET_TASK_SCRIPT returns script text. This query does not execute the proposed DDL. Check for truncation and review the entire output before choosing a test. Actions in a recommendation can depend on each other for their full benefit. Preserve those dependencies and choose one coherent experiment. If partitioning or another feature is outside the chosen scope, rerun analysis with an appropriate scope instead of removing arbitrary script lines and assuming the remainder is equivalent.

Measure the baseline and the trial

For the pack-free probe, execute and fully fetch this statement:

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id, SUM(amount)
FROM orders WHERE status = 'NEW'
GROUP BY customer_id;

Immediately afterward in the same session, before another SQL statement changes the last cursor, inspect its actual plan:

SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  NULL, NULL, 'ALLSTATS LAST'));

The hint requests execution statistics. ALLSTATS LAST displays statistics for the last execution when they were collected and are accessible. If other SQL intervened, identify the intended SQL ID and child cursor explicitly. Review actual rows, starts, and buffers with the plan operations. Account for starts when interpreting repeated operations. Measure elapsed time using a consistent method and fetch all rows in both runs. A plan hash alone does not establish improvement. See DBMS_XPLAN.

Suppose the actual report recommends a standalone index on (status, customer_id). Confirm its definition and dependencies. For an independently justified manual experiment, a possible definition is:

CREATE INDEX orders_struct_trial
ON orders(status, customer_id);

This definition is a candidate, not a reported recommendation or a promise that the optimizer will use it. Do not create it if an equivalent index exists, its name is occupied, or its dependencies require another design.

Repeat all representative queries and writes with comparable data volume, distribution, cache conditions, and concurrency. Include warm-up and repeated runs, and record the execution and commit pattern. Restore comparable data between write trials. Assess logical reads, elapsed time, write latency, storage, and materialized view refresh behavior when relevant. A tiny fixture may not show a meaningful difference. A larger deterministic data set can help when its setup and row count are recorded.

Evidence	Decision condition
Customer, date, and total-query plans, rows, buffers, and elapsed time	Compare representative executions and frequency
Insert and status-update latency and throughput	Include index maintenance and commit behavior
Structure size and maintenance overhead	Fit storage and operational allowances
Materialized view refresh and freshness, when applicable	Meet the selected refresh and freshness requirements
Backout and restored workload	Restore the recorded baseline and verify it

The result to reach is a supported keep, revise, or reject decision, including regressions and business weighting. Do not invent a percentage benefit.

Back out the trial index and the task

Prepare the backout before applying the change. DDL commits. A later ROLLBACK does not undo a created or dropped index. For an unwanted, newly created, standalone index only:

DROP INDEX orders_struct_trial;

Confirm the owner and the exact object identity first. Do not drop a pre-existing, shared, or constraint-supporting index. Preserve the original definitions, restore the recorded data and settings, and repeat the baseline workload to verify restoration. A materialized view and any logs or dependent objects created for the trial need their own dependency-aware removal. A log may serve other views.

Retain the reviewed report and the evidence before removing the task. Unlink only this task's reviewed tuning-set reference, then remove only that task:

BEGIN
  DBMS_ADVISOR.DELETE_STS_REF(
    'ACCESS_ORDERS_01', 'APP_USER', 'ORDERS_WORKLOAD_01');
  DBMS_ADVISOR.DELETE_TASK('ACCESS_ORDERS_01');
END;
/

Do not delete another user's tuning set or shared objects. Restore client formatting if required. Confirm that removal affected only the intended artifacts.

Recap

An index or materialized view earns its place on the workload that matters. A B-tree index can turn a selective lookup into a small set of rows, and it adds a segment plus maintenance on inserts, deletes, and changes to indexed values. A scan can still be the right plan when a query needs much of the table. A materialized view stores a query result and moves cost into refresh work, freshness, storage, and any required logs. Refresh on commit can affect commit latency. Refresh on demand needs a schedule and an acceptable period of staleness. Query rewrite matters when applications keep querying the base tables.

Start from representative statements, writes, frequencies, binds, statistics, concurrency, and business importance. SQL Access Advisor can recommend indexes, materialized views, materialized view logs, and partitioning for that workload. SQL Tuning Advisor answers a different question. WORKLOAD_SCOPE defaults to PARTIAL. Use FULL only when the input contains all interesting SQL for the target objects, because drop advice requires a full workload. Supply representative DML and set DML_VOLATILITY to TRUE when write cost should be considered. The task ACCESS_ORDERS_01 links APP_USER.ORDERS_WORKLOAD_01, limits analysis to indexes, and allows five minutes. Read task status before the advice. GET_TASK_SCRIPT returns script text for review and does not run the DDL. Keep dependent actions together, or rerun with a scope that fits.

Measure with GATHER_PLAN_STATISTICS and ALLSTATS LAST on the last cursor, and compare fully fetched runs. A candidate such as orders_struct_trial on (status, customer_id) is not a promise that the optimizer will use it. Keep, revise, or reject from plans, buffers, elapsed time, write latency, storage, and refresh behavior. DDL commits, so remove an unwanted new standalone index with DROP INDEX on that exact object, then restore the recorded baseline and measure it again. Unlink the tuning-set reference and delete only that task after the report is saved. SQL Access Advisor requires Tuning Pack, with Diagnostics Pack as a prerequisite.

Quiz

1. A recommended index speeds one sampled query. What is the next decision step?

Accept it immediately. Assess workload coverage, write cost, storage, and representative measured behavior. Assume every query becomes faster. Remove all other indexes.

2. Should an index recommended from read samples be accepted without checking a high-volume write workload?

Yes, because indexes affect only reads. Yes, if the task completed. No. Assess the sample and the DML maintenance cost. Only if no baseline was recorded.

3. What does GET_TASK_SCRIPT do in the displayed query?

Executes every recommended DDL action. Returns script text for inspection. Proves that a candidate index was used. Restores the baseline data.

4. Which materialized view consideration belongs in the decision?

Refresh work, freshness, storage, and any required logs. Only the first query's elapsed time. An assumption that stored results update without cost. The number of lines in the recommendation.

5. Which backout is appropriate for an unwanted standalone trial index?

Roll back its creation with ordinary transaction rollback. Drop every index on the table. Drop the exact newly created trial index, restore the recorded baseline, and verify the workload. Assume disabling the advisor task removes the index.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily