熊军 oracle 迁移 spm,oracle_SPM之sql_plan_baseline实现绑定变量值倾斜处理

最新推荐文章于 2022-02-07 09:26:44 发布

weixin_40005542

最新推荐文章于 2022-02-07 09:26:44 发布

阅读量425

点赞数

文章标签：熊军 oracle 迁移 spm

(又一年马上要结束了)

SPM是继SQLPROFILE出现的又一个绑定执行计划的方法，是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会启用，既能够主动地稳定执行计划，又保留了继续使用新的执行计划效率可能更高的执行计划的计划。

SPM通过SQL

PLAN

BASELINE实现执行计划的管理，代表一个执行计划，视图DBA_SQL_PLAN_BASELINES的字段ENABLED和ACCEPTED均为YES时，才会被使用。

通过两种方法产生目标SQL的SQL PLAN BASELINE。

-自动捕获

-手动生成/批量导入(批量导入适用于数据库版本升级，确保升级后SQL执行计划不会发生变更)

自动捕获，通过数据参数optimizer_capture_sql_plan_baselines和optimizer_use_sql_plan_baselines实现稽核的捕捉和使用，可以在系统和会话级别启用。

当自动捕获启用后，语句重复第一次执行的会被记录到视图DBA_SQL_PLAN_BASELINES中，且字段ENABLED和ACCEPTED标记为YES，其他执行计划的产生ENABLED记录为YES，ACCEPTED为NO，可通过dbms_spm.evolve_sql_plan_baseline和dbms_spm.

alter_sql_plan_baseline修改。

手动生成，通过dbms_spm.load_plans_from_cursor_cache传入sql_id,plan_hash_value即改写语句产出正确的执行计划后，使用dbms_spm.load_plans_from_cursor_cache

传入sql_id,plan_hash_value和sql_handle(与第一次load

DBA_SQL_PLAN_BASELINES.

sql_handler相同)，来实现稳定执行计划的目的。

我们测试会话级别捕捉的方式，来对具有倾斜的绑定变化值，可以根据传入值不同来选择正确的执行计划。

SQL> create table sqltab1 tablespace users as select * from

dba_objects;

Table created.

SQL>

SQL> create index idx_sqltab_status on sqltab1(status)

tablespace users;

Index created.

SQL>

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SUN','SQLTAB1')

;

PL/SQL procedure successfully completed.

SQL> show parameter sql_plan

NAME TYPE VALUE

------------------------------------ -----------

------------------------------

optimizer_capture_sql_plan_baselines boolean

FALSE

optimizer_use_sql_plan_baselines boolean TRUE

SQL> alter session set

optimizer_capture_sql_plan_baselines=true;

SQL> alter session set

optimizer_use_sql_plan_baselines=false;

SQL> show parameter sql_plan

NAME TYPE VALUE

------------------------------------ -----------

------------------------------

optimizer_capture_sql_plan_baselines boolean

TRUE

optimizer_use_sql_plan_baselines boolean FALSE

SQL>

VAR B1 VARCHAR2(32);

EXEC :B1 := 'VALID';

SELECT object_name FROM sqltab1 WHERE status = :B1;

退出会话后再次执行

EXEC :B1 := 'INVALID';

SELECT object_name FROM sqltab1 WHERE status = :B1;

SQL> alter session set

optimizer_capture_sql_plan_baselines=true;

SQL> alter session set

optimizer_use_sql_plan_baselines=false;

不通绑定变量值的语句执行两次，被捕捉到的SQL PLAN

BASELINE如下；

SQL> SELECT sql_handle,plan_name,enabled,accepted FROM

dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC

------------------------------ ------------------------------

--- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nkn0944e361 YES NO

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES

SELECT sql_handle,plan_name,enabled,accepted FROM

dba_sql_plan_baselines WHERE

SQL_HANDLE='SQL_a863066d2c135254';

SQL> var temp varchar2(1000) ;

SQL> exec :temp :=

dbms_spm.evolve_sql_plan_baseline(SQL_HANDLE=>'SQL_a863066d2c135254',PLAN_NAME=>'SQL_PLAN_ahss6dnq16nkn0944e361',VERIFY=>'NO',COMMIT=>'YES');

PL/SQL procedure successfully completed.

SQL> SQL> SELECT sql_handle,plan_name,enabled,accepted

FROM dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC

------------------------------ ------------------------------

--- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nkn0944e361 YES YES

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES

执行两次相同的语句，且两次绑定变量值的倾斜度都不一样，模拟真实现网环境中的绑定变量值倾斜导致的性能问题；

