Oracle\Oceanbase固定SQL执行计划范围:上篇

  • zhangweizhao

    2024-08-04 19:02:35
  • 数据库开发技术
  • 原创

        在关系数据库性能优化中，有时数据库优化器可能会评估不准确，生成错误的执行计划或改变执行计划，从而导致 SQL 执行效率下降。当无法或在短时间内难以修改应用程序的 SQL 代码时，我们需要从数据库端快速固定指定的执行计划。Oracle 中有诸如 outline、SQL profile、SQL baseline 和 SQL patch 等技术，可以在不接触应用代码的情况下实现固定执行计划的目的。国产数据库如 OceanBase 同样支持 outline 用于固定SQL 执行计划。然而，它们在作用域上与 Oracle 仍存在一些差异。

两个场景：

  • 多个用户多个表（包括索引）结构相同的情况下，相同的 SQL 文本是否会互相影响？

  • 多个用户相同的表结构下，相同的 SQL 文本是否会互相影响？


本篇测试oracle， 下篇Oceanbase


准备测试数据

# create user anbob and weejar

SQL> create table weejar.test_profile(id int,name varchar2(50));
Table created.

SQL> create table anbob.test_profile(id int,name varchar2(50));
Table created.

SQL> insert into weejar.test_profile select rownum,'weejar'||rownum from xmltable('1 to 10000');
10000 rows created.

SQL> insert into anbob.test_profile select 1,'weejar'||rownum from dual connect by rownum<=10000; --- all of ID value is 1 10000 rows created. SQL> create index anbob.idx_test_profile on anbob.test_profile(id);
Index created.

SQL> create index anbob.idx_test_profile on anbob.test_profile(id);
Index created.

SQL> create index weejar.idx_test_profile on weejar.test_profile(id);
Index created.

Note：
创建了2个用户， 分别每个用户下1张表，id加索引，但是weejar下的表id NDK 10000, 而ANBOB下的表ID NDK 1, 大差异的选择率，后面我们对比full table scan和index scan。

测试执行计划

# session anbob

$sqlplus anbob/anbob@172.20.23.85/rac11g

SQL> @gts TEST_PROFILE
Gather Table Statistics for table TEST_PROFILE...
PL/SQL procedure successfully completed.

SQL> explain plan for select * from test_profile where id=1;
Explained.

SQL> @x2
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 4176284699
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 10000 |   136K|     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_PROFILE | 10000 |   136K|     9   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

13 rows selected.

# session weejar

SQL> @gts test_profile
Gather Table Statistics for table test_profile...
PL/SQL procedure successfully completed.

SQL> explain plan for select * from test_profile where id=1;
Explained.

SQL> @x2
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 3044072948
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PROFILE     |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_PROFILE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)
14 rows selected.

# weejar

SQL> select /*anbob_profile*/ * from test_profile where id=1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar1

SQL> @sqlt anbob_profile

HASH_VALUE SQL_ID                          CHLD# OPT_MODE             SQL_TEXT
---------- -------------------------- ---------- -------------------- ----------------------------------------------------------------------------------------------------
3678309713 d43p4szdmx3aj                       0 ALL_ROWS             select /*anbob_profile*/ * from test_profile where id=1


# anbob
SQL> select /*anbob_profile*/ * from test_profile where id=1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar776
         1 weejar777
...

SQL> @sqlt anbob_profile

HASH_VALUE SQL_ID                          CHLD# OPT_MODE             SQL_TEXT
---------- -------------------------- ---------- -------------------- ----------------------------------------------------------------------------------------------------
3678309713 d43p4szdmx3aj                       0 ALL_ROWS             select /*anbob_profile*/ * from test_profile where id=1
3678309713 d43p4szdmx3aj                       1 ALL_ROWS             select /*anbob_profile*/ * from test_profile where id=1


SQL> select sql_id,SQL_TEXT,EXECUTIONS,PARSING_SCHEMA_NAME,CHILD_NUMBER,PLAN_HASH_VALUE,EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE from v$sql where sql_id='d43p4szdmx3aj'

SQL_ID                     SQL_TEXT                                                     EXECUTIONS PARSING_SCHEMA_ CHILD_NUMBER                PLAN_HASH_VALUE       EXACT_MATCHING_SIGNATURE       FORCE_MATCHING_SIGNATURE
-------------------------- ------------------------------------------------------------ ---------- --------------- ------------ ------------------------------ ------------------------------ ------------------------------
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 WEEJAR                     0                     3044072948            3974185871915606149            3656018408532075703
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 ANBOB                      1                     4176284699            3974185871915606149            3656018408532075703

Note：
和预期一样，因为选择率问题，两个用户的相同SQL, 1个SQL ID 两个sql cursor, 两个执行计划。如果我们把使用索引的用户下的sql 固定执行计划，当前全表扫描的SQL是否也会受到影响？

SQL profile绑定执行计划

