An execution plan is a useful starting point for investigating SQL performance, but the presence of a particular operation does not establish whether a query is efficient.
A full table scan is not automatically a mistake, and an index operation is not automatically a success. The better question is whether the database performed appropriate work for the actual request and data involved.
Understand what the plan represents
Oracle describes an execution plan as the sequence of operations used to run a SQL statement. Its tuning documentation also cautions that the operation name alone cannot distinguish a well-tuned statement from a poorly performing one.
The plan presents a tree of row sources, including how tables are accessed and joined. Reading those relationships is more useful than scanning the output for a single supposedly undesirable keyword.
Begin with the query’s purpose. Identify the result it should produce, the amount of data it normally returns, and the conditions under which users experience delays.
Inspect the relevant cursor
Oracle’s DBMS_XPLAN.DISPLAY_CURSOR can display a plan from the cursor cache. Its parameters let administrators identify the SQL statement and child cursor, while formatting options control the detail shown.
The ALLSTATS LAST format can show statistics for the last execution when the necessary execution statistics were collected. Oracle documents collection requirements and permissions; requesting that format does not create measurements that were never captured.
For an investigation, record the SQL identifier, child cursor, input values where appropriate, and collection conditions. Otherwise, two reports may describe different executions while appearing directly comparable.
Use differences to form a hypothesis
Suppose an operation was expected to return a small set of rows but actually produced a much larger intermediate result. That difference is a reason to investigate assumptions about the data or predicates.
It does not, by itself, prove which change will help. Examine the surrounding operations and the application request before proposing an index, a rewrite, or a statistics change.
Treat each proposed explanation as a hypothesis. State what evidence would support it and what result would contradict it.
Compare under meaningful conditions
Keep the business request consistent when evaluating a change. A fast execution returning fewer rows is not a valid comparison with a slower execution returning the full requested result.
Record elapsed time alongside the available execution evidence. Include relevant workload conditions, because a query tested alone may behave differently during a busy period.
Change one important factor at a time where practical. This preserves the ability to explain why performance changed and avoids accumulating modifications whose individual effects are unclear.
Finally, confirm correctness. Compare result counts and representative values before celebrating a faster plan.
An effective tuning note should describe the original symptom, the execution examined, the hypothesis, the change, and the measured outcome. That record is more useful to the next administrator than an unexplained instruction to “avoid full scans.”