首页 » OceanBase, ORACLE 9i-23ai » Oracle 、Oceanbase、GoldenDB数据库比较系列(二十四)：sql profile/ outline 影响范围（上）

Oracle 、Oceanbase、GoldenDB数据库比较系列(二十四)：sql profile/ outline 影响范围（上）

  • 2024/07/26
  • OceanBase, ORACLE 9i-23ai
  • 77 views
  • Oracle 、Oceanbase、GoldenDB数据库比较系列(二十四)：sql profile/ outline 影响范围（上）已关闭评论

在关系数据库性能优化技术中，有时存在数据库优化器评估问题，产生了错误的执行计划或执行计划改变，导致SQL执行效率变差，在无法或短时间内修改应用SQL代码时，需要从数据库端快速固定指定的执行计划，在oracle中相关技术有outline, sql profile, sql baseline, sql patch 等技术，可以实现不接触应用代码达到固定执行计划的目的，在国产库中如Oceanbase同样支持outline 用于固定执行计划。但是和Oracle在作用域还是存在差异。

2个场景:

  • 多个用户多个table(含索引)相同结构，对于相同的SQL文本，是否会互相影响？
  • 多个用户相同的table， 对于相同的SQL文本，是否会互相影响？

因为篇幅原因，包含测试用例，分成2篇，本篇记录Oracle,后面记录Oceanbase. GoldenDB

准备测试数据

# 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差异较大的地放。

  • 1,  制造差异化

SQL文本或索引名等制造差异化，让SQL HINT失效。

  • 2，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也就不在演示，也是相同的结果。

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：