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

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.