baseline引发的数据库CPU高的问题

某现场发现数据库CPU不管是夜里还是白天占用都较高，看awr发现某些sql执行时间都在1s左右，且全表扫描。

增加索引但是执行计划还是走全表，看执行计划，发现sql被绑定了baseline

1、查看当前enable的基线

怀疑是开启了基线自动捕获（optimizer_capture_sql_plan_baselines为true），查询当前被enable的baseline

select sql_handle,plan_name, ENABLED, ACCEPTED, FIXED, EXECUTIONS,sql_text from dba_sql_plan_baselines where enabled='YES' ORDER BY CREATED;

发现enable的baseline有2000+条

2、查看是否开启基线自动捕获

SQL> show parameter baseline

NAME TYPE VALUE

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

optimizer_capture_sql_plan_baselines boolean FALSE

optimizer_use_sql_plan_baselines boolean TRUE

发现optimizer_capture_sql_plan_baselines为false，怀疑是之前开过，现在被关掉了

3、disable某些top sql的基线使得其走正确的执行计划

可以将awr中的几个top sql的baseline disable，再查看执行计划是否恢复正常

select sql_handle,enabled,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_9aqzr7fuw4mzyf2573b72';

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c44f10fde605f685',plan_name=>'SQL_PLAN_c8mshzrm0bxn5f2573b72'));

select plan_table_output from table(dbms_xplan.display_cursor('35qxjzanp77a4',null,'ADVANCED'));

var i number; exec :i :=DBMS_SPM.alter_sql_plan_baseline(sql_handle=>'SQL_c44f10fde605f685',plan_name=>'SQL_PLAN_c8mshzrm0bxn5f2573b72',attribute_name=> 'ENABLED', attribute_value => 'NO'); var i number; exec :i :=DBMS_SPM.alter_sql_plan_baseline(sql_handle=>'SQL_befc04a5dd762aa7',plan_name=>'SQL_PLAN_bxz04nrfrcap7f2573b72',attribute_name=> 'ENABLED', attribute_value => 'NO'); var i number; exec :i :=DBMS_SPM.alter_sql_plan_baseline(sql_handle=>'SQL_955bf73bb5c24ffe',plan_name=>'SQL_PLAN_9aqzr7fuw4mzyf2573b72',attribute_name=> 'ENABLED', attribute_value => 'NO'); var i number; exec :i :=DBMS_SPM.alter_sql_plan_baseline(sql_handle=>'SQL_2bfdc244aa340328',plan_name=>'SQL_PLAN_2rzf28kp380t8f2573b72',attribute_name=> 'ENABLED', attribute_value => 'NO'); var i number; exec :i :=DBMS_SPM.alter_sql_plan_baseline(sql_handle=>'SQL_b33b862c6cbe44df',plan_name=>'SQL_PLAN_b6fw65jqbwj6z6fe2d437',attribute_name=> 'ENABLED', attribute_value => 'NO');

disable baseline后，这些sql的执行计划恢复正常，走索引了

4、disable剩余所有sql的baseline，并监控CPU的情况

declare

v_plan_num PLS_INTEGER;

begin

for cur in (SELECT * FROM dba_sql_plan_baselines where enabled='YES' and creator in('CC','CRM') loop

begin

v_plan_num := dbms_spm.alter_sql_plan_baseline(sql_handle => cur.sql_handle,plan_name=>cur.plan_name,attribute_name=> 'ENABLED', attribute_value => 'NO');

exception

when others then

null;

end;

end loop;

end;

/