# oceanbase-query

Part of **OCEANBASE**

<!-- intent-backlink:auto -->

> 💡 **Path Selection**: This skill is one implementation path for [Optimize a slow-running SQL query](../../intent/oceanbase-optimize-query/SKILL.md). If you're unsure which path to take, check the routing skill first.

# OceanBase SQL Optimization and Performance Console Guide

## Operations Overview

| Operation | Console Entry | Prerequisites | Description |
|----------|---------------|---------------|-------------|
| View Execution Plan | Documentation > SQL Reference > Query Optimization > View the shape of an execution plan and analyze the plan | SQL query knowledge, access to OceanBase instance, understanding of indexing | Examine a query's execution plan structure using EXPLAIN or real-time display |
| Analyze Distributed Query Plan | Documentation > Query Optimization > Analyze Distributed Plans | Privileges to query gv$sql_audit and gv$plan_cache_plan_stat, knowledge of distributed processing | Investigate RPC timing and correctness in distributed execution plans |
| Find Full Table Scan SQL Statements | Console > Tenant Management > Query Monitoring | Admin access to OceanBase, known tenant ID (e.g., 1001) | Identify SQL statements performing full table scans using gv$sql_audit |
| Analyze Query Wait Events | OceanBase Documentation > SQL Audit > Analyze Wait Events | SQL Audit enabled, access to v$sql_audit view, basic SQL knowledge | Diagnose performance bottlenecks by examining wait event durations |

## Operation Steps

### View Execution Plan

**Navigation**: Documentation > SQL Reference > Query Optimization > View the shape of an execution plan and analyze the plan

**Prerequisites**:
- SQL query knowledge
- Access to OceanBase database instance
- Understanding of indexing and query execution concepts

1. Open the OceanBase documentation portal at **https://www.oceanbase.com/docs/**
   - Element: **Documentation** (link) — top navigation bar

2. Navigate to **SQL Reference > Query Optimization**
   - Element: **Query Optimization** (link) — left sidebar menu

3. Click on the article titled **"View the shape of an execution plan and analyze the plan"**
   - Element: **View the shape of an execution plan and analyze the plan** (link) — content list

4. Review the EXPLAIN syntax examples and execution plan output formats (BASIC, EXTENDED, PARTITIONS)
   - Element: **EXPLAIN statement examples** (section) — main content area

5. Use the provided guidance to interpret operators like **TABLE SCAN**, **HASH JOIN**, **SORT**, and **AGGREGATION**
   - Notes: The plan is displayed in a tree-like structure showing estimated rows, cost, and access paths

**Form Fields**: —

### Analyze Distributed Query Plan

**Navigation**: Documentation > Query Optimization > Analyze Distributed Plans

**Prerequisites**:
- Access to OceanBase database with sufficient privileges to query gv$sql_audit and gv$plan_cache_plan_stat views
- Knowledge of SQL query execution and distributed processing concepts

1. Go to **https://www.oceanbase.com/docs**
   - Element: **Documentation** (link) — header

2. Browse to **Query Optimization > Analyze Distributed Plans**
   - Element: **Analyze Distributed Plans** (link) — left navigation panel

3. Locate the example SQL query using **trace_id** to filter gv$sql_audit
   - Element: **SQL code block** (section) — documentation body

4. Note the key columns: **sql_id**, **is_executor_rpc**, and **elapsed_time**
   - Notes: Rows with **is_executor_rpc = 0** represent the coordinator; others are remote RPC executions

5. Use the **trace_id** from your query log to substitute into the example query
   - Element: **trace_id placeholder** (text_input) — within SQL example

**Form Fields**: —

### Find Full Table Scan SQL Statements

**Navigation**: Console > Tenant Management > Query Monitoring

**Prerequisites**:
- Access to the OceanBase database with administrative privileges
- Knowledge of the tenant ID (e.g., 1001) for which you want to analyze queries

