User SQL statements do not comply with the development specifications of OceanBase AI Database
The way user SQL statements are written significantly impacts execution performance. When using SQL, you should follow the requirements of the development specifications for OceanBase AI Database as much as possible. For details, see SQL Writing Restrictions.
Incorrect execution plan selection due to cost model defects
The built-in cost model in OceanBase AI Database is an inherent logic of the server, and the optimal execution plan depends on this cost model. Therefore, if an incorrect plan is selected due to defects in the cost model, you can only bind the "correct" execution plan to ensure that it is selected.
Physical database design degrades query performance
Query performance largely depends on the physical design of the database, including the schema information of the accessed objects. For example, for a secondary index, if the required projected columns are not included in the indexed columns, the query must access the primary table, which significantly increases the query cost. In this case, you can add the projected columns to the indexed columns to form a "covering index", thereby avoiding table access by index primary key.
System load affects the response time of individual SQL statements
The overall system load not only affects the system's throughput but also causes variations in the response time of individual SQL statements. The SQL engine of OceanBase AI Database uses a queue-based model. For user requests, if all available threads are occupied, new requests must wait in a request queue until a thread finishes the current request. The queuing time of a request can be viewed in the (G)V$OB_SQL_AUDIT view.
Routing feedback logic error between the client router and the server
A primary function of OBProxy is to route SQL queries to the appropriate server node. Specifically, if a user query does not specify the weak-consistency read attribute, the proxy must route it to the leader node of the involved table (or specific partition) to avoid secondary forwarding between server nodes; otherwise, the proxy forwards it to the appropriate node based on preset rules.
Due to the loose coupling between the proxy and the servers, the cached data on the proxy may not be refreshed promptly, leading to incorrect routing choices. Scenarios that may cause routing information changes include:
Network instability leading to re-election of leaders among servers
Re-election of leaders caused by servers going online/offline, rotating major compactions, etc.
Load balancing leading to re-election of leaders
When a large number of remote executions are found in SQL Audit or the execution plan cache, consider whether they match any of the above scenarios. The routing feedback logic exists between the client and the server, so the client proactively refreshes the physical distribution information of data when an error occurs. Then, routing selection returns to normal.