# Oracle Dba Performance Tuning Diagnostics

> USE THIS SKILL WHENEVER the user mentions database is slow / hung / high CPU, performance tuning, SQL tuning, execution plan / explain plan, AWR / ADDM / ASH, SQL Tuning Advisor, SQL profile, SQL plan baseline (SPM), SPA / SQL Performance Analyzer, optimizer statistics / DBMS_STATS / histograms / data skew, top SQL / resource consumers, blocking locks / runaway / kill session, wait event / wait class analysis, latch / mutex / "cursor: pin S" / "cache buffers chains" contention, SGA / PGA / memory tuning, optimizer parameters (optimizer_*, cursor_sharing, OFE), parallel execution / PX downgrades, real-time SQL monitoring, Database Replay (RAT / workload capture & replay), partitioning strategy, or any of the symptoms/errors ORA-04031 (shared pool), ORA-04030 (PGA), ORA-00060 (deadlock), ORA-01555 (snapshot too old), "log file sync", "db file sequential/scattered read", "library cache" / "row cache" lock — EVEN IF they do not name a specific task or tool. Covers L1 triage, L1/L2 read-only diagnostics (AWR/ADDM/

- Skill: `shreyas70773/oracle-dba-performance-tuning-diagnostics` (Agent Skill, multi-file: 51 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-performance-tuning-diagnostics`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-performance-tuning-diagnostics/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/oracle-dba-performance-tuning-diagnostics

---

# Oracle DBA - Performance Tuning & Diagnostics

Diagnose and (within safe tiers) remediate Oracle performance problems on Oracle
19c and 23ai (EE; single-instance and Data Guard; on-prem). Everything that
self-executes is **T1 (observe, read-only)** or **T2 (reversible, dry-run-default)**.
Every change that alters an execution plan, an optimizer/instance parameter, memory
sizing, or object structure is **T3** and lives only in `references/runbooks.md` —
it PRINTS commands for a human operator and never self-executes.

Routing: in-DB SQL/PLSQL runs as `sql/*.sql` through the SQLcl MCP `run-sql` on the
saved connection `dba_ai_conn` (mechanism `sqlcl-mcp`). Host-side report drivers and
gated mutators run as behaviour-equivalent `.sh`/`.ps1` (mechanism `script`). T3
remediation requires `require_approval_token` and a runbook. **When in doubt, runbook.**

## Scope & risk map

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| Performance issue triage / first response | L1 | T1 | `scripts/checks/perf_triage.sh` / `.ps1` + `sql/perf_triage.sql` | yes — read-only snapshot; surfaces findings, never acts |
| Scheduled optimizer stats gathering (autotask enable/disable) | L1 | T2 | `scripts/autotask_stats_set.sh` / `.ps1` + `sql/autotask_stats_set.sql` (`sql/autotask_stats_status.sql` audit) | yes — noop if already in desired state; flip via opposite `--state` |
| Manual statistics gathering (schema/table) | L2 | T2 | `scripts/gather_stats.sh` / `.ps1` + `sql/gather_stats.sql` | yes — re-gather is safe; prior stats kept in history (restore path: `#optimizer-param-tuning`) |
| Fixed-object / dictionary statistics gathering | L2 | T2 | `scripts/gather_fixed_dict_stats.sh` / `.ps1` + `sql/gather_fixed_dict_stats.sql` | yes — re-runnable; restore via `RESTORE_DICTIONARY/FIXED_OBJECTS_STATS` |
| AWR report generation | L2 | T1 | `sql/awr_report.sql` (+ `sql/awr_snapshots.sql` to pick snap ids) — SQLcl MCP | yes — read-only repository report; Diagnostics Pack |
| ADDM report review | L2 | T1 | `sql/addm_report.sql` — SQLcl MCP | yes — reuses/reprints an existing same-name task; Diagnostics Pack |
| ASH report review | L2 | T1 | `sql/ash_report.sql` — SQLcl MCP | yes — read-only window report; Diagnostics Pack |
| Identify top SQL / resource consumers | L2 | T1 | `sql/top_sql.sql` — SQLcl MCP (or via triage) | yes — read-only ranking |
| Kill blocking / runaway session (ONE session) | L2 | T2 | `scripts/kill_session.sh` / `.ps1` + `sql/kill_session.sql`; finder `sql/blockers.sql` | yes — noop if no live USER session matches; PMON-stuck escalation -> `#kill-session-escalation` |
| SQL plan baseline management (LOAD/ENABLE/DISABLE/FIX/UNFIX) | L2 | T2 | `scripts/baseline_manage.sh` / `.ps1` + `sql/baseline_manage.sql` (`sql/baseline_list.sql` audit) | yes — toggles/loads are reversible; **DROP excluded -> `#baseline-drop`** |
| SQL Performance Analyzer (SPA) trial + compare | L2 | T2 | `scripts/spa_run.sh` / `.ps1` + `sql/spa_run.sql` (`sql/spa_status.sql` audit) | yes — EXPLAIN default (no execution); TEST EXECUTE needs `--allow-test-execute` + test system; RAT/Tuning Pack |
| SQL Tuning Advisor — review existing tasks | L3 | T1 | `scripts/sql_tuning_advisor.sh review` / `.ps1 review` + `sql/sql_tuning_advisor_review.sql` | yes — read-only |
| SQL Tuning Advisor — run for a sql_id (recommend only) | L3 | T2 | `scripts/sql_tuning_advisor.sh run --sql-id` / `.ps1 run` + `sql/sql_tuning_advisor_run.sql` | yes — drops/re-creates STA_<id>; recommends only, applies nothing; Tuning Pack |
| SQL Tuning Advisor — APPLY a recommendation (profile/index/baseline) | L3 | **T3** | `references/runbooks.md#sql-tuning-advisor` | no — changes plans (profile accept / index DDL) |
| SQL tuning / execution-plan analysis | L3 | T1 (diag) / **T3** (fix) | `sql/sql_plan_analysis.sql` (read-only) -> `references/runbooks.md#sql-tuning` | diag yes; the fix is T3 (profile/baseline/index) |
| Index analysis, creation & strategy | L3 | T1 (diag) / **T3** (DDL) | `scripts/perf_diag.sh indexes --owner` + `sql/index_analysis.sql` -> `references/runbooks.md#index-strategy` | diag yes; CREATE/DROP/REBUILD is structural T3 |
| Wait event analysis | L3 | T1 (diag) / **T3** (fix) | `scripts/perf_diag.sh waits` + `sql/wait_event_analysis.sql` -> `references/runbooks.md#wait-event` | diag yes; remediation (param/structural/resource) is T3 |
| Latch / mutex contention analysis | L3 | T1 (diag) / **T3** (fix) | `scripts/perf_diag.sh latch` + `sql/latch_mutex_analysis.sql` -> `references/runbooks.md#latch-mutex` | diag yes; remediation is T3 |
| Memory tuning (SGA / PGA) | L3 | T1 (diag) / **T3** (resize) | `scripts/perf_diag.sh memory` + `sql/memory_advisor.sql` -> `references/runbooks.md#memory-tuning` | diag yes; resize is instance-affecting T3 (may need restart) |
| Optimizer parameter tuning | L3 | T1 (diag) / **T3** (change) | `scripts/perf_diag.sh optimizer` + `sql/optimizer_param_analysis.sql` -> `references/runbooks.md#optimizer-param-tuning` | diag yes; parameter change is plan-wide T3 (also the stats-restore rollback home) |
| Histogram / data-skew analysis | L3 | T1 (diag) / **T3** (change) | `scripts/perf_diag.sh histogram --owner` + `sql/histogram_skew_analysis.sql` -> `references/runbooks.md#histogram-skew` | diag yes; adding/removing a histogram changes cardinality = T3 |
| Parallel execution tuning | L3 | T1 (diag) / **T3** (change) | `scripts/perf_diag.sh parallel` + `sql/parallel_exec_analysis.sql` -> `references/runbooks.md#parallel-exec-tuning` | diag yes; PX param / object DOP change is T3 |
| Real-time SQL monitoring analysis | L3 | T1 (diag) / **T3** (act) | `sql/sqlmon_report.sql` (read-only) -> `references/runbooks.md#realtime-sqlmon` | diag yes; acting (kill / plan change) routes to T3 |
| Database Replay (workload capture & replay) | L3 | **T3** | `references/runbooks.md#database-replay` | no — capture marks prod; replay only on a restored TEST target; RAT license |
| Partitioning strategy design (convert / split / merge / drop) | L3 | T1 (diag) / **T3** (DDL) | `sql/partition_analysis.sql` (read-only) -> `references/runbooks.md#partitioning` | diag yes; structural DDL is T3 (DROP PARTITION removes data) |

## Preconditions

- **SQLcl MCP connection `dba_ai_conn`** is reachable and wallet-backed. All in-DB
  prechecks, postchecks, idempotency probes, reports, and `sql/*.sql` run through it
  via `connect_db` / `Connect-Db` (mechanism `sqlcl-mcp`). Never SYS/SYSTEM — except
  inside a T3 runbook step explicitly marked `[SYSDBA]` (e.g. an SPFILE change needing
  a restart, or `orakill`), supplied interactively by the operator.
- **Secrets resolve from the Oracle Wallet / external password store. No plaintext
  anywhere.** No user/password on any command line; an argument that looks like a
  credential is rejected with `ERR_SECRET` (8).
- **Licensing gates.** AWR/ADDM/ASH and Real-Time SQL Monitoring require the
  **Diagnostics Pack**; SQL Tuning Advisor and SPA require the **Tuning Pack**;
  Database Replay/SPA capture-replay requires **Real Application Testing**. Where a
  host wrapper exists for a Pack-gated mutator (`spa_run`), it refuses to run without an
  explicit operator attestation flag (`--confirm-licensed`) and logs it; it never
  bypasses it. The read-only Diagnostics-Pack reports (AWR/ADDM/ASH) are invoked on
  demand directly via SQLcl MCP and carry the Pack requirement in their header — run
  them only where the Pack is licensed. (`sql_tuning_advisor run` is Tuning-Pack gated by
  privilege/role on the `dba_ai_conn` user rather than a `--confirm-licensed` flag.)
- **Helpers are sourced from `_common`** (`scripts/lib.sh` / `lib.ps1`): `connect_db`,
  `log_event`, `emit_metric`, `require_dry_run`, `guard_blocked_dry_run`,
  `require_approval_token`, `precheck`, `postcheck`, `print_banner`. Not reinvented.
- **Logging**: structured `key=value` lines to the canonical paths
  `/var/log/oracle-dba/performance-tuning/performance-tuning.log` (Linux) /
  `C:/ProgramData/oracle-dba/logs/performance-tuning/performance-tuning.log` (Windows);
  skill log segment is `performance-tuning`.
- **T2 dry-run default**: every T2 script previews and changes nothing unless given
  `--execute` (PowerShell `-Execute`). Identifiers (owner/table/sql_id/handle, SID/SERIAL#)
  are validated before they reach a `DEFINE` to prevent injection.

## Procedures

Scripts ship as behaviour-equivalent `.sh` (Linux) / `.ps1` (Windows) pairs. T2
scripts default to `--dry-run`; pass `--execute` to act. See each script's `--help`.
Read-only SQL reports run directly through SQLcl MCP (`@sql/<file>.sql` on `dba_ai_conn`).

### Performance triage / first response (T1)
- **Trigger**: "the database is slow / hung"; the first thing to run.
- **Precheck**: `dba_ai_conn` reachable (read-only probe).
- **Action**: `scripts/checks/perf_triage.sh [--max-tbs-pct N --block-warn-secs N]` / `.ps1` -> `sql/perf_triage.sql`.
- **Postcheck**: n/a — emits KEYVAL metrics + threshold findings (blockers, high CPU, full tablespace).
- **Rollback note**: none — read-only. Next steps: AWR/ASH/ADDM, `top_sql`, `blockers`, then the relevant T3 runbook.

### Read-only deep diagnostics (T1, in-DB via SQLcl MCP)
- **Trigger**: characterise the problem before changing anything.
- **Precheck**: connection reachable; Diagnostics/Tuning Pack as noted.
- **Action**: `@sql/awr_snapshots.sql` then `@sql/awr_report.sql`; `@sql/addm_report.sql`; `@sql/ash_report.sql`; `@sql/top_sql.sql`; `@sql/blockers.sql`; `@sql/sql_plan_analysis.sql`; `@sql/sqlmon_report.sql`. Owner-scoped reports via `scripts/perf_diag.sh waits|latch|memory|optimizer|parallel|histogram|indexes|partition`.
- **Postcheck**: n/a (read-only).
- **Rollback note**: none. Findings route to the matching T3 runbook for any change.

### Optimizer statistics — gather / autotask / fixed+dict (T2)
- **Trigger**: stale stats suspected; post-load; post-patch; control the nightly autotask.
- **Precheck**: connection reachable; for table/schema scope the owner/table validated.
- **Action**: `scripts/gather_stats.sh --scope SCHEMA|TABLE --owner ... [--table ...] --execute` / `.ps1`; `scripts/gather_fixed_dict_stats.sh [--kind FIXED|DICT|BOTH] --execute` / `.ps1`; `scripts/autotask_stats_set.sh --state ENABLE|DISABLE --execute` / `.ps1`.
- **Postcheck**: `last_analyzed` advanced / autotask client in desired state.
- **Rollback note**: stats history retains prior stats — restore via `references/runbooks.md#optimizer-param-tuning`; autotask flips back with the opposite `--state`.

### Kill blocking / runaway session (T2)
- **Trigger**: `blockers.sql` shows a blocker or runaway you have decided to terminate.
- **Precheck**: exactly ONE live USER session matches `--sid` + `--serial`.
- **Action**: `scripts/kill_session.sh --sid N --serial N [--mode IMMEDIATE|MARK] --execute` / `.ps1`.
- **Postcheck**: session is GONE or `STATUS=KILLED`.
- **Rollback note**: not reversible (app reconnects/retries); uncommitted work rolls back. PMON-stuck -> `references/runbooks.md#kill-session-escalation`.

### SQL plan baseline management (T2)
- **Trigger**: pin/unpin/load a plan for a problem SQL (SPM).
- **Precheck**: connection reachable; handle/sql_id/snaps validated per action.
- **Action**: `scripts/baseline_manage.sh --action LOAD_CACHE|LOAD_AWR|ENABLE|DISABLE|FIX|UNFIX ... --execute` / `.ps1` (audit: `sql/baseline_list.sql`).
- **Postcheck**: SQL reports OK/NOOP for the affected baseline.
- **Rollback note**: every supported action is a reversible toggle/load. **DROP is refused** here -> `references/runbooks.md#baseline-drop`.

### SQL Performance Analyzer (T2) & SQL Tuning Advisor run (T2) / review (T1)
- **Trigger**: predict change impact on a SQL tuning set (SPA); get tuning recommendations for a sql_id.
- **Precheck**: license attestation; for SPA, EXPLAIN (no execution) unless `--allow-test-execute` on a test system.
- **Action**: `scripts/spa_run.sh --task T --sts S --trial V --confirm-licensed [--method EXPLAIN] --execute` / `.ps1`; `scripts/sql_tuning_advisor.sh run --sql-id <id> --execute` / `.ps1` (review with `sql_tuning_advisor.sh review`).
- **Postcheck**: SPA trial recorded (`sql/spa_status.sql`); advisor task `STA_<id>` COMPLETED.
- **Rollback note**: both produce analysis/recommendations only — nothing applied. **Applying** any recommendation is T3 -> `references/runbooks.md#sql-tuning-advisor`.

## Tier-3 runbooks

All **plan-changing, instance-affecting, and structural** remediation is Tier 3 and
lives in **`references/runbooks.md`**: `#sql-tuning-advisor` / `#sql-tuning` (apply a
profile/index/baseline), `#baseline-drop`, `#kill-session-escalation`, `#index-strategy`,
`#wait-event`, `#latch-mutex`, `#memory-tuning`, `#optimizer-param-tuning`,
`#histogram-skew`, `#parallel-exec-tuning`, `#realtime-sqlmon`, `#database-replay`,
`#partitioning`.

These runbooks **PRINT exact SQL/PLSQL/OS commands for a human operator and NEVER
self-execute.** They are gated by `require_approval_token` / `Require-ApprovalToken`:
an automated agent's only sanctioned action is to print the relevant section and stop
with `ERR_APPROVAL` (6) until a human supplies a real change ticket/token
(`--token <TICKET>` or `ODB_APPROVAL_TOKEN`; placeholders are rejected, the token is
never logged). The T2 wrappers deliberately refuse the destructive escape hatch
(`baseline DROP`, applying advisor recommendations, structural DDL) and point to the
runbook instead. Each runbook captures a back-out (restore point / saved stats / saved
plan / old parameter value) BEFORE the change and gives an explicit rollback. SYSDBA is
a T3-only, interactively-supplied exception (steps marked `[SYSDBA]`). When in doubt, runbook.

See `references/version-notes.md` for 19c-vs-23ai behavioural deltas.