OceanBase uses a cost-based optimizer with hint syntax similar to Oracle. There are key operational details to note:

• When executing hinted SQL via MySQL client, connect with -c to avoid client-side stripping of hint comments
• Unrecognized server hints are silently ignored instead of throwing errors
• Hints only modify optimizer plan generation logic, not the core SQL statement smeantics

Hint Syntax Rules

OceanBase hints come in two forms: parameterless (/*+ HINT_NAME */) and parameterized (/*+ HINT_NAME(param_val) */). Multiple hints can be grouped in a single comment separated by commas, such as /*+ HINT_ONE, HINT_TWO('val') */. Placement requirements are strict:

• For SELECT, hints must sit immediately after SELECT and before other clause components
• For UPDATE or DELETE, hints must follow the respective command keyword directly

Common Hint Use Cases

• Consistency control: /*+READ_CONSISTENCY(STRONG)*/ or /*+READ_CONSISTENCY(WEAK)*/
• Query timeouts: /*+query_timeout(5000000)*/ (duration specified in microseconds)
• Join enforcement: /*+USE_MERGE(table_a table_b)*/ for sort-merge joins
• Index forcing: /*+INDEX(target_table idx_col_ab)*/
• Statement-level parallelism: /*+PARALLEL(4)*/ overrides the ob_stmt_parallel_degree system variable
• Join ordering: /*+LEADING(table_list)*/ specifies table join sequence, invalidates if any listed table is missing or duplicated

Hint Design Philosophy & Full Supported List

Unlike many databases that use hints to expand optimizer search paths, OceanBase’s dynamic planning approach already explores all feasible options. Thus, OceanBase hints act as hard directives rather than suggestions.

Statement-Level Hints

MAX_CONCURRENT, FROZEN_VERSION, QUERY_TIMEOUT, READ_CONSISTENCY, LOG_LEVEL, QB_NAME, ACTIVATE_BURIED_POINT, TRACE_LOG

Plan-Related Hints

FULL, INDEX, LEADING, USE_MERGE, USE_HASH, USE_NL, ORDERED, NO_REWRITE

Query Throttling with MAX_CONCURRENT

This example uses ? for parameterized query binding:

CREATE TABLE sales_log (log_id INT PRIMARY KEY, region VARCHAR(20), amount DECIMAL(10,2));

CREATE OUTLINE throttle_region_sales ON SELECT/*+max_concurrent(0)*/ * FROM sales_log WHERE region = 'APAC' AND amount = 5000;

-- This exact match query hits the throttle
SELECT * FROM sales_log WHERE region = 'APAC' AND amount = 5000;
-- Returns ERROR 5268 (HY000): SQL reach max concurrent num 0

-- Non-matching query proceeds normally
SELECT * FROM sales_log WHERE region = 'APAC' AND amount = 6000;

-- Create a parameterized outline for broader throttling
CREATE OUTLINE throttle_apac_all_sales ON SELECT/*+max_concurrent(0)*/ * FROM sales_log WHERE region = 'APAC' AND amount = ?;

-- Both parameterized variations now hit the throttle
SELECT * FROM sales_log WHERE region = 'APAC' AND amount = 5000;
SELECT * FROM sales_log WHERE region = 'APAC' AND amount = 6000;

Outline Plan Binding

Outline binding applies pre-defined hints to SQL without modifying application code, ideal for resolving suboptimal post-deployment exeuction plans.

Outline Creation Methods

1. Using raw SQL text:

CREATE [OR REPLACE] OUTLINE <outline_identifier> ON <target_sql> [ TO <reference_sql> ];

2. Using a captured SQL ID:

CREATE OUTLINE outline_identifier ON sql_id USING HINT '/*+ HINT_LIST */';

Outline Deletion

DROP OUTLINE outline_identifier;

Note: Either specify the database name in outline_identifier or run this after a USE <database_name> command.

Outline Validation Checks

1. Verify outline creation success:

SELECT * FROM oceanbase.gv$outline WHERE OUTLINE_NAME = 'outline_identifier';

2. Confirm new SQL uses the bound outline:

SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA 
FROM oceanbase.gv$plan_cache_plan_stat 
WHERE STATEMENT LIKE '%target_sql_snippet%';

3. Validate plan structure matches expectations:

SELECT OPERATOR, NAME FROM oceanbase.gv$plan_cache_plan_explain 
WHERE TENANT_ID = tenant_id_num AND IP = 'host_ip_addr' 
AND PORT = ob_port AND PLAN_ID = plan_id_num;

SQL Plan Management (SPM)

SPM stabilizes performance and controls plan evolution by only deploying validated new execution plans, built on SQL Plan Baselines. Each baseline stores validated plan metadata (icnluding Outline Data) to reproduce exact execution plans.

Core SPM Processes

1. Plan Capture: Newly generated plans are added to an empty baseline immediately. If a baseline exists, the new plan is added only after performance validation, replacing the old one.
2. Plan Evolution: Gray traffic testing compares new captured plans against existing baselines. Only superior-performing plans are promoted and activated.
3. Plan Selection: The optimizer prioritizes validated baseline plans; new unvalidated plans require evolution approval first.

DBMS_SPM System Package

This package manages Plan Baselines:

Load Plans from Cursor Cache

Loads Plan Cache plan metadata into __all_tenant_plan_baseline:

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
  sql_id IN VARCHAR2,
  plan_hash_value IN NUMBER := NULL,
  fixed IN VARCHAR2 := 'NO',
  enabled IN VARCHAR2 := 'YES'
) RETURN PLS_INTEGER;

Alter Plan Baseline Attributes

Modifies specific baseline properties:

DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( 
  sql_handle IN VARCHAR2 := NULL,
  plan_name IN VARCHAR2 := NULL,
  attribute_name IN VARCHAR2,
  attribute_value IN VARCHAR2
) RETURN PLS_INTEGER;

Drop Plan Baseline

Removes a targeted baseline:

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
  sql_handle IN VARCHAR2 := NULL,
  plan_name IN VARCHAR2 := NULL
) RETURN PLS_INTEGER;