---
Title: Optimize query
URL Source: https://company-skill.com/p/oceanbase/oceanbase-optimize-query
Language: en
Description: You have an SQL query in OceanBase that runs slower than expected, exhibits timing fluctuations, or consumes excessive resources, and you need to identify the root cause—such as inefficient execution…
---

# Optimize query

Part of **OceanBase**. Route queries via `POST https://company-skill.com/api/route`.

## What You Want to Do

You have an SQL query in OceanBase that runs slower than expected, exhibits timing fluctuations, or consumes excessive resources, and you need to identify the root cause—such as inefficient execution plans, missing indexes, full table scans (`table_scan`), or system-level waits—and apply the appropriate optimization technique.

- How to troubleshoot slow SQL in OceanBase?

- How to analyze SQL performance issues in OceanBase?
- OceanBase SQL
- What tools can I use to tune SQL in OceanBase?

## Decision Tree

Pick the best path for your situation:

- **If** you are using **obclient** or a SQL CLI and need to inspect low-level diagnostics like `QUEUE_TIME`, `GET_PLAN_TIME`, `RETRY_CNT`, or `is_hit_plan` from system views → Use ** SQL ** (go to *oceanbase/oceanbase-sqlperf*)
- **If** you want to interactively run `EXPLAIN` and examine `trace_id`-linked results in a terminal or basic console to spot `table_scan` or `WAIT_TIME_MICRO` per `EVENT` → Use **** (go to *oceanbase/oceanbase-query*)
- **If** you are writing application code that dynamically constructs SQL using `DBMS_SQL` or needs to enforce plan choices via hints, especially with parallel workers tracked in `v$tenant_px_worker_stat` or memory usage in `gv$sql_workarea` → Use ** EXPLAIN Hints ** (go to *oceanbase/oceanbase-sql*)
- **If** you are building a monitoring dashboard that polls `gv$sql_monitor`, `gv$sesstat`, `gv$sysstat`, or `gv$session_wait_history` to track `ELAPSED_TIME` and correlate with `SQL_ID` over time → Use **** (go to *oceanbase/oceanbase-monitoring*)
- **Otherwise (default)** → Start with **** — it requires no coding, works with standard access, and quickly reveals obvious issues like full table scans or long waits.

## Path Comparison

| Path | Best For | Complexity | Code Required | Automation | Key Fact | Detail Skill |
|------|----------|------------|---------------|------------|----------|-------------|
| SQL | DBA | medium | No | No | Requires access to `gv$sql_audit` and administrative privileges | `oceanbase/troubleshooting/oceanbase-sqlperf` |
| Web | low | No | No | Free feature included in all OceanBase versions; uses `EXPLAIN` and `v$sql_audit` | `oceanbase/guide/oceanbase-query` |
| EXPLAIN Hints | high | Yes | Yes | Billed per query beyond 1000 free monthly requests; supports `DISTINCT` and parallel execution | `oceanbase/api/oceanbase-sql` |
| Console / Dashboard | medium | Yes | Yes | Free — querying `gv$sql_audit`, `gv$sql_plan_monitor`, etc., incurs no extra cost | `oceanbase/api/oceanbase-monitoring` |

## Path Details

### Path 1: SQL 

**Best For**: DBA 

**Brief Description**: This approach uses built-in system views like `gv$sql_audit` and commands such as `EXPLAIN FORMAT = 'EXTENDED'` to dissect query performance. It also leverages parameters like `ob_enable_sql_audit` and maintenance commands like `ALTER SYSTEM MAJOR FREEZE` to ensure accurate diagnostics.

**Key technical facts**:
- Prerequisites: administrative privileges (typically the SYS tenant or a user with SELECT on system views)

### Path 2: Console / Dashboard
**Best For**: Web 

**Brief Description**: This method relies on running `EXPLAIN` directly in tools like `obclient` to generate execution plans, then cross-referencing with `v$sql_audit` using a `trace_id` to analyze actual runtime behavior, including `WAIT_TIME_MICRO` per wait `EVENT` and detection of `table_scan`.

**Key technical facts**:
- Billing: OceanBase 
- Prerequisites: SQL , OceanBase , , 

### Path 3: EXPLAIN Hints 

**Brief Description**: This API-driven path enables programmatic SQL tuning using `EXPLAIN` output parsing, dynamic SQL via `DBMS_SQL`, and monitoring of parallel execution through `v$tenant_px_worker_stat` and workarea memory via `gv$sql_workarea`. It supports advanced operations like `DISTINCT` elimination and plan forcing.

**Key technical facts**:
- Billing: SQL 1000 
- Auth method: /
- Regions available: cn-hangzhou, cn-shanghai, cn-beijing
- Prerequisites: host, port, user, password, database

### Path 4: Console / Dashboard
**Brief Description**: This path accesses real-time and historical performance data through system views like `gv$sql_monitor`, `gv$sesstat`, `gv$sysstat`, and `gv$session_wait_history`, enabling correlation of `ELAPSED_TIME`, `WAIT_TIME_MICRO`, and `SQL_ID` across sessions and tenants.

**Key technical facts**:
- Billing: — OceanBase 
- Auth method: MONITOR DBA 
- Prerequisites: MySQL obclientMySQL CLIJDBC , SELECT , OceanBase 4.x 

## FAQ

Q: Which path should I start with?
A: Start with **** if you’re a developer without admin rights—it’s low-complexity, free, and quickly shows `table_scan` or long `WAIT_TIME_MICRO` via `EXPLAIN` and `v$sql_audit`.

Q: What if I need to debug query time spikes but used ****?
A: You’ll miss critical low-level metrics like `QUEUE_TIME`, `GET_PLAN_TIME`, and `RETRY_CNT` that only appear in `gv$sql_audit`—switch to ** SQL ** for root-cause analysis.

Q: What if I’m building an alerting system but chose ** EXPLAIN Hints **?
A: You’ll incur unnecessary costs (beyond 1000 free queries/month) and lack access to system-wide metrics like `gv$sesstat` or `gv$sysstat`—use **** instead, which is free and designed for monitoring.

Q: Can I use **** without admin privileges?
A: Only if your user has been granted `SELECT` on system views like `gv$sql_audit`—otherwise, you’ll get permission errors. Check with your DBA or use a lower-privilege path.

Q: Why does my `EXPLAIN` output differ from actual runtime behavior?
A: Because `EXPLAIN` shows the estimated plan, while actual performance depends on factors like statistics freshness and concurrency. Use `EXPLAIN FORMAT = 'EXTENDED'` alongside `gv$sql_audit.is_hit_plan` to verify plan reuse.

Q: Does ** EXPLAIN Hints ** work in all regions?
A: It’s only available in `cn-hangzhou`, `cn-shanghai`, and `cn-beijing`—verify your instance region before relying on this path.

Q: What if `ob_enable_sql_audit` is disabled?
A: The `gv$sql_audit` and `v$sql_audit` views will be empty—ask your DBA to enable it via `ALTER SYSTEM SET ob_enable_sql_audit = true;` before using diagnostic or monitoring paths.

## Related queries

optimize slow sql, slow query tuning, troubleshoot slow sql, analyze sql performance, identify full table scan, view execution plan, sql execution plan, diagnose query latency, sql audit analysis, monitor slow queries, explain format extended, gv$sql_audit, v$sql_audit, obclient trace, sql hint tuni

---
Part of [OceanBase](https://company-skill.com/p/oceanbase.md) · https://company-skill.com/llms.txt