Plan binding|V4.3.5| docs|Distributed Database

Plan binding

Last Updated：2025-07-16 10:33:22 Updated

You can create an outline for an SQL statement to bind a plan.

Before the system goes online, you can directly add hints to the SQL statement to control the optimizer to generate a plan based on the specified behavior.

However, if the optimizer selects a suboptimal plan after the system goes online, you can bind a plan to the SQL statement without modifying the SQL statement. You can use DDL statements to add a set of hints to the SQL statement. The optimizer will generate a more optimal plan for the SQL statement based on the specified hints. This set of hints is called an outline.

Create an outline

OceanBase Database supports creating outlines in two ways: by using SQL_TEXT (the original statement with parameters executed by the user), and by using SQL_ID.

Notice

To create an outline, you must execute the statement in the target database.

Create an outline by using SQL_TEXT

After you create an outline by using SQL_TEXT, a key-value pair is stored in a map. The key is the parameterized text of the bound SQL statement, and the value is the hint bound to the SQL statement. The parameterization principle is described in Quick parameterization.

The syntax for creating an outline by using SQL_TEXT is as follows:

CREATE [OR REPLACE] OUTLINE <outline_name> ON <stmt> [ TO <target_stmt> ];

The preceding syntax has the following parameters:

If you specify OR REPLACE, an existing plan can be replaced.
Generally, stmt is a DML statement with hints and parameters.
If you do not specify TO target_stmt, the optimizer generates an execution plan for the SQL statement based on the hints in stmt if the parameterized SQL statement accepted by the database is the same as the hint-parameterized text of stmt.

If you want to execute a fixed plan for a statement with hints, you must specify TO target_stmt to indicate the original SQL statement.

Here is an example:
```
obclient> CREATE OUTLINE outline1 ON SELECT /*+NO_REWRITE*/ * FROM tbl1 WHERE col1 = 4
AND col2 = 6 ORDER BY 2 TO SELECT  * FROM tbl1 WHERE col1 = 4 AND col2 = 6 ORDER BY 2;
```
Notice

When you use target_stmt, the stmt and target_stmt must be exactly the same after hints are removed.

The following example shows that the optimizer chooses to perform a primary key scan. If the data volume increases, an index scan on idx_c2 will be more optimal. In this case, you can create an outline to bind the index plan to the SQL statement and execute it.

obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX idx_c2(c2));
Query OK, 0 rows affected  

obclient> INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3);
Query OK, 1 rows affected  

obclient> EXPLAIN SELECT * FROM t1 WHERE c2 = 1;

Query Plan:
===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |1        |37  |
===================================
Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 1]),
      access([t1.c2], [t1.c1], [t1.c3]), partitions(p0)

Create an outline for the preceding SQL statement:

obclient> CREATE OUTLINE otl_idx_c2
       ON SELECT/*+ INDEX(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
Query OK, 0 rows affected

Create an outline by using SQL_ID

The syntax for creating an outline by using SQL_ID is as follows:

obclient> CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;

SQL_ID is the SQL_ID of the SQL statement to be bound. You can obtain the SQL_ID by using any of the following methods:

Query the V$OB_PLAN_CACHE_PLAN_STAT view.
Query the GV$OB_SQL_AUDIT view.
Use MD5 to generate the SQL_ID from the original SQL statement. The following script generates the SQL_ID for the corresponding SQL statement.
```
IMPORT hashlib
sql_text='SELECT * FROM t1 WHERE c2 = ?'
sql_id=hashlib.md5(sql_text.encode('utf-8')).hexdigest().upper()
PRINT(sql_id)
```

Create an outline by using SQL_ID:

obclient> CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74'
USING HINT /*+ INDEX(t1 idx_c2)*/ ;

Notice

The hint format is /*+ xxx */. For more information about hints, see Optimizer hints.
An outline created by using SQL_TEXT will override an outline created by using SQL_ID. The priority of an outline created by using SQL_TEXT is higher.
If the SQL statement corresponding to the SQL_ID contains hints, the hints specified when you create the outline will override all hints in the original statement.

Outline data is a set of hint information generated by the optimizer to completely replicate a plan. Outline data starts with BEGIN_OUTLINE_DATA and ends with END_OUTLINE_DATA.

You can obtain outline data by using the EXPLAIN EXTENDED statement. Here is an example:

obclient> EXPLAIN EXTENDED SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;