VAR B1 VARCHAR2(32);

EXEC :B1 := 'VALID';

SELECT object_name FROM sqltab1 WHERE status = :B1;

14055 rows selected.

SQL> SELECT * FROM

table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------

SQL_ID 4kxaqthukyc1x, child number 0

-------------------------------------

SELECT object_name FROM sqltab1 WHERE status

= :B1

Plan hash value: 1412425469

-----------------------------------------------------------------------------

| Id | Operation | Name

| Rows |

Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT |

| | | 56 (100)|

|* 1 | TABLE ACCESS FULL|

SQLTAB1 | 14055 | 356K| 56 (2)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -

filter("STATUS"=:B1)

Note

-----

- SQL plan baseline

SQL_PLAN_ahss6dnq16nknf331ec6f used for this statement

22 rows selected.

SQL> VAR B1 VARCHAR2(32);

SQL> EXEC :B1 := 'INVALID';

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT object_name FROM sqltab1 WHERE status =

:B1;

OBJECT_NAME

-------------------------------------------------------------------

DBA_COMMON_AUDIT_TRAIL

FGA_LOG$FOR_EXPORT

SUDOKU

T1_V

SQL> SELECT * FROM

table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------

SQL_ID fpd5qpchd1qx9, child number 0

-------------------------------------

SELECT object_name FROM sqltab1 WHERE status = :B1

Plan hash value: 2067510433

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows

| Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY

INDEX ROWID| SQLTAB1 |

5 | 130 |

2 (0)|

00:00:01 |

|* 2 | INDEX RANGE SCAN

| IDX_SQLTAB_STATUS |

5 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 -

access("STATUS"=:B1)

Note

-----

- SQL plan baseline

SQL_PLAN_ahss6dnq16nkn0944e361 used for this statement

23 rows selected.

SQL>

Outline Data

-------------

当sql_plan_baseline第一次启用后，绑定变量值为VALID时，也是走的索引，有问题

；当时时间很晚，没有再继续研究，几天后打开环境，再次执行不同的绑定值，VALID和INVALID不同值，满足我们提出的需求；将PLAN_NAME对应的属性FIXED改为YES即可。语句执行时会recost重新计算当前执行计划是否是最佳执行计划，不是的话会选择另一个SPB的可用执行计划；

SQL> var temp varchar2(1000) ;

SQL> exec :temp :=

dbms_spm.alter_sql_plan_baseline(SQL_HANDLE=>'SQL_a863066d2c135254',PLAN_NAME=>'SQL_PLAN_ahss6dnq16nkn0944e361',ATTRIBUTE_NAME=>'FIXED',ATTRIBUTE_VALUE=>'YES');

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT

sql_handle,plan_name,enabled,accepted,fixed FROM

dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX

------------------------------ ------------------------------

--- --- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nkn0944e361 YES YES YES

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES NO

SQL> var temp varchar2(1000) ;

SQL> exec :temp :=

dbms_spm.alter_sql_plan_baseline(SQL_HANDLE=>'SQL_a863066d2c135254',PLAN_NAME=>'SQL_PLAN_ahss6dnq16nknf331ec6f',ATTRIBUTE_NAME=>'FIXED',ATTRIBUTE_VALUE=>'YES');

PL/SQL procedure successfully completed.

SQL> SELECT

sql_handle,plan_name,enabled,accepted,fixed FROM

dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX

------------------------------ ------------------------------

--- --- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nkn0944e361 YES YES YES

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES YES

当FIXED=YES时查看10053会看到有recost的过程，否则直接走SPM的执行计划；

FIXED=NO(默认)

*** 2018-12-16 22:41:22.761

*** SESSION ID:(29.11) 2018-12-16 22:41:22.761

*** CLIENT ID:() 2018-12-16 22:41:22.761

*** SERVICE NAME:(SYS$USERS) 2018-12-16 22:41:22.761

*** MODULE NAME:(SQL*Plus) 2018-12-16 22:41:22.761

*** ACTION NAME:() 2018-12-16 22:41:22.761

SPM: kkopmCheckSmbUpdate (enter) xscP=0x2b270e8fccc0,

pmExCtx=0x634bb8a8, ciP=0x60c88050, dtCtx=0xc0d0670

[root@prim trace]#

FIXED=YES

*** 2018-12-16 23:00:14.217

*** SESSION ID:(37.1) 2018-12-16 23:00:14.217

*** CLIENT ID:() 2018-12-16 23:00:14.217

*** SERVICE NAME:(SYS$USERS) 2018-12-16 23:00:14.217

