Why Does the Same SQL Run Faster on Oracle Than on OceanBase? Uncovering Execution Plan Discrepancies

Background

Problem Description

During performance testing a customer found that the same SQL sometimes runs fast and sometimes slow; the slow case is more common, with execution time over 7 seconds on OceanBase, more than twice the Oracle baseline.

Tenant Information

Version: OceanBase (Oracle tenant) 4.2.1.9

Hardware: 24C240G

Replica Distribution: 1‑1‑1

PrimaryZone Distribution: Random (balanced priority)

Slow SQL Statement

select *
from (
    select A.*, RowNum as Fast_RowNum
    from (
        SELECT ...
        FROM TMPAYMENTSETTLEMENT
        LEFT JOIN BFNATIONALANDREGIONALDICT BFNATIONALANDREGIONALDICT ON TMPAYMENTSETTLEMENT.RECEIVINGCOUNTRY = BFNATIONALANDREGIONALDICT.ID
        LEFT JOIN BRENZYRENZYIIIN BRENZYRENZYIIIN ON TMPAYMENTSETTLEMENT.RECIPROCALCITY = BRENZYRENZYIIIN.ID
        ...
        WHERE 1 = 1
          AND (ISTASK = '1' AND ISSHARE = '0' AND GENERATEDTIME = '1' AND BIZTYPE = '1' AND DOCSTATUS in ('1','-2','2','-3','-18') AND DOCSRC in ('1','8','12','15')
               AND (TMPAYMENTSETTLEMENT.FK13 is null OR TMPAYMENTSETTLEMENT.FK13 = 'rzy' OR TMPAYMENTSETTLEMENT.FK14 = 'rzy' OR TMPAYMENTSETTLEMENT.APPLICANT = '3d69999f-43ba-4e3e-9bf0-e7d69991b729'))
          AND PRENZYI in (select distinct aotest from (
                select distinct orgid aotest from gspuserposorg where userId = '3d69999f-43ba-4e3e-9bf0-e7d99999b729' and orgTypeId = 'AdminOrg_Grenzy' and positionId in ('25e9999f-e9c9-4f37-9999-787fceb6da19')
                union all
                SELECT ID aotest from BFAdminOrganization where treeinfo_path like '9999%') User_TM_AdminOrg)
          AND 1 = 1) A
    where RowNum < 1051) where Fast_RowNum > 1000;

Analysis Process

Fast Execution in ODC

Running the query in the ODC environment shows a fast execution time of about 544 ms, even faster than Oracle.

Slow Execution Trace

Trace information from the user side confirms the slow execution.

Execution Plans Comparison

Both fast and slow cases use a distributed execution plan, but the slow plan contains more operators (90 vs 82) and includes EXCHANGE OUT operators that require full‑table scans to be sent across nodes.

Leader Distribution Check

select DATABASE_NAME, TABLE_NAME, TABLE_TYPE, ZONE, SVR_IP, ROLE
from dba_ob_table_locations
where role = 'LEADER' and database_name = 'RENZYTEST1' and table_name in (
    'TMPAYMENTSETTLEMENT','BFNATIONALANDREGIONALDICT','BRENZYRENZYIIIN','BFADMINORGANIZATION','BRENZYRENZYRENZ','BRENZYRENZI','BRENZYRENY','BRENZYFLOWTYPE','BFBUSINESSMATTER','BRENZYACCOUNTS','BRENZY','BFFINANCIALSUBJECTS','BRENZYTESOP','BFACCOUNTINGORGANIZATION','GSPUSERPOSORG','BFADMINORGANIZATION')
order by zone;

The query shows that leaders for some tables (e.g., BFFINANCIALSUBJECTS, BFBUSINESSMATTER) reside on node 55, while TMPAYMENTSETTLEMENT leader is on node 54, causing cross‑node data movement.

Root Cause

The SQL is executed as a distributed plan; depending on which observer node receives the request, the optimizer generates different plans because table leaders are distributed across zones. This leads to inconsistent execution times.

Conclusion and Optimizations

Hint Optimization

Adding a parallel hint, e.g., SELECT /*+ PARALLEL(9) */ ..., leverages distributed parallelism and reduces the slow case from ~7 s to 2‑3 s, though it requires SQL‑level changes.

Table Group Optimization

Creating a table group forces the leaders of all involved tables onto the same observer, eliminating cross‑node exchanges.

# Create table group
CREATE TABLEGROUP tg_sk1 SHARDING = 'NONE';
# Add tables to the group
ALTER TABLEGROUP tg_sk1 ADD TMPAYMENTSETTLEMENT, BFNATIONALANDREGIONALDICT, BRENZYRENZYIIIN, BFADMINORGANIZATION, BRENZYRENZYRENZ, BRENZYRENZI, BRENZYRENY, BRENZYFLOWTYPE, BFBUSINESSMATTER, BRENZYACCOUNTS, BRENZY, BFFINANCIALSUBJECTS, BRENZYTESOP, BFACCOUNTINGORGANIZATION, GSPUSERPOSORG, BFADMINORGANIZATION;

After the table group is applied, all leaders are located on the same node (zone 2), and the execution time stabilises around 400 ms.

Result

Original Oracle execution: ~3 s.

Original OceanBase (Oracle tenant) slow execution: up to 7 s.

After hint: 2‑3 s.

After table‑group: ~0.4 s, with consistent performance across observers.