Analyzing and Resolving Slow Query Plan Issues in OceanBase 3.2.3

The author, a senior database engineer, reports a case where a specific SELECT query on OceanBase 3.2.3 BP8 exhibited execution times more than a thousand times longer than normal.

Problem statement

SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882')
  AND LAWENF_NTIST_TP_CD NOT LIKE '12%'
  AND LAWENF_NTIST_TP_CD NOT LIKE '05%'
  AND EMRG_STPY_SRC_CD != 'JZ05'
  AND ACCTG_DT >= '1900-01-01'
  AND ACCTG_DT <= '2025-03-31';

The table definition (simplified) is shown below:

SHOW CREATE TABLE renzy\G
CREATE TABLE `renzy` (
  `ID` char(18) NOT NULL,
  `ACCT_NO` char(40) NOT NULL,
  `ACCTG_DT` date DEFAULT NULL,
  ...,
  PRIMARY KEY (`ID`),
  KEY `renzy_I2` (`ACCT_NO`) BLOCK_SIZE 16384 LOCAL,
  KEY `renzy_I5` (`ACCTG_DT`,`ENQ_INST_CD`,`BLON_INST_CD`,`EMRG_STPY_SRC_CD`) BLOCK_SIZE 16384 LOCAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Initial investigation

Running the query directly shows a normal execution time (≈0.02 s) and confirms that index renzy_I2 is the optimal choice, requiring only one table‑row lookup.

Query Plan:
|ID|OPERATOR|NAME|EST. ROWS|COST|
|0 |LIMIT   |   |1        |92 |
|1 |SCALAR GROUP BY| |1   |92 |
|2 |TABLE SCAN|renzy(renzy_I2)|1|92 |

Using OceanBase’s OCP SQL diagnostic tool, the plan ID of the slow query is retrieved and examined. Key fields such as query_sql , first_load_time , slowest_exe_usec , outline_id , and statement are highlighted.

Why the first execution used index I5

The execution plan for the first run shows a table scan on renzy_I5 . Because the predicate on ACCTG_DT does not filter any rows (the date range covers the whole table), the optimizer prefers the composite I5 index, which results in a full‑index scan and massive row reads.

EXPLAIN EXTENDED SELECT * FROM (
  SELECT COUNT(*) AS TOT_CNT FROM renzy WHERE (ACCT_NO = '6222620117273900882')
  AND LAWENF_NTIST_TP_CD NOT LIKE '12%'
  AND LAWENF_NTIST_TP_CD NOT LIKE '05%'
  AND EMRG_STPY_SRC_CD != 'JZ05'
  AND ACCTG_DT >= ''
  AND ACCTG_DT <= ''
) AS original LIMIT 2000\G
Query Plan:
|2 |TABLE SCAN|renzy(renzy_I5)|0|92 |

Plan cache behavior and expiration

OceanBase caches query plans to avoid repeated hard parsing. The cached plan is reused until it is considered unstable. The source code (simplified) shows the expiration logic:

// key snippet (410bp1 community edition)
if (sample_count < SLOW_QUERY_SAMPLE_SIZE) {
  // not enough samples, do nothing
} else {
  if (stat_.cpu_time_ <= SLOW_QUERY_TIME_FOR_PLAN_EXPIRE * stat_.execute_times_) {
    // fast query, keep plan
  } else if (is_plan_unstable(sample_count, sample_exec_row_count, sample_exec_usec)) {
    set_is_expired(true);
  }
  ATOMIC_STORE(&(stat_.sample_times_), 0);
}

bool ObPhysicalPlan::is_plan_unstable(const int64_t sample_count,
                                      const int64_t sample_exec_row_count,
                                      const int64_t sample_exec_usec) {
  if (sample_exec_usec > SLOW_QUERY_TIME_FOR_PLAN_EXPIRE * sample_count) {
    if (plan_type_ == OB_PHY_PLAN_LOCAL) {
      int64_t first_query_range_rows = ATOMIC_LOAD(&stat_.first_exec_row_count_);
      if (sample_exec_row_count / sample_count > first_query_range_rows * 10) {
        LOG_INFO("local query plan is expired due to unstable performance", ...);
        return true;
      }
    } else if (plan_type_ == OB_PHY_PLAN_DISTRIBUTED) {
      int64_t first_exec_usec = ATOMIC_LOAD(&stat_.first_exec_usec_);
      if (sample_exec_usec / sample_count > first_exec_usec * 2) {
        LOG_INFO("distributed query plan is expired due to unstable performance", ...);
        return true;
      }
    }
  }
  return false;
}

static const int64_t SLOW_QUERY_SAMPLE_SIZE = 20; // OceanBase 4.1.0 definition

Thus a plan is evicted when:

The query has been executed at least SLOW_QUERY_SAMPLE_SIZE (20) times.

The average scanned rows per execution exceed ten times the row count of the first execution.

Reproduction steps

Flush the plan cache: ALTER SYSTEM FLUSH PLAN CACHE;

Run the original query once (fast execution).

Execute the same query repeatedly (e.g., via a Bash loop) to exceed 20 executions; observe the plan ID remains the same and execution time grows to minutes.

After the 20th execution the plan is marked expired (log shows "local query plan is expired due to unstable performance").

Subsequent executions generate a new plan that uses index renzy_I2 , restoring normal performance.

Log excerpts confirm the expiration condition (sample_exec_row_count ≈ 1.5 billion, sample_count = 20, first_query_range_rows = 0).

Conclusion and recommendations

The slowdown was caused by the optimizer choosing index I5 for the first execution, which became a full‑index scan.

In OceanBase 3.2.3 BP8 there is no automatic fix; recommended actions are to drop the unused I5 index or bind an outline to force the use of I2 .

Plan cache can be cleared manually with ALTER SYSTEM FLUSH PLAN CACHE (tenant‑level or specific SQL) or automatically based on the ob_plan_cache_percentage parameter.

The article ends with a brief note about the "OB 运维派" column, encouraging readers to share their own OceanBase operational experiences.