1. Launch **obclient** and connect to your OceanBase cluster and target tenant
   - Element: **obclient** (text_input) — terminal/command line
   - Notes: Ensure connection parameters (host, port, user, password) are correctly configured

2. In the query editor, enter the following SQL (replace `1001` with your actual tenant ID):
   ```sql
   SELECT query_sql, table_scan FROM oceanbase.gv$sql_audit WHERE tenant_id = 1001 AND table_scan = 1;
   ```
   - Element: **Run Query** (button) — query editor toolbar
   - Notes: Only rows with **table_scan = 1** indicate full table scans

3. Execute the query by clicking **Run Query**
   - Element: **Run Query** (button) — right side of query editor
   - Notes: A result table appears showing problematic SQL statements

**Form Fields**: —

### Analyze Query Wait Events

**Navigation**: OceanBase Documentation > SQL Audit > Analyze Wait Events

**Prerequisites**:
- SQL Audit must be enabled in the OceanBase database
- Access to the v$sql_audit system view
- Basic knowledge of SQL querying

1. Connect to OceanBase using **obclient** via command line
   - Element: **obclient** (text_input) — terminal
   - Notes: Verify connectivity before proceeding

2. In the SQL editor, run a query against **v$sql_audit** using a known **TRACE_ID**:
   ```sql
   SELECT EVENT, WAIT_TIME_MICRO, TOTAL_WAIT_TIME_MICRO FROM v$sql_audit WHERE TRACE_ID = 'YB420AB74FC6-00056349D323483A';
   ```
   - Element: **SELECT ... FROM v$sql_audit** (text_input) — SQL editor
   - Notes: Replace the trace ID with one from your application logs or monitoring system

3. Examine the result set for high values in **WAIT_TIME_MICRO**
   - Element: **EVENT** (field) — result table column
   - Notes: Common wait events include **APPLICATION_WAIT_TIME**, **USER_IO_WAIT_TIME**, and **CONCURRENCY_WAIT_TIME**

4. Identify the dominant wait event type to guide further tuning actions
   - Element: **result table** (table_row) — output panel

**Form Fields**: —

## FAQ

Q: Where can I find the execution plan for my SQL query in the OceanBase console?
A: OceanBase does not provide a built-in graphical execution plan viewer in its management console. You must use the **EXPLAIN** SQL command via **obclient** or refer to documentation examples for plan interpretation.

Q: Do I need special permissions to query gv$sql_audit or v$sql_audit?
A: Yes. You typically need administrative or monitoring roles that grant SELECT access to OceanBase system views like **gv$sql_audit**, **v$sql_audit**, and **gv$plan_cache_plan_stat**.

Q: Can I modify an execution plan directly in the console?
A: No. Execution plans are generated automatically by the optimizer. To influence the plan, you must adjust SQL syntax, add hints (e.g., /*+ USE_INDEX(...) */), create indexes, or update table statistics.

Q: How do I get the trace_id for a slow query?
A: Enable SQL Audit, then query **gv$sql_audit** for your slow SQL. The **trace_id** column contains the unique identifier needed for distributed plan analysis.

Q: Are full table scans always bad?
A: Not necessarily. For small tables or queries retrieving most rows, a full table scan may be optimal. However, they often indicate missing indexes on large tables and should be reviewed during performance tuning.

## Pricing & Billing

### Billing Model
Execution plan analysis, SQL Audit, and query diagnostics are **free** features included in all OceanBase editions. They are part of core database functionality and do not incur additional usage-based charges.

### Free Tier
No explicit free tier is defined because these features are bundled with standard OceanBase licensing and deployment models (on-premises, private cloud, or managed service).

### Billing Notes
- Query auditing consumes storage based on retention settings; long-term archival may involve external storage costs.
- Diagnostic operations themselves (e.g., running EXPLAIN or querying gv$sql_audit) generate no direct billing events.
- Resource usage (CPU, I/O) from diagnostic queries follows standard database consumption patterns but is typically negligible.