SQL> @coe d43p4szdmx3aj 3044072948

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3044072948        .002
     4176284699        .005

Parameter 2:
PLAN_HASH_VALUE (required)


Values passed:
~~~~~~~~~~~~~
SQL_ID         : "d43p4szdmx3aj"
PLAN_HASH_VALUE: "3044072948"


Execute coe_xfr_sql_profile_d43p4szdmx3aj_3044072948.sql
on TARGET system in order to create a custom SQL Profile
with plan 3044072948 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.

SQL>@coe_xfr_sql_profile_d43p4szdmx3aj_3044072948.sql
...
SQL>REM
SQL>VAR signature NUMBER;
SQL>REM
SQL>DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select /*anbob_profile*/ * from test_profile where id=1
  7  ]';
  8  h := SYS.SQLPROF_ATTR(
  9  q'[BEGIN_OUTLINE_DATA]',
 10  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 11  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
 12  q'[DB_VERSION('11.2.0.4')]',
 13  q'[ALL_ROWS]',
 14  q'[OUTLINE_LEAF(@"SEL$1")]',
 15  q'[INDEX_RS_ASC(@"SEL$1" "TEST_PROFILE"@"SEL$1" ("TEST_PROFILE"."ID"))]',
 16  q'[END_OUTLINE_DATA]');
 17  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 18  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 19  sql_text    => sql_txt,
 20  profile     => h,
 21  name        => 'coe_d43p4szdmx3aj_3044072948',
 22  description => 'coe d43p4szdmx3aj 3044072948 '||:signature||'',
 23  category    => 'DEFAULT',
 24  validate    => TRUE,
 25  replace     => TRUE,
 26  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 27  END;
 28  /

PL/SQL procedure successfully completed.

# weejar

SQL>select /*anbob_profile*/ * from test_profile where id=1;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar1

SQL>@x2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------ 
Plan hash value: 3044072948

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PROFILE     |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_PROFILE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)
Note
-----
   - SQL profile "coe_d43p4szdmx3aj_3044072948" used for this statement


SQL> select NAME,category, status, sql_text, force_matching,SIGNATURE from dba_sql_profiles;

NAME                                                         CATEGORY        STATUS           SQL_TEXT                                                               FORCE_                                         SIGNATURE
------------------------------------------------------------ --------------- ---------------- ---------------------------------------------------------------------- ------ -------------------------------------------------
coe_d43p4szdmx3aj_3044072948                                 DEFAULT         ENABLED                                                                                 NO                                   3974185871915606149
z_bmvxw9vnys273_1270443855                                   DEFAULT         ENABLED          select '--' pdb, t.tablespace_name, t.totalmb,  t.totals,              YES                                  7807399389790495497
coe_adx0bqa0kb9tp_3956160932                                 DEFAULT         ENABLED          select /*+ FULL(emp) */ ename,empno from scott.emp where empno=7788    YES                                 15426704112737772199

SQL> @sql_profile_hints coe_d43p4szdmx3aj_3044072948

OUTLINE_HINTS
--------------------------------------------------------------------------------------------------  
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "TEST_PROFILE"@"SEL$1" ("TEST_PROFILE"."ID"))
END_OUTLINE_DATA

8 rows selected.

SQL> l
  1  SELECT
  2      hint outline_hints
  3  FROM (
  4      SELECT p.name, p.signature, p.category, ROW_NUMBER()
  5      OVER (PARTITION BY d.signature, d.category ORDER BY d.signature) row_num,
  6      EXTRACTVALUE(VALUE(t), '/hint') hint
  7      FROM
  8          sys.sqlobj$data d
  9        , dba_sql_profiles p,
 10      TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(d.comp_data), '/outline_data/hint'))) t
 11  WHERE
 12      d.obj_type = 1
 13  AND p.signature = d.signature
 14  AND p.category = d.category
 15  AND p.name LIKE ('&1'))
 16  ORDER BY
 17      name
 18*   , row_num
SQL>

SQL> select sql_id,SQL_TEXT,EXECUTIONS,PARSING_SCHEMA_NAME,CHILD_NUMBER,SQL_PROFILE,PLAN_HASH_VALUE,EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE from v$sql where sql_id='d43p4szdmx3aj';
SQL_ID                     SQL_TEXT                                                     EXECUTIONS PARSING_SC CHILD_NUMBER SQL_PROFILE                    PLAN_HASH_VALUE  EXACT_MATCHING_SIGNATURE  FORCE_MATCHING_SIGNATURE
-------------------------- ------------------------------------------------------------ ---------- ---------- ------------ ------------------------------ --------------- ------------------------- -------------------------
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 WEEJAR                0                                     3044072948       3974185871915606149       3656018408532075703
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 ANBOB                 1                                     4176284699       3974185871915606149       3656018408532075703
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               2 WEEJAR                2 coe_d43p4szdmx3aj_3044072948        3044072948       3974185871915606149       3656018408532075703