*** MODULE NAME:(SQL*Plus) 2018-12-16 23:00:14.217

*** ACTION NAME:() 2018-12-16 23:00:14.217

Registered qb: SEL$1 0x23bccfa0

(PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$1 nbfros=1

flg=0

fro(0): flg=4 objn=14675

hint_alias="SQLTAB1"@"SEL$1"

SPM: statement found in SMB

CBRID: SQLTAB1 @ SEL$1 TableLookup allocation - Failure - :

disabled by parameter

SPM: cost-based plan found in the plan baseline, planId =

155509601

SPM: cost-based plan successfully matched, planId =

155509601

Starting SQL statement dump

SQL> SELECT sql_handle,sql_text FROM

dba_sql_plan_baselines ;

SQL_HANDLE SQL_TEXT

------------------------------

--------------------------------------------------------------------------------

SQL_115024ccba5e158c DELETE

FROM PLAN_TABLE WHERE STATEMENT_ID=:1

SQL_294c437e331fa51f SELECT

PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',

:1))

SQL_301247dc6a5a9191 SELECT *

FROM sqltab1 WHERE status='INVALID'

SQL_a863066d2c135254 SELECT

object_name FROM sqltab1 WHERE status = :B1

SQL_a863066d2c135254 SELECT

object_name FROM sqltab1 WHERE status = :B1

SQL_c259a88bdd2c3d5a SELECT * FROM

