# oceanbase-monitoring

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 Monitoring and Auditing

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| View Session Statistics | Synchronous | Retrieve performance statistics for database sessions using gv$sesstat. |
| Query System Statistics | Synchronous | Retrieve comprehensive system-level performance metrics from gv$sysstat or v$sysstat. |
| View Session Wait Events | Synchronous | Access historical session wait event data for troubleshooting via gv$session_wait_history. |
| Monitor Slow Queries | Synchronous | Track and analyze slow SQL query execution using gv$sql_monitor. |
| Analyze Slow Query Performance | Synchronous | Examine detailed performance metrics for slow queries via gv$sql_plan_monitor. |
| Query SQL Traffic Distribution and QPS | Synchronous | Monitor SQL query traffic distribution and queries per second (QPS) metrics. |
| Query SQL Execution Statistics | Synchronous | Retrieve detailed statistics about SQL statement execution performance from v$sql or v$sql_audit. |
| Monitor Slow SQL Execution | Synchronous | Track and analyze slow-running SQL statements using v$sql_monitor. |
| View SQL Execution Statistics | Synchronous | Access detailed statistics about SQL statement execution plans via gv$sql_plan_statistics or v$sql_plan_statistics. |
| Query SQL Workarea Statistics | Synchronous | Retrieve histogram data for SQL workarea memory usage from gv$sql_workarea_histogram. |
| Monitor SQL Workarea Usage | Synchronous | Track memory usage and activity in SQL workareas for complex operations using v$sql_workarea_active. |
| Query SQL Audit Statistics | Synchronous | Access detailed audit information for SQL statement execution via gv$sql_audit. |

## API Calling Mode

### Authentication
No authentication is required beyond standard database connection credentials. These monitoring views are queried directly via SQL using an existing database client connection (e.g., obclient, JDBC, ODBC). Ensure your database user has sufficient privileges (typically `SELECT` on system views, often granted via roles like `MONITOR` or `DBA`).

- **Header format**: Not applicable (SQL-based access)
- **Environment variable**: Not applicable — use standard database connection parameters (`host`, `port`, `user`, `password`, `database`)
- **Note**: Some views (e.g., `gv$sql_audit`) are tenant-specific and may only be accessible to the system tenant or users with elevated privileges.

### Service Endpoint
Queries are executed directly against your OceanBase database instance. There is no separate HTTP API endpoint.

- Use your standard database connection string (e.g., `mysql -h<observer_ip> -P<sql_port> -u<user>@<tenant> -p`)
- Common ports: SQL port is typically `2881` or `2883` (depending on deployment)
- Regions: Not applicable — connect to your specific OceanBase cluster endpoint

### Synchronous Query Pattern
All monitoring data is accessed via synchronous SQL queries against system views:

1. Establish a standard database connection to your OceanBase instance
2. Execute a `SELECT` statement against the relevant system view (e.g., `SELECT * FROM gv$sysstat;`)
3. Process the result set immediately — responses are returned synchronously
4. Apply filters (e.g., by `TENANT_ID`, `SQL_ID`, time range) as needed for targeted diagnostics

Example flow:
```sql
-- Connect via obclient or MySQL client
obclient -h192.168.1.10 -P2881 -uroot@sys -p

-- Query system statistics
SELECT NAME, VALUE FROM gv$sysstat WHERE NAME LIKE '%cpu%';
```

## Parameter Reference

### Query SQL Traffic Distribution and QPS

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| zone | string | false | null | — | The zone where the server is located. |
| svr_ip | string | false | null | — | The IP address of the server. |
| RPC_COUNT | integer | false | null | — | The count of RPC calls. |
| elapsed_time | float | false | null | — | The average elapsed time of the SQL execution. |
| queue_time | float | false | null | — | The average queue time before execution. |

## Code Examples

### Query SQL Traffic Distribution and QPS - SQL - all

```sql
obclient>SELECT/*+ PARALLEL(15)*/t2.zone, t1.svr_ip,  COUNT(*) AS RPC_COUNT,
      AVG(t1.elapsed_time), AVG(t1.queue_time)  
      FROM oceanbase.gv$sql_audit t1, __all_server t2  
      WHERE t1.svr_ip = t2.svr_ip 
      AND tenant_id = 1001
      AND SQL_ID = 'BF7AA13A28DF50BA5C33FF19F1DBD8A9'
      AND IS_EXECUTOR_RPC = 0    
      AND request_time > (time_to_usec(now()) - 1000000)    
      AND request_time < time_to_usec(now())
      GROUP BY t1.svr_ip;
```

### View Session Statistics - SQL - all

```sql
-- Query session-level statistics across all OBServers
SELECT SID, CON_ID, SVR_IP, STATISTICS#, VALUE 
FROM gv$sesstat 
WHERE VALUE > 0 
LIMIT 10;
```

### Analyze Slow Query Performance - SQL - all

```sql
-- Get detailed plan-level metrics for slow queries
SELECT SQL_ID, PLAN_OPERATION, OUTPUT_ROWS, ELAPSED_TIME, WORKAREA_MEM
FROM gv$sql_plan_monitor
WHERE SQL_EXEC_START > NOW() - INTERVAL 1 HOUR
ORDER BY ELAPSED_TIME DESC
LIMIT 5;
```

### Monitor SQL Workarea Usage - SQL - all

```sql
-- Check active workareas consuming significant memory
SELECT sql_id, operation_type, actual_mem_used, tempseg_size
FROM v$sql_workarea_active
WHERE actual_mem_used > 1048576  -- > 1MB
ORDER BY actual_mem_used DESC;
```

