PolarDB for Oracle provides the Outline feature to capture, view, and delete fixed execution plans.

Overview

SQL plan management (SPM) is a preventive mechanism. SPM allows the optimizer to automatically manage execution plans to ensure that your database uses only known or verified execution plans.

SPM provides the Outline feature. The Outline feature supports only fixed execution plans. The Outline feature does not support evolved execution plans. The Outline feature of PolarDB can be used to mark the execution plans of only PREPARE statements as fixed execution plans.

The Outline feature of PolarDB is implemented by using the polar_outline plug-in. If you want to enable this feature, Submit a ticket to contact technical support.

Create the polar_outline plug-in

To create the polar_outline plug-in, execute the following statement:

CREATE EXTENSION polar_outline;

Configure parameters

To use the Outline feature to mark execution plans as fixed execution plans, execute the following statement:

SET polar_outline.use_stored_plan TO ON;

The functions and relations defined by the Outline feature are stored in the polar_outline schema. You can execute the following statement to add the polar_outline schema to the search path:

SET search_path To "$user",public,polar_outline;

Prepare test data

Execute the following statements to create a table named t and insert data into the table:

CREATE TABLE t(a INT,b INT);
INSERT INTO t SELECT i, i FROM generate_series(1, 1000)i;

Update the data in the table to ensure that the optimizer uses accurate statistics.

ANALYZE t;

Execute a PREPARE statement. The following code provides an example:

PREPARE test AS SELECT * FROM t WHERE a=$1;
Note
  • The test data is applicable only to the example in this section. Modify the statements based on your business requirements.
  • The Outline feature provides a hint to control the execution plan to capture. If you use the hint in a statement, the execution plan of the statement is captured. Then, if you execute this statement without the hint, the statement is still executed based on the captured execution plan.

Capture execution plans and mark them as fixed plans

PolarDB for Oracle provides the following methods to capture execution plans and mark the execution plans as fixed execution plans:

  • Call the polar_outline_create function to capture execution plans. We recommend that you use this method. For example, execute the following statement:
    SELECT polar_outline_create('EXECUTE test(100)');

    The following information is returned:

     polar_outline_create 
    ----------------------
     t
    (1 row)
  • Use the Outline feature to capture execution plans. This method can be used to capture multiple execution plans at the same time. To use the Outline feature to capture execution plans, perform the following steps:
    Notice If the Outline feature is used to capture execution plans, a plan cache is generated.
    1. Execute the following statement to allow the Outline feature to capture execution plans:
      SET polar_outline.capture_plan TO ON;
    2. Execute the following statement to capture an execution plan:
      EXECUTE test(100);

      The following information is returned:

        a  |  b
      -----+-----
       100 | 100
      (1 row)
    3. If you do not want to use the Outline feature to capture execution plans, execute the following statement:
      SET polar_outline.capture_plan TO OFF;

Parameters

Parameter Description
use_stored_plan Specifies whether to allow the Outline feature to mark execution plans as fixed execution plans.
  • To allow the Outline feature to mark execution plans as fixed execution plans, execute the following statement:
    SET polar_outline.use_stored_plan TO ON;
  • Otherwise, execute the following statement:
    SET polar_outline.use_stored_plan TO OFF;
capture_plan Specifies whether to allow the Outline feature to capture execution plans.
  • To allow the Outline feature to capture execution plans, execute the following statement:
    SET polar_outline.capture_plan TO ON;
  • Otherwise, execute the following statement:
    SET polar_outline.capture_plan TO OFF;
log_usage Specifies the level of Outline logs to print.
set polar_outline.log_usage To none;
set polar_outline.log_usage To debug;
set polar_outline.log_usage To debug1;
set polar_outline.log_usage To debug2;
set polar_outline.log_usage To debug3;
set polar_outline.log_usage To debug4;
set polar_outline.log_usage To debug5;
set polar_outline.log_usage To log;
set polar_outline.log_usage To info;
set polar_outline.log_usage To notice;
set polar_outline.log_usage To warning;

