observer-sql-analysis

To diagnose a slow SQL query in OceanBase using a trace id, you need a standard SOP that analyzes performance, execution plans, trace packages, and lock waits. This process correlates trace data with observer logs to uncover the root cause of the query latency.

Execution plan analysis helps find the root cause of a slow SQL statement by retrieving the query plan and monitoring data. Examining the plan alongside lock wait information reveals performance bottlenecks and explains why the specific query is underperforming.

Yes, you can use obdiag-performance and observer-log-analysis for single SQL diagnostics. They integrate directly into the diagnostic workflow to collect trace data, gather plan monitors, and analyze observer logs for a targeted query investigation.

Yes, you need access to observer and cluster views to analyze a SQL execution plan. Accessing these views is required to run db_query for EXPLAIN, gather plan monitors, and retrieve the lock wait information needed for a complete diagnosis.

The best way to gather plan monitor data for an OceanBase query is to run a targeted diagnostic SOP against a specific sql_id or trace_id. This retrieves the execution plan, trace packets, and lock information, presenting the results in a structured format.

Your OceanBase SQL execution hangs due to locking waits when concurrent transactions block each other. Analyzing the trace data and lock wait information for the specific query identifies the blocking sessions and reveals the root cause of the hang.