dbms_spm之baseline FIXED=YES使用陷阱（执行计划不通用），严重影响系统；

来源：互联网发布：java web war包混淆编辑：程序博客网时间：2024/05/16 02:09
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--aiki下创建一实体表T1SQL> create table t1 as select * from dba_objects where object_id is not null;Table created.SQL> create index idx_t1_id on t1(object_id);Index created.SQL> select status from t1 where object_id=33;STATUS-------VALIDSQL> /STATUS-------VALIDSQL> /STATUS-------VALID--生成SQL_ID,PHV(PLAN HASH VALUE)SQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  71v4d139gzpzq, child number 0-------------------------------------select status from t1 where object_id=33Plan hash value: 190799060-----------------------------------------------------------------------------------------| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          ||   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    18 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1   2 - SEL$1 / T1@SEL$1Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=33)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "STATUS"[VARCHAR2,7]   2 - "T1".ROWID[ROWID,10]Note-----   - dynamic sampling used for this statement (level=2)  --采用SPM绑定 SQL>  declare  2   aa binary_integer;  3   begin  4   aa:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'71v4d139gzpzq',PLAN_HASH_VALUE=>190799060,FIXED=>'YES',ENABLED=>'YES');  5   return ;  6   end;  7   / PL/SQL procedure successfully completed.--查看baseline视图，已经绑定计划  1* select  SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselinesSQL> /SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX-------------------------------------------------- ------------------------------ --- --- ---select status from t1 where object_id=33           SQL_PLAN_b6tztbgtkhqdt74b15d2b YES YES YES--在Note部份可以看到baseline绑定已经生效，是走索引的--SQL plan baseline SQL_PLAN_b6tztbgtkhqdt74b15d2b used for this statementSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  71v4d139gzpzq, child number 1-------------------------------------select status from t1 where object_id=33Plan hash value: 190799060-----------------------------------------------------------------------------------------| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          ||   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    18 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1   2 - SEL$1 / T1@SEL$1Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"=33)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "STATUS"[VARCHAR2,7]   2 - "T1".ROWID[ROWID,10]Note-----   - dynamic sampling used for this statement (level=2)   - SQL plan baseline SQL_PLAN_b6tztbgtkhqdt74b15d2b used for this statement--看下内存中此SQL的游标分布,子游标号为2，0为父游标，phv:190799060SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------71v4d139gzpzq            0       19079906071v4d139gzpzq            2       190799060   --在用户AIKI2下创建一实体表T1,但无索引--可以看到baseline对它不生效，因为create table t1 as select * from dba_objects where object_Id is not null;   SQL> select status from t1 where object_id=33;STATUS-------VALIDSQL> select status from t1 where object_id=33;STATUS-------VALIDSQL> select status from t1 where object_id=33;STATUS-------VALIDPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  71v4d139gzpzq, child number 1-------------------------------------select status from t1 where object_id=33Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       |   300 (100)|          ||*  1 |  TABLE ACCESS FULL| T1   |    12 |   216 |   300   (1)| 00:00:04 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"=33)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "STATUS"[VARCHAR2,7]Note-----   - dynamic sampling used for this statement (level=2)--再次检查下可以发现子游标为2的游标已经被刷出内存，而取代它的是子游标为1的游标，phv:3617692013select sql_id,child_number,plan_hash_value from v$sql where sql_id='71v4d139gzpzq'SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------71v4d139gzpzq            0       19079906071v4d139gzpzq            1      3617692013--小结下：可以发现两条SQL_ID一样SQL,执行计划不同而导致两个执行计划的游标相互竞争，有你没我；--严重后果：在我国很多企业都是按省市下的地市来做业务，同一个库中包含多个地市的模型，而且大多--模型结果相同；当某SQL由于性能原因而采用SPM进行稳固，且fixes=>yes时得万分小心，否则将引由于--其中1SQL游标被刷出而再次高并发登录时产生大规模的library cache lock事件，导致CPU资源被耗光；--这个已经碰到过一次了；=》我们来看第2种情况，当两者之间也是SQL_ID相同而模型结构不同导致的执行计划不同时的情况；AIKI2用户建为视图：SQL> create view t2 as select distinct object_id,status,owner from dba_objects where object_id is not null;View created.AIKI用户建为实体表：SQL> create table t2 as select object_id,status,owner from dba_objects where object_id is not null;Table created.--AIKI用户下运行,sql_id:d7y1m4pt52xwd,phv:1513984157select status from t2 where object_id=55;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  d7y1m4pt52xwd, child number 0-------------------------------------select status from t2 where object_id=55Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       |    67 (100)|          ||*  1 |  TABLE ACCESS FULL| T2   |     3 |    54 |    67   (2)| 00:00:01 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T2@SEL$1Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"=55)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "STATUS"[VARCHAR2,7]Note-----   - dynamic sampling used for this statement (level=2)32 rows selected.--进行计划的稳固绑定SQL>  declare  2   aa binary_integer;  3   begin  4   aa:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'d7y1m4pt52xwd',PLAN_HASH_VALUE=>1513984157,FIXED=>'YES',ENABLED=>'YES');  5   return ;  6   end;  7   / PL/SQL procedure successfully completed.--查看已经生效SQL> select  SQL_TEXT,PLAN_NAME,ENABLED,ACCEPTED,FIXED from dba_sql_plan_baselines  2  /SQL_TEXT                                           PLAN_NAME                      ENA ACC FIX-------------------------------------------------- ------------------------------ --- --- ---select status from t2 where object_id=55           SQL_PLAN_7j9ssum4sn3fkb860bcf2 YES YES YESSQL> @allPLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  d7y1m4pt52xwd, child number 1-------------------------------------select status from t2 where object_id=55Plan hash value: 1513984157--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       |    67 (100)|          ||*  1 |  TABLE ACCESS FULL| T2   |     3 |    54 |    67   (2)| 00:00:01 |--------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T2@SEL$1Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("OBJECT_ID"=55)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "STATUS"[VARCHAR2,7]Note-----   - dynamic sampling used for this statement (level=2)   - SQL plan baseline SQL_PLAN_7j9ssum4sn3fkb860bcf2 used for this statement33 rows selected.--检查下当前的游标情况：SQL_ID:d7y1m4pt52xwd,子游标：1，PHV:1513984157SQL> select sql_id,child_number,plan_hash_value from v$sql where sql_id='d7y1m4pt52xwd';SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------d7y1m4pt52xwd            0      1513984157d7y1m4pt52xwd            1      1513984157--在AIKI2用户上运行SQL> select status from t2 where object_id=55;STATUS-------VALIDSQL> /STATUS-------VALIDSQL> /STATUS-------VALIDSQL> /STATUS-------VALIDSQL> /SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------d7y1m4pt52xwd            0      1513984157d7y1m4pt52xwd            2      3629752164aiki下再次执行，可以看到情况和第一次实验完全相同；SQL> select status from t2 where object_id=55;STATUS-------VALIDSQL> /STATUS-------VALIDSQL> /SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE------------- ------------ ---------------d7y1m4pt52xwd            0      1513984157d7y1m4pt52xwd            1      1513984157--可以看到情况与上面一样；这里纠正个问题，昨天写得不全面重改了以下部份；以于FIXED=YES的情况，才会导到多个会话间相互挤兑而引起的高并发下的大量library cache lock事件；由于实验过于繁琐冗长，我这里只写下小结，详细的证明可以自行去实验下；假定会话1叫S1,会话2叫S2，以及它们的计划和通用性；如下所示：
会话   SQL_ID   phv   FIXED   S1兼容S2       S1不兼容S2
S1    sqlid1    phv1   YES   不相互挤兑   相互挤兑
S2   sqlid1    phv2   NO      不相互挤兑   相互挤兑
补充备注：
当S1的FIXED=NO，S2不兼容时会自动将它的执行计划添加到BSLINE，默认accepted=no,但不发生挤兑；
当S1的FIXED=NO，S2兼容时会自动将它的最优执行计划添加到BSLINE，默认accepted=no,但不发生挤兑，同时使用S1的执行计划而不是它的最优计划；
当S1,S2都在BSLINE时FIXED=YES OR NO时两者是平等的；
这个实验最好自己去试下才会清楚，容易搞混；
0 0