oracle_SPM之sql_plan_baseline实现绑定变量值倾斜处理_孙成子子

http://blog.sina.com.cn/u/2199343467

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

oracle_SPM之sql_plan_baseline实现绑定变量值倾斜处理

(2018-12-16 23:49:38)

标签：

spm

bind

sqlprofile

plan

baseline

分类： oracleops

（又一年马上要结束了）

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相同），来实现稳定执行计划的目的。

另一个文章对SQLPRFILE和SPM原理讲解http://blog.sina.com.cn/s/blog_8317516b0102v9qw.html

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

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

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

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

| 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

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

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

| 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_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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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.

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：oracle_针对二阶段锁（Two-Phase-Commit(2PC)）的处理方法

后一篇：oracle_SQLT之介绍章节

新浪BLOG意见反馈留言板　欢迎批评指正