Proper Creation and Verification of Outlines in OceanBase to Stabilize Execution Plans

To prevent execution plan changes for certain SQL statements, developers often create outlines to bind a specific plan. However, outlines may appear ineffective if not created or verified correctly.

What You Will Learn

How to retrieve the SQL ID.

How to create an outline.

How to correctly verify the outline’s effect.

1. Example Demonstration

In the example, the SQL SELECT count(*) FROM ACT_HI_COMMENT; uses the wrong index and should be forced to use IDX_ACT_HI_COMMENT_02 .

Retrieving the SQL ID

The SQL ID can be obtained from the gv$sql_audit view:

obclient [SYS]> select query_sql,sql_id,svr_ip,plan_id,tenant_id from gv$sql_audit where query_sql like 'SELECT%count%ACT_HI_COMMENT%';
+-------------------------------------------+----------------------------------+------------+---------+-----------+
| QUERY_SQL                                 | SQL_ID                           | SVR_IP     | PLAN_ID | TENANT_ID |
+-------------------------------------------+----------------------------------+------------+---------+-----------+
| SELECT count(*) FROM ACT_HI_COMMENT;      | AC1ED40EC4D5E1A9D75944216745063A | 26.0.8.170 |   99968 |      1001 |
| SELECT count(*) FROM ACT_HI_COMMENT       | 46815AF386F959D17293BCF931FEEAF1 | 26.0.8.170 |   99798 |      1001 |
+-------------------------------------------+----------------------------------+------------+---------+-----------+
2 rows in set (8.695 sec)

The SQL without a trailing semicolon (the second row) is the one issued by the application and should be used for the outline.

Creating the Outline

Use the SQL ID to create the outline under the correct schema (not the sys user):

-- Create outline
conn JTZJGL;
create outline test_outline on '46815AF386F959D17293BCF931FEEAF1' using hint /*+ index(ACT_HI_COMMENT IDX_ACT_HI_COMMENT_02) */;

-- Query outlines
obclient [JTZJGL]> select OUTLINE_ID,DATABASE_NAME,OUTLINE_NAME,OUTLINE_SQL,SQL_ID,OUTLINE_CONTENT from gv$outline;
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
| OUTLINE_ID       | DATABASE_NAME | OUTLINE_NAME | OUTLINE_SQL | SQL_ID                           | OUTLINE_CONTENT                                   |
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
| 1100611139404781 | JTZJGL        | TEST_OUTLINE |             | 46815AF386F959D17293BCF931FEEAF1 | /*+index(ACT_HI_COMMENT IDX_ACT_HI_COMMENT_02) */ |
+------------------+---------------+--------------+-------------+----------------------------------+---------------------------------------------------+
1 row in set (0.005 sec)

Verifying the Effect

Common Misconception

Using EXPLAIN to check the execution plan does not reflect the outline effect; the plan will not change.

obclient [JTZJGL]> explain SELECT count(*) FROM ACT_HI_COMMENT\G
*************************** 1. row ***************************
Query Plan: ===========================================================================
|ID|OPERATOR       |NAME                                 |EST. ROWS|COST |
--------------------------------------------------------------------------
|0 |SCALAR GROUP BY|                                   |1        |89615|
|1 | TABLE SCAN    |ACT_HI_COMMENT(IDX_ACT_HI_COMMENT_01)|210872   |81567|
==========================================================================

Correct Verification Method

Execute the original SQL (without any modifications) using the obclient client, then query gv$plan_cache_plan_stat for the OUTLINE_ID . If the OUTLINE_ID is not -1, the outline is effective, and OUTLINE_DATA will show the hint being used.

-- Execute original SQL
obclient [JTZJGL]> SELECT count(*) FROM ACT_HI_COMMENT;
+----------+
| COUNT(*) |
+----------+
|   210872 |
+----------+
1 row in set (0.110 sec)

-- Check outline effect
obclient [SYS]> select SQL_ID,PLAN_ID,QUERY_SQL,OUTLINE_ID,OUTLINE_DATA from gv$plan_cache_plan_stat where sql_id='46815AF386F959D17293BCF931FEEAF1';
+----------------------------------+---------+-------------------------------------+------------------+---------------------------------------------------------------+
| SQL_ID                           | PLAN_ID | QUERY_SQL                           | OUTLINE_ID       | OUTLINE_DATA                                                 |
+----------------------------------+---------+-------------------------------------+------------------+---------------------------------------------------------------+
| 46815AF386F959D17293BCF931FEEAF1|   99996 | SELECT count(*) FROM ACT_HI_COMMENT | 1100611139404781 | /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "JTZJGL.ACT_HI_COMMENT"@"SEL$1" "IDX_ACT_HI_COMMENT_02") END_OUTLINE_DATA*/ |
+----------------------------------+---------+-------------------------------------+------------------+---------------------------------------------------------------+
1 row in set (0.057 sec)

2. Summary

Always use the SQL ID to create an outline; even a minor change in the SQL text (such as an extra space) will cause the outline to fail.

Obtain the SQL ID from gv$sql_audit ; note that application‑issued SQLs appear without a trailing semicolon.

Create the outline under the business tenant’s schema, not under the sys user.

When verifying an outline, remember that EXPLAIN does not reflect outline usage; instead, run the original SQL in obclient and check gv$plan_cache_plan_stat (or gv$plan_cache_plan_explain ) for a non‑negative OUTLINE_ID and matching hint data.