Diagnostics

The previous two posts walked through specific investigations. This one is the reference behind them — the Logdump commands worth knowing well, plus the tricks that separate a five-minute trail file check from an hour of scrolling through raw hex. 1. Session Setup: Get Readable Output First The default Logdump view is barely usable — raw hex with minimal structure. Every session should start the same way: Logdump 1 > OPEN ./dirdat/rt000412 Logdump 2 > GHDR ON Logdump 3 > DETAIL DATA Logdump 4 > USETOKEN ON Command What it does Skip it and you get GHDR ON Shows the record header — timestamp, RBA, operation type Records with no context on when/what DETAIL DATA Shows actual column values, decoded Raw hex offsets only DETAIL ON (without DATA) Shows column names/lengths but not values Structure without content — useful when scanning fast USETOKEN ON Decodes GoldenGate’s internal tokens (source DB type, CSN, transaction ID) Tokens print as unreadable hex Trick: if you’re scanning hundreds of records just to find where a table’s activity starts, use DETAIL ON without DATA — it’s faster to read and enough to spot the record you actually want, then flip to DETAIL DATA once you’re near it. ...

When Replicat abends, the error message tells you what failed, but rarely why. INFO REPLICAT and the report file point you at a table and an error code — to actually understand the data that caused it, you need to open the trail file itself with Logdump, GoldenGate’s trail-file inspection utility. This post walks through a real investigation end to end, from the initial abend to the actual root cause sitting inside the raw record bytes. ...

Performance diagnostics without a methodology is archaeology — you dig until you find something interesting, not necessarily something important. Oracle’s AWR and ASH provide an extraordinarily detailed record of database activity, but interpreting them requires a structured approach. This article presents a top-down methodology that moves from macro to micro: DB Time → Wait Profile → Top SQL → Root Cause. The Foundation: DB Time DB Time is the total time all sessions spent working in the database (on CPU or waiting for database resources) during a snapshot period. It is the single most important number in the AWR report. ...

This article is a practical tuning script library. Each script targets a specific, common real-world performance scenario — not abstract examples, but the situations that actually appear at 2am during an incident. Scripts are annotated with where they run, what the output means, and what action to take. Reference Environment Single Instance: Host: ora19c01.prod.example.com DB Name: ORCL19C Oracle: 19.22.0.0 (Oracle Linux 8) Memory: 256 GB RAM, 32 vCPU Storage: Local NVMe (non-Exadata) CDB Environment: Host: cdb01.prod.example.com (Exadata VM) CDB Name: PRODCDB PDBs: PDB_OLTP, PDB_DWH, PDB_REPORTING Oracle: 19.22.0.0 Memory: 512 GB RAM (Exadata X8M-2 node) Scenario 1: “The Database Was Slow from 09:00 to 09:30 This Morning” Situation: Application team reports intermittent slowness on cdb01. You need to identify what happened between 09:00 and 09:30. ...

Oracle SQL Monitor is the most powerful single-query diagnostic tool in the Oracle performance toolkit. Unlike DBMS_XPLAN.DISPLAY_CURSOR, which gives you a static execution plan, SQL Monitor captures real-time runtime statistics at every step of the execution plan — rows processed, memory used, temporary I/O, CPU time, and elapsed time per operation. For parallel queries, it shows the workload distribution across parallel server processes. When SQL Monitor Activates Automatically SQL Monitor begins monitoring a SQL statement automatically when either: ...