在关系数据库性能优化技术中，有时存在数据库优化器评估问题，产生了错误的执行计划或执行计划改变，导致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也就不在演示，也是相同的结果。