terça-feira, 1 de agosto de 2017

How to force a sql_id to use a plan_hash_value using SQL Baselines

Olá pessoal,

Hoje pela manhã um cliente me ligou dizendo que estava com uma carga anormal no banco de dados.

Após verificar, vi que o SQL ID b6hd1bvsg59wr era responsável por 80% da carga do banco.

A primeira coisa foi verificar se ocorreu alguma mudança no plano de execução e quando ocorreu:



Usando o SQL abaixo, verifiquei que o novo plano foi criado no dia 31/7 às 16:45hrs:


O tempo médio de execuçao do PLAN_HASH_VALUE 2669208802 é de 63 segundos:


Enquanto o plano antigo era de apenas 4s.

Como as estatísticas estavam atualizadas, minha idéia inicial foi utilizar um SQL PLAN BASELINE e fixar o melhor plano de execução até ter tempo de otimizar e analisar melhor o SQL em questão.

Para fazer isso, segue o script utilizado:

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'MySTS');
END;
/

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MySTS',
    description => 'SQL Tuning Set SQL I_ID b6hd1bvsg59wr');
END;
/

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>13089, end_snap=>13090,basic_filter=>'sql_id = ''b6hd1bvsg59wr''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS', populate_cursor=>cur);
  CLOSE cur;
END;
/

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS', 
    basic_filter=>'plan_hash_value = ''368739152'''
    );
END;

SELECT * FROM dba_sql_plan_baselines order by created desc;


Após isso é necessário fazer o FLUSH do plano antigo da shared pool em todos nós do RAC:

select inst_id,ADDRESS, HASH_VALUE from gV$SQLAREA where SQL_ID like 'b6hd1bvsg59wr%';

exec dbms_shared_pool.purge('0000001C39C45688, 4042434455','C');
exec dbms_shared_pool.purge('0000001D0F7769C0, 4042434455','C');