### Query System Statistics - SQL - all

```sql
-- Retrieve key system metrics (CPU, I/O, network)
SELECT NAME, VALUE 
FROM gv$sysstat 
WHERE NAME IN ('cpu used', 'physical read total bytes', 'physical write total bytes', 'net packets received');
```

### View Session Wait Events - SQL - all

```sql
-- Identify recent session wait events for troubleshooting
SELECT SID, EVENT, WAIT_TIME_MICRO, TIME_SINCE_LAST_WAIT_MICRO
FROM gv$session_wait_history
WHERE WAIT_TIME_MICRO > 100000  -- > 100ms waits
ORDER BY WAIT_TIME_MICRO DESC;
```

### Query SQL Audit Statistics - SQL - all

```sql
-- Audit recent slow queries (> 1s execution)
SELECT SQL_ID, QUERY_SQL, ELAPSED_TIME, USER_NAME, CLIENT_IP
FROM gv$sql_audit
WHERE ELAPSED_TIME > 1000000  -- > 1 second
  AND REQUEST_TIME > time_to_usec(now()) - 3600000000  -- last hour
ORDER BY ELAPSED_TIME DESC
LIMIT 10;
```

### View SQL Execution Statistics - SQL - all

```sql
-- Get execution plan statistics for high-load SQL
SELECT SQL_ID, EXECUTIONS, AVG_EXE_USEC, DISK_READS, BUFFER_GETS
FROM v$sql
WHERE EXECUTIONS > 100
  AND AVG_EXE_USEC > 500000  -- > 500ms avg
ORDER BY (EXECUTIONS * AVG_EXE_USEC) DESC
LIMIT 5;
```

## Response Format

**Key Fields**:
- `SID` — Session identifier for tracking user connections
- `CON_ID` — Tenant ID for multi-tenant isolation
- `SVR_IP` / `SVR_PORT` — Observer server location
- `SQL_ID` — Unique identifier for normalized SQL statements
- `ELAPSED_TIME` — Total execution time in microseconds
- `WAIT_TIME_MICRO` — Time spent waiting for resources
- `OUTPUT_ROWS` — Number of rows returned by operator
- `DISK_READS` — Physical I/O operations performed
- `WORKAREA_MEM` — Memory allocated for complex operations (sorts, joins)
- `RPC_COUNT` — Number of remote procedure calls in distributed execution

## Error Handling

| Error Code | Description | Recommended Action |
|------------|-------------|-------------------|
| 400 | Invalid SQL syntax or malformed query parameters. | Verify SQL syntax and parameter types; ensure view names are correct (e.g., `gv$sysstat` not `gv_sysstat`). |
| 404 | Requested tenant or SQL ID not found in audit logs. | Confirm tenant exists and is active; check if audit logs cover the requested time window. |
| 500 | Internal server error during query execution. | Retry the query; if persistent, check Observer logs for system errors. |

### Rate Limits & Retry
- **Rate limit**: 10 QPS per user session for audit-related queries (e.g., `gv$sql_audit`)
- **Retry strategy**: For 429/500 errors, implement exponential backoff (start with 1s delay, max 3 retries)
- **Note**: Most system views (`gv$sysstat`, `v$sql`, etc.) have no explicit rate limits but are subject to general database resource constraints

## Environment Requirements
- **Database client**: Any MySQL-compatible client (obclient, MySQL CLI, JDBC, etc.)
- **Privileges**: `SELECT` access on system views (typically granted via `MONITOR` or `DBA` role)
- **OceanBase version**: 4.x or later (view availability may vary by version)

## FAQ

Q: Do I need special credentials to access these monitoring views?
A: No additional credentials beyond standard database login are required, but your user must have sufficient privileges (e.g., `SELECT` on system views). System views like `gv$sql_audit` may require system tenant access.

Q: Why am I getting empty results from gv$sql_monitor?
A: `gv$sql_monitor` only tracks queries that exceed the slow query threshold (default: 100ms). Adjust the `_enable_sql_operator_dump` or `_trace_log_slow_query_time` parameters if needed, or check longer time windows.

Q: How do I correlate data across different views (e.g., link v$sql_audit to v$sql_plan_monitor)?
A: Use common identifiers: `SQL_ID` + `PLAN_HASH_VALUE` links execution metadata to plan details, while `TRACE_ID` connects distributed execution fragments across servers.

Q: Are there performance impacts from querying these system views?
A: Minimal impact for occasional queries. Avoid frequent polling of large views (e.g., `gv$sql_audit` without filters) as they scan in-memory structures. Always filter by time range or specific IDs when possible.

Q: What's the difference between gv$ and v$ prefixed views?
A: `gv$` views return cluster-wide data across all OBServers, while `v$` views show data only from the current OBServer you're connected to. Use `gv$` for comprehensive diagnostics in distributed deployments.

## Pricing & Billing

### Billing Model
Free — included with OceanBase database subscription. No additional charges for querying system monitoring views.

### Free Tier
Included with OceanBase subscription; no additional cost for using any monitoring views (gv$sesstat, gv$sql_audit, etc.).

### Usage Limits
- **Audit queries**: 1000 queries/day free tier for SQL traffic analysis (applies only to custom audit queries, not direct view access)
- **General views**: No specific quota limits; subject to general system resource constraints

### Billing Notes
These system views are part of OceanBase's core monitoring infrastructure and do not incur additional billing costs. Failed queries (e.g., syntax errors) are not billed.