SQL Trace/TKPROF, Real-Time SQL Monitoring Concepts, ADDM, and Evidence-Based Troubleshooting

Learning outcomes

A ServiceHub endpoint is slow only for one customer and only after a new release. AWR says SQL execution time rose, but the team still does not know where a specific request spent parse/execute/fetch time or which waits occurred inside it. The final escalation layer combines targeted SQL trace and TKPROF for exact traced calls with Real-Time SQL Monitoring and Automatic Database Diagnostic Monitor (ADDM) when their packs are available.

1. Targeted SQL trace is the narrow pack-independent microscope

Oracle SQL trace records parse/execute/fetch calls, resource consumption and optionally waits/binds for traced sessions. The old SQL_TRACE initialization parameter is deprecated for routine tracing; Oracle recommends DBMS_MONITOR/DBMS_SESSION. Tracing an entire production instance can create severe CPU/I/O/disk overhead.

2. Identify the exact ServiceHub session

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE(    module_name => 'SERVICEHUB_API',    action_name => 'GET_WORK_ORDER'  );  DBMS_SESSION.SET_IDENTIFIER('incident-customer-42');END;/SELECT  SYS_CONTEXT('USERENV','SID') AS sidFROM dual;

SELECT  sid,  serial#,  username,  service_name,  module,  action,  client_identifier,  sql_idFROM v$sessionWHERE module='SERVICEHUB_API'  AND action='GET_WORK_ORDER'  AND client_identifier='incident-customer-42';

3. Enable trace for only that session

BEGIN  DBMS_MONITOR.SESSION_TRACE_ENABLE(    session_id => :sid,    serial_num => :serial_no,    waits      => TRUE,    binds      => FALSE,    plan_stat  => 'ALL_EXECUTIONS'  );END;/

Bind capture can expose credentials/personal/business values and increase trace volume. Start with binds=>FALSE; enable bind capture only through an approved incident/security procedure when it is necessary.

4. Run the problematic request, then disable immediately

SELECT work_order_id,status_code,payload_jsonFROM servicehub_owner.work_ordersWHERE work_order_id=:work_order_id;

BEGIN  DBMS_MONITOR.SESSION_TRACE_DISABLE(    session_id => :sid,    serial_num => :serial_no  );END;/

Always record trace start/end timestamps and disable in a finally/incident-close step. A forgotten trace can fill ADR storage.

5. Locate the trace in Automatic Diagnostic Repository

SELECT name,valueFROM v$diag_infoWHERE name IN (  'Diag Trace',  'Default Trace File',  'ADR Home')ORDER BY name;

For a traced remote session, Default Trace File in the observer session is not necessarily the application's trace file. Use the trace directory plus timestamp/process/session/module/client-id criteria; TRCSESS can consolidate/filter traces when necessary.

6. Format with TKPROF

tkprof servicehub_ora_12345.trc sh22_tkprof.txt   sort=exeela,fchela,prsela   sys=no

TKPROF reports parse/execute/fetch counts, CPU/elapsed, disk/query/current gets, rows and waits from the trace. If a cursor did not close, TKPROF may not contain its actual execution plan; use runtime DBMS_XPLAN.DISPLAY_CURSOR where the cursor is still available rather than generating an unrelated EXPLAIN PLAN.

7. Real-Time SQL Monitoring is a Tuning Pack feature

Real-Time SQL/PLSQL Monitoring provides live/finished execution-level plan-line progress, elapsed/CPU/wait/I/O and parallel-server information for monitored statements. Current licensing puts it in Oracle Tuning Pack, which is included in Free but extra-cost on EE/EE-ES and also requires Diagnostics Pack there.

SELECT /*+ MONITOR */       COUNT(*)FROM servicehub_owner.work_ordersWHERE status_code='OPEN';SELECT  sql_id,  sql_exec_id,  status,  username,  module,  sql_exec_start,  elapsed_time,  cpu_time,  user_io_wait_time,  concurrency_wait_timeFROM v$sql_monitorWHERE module='SERVICEHUB_API'ORDER BY sql_exec_start DESC;