# anbob

SQL> select /*anbob_profile*/ * from test_profile where id=1;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar776
         1 weejar777
         1 weejar778
         1 weejar779
...

SQL> @x2

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------  
Plan hash value: 3044072948

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  | 10000 |   136K|    62   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PROFILE     | 10000 |   136K|    62   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_PROFILE | 10000 |       |    34   (0)| 00:00:01 | --受影响原来是FULL TABLE SCAN
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)
Note
-----
   - SQL profile "coe_d43p4szdmx3aj_3044072948" used for this statement

Note：
FTS 变成了sql profile固定的 IFS, cost从9增加到了62。

SQL> select sql_id,SQL_TEXT,EXECUTIONS,PARSING_SCHEMA_NAME,CHILD_NUMBER,SQL_PROFILE,PLAN_HASH_VALUE,EXACT_MATCHING_SIGNATURE,FORCE_MATCHING_SIGNATURE from v$sql where sql_id='d43p4szdmx3aj'

SQL_ID                     SQL_TEXT                                                     EXECUTIONS PARSING_SC CHILD_NUMBER SQL_PROFILE                    PLAN_HASH_VALUE  EXACT_MATCHING_SIGNATURE  FORCE_MATCHING_SIGNATURE
-------------------------- ------------------------------------------------------------ ---------- ---------- ------------ ------------------------------ --------------- ------------------------- -------------------------
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 WEEJAR                0                                     3044072948       3974185871915606149       3656018408532075703
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 ANBOB                 1                                     4176284699       3974185871915606149       3656018408532075703
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               2 WEEJAR                2 coe_d43p4szdmx3aj_3044072948        3044072948       3974185871915606149       3656018408532075703
d43p4szdmx3aj              select /*anbob_profile*/ * from test_profile where id=1               1 ANBOB                 3 coe_d43p4szdmx3aj_3044072948        3044072948       3974185871915606149       3656018408532075703

Note：
可见在Oracle中是基于SQL TEXT,共享SQL, 所以在用户WEEJAR创建了sql profile固定为index scan时，另一个用户是相同的SQL ,也使用了sql profile，新生成了sql cursor，降低了SQL的使用效率。那有没有办法可以控制 SQL PROFILE的影响范围呢？


限制SQL PROFILE 影响范围

不同用户不同表相同SQL，默认创建的SQL PROFILE会互相影响，所以提醒我们不要在数据库中任性创建sql profile，而好心做了错事，影响了过其他用户的SQL.  有没有办法控制SQL PROFILE影响边界呢？当然有,我相信大多数人可能不知道。(同样这点与Ocenabase差异较大的。)

  • 制造差异化

           SQL文本或索引名称等,人为地制造差异化，让SQL HINT失效。

  • SQL PROFILE配置CATEGORY

BEGIN 
DBMS_SQLTUNE.ALTER_SQL_PROFILE ( 
   name            =>  'coe_d43p4szdmx3aj_3044072948'
,  attribute_name  =>  'CATEGORY'
,  value           =>  'WEEJAR_TEST'      
);
END;

# anbob
SQL> select /*anbob_profile*/ * from test_profile where id=1;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar776
         1 weejar777

SQL> @x2

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 4176284699

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 10000 |   136K|     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_PROFILE | 10000 |   136K|     9   (0)| 00:00:01 | --恢复了full table scan
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

# weejar 
SQL> select /*anbob_profile*/ * from test_profile where id=1;

        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar1

SQL> @x2
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3044072948

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PROFILE     |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_PROFILE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)
-- without sql profile
SQL> @pd sqltune
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX      NAME                                                     VALUE                          DESCRIPTION
---------- ---------- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      2299   8FB      _enable_automatic_sqltune                                TRUE                           Automatic SQL Tuning Advisory enabled parameter
      2838   B16      sqltune_category                                         DEFAULT                        Category qualifier for applying hintsets
      2839   B17      _sqltune_category_parsed                                 DEFAULT                        Parsed category qualifier for applying hintsets


SQL> alter session set sqltune_category='WEEJAR_TEST';
Session altered.

SQL>  select /*anbob_profile*/ * from test_profile where id=1;
        ID NAME
---------- ----------------------------------------------------------------------------------------------------
         1 weejar1
SQL> @x2

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
Plan hash value: 3044072948
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    15 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_PROFILE     |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_PROFILE |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=1)

Note
-----
   - SQL profile "coe_d43p4szdmx3aj_3044072948" used for this statement

Note:
使用sql profile CATEGORY和sqltune_category参数约束了SQL PROFILE的影响范围。

总结

默认oracle 是基于sql text实现的SQL PROFILE时的匹配，这也是oracle设计为的是共享的初衷，同时为了影响范围，引入了category分组，默认为default组。那不同用户相同表相同SQL也就不在演示，也是相同的结果。


请使用浏览器的分享功能分享到微信等