Diagnosing Slow Stored Procedure Execution in OceanBase: Trace ID Analysis and Plan Cache Issues

Problem description : A business reported that a stored procedure in OceanBase runs slowly and needs investigation. The procedure is long and touches many tables, so the article shares a step‑by‑step analysis method.

1. Find the trace_id of the stored procedure

obclient [oceanbase]> select usec_to_time(request_time),svr_ip,trace_id,query_sql,elapsed_time from gv$sql_audit where query_sql like 'call TEST_CHENG%' order by request_time;

Each execution generates two audit rows: an early pre‑compile record with minimal elapsed time and a later actual execution record with a much larger elapsed time.

2. Analyze the slow SQL statements

Using the trace_id, the slow SQL statements inside the procedure are examined. Several UPDATE statements consume a lot of time, and their IS_HIT_PLAN flag is 0, indicating the plan cache was not hit. Moreover, GET_PLAN_TIME is 0, which is abnormal.

obclient [oceanbase]> select usec_to_time(request_time),query_sql,elapsed_time,execute_time,AFFECTED_ROWS,EVENT,TOTAL_WAIT_TIME_MICRO,WAIT_TIME_MICRO,TOTAL_WAITS,GET_PLAN_TIME,QUEUE_TIME,IS_HIT_PLAN,plan_id,svr_ip from gv$sql_audit where trace_id='YB420ABA3D1F-00060A149CFDF57C-0-0' order by elapsed_time desc limit 6\G

The result shows rows with large elapsed_time (e.g., 2,362,075 µs) while GET_PLAN_TIME remains 0, confirming that the execution plan was not retrieved.

3. Examine procedure execution details

The procedure modifies system views such as __all_tenant_dependency during PL object compilation, generating additional audit rows that can be filtered by tenant_id . More importantly, a TRUNCATE TEST1 statement appears before the costly UPDATE s.

The whole procedure’s plan cache cannot be hit, forcing recompilation on every call.

The plan cache for SQL statements that operate on the truncated table also becomes invalid, requiring a new plan each execution.

obclient [oceanbase]> select substr(query_sql,1,60),usec_to_time(request_time),elapsed_time,svr_ip from gv$sql_audit where trace_id='YB420ABA3D1F-00060A149CFDF57C-0-0' order by request_time;

4. Manual execution of the slow UPDATE

When the UPDATE hits the plan cache, it finishes in a few milliseconds. If the plan cache is missed (e.g., after a preceding TRUNCATE ), execution time jumps to over 300 ms and GET_PLAN_TIME rises to several hundred milliseconds.

obclient [oceanbase]> select usec_to_time(request_time),query_sql,elapsed_time,GET_PLAN_TIME,IS_HIT_PLAN from gv$sql_audit where query_sql like 'update "CC"."TEST1" "T" set "T"."CITY"%' and GET_PLAN_TIME>0 order by request_time\G

Observation: Even when the stored‑procedure SQL does not hit the plan cache, gv$sql_audit still shows GET_PLAN_TIME: 0 , and the time is accounted for in execute_time . In normal manual execution, a missing plan cache would produce a noticeable GET_PLAN_TIME value.

Conclusion

The TRUNCATE operation invalidates the plan cache for subsequent UPDATE statements, causing the stored procedure to run slowly.

Solution

Replace the TRUNCATE inside the procedure with a DELETE statement, allowing the UPDATE statements to reuse the cached execution plan and restoring performance.