The MONITOR hint requests monitoring; it does not grant a license. Do not query/use SQL Monitor on an EE/EE-ES system without Tuning Pack entitlement.

8. ADDM analyzes AWR intervals

Automatic Database Diagnostic Monitor (ADDM) analyzes pairs of AWR snapshots and identifies high-impact findings/recommendations. It belongs to Diagnostics Pack. On a single-instance database it analyzes that instance; in RAC it can operate in database/instance/partial modes.

SELECT  owner,  task_name,  execution_name,  finding_id,  finding_name,  type,  impact,  messageFROM dba_addm_findingsORDER BY impact DESCFETCH FIRST 20 ROWS ONLY;

ADDM requires CONTROL_MANAGEMENT_PACK_ACCESS to include DIAGNOSTIC and STATISTICS_LEVEL to be TYPICAL/ALL. Manually running ADDM APIs requires appropriate ADVISOR privilege.

9. Deliberately wrong: execute every ADDM recommendation automatically

An ADDM finding is based on measured database impact but cannot know all business semantics, deployment constraints, licensing, maintenance windows or application-side causality. For example, “high User I/O” can originate from an inefficient SQL plan; increasing storage performance may hide rather than fix the plan. Validate the recommended mechanism with plan/wait/SQL/application evidence and test rollback.

10. One evidence-based incident ladder

11. Trace is not free

Wait/bind/plan-stat tracing writes diagnostic files and adds work. Scope it to one session/module/client ID, use short windows, monitor ADR disk, and redact/protect trace artifacts. Do not attach raw production traces containing sensitive SQL/binds to public tickets.

12. Tool and OS/container considerations

• SQLcl/SQL*Plus: enable/disable trace, spool reports and query dynamic views.
• TKPROF/TRCSESS: Oracle client/server command-line utilities; need filesystem access to trace files.
• Containerized Free: ADR paths are inside the database container unless volume/mount/exec access is provided.
• SQL Developer 26.2: optional GUI; not required by the chapter.
• Enterprise Manager/OCI: can expose pack features but UI availability does not replace licensing checks.

13. Cleanup/close incident instrumentation

BEGIN  DBMS_APPLICATION_INFO.SET_ACTION(NULL);  DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL);  DBMS_SESSION.CLEAR_IDENTIFIER;END;/

Verify no unintended tracing remains and archive/delete trace output under the organization's diagnostic retention policy.

14. Production judgment and chapter close

Use the narrowest tool that answers the question. Live V$ views and targeted trace are the safe default when licensing is uncertain. AWR/ASH/ADDM are powerful for historical/statistical diagnosis when Diagnostics Pack is included/licensed. SQL Monitor provides execution-level visibility when Tuning Pack is included/licensed. Never tune by folklore, one top wait, one advisor finding or one unrepeatable benchmark.

Current baseline: Oracle AI Database 26ai RU 23.26.3; Diagnostics Pack and Tuning Pack are included in Free and extra-cost on EE/EE-ES. CONTROL_MANAGEMENT_PACK_ACCESS is dynamic and non-PDB-modifiable; it is not entitlement proof. SQL trace itself needs no management pack; SQL_TRACE parameter is deprecated in favor of DBMS_MONITOR/DBMS_SESSION. No COMPATIBLE change or restart is required. The next chapter can build on this evidence discipline for migration/upgrade/patching and other production operations.

Authoritative references

• Performing Application Tracing — DBMS_MONITOR/trace/TKPROF workflow
• SQL_TRACE — deprecated parameter/overhead warning
• Automatic Performance Diagnostics — ADDM setup/analysis
• Monitoring Database Operations — Real-Time SQL Monitoring concepts
• Licensing Information — Diagnostics/Tuning licensed APIs/views/features