Plan binding

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

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.

Outline view

DBA_OB_OUTLINES is the outline view. The following table describes the fields in the outline view.

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.

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 text of the bound SQL statement, and the value is the bound hints. For more information about the parameterization principle, see Fast parameterization.

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

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

Take note of the following considerations:

• 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.

  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 specify target_stmt, you must ensure that stmt exactly matches target_stmt when hints are removed.

In the following example, 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 this case, you can create an outline to bind the SQL statement to an indexed plan and execute this plan.

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 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

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;

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

• Query V$OB_PLAN_CACHE_PLAN_STAT.

• Query GV$OB_SQL_AUDIT.

• 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)
  

The following example binds an outline based on SQL_ID:

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

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 execute the EXPLAIN EXTENDED statement to get the Outline Data, as shown in the following 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 also a hint and is therefore applicable to plan binding, as shown in the following sample statement:

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 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.

   Query the DBA_OB_OUTLINES view to verify whether the outline of the corresponding name has been 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
   

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

   After the SQL statement bound to the outline executes a new query, check the GV$OB_PLAN_CACHE_PLAN_STAT view 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 DBA_OB_OUTLINES 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$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*/
   

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$OB_PLAN_CACHE_PLAN_EXPLAIN view to check 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$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 an outline is dropped, new execution plans are not generated for the corresponding SQL query based on the bound outline.

The syntax for dropping an outline is as follows:

DROP OUTLINE outline_name;

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.