Uses the Outline feature to rewrite queries

If performance issues occur after PolarDB rewrites SQL queries, you can use the Outline feature to rewrite SQL queries.
Note Make sure that the execution plan of the source query is equivalent to the execution plan of the rewritten query. Otherwise, PolarDB may generate an incorrect execution plan and return an incorrect result set.
In the following example, the execution plan of query SQL1 is equivalent to the execution plan of query SQL2.
SQL1: 
select t.a, t2.avg_b
from t join (select avg(b) as avg_b, a 
             from t2
             group by a) t2
on t2.a = t.a and t.c < $1 
order by t.a;

SQL2:
select t.a, t2.avg_b
from t join lateral (select avg(b) as avg_b
                     from t2
                     where t2.a = t.a) as t2
on t.c < $1
order by t.a;

You can perform the following steps to use the Outline feature to replace the execution plan of SQL1 with the execution plan of SQL2:

  1. Execute the following statements to prepare data:
    CREATE TABLE t(a int, b int, c int);
    insert into t select i % 100000, i, i from generate_series(1, 1000000) i;
    create table t2 as select * from t;
    create index on t(c);
    create index on t2(a);
    ANALYZE t,t2;
  2. Execute the following statements to capture the execution plans of the source query SQL1 and the rewritten query SQL2:
    prepare s1 as select t.a, t2.avg_b from t join (select avg(b) as avg_b, a from t2 group by a) t2
    on t2.a = t.a and t.c < $1 order by t.a;
    prepare s2 as select t.a, t2.avg_b from t join lateral (select avg(b) as avg_b from t2 where t2.a = t.a) as t2
    on t.c < $1 order by t.a;
    SELECT polar_outline.polar_outline_create('EXECUTE s1(5)');
     polar_outline_create 
    ----------------------
     t
    (1 row)
    
    SELECT polar_outline.polar_outline_create('EXECUTE s2(5)');
     polar_outline_create 
    ----------------------
     t
    (1 row)
  3. Execute the following statements to use polar_outline_switch to exchange the execution plans of the two queries:
    Note The input parameters are the IDs of the two execution plans in the outline. The execution plan of SQL1 is replaced with the execution plan of SQL2.
    SELECT polar_outline.polar_outline_switch(1,2);
     polar_outline_switch 
    ----------------------
     t
    (1 row)
  4. Execute the following statement to delete the execution plan of SQL2 from the outline:
    Note After SQL1 is rewritten, the execution plan of SQL2 is unnecessary and needs to be cleared.
    SELECT polar_outline.polar_outline_delete(2);
  5. The following code shows the execution result of SQL1 before SQL1 is rewritten and the execution result of SQL1 after SQL1 is rewritten:
    Before SQL1 is rewritten, s1 indicates the original execution plan of SQL1.
    EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                    QUERY PLAN                 
    -------------------------------------------
     Sort
       Sort Key: t.a
       ->  Hash Join
             Hash Cond: (t.a = t2.a)
             ->  Index Scan using t_c_idx on t
                   Index Cond: (c < $1)
             ->  Hash
                   ->  HashAggregate
                         Group Key: t2.a
                         ->  Seq Scan on t2
    (10 rows)
    
    SELECT polar_outline_switch(1,2);
     polar_outline_switch 
    ----------------------
     t
    (1 row)
    
    After SQL1 is rewritten, s1 indicates the current execution plan of SQL1. The current execution plan is sourced from SQL2.
    EXPLAIN (COSTS FALSE) EXECUTE s1(5);
                          QUERY PLAN                       
    -------------------------------------------------------
     Sort
       Sort Key: t.a
       ->  Nested Loop
             ->  Index Scan using t_c_idx on t
                   Index Cond: (c < $1)
             ->  Aggregate
                   ->  Bitmap Heap Scan on t2
                         Recheck Cond: (a = t.a)
                         ->  Bitmap Index Scan on t2_a_idx
                               Index Cond: (a = t.a)
    (10 rows)