table(dbms_xplan.display_cursor(null,null,'allstats +

6 rows selected.

我们先删除掉所有的SQL PLAN

BASELINE.

var temp varchar2(1000) ;

exec :temp :=

dbms_spm.drop_sql_plan_baseline(SQL_HANDLE=>'xxxxxxx');

SQL> SELECT sql_handle,sql_text FROM dba_sql_plan_baselines

;

no rows selected

以上是客户端直接执行来抓取，但在现网中往往是执行过的，需要绑定一个SQL_PLAN_BASELINE到指定的SQL_ID上即SQL_HANDLE(其实就是偷梁换柱)，那么就需要自动的手动方式实现。

假设下面语句是程序语句

VAR B1 VARCHAR2(32);

EXEC :B1 := 'VALID';

SELECT object_name FROM sqltab1 WHERE status = :B1;

SQL> SELECT * FROM

table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------

SQL_ID fpd5qpchd1qx9, child number 0

-------------------------------------

An uncaught error happened in prepare_sql_statement :

ORA-01403: no data found

Plan hash value: 1412425469

-----------------------------------------------------------------------------

| Id | Operation | Name

| Rows |

Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT |

| | | 56 (100)|

|* 1 | TABLE ACCESS FULL|

SQLTAB1 | 14055 | 356K| 56 (2)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -

filter("STATUS"=:B1)

18 rows selected.

手工编辑一个HINT走索引的语句执行，且绑定到程序语句中(重要改写的语句，只能加HINT且其它文本内容必须相同，也可以刷出后变绑定变量，只要取到正确执行计划即可)；

SQL> SELECT object_name FROM sqltab1 WHERE status =

:B1

OBJECT_NAME

--------------------------------------------------------------------

DBA_COMMON_AUDIT_TRAIL

FGA_LOG$FOR_EXPORT

SUDOKU

T1_V

SQL> SELECT * FROM

table(dbms_xplan.display_cursor(null,null,'allstats +alias +outline

+peeked_binds'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------

SQL_ID 3c39cvjp07njf, child number 0

-------------------------------------

Plan hash value: 2067510433

------------------------------------------------------------------

| Id | Operation | Name | E-Rows

------------------------------------------------------------------

| 0 | SELECT STATEMENT | | |

| 1 | TABLE ACCESS BY

INDEX ROWID| SQLTAB1 |

5 |

|* 2 | INDEX RANGE SCAN

| IDX_SQLTAB_STATUS |

5 |

------------------------------------------------------------------

SQL> SELECT sql_Id,plan_hash_value,sql_text FROM v$sql

WHERE sql_text LIKE '%sqltab1%';

SQL_ID PLAN_HASH_VALUE

------------- ---------------

SQL_TEXT

-------------------------------------------------------------------

fpd5qpchd1qx9 1412425469

SELECT object_name FROM sqltab1 WHERE status = :B1

4auhrudz8295w 2067510433

SELECT object_name FROM sqltab1 WHERE status = :B1

SQL> var temp varchar2(1000) ;

SQL> exec :temp :=

dbms_spm.load_plans_from_cursor_cache(sql_id=>'fpd5qpchd1qx9',plan_hash_value=>'1412425469');

SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed

FROM dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX

------------------------------ ------------------------------

--- --- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES NO

SQL> exec :temp :=

dbms_spm.load_plans_from_cursor_cache(sql_id=>'4auhrudz8295w',plan_hash_value=>'2067510433',sql_handle=>'SQL_a863066d2c135254');

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed

FROM dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX

------------------------------ ------------------------------

--- --- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nkn0944e361 YES YES NO

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES NO

使用dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_a863066d2c135254',plan_name=>'SQL_PLAN_ahss6dnq16nknf331ec6f')删掉全部扫描的执行计划，

且FIXED=NO就实现了SQL

PROFILE绑定执行计划的目的；

这里的测试场景是recost自动选择好的执行计划，所以保留两个plan_name,且设置FIXED=YES。

SQL> var temp varchar2(1000) ;

SQL> exec :temp :=

dbms_spm.alter_sql_plan_baseline(SQL_HANDLE=>'SQL_a863066d2c135254',PLAN_NAME=>'SQL_PLAN_ahss6dnq16nkn0944e361',ATTRIBUTE_NAME=>'FIXED',ATTRIBUTE_VALUE=>'YES');

SQL> exec :temp :=

dbms_spm.alter_sql_plan_baseline(SQL_HANDLE=>'SQL_a863066d2c135254',PLAN_NAME=>'SQL_PLAN_ahss6dnq16nknf331ec6f',ATTRIBUTE_NAME=>'FIXED',ATTRIBUTE_VALUE=>'YES');

SQL> SELECT sql_handle,plan_name,enabled,accepted,fixed

FROM dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX

------------------------------ ------------------------------

--- --- ---

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nkn0944e361 YES YES YES

SQL_a863066d2c135254 SQL_PLAN_ahss6dnq16nknf331ec6f YES YES YES

测试结果如下：

SQL> VAR B1 VARCHAR2(32);

SQL> EXEC :B1 := 'INVALID';

PL/SQL procedure successfully completed.

SQL>

SQL> SELECT object_name FROM sqltab1 WHERE status =

:B1;

14055 rows selected.

SQL> SELECT * FROM

table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------

SQL_ID fpd5qpchd1qx9, child number 0

-------------------------------------

An uncaught error happened in prepare_sql_statement :

ORA-01403: no data found

Plan hash value: 1412425469

-----------------------------------------------------------------------------

| Id | Operation | Name

| Rows |

Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT |

| | | 56 (100)|

|* 1 | TABLE ACCESS FULL|

SQLTAB1 | 14055 | 356K| 56 (2)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 -

filter("STATUS"=:B1)

Note

-----

- SQL plan baseline

SQL_PLAN_ahss6dnq16nknf331ec6f used for this statement

22 rows selected.

SQL> EXEC :B1 := 'INVALID';

PL/SQL procedure successfully completed.

SQL> SELECT object_name FROM sqltab1 WHERE status =

:B1;

OBJECT_NAME

---------------------------------------------------------------------

DBA_COMMON_AUDIT_TRAIL

FGA_LOG$FOR_EXPORT

SUDOKU

T1_V

SQL> SELECT object_name FROM sqltab1 WHERE status =

:B1;

OBJECT_NAME

--------------------------------------------------------------------

DBA_COMMON_AUDIT_TRAIL

FGA_LOG$FOR_EXPORT

SUDOKU

T1_V

SQL> SELECT * FROM

table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------

SQL_ID fpd5qpchd1qx9, child number 0

-------------------------------------

SELECT object_name FROM sqltab1 WHERE status = :B1

Plan hash value: 2067510433

-------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows

| Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | TABLE ACCESS BY

INDEX ROWID| SQLTAB1 |

5 | 130 |

2 (0)|

00:00:01 |

|* 2 | INDEX RANGE SCAN

| IDX_SQLTAB_STATUS |

5 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 -

access("STATUS"=:B1)

Note

-----

- SQL plan baseline

SQL_PLAN_ahss6dnq16nkn0944e361 used for this statement

23 rows selected.

weixin_40005542

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
熊军 oracle 迁移 spm,oracle_SPM之sql_plan_baseline实现绑定变量值倾斜处理

(又一年马上要结束了)SPM是继SQLPROFILE出现的又一个绑定执行计划的方法，是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会启用，既能够主动地稳定执行计划，又保留了继续使用新的执行计划效率可能更高的执行计划的计划。SPM通过SQLPLANBASELINE实现执行计划的管理，代表一个执行计划，视图DBA_SQL_PLAN_BASELINES的字段ENABLED和ACCEPT...
复制链接

扫一扫