AWR is good at telling you something got slower. It is not built to tell you why the optimizer changed its mind about a specific statement, and it was never meant to be — it’s a snapshot-interval rollup, not a per-statement diagnostic.
OraTune’s AWR parser reads AWR text reports (from awrrpt.sql), AWR HTML reports (from Enterprise Manager or Cloud Control), and TKPROF listings. Out of an AWR report it pulls DB Time, elapsed time, top wait events, and key instance statistics. Out of TKPROF it pulls per-SQL elapsed time, CPU, logical reads, physical reads, and execution counts, and identifies the top SQL by total elapsed time. If you’re connected live, the Top SQL tab ranks statements by elapsed time, CPU percentage, buffer gets, disk reads, or execution count — sourced from DBA_HIST_SQLSTAT when AWR history is available, falling back to V$SQL for in-memory data. The AWR Trends tab takes a single SQL_ID and plots elapsed time, CPU time, buffer gets, and disk reads per execution over a date range.
All of that answers one question well: which statement regressed, and by how much, over what window. None of it explains why. AWR and TKPROF report on execution behavior — time, reads, waits — not on the optimizer inputs that produced a given plan for that statement.
That’s the gap a SQLT (SQLTXPLAIN) dump fills. OraTune’s dump parser auto-detects a SQLT dump by the SQLTXPLAIN or TOOL: SQLT markers in the first 8KB of the file, then extracts: optimizer parameters, table statistics, index statistics, histograms, execution statistics, and the plans embedded in the dump itself. This is per-statement, per-object detail — the specific inputs the optimizer had in front of it when it built the plan you’re now troubleshooting.
When you diff a baseline SQLT dump against a current one, OraTune turns the raw extraction into findings using a fixed set of thresholds: an optimizer parameter that changed between the two runs is flagged HIGH or MEDIUM depending on which parameter; a table’s NUM_ROWS drifting more than 20% is MEDIUM, more than 50% is HIGH; an index clustering factor that degraded more than 50% is HIGH; a histogram removed from a column is MEDIUM; an elapsed-time regression of 2x or more is HIGH or CRITICAL. These same deltas — optimizer parameter changes, table stat changes, clustering factor changes, histogram changes — are what get passed into the AI (or offline) recommendation engine as context, which is why the Recommendations tab can talk about a specific parameter or a specific column’s histogram rather than a generic “check your statistics” suggestion.
As an illustration of what that per-statement detail looks like in practice — again, a constructed example, not a real dump — a baseline SQLT extraction for a statement against a SHIPMENTS table might show optimizer_index_cost_adj unchanged at 100, a clustering factor on SHIPMENTS_STATUS_IX of roughly 4,800 against 1.1 million rows, and a histogram present on the STATUS column. The current dump for the same statement might show the same clustering factor now above 400,000 after a bulk reload changed physical row order, and the histogram on STATUS missing because a stats gather ran without METHOD_OPT preserving it. Nothing in an AWR report would surface either of those two facts. AWR would only tell you the statement’s elapsed time went up.
The practical order of operations follows from this: use AWR or Top SQL to find which SQL_ID needs attention — that part is aggregated and fast to scan across an entire instance. Once you know the SQL_ID, pull a SQLT dump for that specific statement, on both the baseline and current side if you have one, and let OraTune’s dump comparison do the per-object work: parameter diffs, stat drift, clustering factor, histograms. AWR narrows the search. SQLT explains the result.