How a Veteran DBA Built the DPM Database Performance Analysis Platform

DBA Core Responsibilities

Ensure data safety through backup and disaster‑recovery strategies.

Maintain database availability and uptime.

Perform routine fault handling and problem diagnosis.

Conduct performance analysis and tuning.

Manage database upgrades, patching, and architectural improvements.

Common Operational Scenarios and Technical Solutions

Inspection Report Automation – Monthly or holiday inspections can produce reports up to 65 pages. The OraZ tool (used by DPM) performs deep automated inspections of system configuration, CRS, OS, patches, etc., and generates a concise health score with actionable recommendations.

Standardized Diagnostic Data Collection – For Oracle support cases a complete set of trace, alert, and OS logs is required. Typical files include:

RAC: each node's Alert.log, LMS[0|9] trace, LCK trace, LMON trace, LMD0 trace, DIAGA trace, and any trace files referenced in the alert log.

Non‑RAC: single‑instance Alert.log and referenced trace files.

OS logs: /var/log/messages (Linux), /var/adm/messages (Solaris), /var/adm/syslog/syslog.log (HP‑UX), /var/adm/messages (Tru64), errpt (AIX), Windows Event logs.

The racdiag.sql script automates collection of Hang Analyze traces, system state dumps, and other diagnostics. Example commands:

oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 267
sqlplus "/ as sysdba"
@racdiag.sql

These commands may impact performance due to long‑running dumps.

Performance Issue Diagnosis – Gather ASH, AWR, OS, RDA, Statspack, and process dump data. Pay special attention to internal errors such as ORA‑600, ORA‑7445, and instance crashes. Short‑duration incidents require high‑frequency AWR snapshots; longer incidents benefit from extended AWR series.

Lock (TX, TM, DX) Emergency Handling – Identify lock holders and waiters with the following query:

column event format a30
column sess format a20
set linesize 250
set pagesize 0
break on id1 skip 1
select decode(request,0,'Holder:','Waiter:') || s.inst_id || ':' || s.sid||','|| s.serial# sess,
       id1, id2, lmode, request, l.type, ctime, s.username, s.sql_id, s.event
from gv$lock l, gv$session s
where (id1, id2, l.type) in (select id1, id2, type from gv$lock where request>0)
  and l.sid = s.sid
  and l.inst_id = s.inst_id
order by id1, ctime desc, request;
/

Repeated execution helps pinpoint persistent blockers; the DBA can then decide to terminate offending sessions.

SQL Optimization Workflow – A nine‑step process to improve SQL performance:

Define optimization goals (reduce response time, increase throughput, improve load capacity).

Examine execution plans using EXPLAIN, AUTOTRACE, DBMS_XPLAN, or 10046/10053 traces.

Verify that statistics are up‑to‑date (DBMS_STATS for tables, indexes, partitions, columns).

Review access structures (indexes, partitions, table fragmentation, data skew).

Check optimizer parameters (e.g., optimizer_mode, optimizer_index_cost_adj, optimizer_dynamic_sampling, _optimizer_mjc_enabled, _optimizer_cost_based_transformation, hash_join_enabled).

Leverage new optimizer features (ACS, automatic serial direct path, extended statistics, result cache) while being aware of known bugs.

Identify SQL coding issues (unnecessary UNION, complex subqueries, misuse of functions).

Recognize optimizer limitations (missing statistics, inability to transform certain joins).

Consider design improvements (application scheduling, proper schema design, appropriate indexing).

Typical remedies include creating missing indexes, refreshing statistics, and eliminating costly operations.

DPM (Database Performance Management) Platform Capabilities

One‑click deep health checks covering configuration, performance, security, and clustering (150+ built‑in metrics).

Automated report generation with Word export.

Slow‑SQL identification based on CPU time, execution count, I/O reads, and wait times.

Performance trend visualization with drill‑down to top‑consuming SQL, sessions, and events.

Object‑level statistics highlighting stale statistics.

One‑click optimization suggestions with estimated improvement percentages.

Integration with automation tools, APM solutions, and big‑data log analysis platforms.

Technical Q&A Highlights

Supported databases: Oracle 10g and above; MySQL and DB2 are also supported.

OraZ vs. DPM: OraZ is a community‑level tool focused on ASH data collection; DPM is an enterprise B/S platform offering deeper analytics, broader metric coverage, and integration capabilities.

Lock‑analysis workflow: Use the provided lock query to locate holders, verify persistence, and optionally terminate sessions.

Performance monitoring: DPM’s log‑analysis module applies big‑data techniques to extract real‑time backend log alerts and present them in a searchable UI.