Query Plan:
| =========================================
|ID|OPERATOR  |NAME      |EST. ROWS|COST|
-----------------------------------------
|0 |TABLE SCAN|t1(idx_c2)|1        |88  |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1(0x7ff95ab37448)], [t1.c2(0x7ff95ab33090)], [t1.c3(0x7ff95ab377f0)]), filter(nil),
      access([t1.c2(0x7ff95ab33090)], [t1.c1(0x7ff95ab37448)], [t1.c3(0x7ff95ab377f0)]), partitions(p0),
      is_index_back=true,
      range_key([t1.c2(0x7ff95ab33090)], [t1.c1(0x7ff95ab37448)]), range(1,MIN ; 1,MAX),
      range_cond([t1.c2(0x7ff95ab33090) = 1(0x7ff95ab309f0)])

Used Hint:
-------------------------------------
  /*+
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------

t1:table_rows:3, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[idx_c2], pruned_index_name[t1]
level 0:
***********
   paths(@1101710651081553(ordering([t1.c2], [t1.c1]), cost=87.951827))

The outline data is as follows:

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */

Outline data is also a hint, and can be used in plan binding. Here is an example:

obclient> CREATE OUTLINE otl_idx_c2
     ON 'ED570339F2C856BA96008A29EDF04C74'
      USING HINT /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
      END_OUTLINE_DATA
  */;
Query OK, 0 rows affected

Verify the creation of an outline

To verify whether an outline is created successfully and as expected, perform the following steps:

Verify whether the outline is created.

Check the DBA_OB_OUTLINES view to see whether an outline with the specified name is created.

obclient> SELECT * FROM DBA_OB_OUTLINES WHERE OUTLINE_NAME = 'otl_idx_c2'\G

*************************** 1. row ***************************
        tenant_id: 1001
      database_id: 1100611139404776
       outline_id: 1100611139404777
    database_name: test
     outline_name: otl_idx_c2
visible_signature: SELECT * FROM t1 WHERE c2 = ?
         sql_text: SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1
   outline_target:
      outline_sql: SELECT /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/*
FROM t1 WHERE c2 = 1

Verify whether a new execution plan is generated for the SQL statement based on the bound outline.

After the SQL statement bound to an outline is executed for a new query, query the outline_id of the plan information of the SQL statement in the GV$OB_PLAN_CACHE_PLAN_STAT view. If the outline_id is the same as the outline_id found in the DBA_OB_OUTLINES view, the execution plan is generated based on the bound outline. Otherwise, the execution plan is not generated based on the bound outline.

obclient> SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA
      FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
       WHERE STATEMENT LIKE '%SELECT * FROM t1 WHERE c2 =%'\G
*************************** 1. row ***************************
      sql_id: ED570339F2C856BA96008A29EDF04C74
     plan_id: 17225
   statement: SELECT * FROM t1 WHERE c2 = ?
  outline_id: 1100611139404777
outline_data: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/

Verify whether the generated execution plan is as expected.

After you verify that the execution plan is generated based on the bound outline, check whether the plan is as expected. You can query the GV$OB_PLAN_CACHE_PLAN_EXPLAIN view to view the cached execution plan shape in the plan_cache column. For more information, see Real-time execution plan display.

obclient> SELECT OPERATOR, NAME FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN
      WHERE TENANT_ID = 1001 AND SVR_IP = '10.XXX.XXX.XXX'
       AND SVR_PORT = 30474 AND PLAN_ID = 17225;

+--------------------+------------+
| OPERATOR           | NAME       |
+--------------------+------------+
|  PHY_ROOT_TRANSMIT | NULL       |
|   PHY_TABLE_SCAN   | t1(idx_c2) |
+--------------------+------------+

Drop an outline

After you drop an outline, the execution plan for the corresponding SQL statement will no longer be generated based on the outline.

The syntax for dropping an outline is as follows:

DROP OUTLINE outline_name;

Notice

To drop an outline, you must specify the name of the database in the outline_name parameter or execute the USE DATABASE statement.

Relationship between plan binding and execution plan cache

After you create an outline by using SQL_TEXT, the key used to search for an outline when an SQL request is generated is the same as the key used by the execution plan cache, which is the parameterized text of the SQL statement.
After an outline is created or dropped, if a new request is generated for the corresponding SQL statement, the execution plan in the execution plan cache is invalidated and updated to the execution plan generated based on the bound outline.

References

Outline view