Plan binding

Before you launch an application system, you can directly add hints to SQL statements of the application and control the optimizer to generate plans based on the specified hints.

For an application that you already launched, if a plan selected by the optimizer is not optimal for an SQL statement of the application, you need to bind an optimal plan to this SQL statement online. This means that you need to add a set of hints to this SQL statement by using DDL operations, instead of modifying the SQL statement. Then, the optimizer generates an optimal plan for the SQL statement based on the specified hints. The set of hints is referred to as an outline. You can create an outline for an SQL statement to bind a plan to it.

Outline view - gv$outline

The outline view is named gv$outline. Fields of the view are described in the following table.

Create an outline

OceanBase Database supports creating an outline based on SQL_TEXT or SQL_ID, where SQL_TEXT is the original SQL statement with parameters. Notice

To create an outline, you must go to the corresponding database.

Create an outline based on SQL_TEXT

After you create an outline based on SQL_TEXT, a key-value pair is generated and stored in the map. The key is the parameterized code of the bound SQL statement, and the value is the bound hints. For more information about the parameterization principle, see Constraints in Fast parameterization .

You can use the following syntax to create an outline based on SQL_TEXT:

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

Notes:

• After you specify OR REPLACE, you can replace the existing execution plan.

• stmt is generally a DML statement with hints and original parameters.

• If you do not specify the TO target_stmt clause, when the parameterized SQL statement accepted by the database is the same as the parameterized stmt with hints removed, the SQL statement is bound to hints in the stmt to generate an execution plan.

• To fix a plan to a statement that contains hints, you need to specify the TO target_stmt clause to indicate the original SQL statement.

Notice

When you specify target_stmt, you must ensure that stmt exactly matches target_stmt when hints are removed.

Example:

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

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

obclient> EXPLAIN SELECT * FROM t1 WHERE c2 = 1\G;
*************************** 1. row ***************************
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)

The optimizer scans by primary key. If the data volume surges, you can run the idx_c2 index to improve the performance of the SQL statement. In that case, you can create an outline to bind the SQL statement to an indexed plan and execute this plan.

Create an outline based on the following SQL statement:

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

Create an outline based on SQL_ID

You can use the following syntax to create an outline based on SQL_ID:

obclient>CREATE OUTLINE outline_name ON sql_id USING HINT  hint_text;

Notes:

• sql_id is the SQL ID of the SQL statement to be bound. You can get the SQL ID in the following ways:

The following example binds an outline based on sql_id:

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

Notice

• A hint is specified in the /*+ xxx */ format. For more information about hints, see Optimizer hints.

• An outline created based on SQL_TEXT overrides an outline created based on sql_id because the former has a higher priority.

• If the SQL statement of a specific sql_id already contains hints, the hints specified when you create an outline will override all hints in the original statement.

Outline Data is a set of hint information generated by the optimizer to fully reproduce a plan. It begins with BEGIN_OUTLINE_DATA and ends with END_OUTLINE_DATA.

You can run the EXPLAIN EXTENDED command to get the Outline Data, as shown in the following example:

obclient>EXPLAIN EXTENDED SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1\G;
*************************** 1. row ***************************
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 following example shows a set of Outline Data:

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

The Outline Data is also a hint and is therefore applicable to plan binding. See the following 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 (0.01 sec)

Verify whether the outline creation takes effect

To verify whether the outline has been created and meets expectations, perform the following three steps:

1. Verify whether the outline is created.

   Check the gv$outline view to verify whether the outline of the corresponding outline name has been created.

   obclient> SELECT * FROM oceanbase.gv$outline 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
   

2. Verify whether the execution of the new SQL statement generates a new plan based on the bound outline.

   After the SQL statement bound to the outline executes a new query, check the gv$plan_cache_plan_stat table for outline_id in the plan information that corresponds to the SQL statement. If the value of outline_id is identical to that found in the gv$outline view, the plan is generated based on the bound outline. Otherwise, it is not.

   obclient>SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA 
         FROM oceanbase.gv$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*/
   

3. Verify whether the execution plan meets expectations.

   After you verify that the plan is generated based on the bound outline, you can query the gv$plan_cache_plan_stat table to view the shape of the execution plan cached in plan_cache to verify whether the plan meets expectations. For more information, see

   Real-time execution plan display.

   obclient>SELECT OPERATOR, NAME FROM oceanbase.gv$plan_cache_plan_explain 
         WHERE TENANT_ID = 1001 AND IP = '10.101.163.87' 
          AND PORT = 30474 AND PLAN_ID = 17225;
   
   +--------------------+------------+
   | OPERATOR           | NAME       |
   +--------------------+------------+
   |  PHY_ROOT_TRANSMIT | NULL       |
   |   PHY_TABLE_SCAN   | t1(idx_c2) |
   +--------------------+------------+
   

Drop an outline

After an outline is dropped , new plans for the corresponding SQL query are generated without taking into account the bound outline. Syntax for dropping an outline:

DROP OUTLINE outline_name;

Notice

To drop an outline, you need to specify the database name in outline_name, or execute the DROP OUTLINE statement after the USE DATABASE statement.

Relationship between plan binding and plan caching

• After you create an outline based on SQL_TEXT, the optimizer generates a new plan for the SQL query. The key used by the optimizer to locate the outline is the same as the one used for plan caching. They are both text strings that you get after the SQL query is parameterized.

• After an outline is created or deleted, a new request of the corresponding SQL query triggers the invalidation of the corresponding execution plan in the plan cache and updates it to the execution plan generated based on the bound outline.