All Products
Search
Document Center

Plan binding

Last Updated: Jun 18, 2021

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 through 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. Parameters of the view are described in the following table.

Field

Type

Description

tenant_id

BIGINT(20)

The ID of the tenant.

database_id

BIGINT(20)

The ID of the database.

outline_id

BIGINT(20)

The ID of the Outline.

database_name

VARCHAR(128)

The name of the database.

outline_name

VARCHAR(128)

The name of the Outline.

visible_signature

VARCHAR(32768)

The result of signature deserialization, which makes the signature information easier to read.

sql_text

VARCHAR(32768)

The SQL statement specified in the ON clause when an Outline is created.

outline_target

VARCHAR(32768)

The SQL statement specified in the TO clause when an Outline is created.

outline_sql

VARCHAR(32768)

The SQL statement with complete Outline information.

Create an Outline

OceanBase Database supports creating an Outline based on SQL_TEXT (the original SQL statement with parameters) or SQL_ID.

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 a set of problems generated during the parameterization of the SQL statement, and the value is a set of hints for these problems. 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 the target_stmt, you must ensure that the stmt exactly matches the 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 perform the following operations to get the SQL_ID:

    • Query the gv$plan_cache_plan_stat table.

    • Query the gv$sql_audit table.

    • Use MD5 to generate SQL_ID based on the parameterized original SQL statement. You can use the following script to generate the corresponding SQL_ID.

      import hashlib
      sql_text='SELECT * FROM t1 WHERE c2 = ?'
      sql_id=hashlib.md5(sql_text.encode('utf-8')).hexdigest().upper()
      print(sql_id)

To bind an Outline based on SQL_ID, see the following example:

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 overwrites the one 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 overwrite all hints in the original statement.

Outline Data is a set of hints 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))

Where, the Outline Data is indicated as:

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

The Outline Data is a set of hints 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 that the Outline has been successfully created and meets the expectations, perform the following three steps:

  1. Verify whether the Outline is successfully created.

    Check the table in gv$outline to verify whether the outline of the corresponding outline name has been successfully 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 t 1WHERE c 2=1
  1. 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 is executed, check the gv$plan_cache_plan_stat table for the outline_id in the plan information that corresponds to the SQL statement. If the outline_id is identical to the one found in the gv$outline table, 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*/
  1. 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) |
    +--------------------+------------+

Delete an Outline

After an Outline is deleted, new plans for the corresponding SQL query are generated without taking into account the bound Outline. Syntax for deleting an Outline:

DROP OUTLINE outline_name;
Notice

To delete the Outline, you need to specify the database name in outline_name, or execute it after 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 parameterizing the SQL query.

  • After an Outline is created/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 by the bound Outline.