Quando precisamos forçar o expurgo do cursor de um SQL ID da Shared Pool para zerar as estatísticas da GV$SQL ou para forçar o otimizador pegar um novo plano após criar um SQL Profile / Baseline ou SQL Patch, podemos usar usar a procedure DBMS_SHARED_POOL.PURGE, como no exemplo abaixo:
-- gerando o comando de purge
select 'EXEC SYS.DBMS_SHARED_POOL.PURGE ('''||address||','||hash_value||''',''C'');' limpar_memoria
from V$SQLAREA
where SQL_ID='dd5mqfm821s4y' ;
LIMPAR_MEMORIA
--------------------------------------------------------------------------------------------------
EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000015D8359BC8,3491815582','C');
-- executando o comando
SQL> EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000015D8359BC8,3491815582','C');
PL/SQL procedure successfully completed.
Problema
Em um RAC, consultando a GV$SQLAREA ao invés da V$SQLAREA, temos o resultado equivalente para todos os nodes do cluster:
-- listando os cursores
SQL> select INST_ID, SQL_ID,ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID='&1' order by inst_id;
Inst SQL Id ADDRESS HASH_VALUE
---------- -------------------- ---------------- ----------
3 dd5mqfm821s4y 0000004BA8D56570 3491815582
4 dd5mqfm821s4y 00000015D8359BC8 3491815582
-- gerando os comandos de purge
SQL> select 'EXEC SYS.DBMS_SHARED_POOL.PURGE ('''||address||','||hash_value||''',''C'');' limpar_memoria
from GV$SQLAREA
where SQL_ID='&1'
order by inst_id;
LIMPAR_MEMORIA
--------------------------------------------------------------------------------------------------
EXEC SYS.DBMS_SHARED_POOL.PURGE ('0000004BA8D56570,3491815582','C');
EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000015D8359BC8,3491815582','C');
Mas na hora de executar, um dos comandos completa com sucesso e outro falha com o seguinte erro:
06570. 00000 - "shared pool object does not exist, cannot be pinned/purged"
*Cause: The specified shared pool shared cursor could not be found,
therefore it cannot be pinned/purged.
*Action: Make sure that a correct shared cursor name is given. Names
are a string of the form 'HHHHHHHH,SDDDDDDDDDD' where the H's
are an 8 digit hex number from the 'address' column of v$sqlarea,
and the D's are a 1 to 10 digit decimal number with an optional
leading sign (from the 'hash_value' column)
*Action: Remove the procedure from the calling stored procedure.
Isso ocorre porque internamente a procedure DBMS_SHARED_POOL.PURGE olha para a V$SQLAREA, por tanto o comando precisa ser executado na instância que contém o cursor em sua Shared Pool.
Solução (Alternativa)
Bom, por algum tempo eu estive abrindo uma sessão direto nos demais nodes que eu precisava executar esses comandos, mas era algo mais pontual, até que eu precisei ficar alguns dias na semana tendo que fazer diversos testes de fix de plano, o que tornou essa tarefa cansativa.
Então pra automatizar esse processo de ir na instância correta para executar cada comando, criei um script PL/SQL que usa a DBMS_SCHEDULER para criar um JOB temporário com a opção AUTO_DROP, pino o JOB para executar na instância em questão e voilá!
Script:
set verify off;
set serveroutput on;
declare
vJobName varchar2(200);
cursor listaCursores is
select inst_id, sql_id, hash_value, 'SYS.DBMS_SHARED_POOL.PURGE ('''||address||','||hash_value||''',''C'');' cmd
from GV$SQLAREA
where SQL_ID = '&1'
order by inst_id;
begin
for i in listaCursores loop
vJobName := '"PURGE_CURSOR_' || i.sql_id || '_' || i.hash_value || '_' || i.inst_id || '"';
dbms_scheduler.create_job
(
job_name => vJobName,
job_type => 'PLSQL_BLOCK',
job_action => 'begin ' || i.cmd ||' end;',
start_date => sysdate,
enabled => FALSE,
auto_drop => TRUE,
comments => 'Limpa cursor com SQL ID ' || i.sql_id || ' e Plan Hash Value ' || i.hash_value || ' na instance ' || i.inst_id
);
dbms_scheduler.set_attribute (name => vJobName, attribute => 'INSTANCE_ID', value => i.inst_id);
dbms_scheduler.enable (name => vJobName);
dbms_output.put_line('JOB: ' || vJobName || u'\000A' || i.cmd || u'\000A');
end loop;
end;
/
Exemplo
Para simplificar na usabilidade, coloco o script em arquivo purge_cursor_rac.sql” de onde posso executar no SQLPLUS ou SQLcl:
SQL> @purge_cursor_rac dd5mqfm821s4y
JOB: "PURGE_CURSOR_dd5mqfm821s4y_3491815582_1"
SYS.DBMS_SHARED_POOL.PURGE ('000000139FE12418,3491815582','C');
JOB: "PURGE_CURSOR_dd5mqfm821s4y_3491815582_2"
SYS.DBMS_SHARED_POOL.PURGE ('0000001477BCCE58,3491815582','C');
JOB: "PURGE_CURSOR_dd5mqfm821s4y_3491815582_3"
SYS.DBMS_SHARED_POOL.PURGE ('0000004BA8D56570,3491815582','C');
JOB: "PURGE_CURSOR_dd5mqfm821s4y_3491815582_4"
SYS.DBMS_SHARED_POOL.PURGE ('00000015D8359BC8,3491815582','C');
PL/SQL procedure successfully completed.
No exemplo acima, com um único comando, executei a DBMS_SHARED_POOL.PURGE nos 4 nodes do RAC sem precisar “sair do lugar”.
Pesquisando os JOBs
Como os JOBs criados são do tipo AUTO_DROP, eles não aparecem na DBA_SCHEDULER_JOBS, pois são dropados assim que são executados:
SQL> SELECT COUNT(*) FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME like 'PURGE_CURSOR%';
COUNT(*)
----------
0
Mas aparecem nos logs de JOBs executados:
SEL LINES 400
COL JOB_NAME FORMAT A50
SELECT log_date, job_name FROM dba_scheduler_job_log WHERE JOB_NAME like 'PURGE_CURSOR%';
LOG_DATE JOB_NAME
---------------------------------- --------------------------------------------------
03/11/22 22:01:54,268722000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_3
03/11/22 22:08:34,282696000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_3
03/11/22 21:50:11,494017000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 23:25:58,191698000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 22:01:53,993144000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 23:25:59,649110000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_1
03/11/22 21:44:55,538255000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 22:11:33,291855000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 23:25:59,628672000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_2
03/11/22 21:59:07,842071000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 22:08:34,346205000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_4
03/11/22 23:25:59,291732000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_3
03/11/22 22:11:33,412166000 -03:00 PURGE_CURSOR_dd5mqfm821s4y_3491815582_3
03/11/22 21:38:40,295864000 -03:00 PURGE_CURSOR_DD5MQFM821S4Y_3491815582_4
Para facilitar a pesquisa depois, o nome do JOB é gerado com a string “PURGE_CURSOR” + SQL ID + Plan Hash Value + Inst ID.
[…] de longa duração, pode ser mais produtivo solicitar o cancelamento da execução atual, forçar o purge do SQL_ID e iniciar uma nova […]