Simplificando Tarefas: Como Realizar a Limpeza de SQLIDs de Forma Eficiente em Clusters RAC

Olá, pessoal! Sextou!!! > Hoje, vou compartilhar com vocês uma solução que facilitará a vida de muitos DBAs Oracle que trabalham com ambientes RAC. Estou me referindo ao script flush_sqlid_rac.sql, uma ferramenta eficiente que permite limpar um SQLID específico em todas as áreas de memória do seu cluster com apenas alguns cliques.

Problema: Desempenho Degradado de um SQLID

Imagine um SQLID específico (da5aa5muh76r7) causando problemas de desempenho em um ambiente RAC de 5 nós. É notável que o plano de execução associado a este SQLID está ineficiente e precisa de correção.

Passos Tradicionais

Sem um script automatizado, o processo tradicional seria:

Conectar em Cada Instância: Você teria que se conectar manualmente a cada uma das 5 instâncias.
Executar o Flush Manualmente: Em cada instância, seria necessário executar o comando para limpar o SQLID da shared pool:

EXEC SYS.DBMS_SHARED_POOL.PURGE('00000001234ABCDEF,123456789','C');

Esse método é trabalhoso, lento e sujeito a erros, afinal, você terá que se conectar manualmente a cada instância do seu ambiente, o que demanda tempo e pode gerar erro durante o processo.

Solução: Script flush_sqlid_rac.sql

O script flush_sqlid_rac.sql simplifica todo esse processo, permitindo que você limpe o SQLID de todas as instâncias a partir de uma única conexão. Vamos ver como isso funciona na prática.

Aqui está o script que será utilizado (`flush_sqlid_rac.sql`):

-- +-------------------------------------------------------------------------------------------+
-- | Objetivo   : Remove SQLID da Shared Pool de Todas as Instancias                           |
-- | Criador    : Roberto Fernandes Sobrinho                                                   |
-- | Data       : 24/05/2024                                                                   |
-- | Exemplo    : @flush_sqlid_rac.sql                                                         |  
-- | Arquivo    : flush_sqlid_rac.sql                                                          |
-- | Referncia  :                                                                              |
-- | Modificacao:                                                                              |
-- +-------------------------------------------------------------------------------------------+
-- |                                                                https://dbasobrinho.com.br |
-- +-------------------------------------------------------------------------------------------+
-- |"Se você tem amor pelo que tem no peito, mantenha o respeito."
-- +-------------------------------------------------------------------------------------------+
SET TERMOUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
EXEC dbms_application_info.set_module( module_name => 'f[flush_sqlid_rac.sql ]', action_name =>  'f[flush_sqlid_rac.sql ]');
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual;
SET SERVEROUTPUT ON;
SET TERMOUT ON;
PROMPT
PROMPT +-------------------------------------------------------------------------------------------+
PROMPT | https://github.com/dbasobrinho/g_gold/blob/main/flush_sqlid_rac.sql                       |
PROMPT +-------------------------------------------------------------------------------------------+
PROMPT | Script   : Remove SQLID da Shared Pool de Todas as Instancias    +-+-+-+-+-+-+-+-+-+-+-+  |
PROMPT | Instancia: &current_instance                                     |d|b|a|s|o|b|r|i|n|h|o|  |
PROMPT | Versao   : 1.0                                                   +-+-+-+-+-+-+-+-+-+-+-+  |
PROMPT +-------------------------------------------------------------------------------------------+
PROMPT
PROMPT .
PROMPT .
PROMPT -------------------------------------------------------->
ACCEPT v_sql_id CHAR PROMPT 'Informe o SQL_ID: ';
PROMPT -------------------------------------------------------->
PROMPT .
PROMPT .
SET ECHO        OFF
SET FEEDBACK    off
SET HEADING     ON
SET LINES       188
SET PAGES       300 
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
BEGIN
    FOR sql_row IN (
        SELECT DISTINCT inst_id, sql_id, hash_value, address
        FROM GV$SQL
        WHERE SQL_ID = '&&v_sql_id'
        ORDER BY inst_id
    ) LOOP
        DBMS_SCHEDULER.CREATE_JOB(
            job_name      => '"PBUM_' || sql_row.sql_id || '_' || sql_row.inst_id || '"',
            job_type      => 'PLSQL_BLOCK',
            job_action    => 'BEGIN SYS.DBMS_SHARED_POOL.PURGE (''' || sql_row.address || ',' || sql_row.hash_value || ''',''C''); END;',
            start_date    => SYSDATE,
            enabled       => TRUE,
            auto_drop     => TRUE,
            comments      => 'O Guina não tinha dó e removeu o SqlID: ' || sql_row.sql_id || ' na instância ' || sql_row.inst_id
        );

        DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"PBUM_' || sql_row.sql_id || '_' || sql_row.inst_id || '"', attribute => 'INSTANCE_ID', value => sql_row.inst_id);

        DBMS_OUTPUT.PUT_LINE('JOB: "PBUM_' || sql_row.sql_id || '_' || sql_row.inst_id || '" Plan Hash Value: ' || sql_row.hash_value || ' SYS.DBMS_SHARED_POOL.PURGE (''' || sql_row.address || ',' || sql_row.hash_value || ''',''C'');');
    END LOOP;
    --/
    DBMS_OUTPUT.PUT_LINE('.    ');
    DBMS_OUTPUT.PUT_LINE('.    ');
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------->');
    DBMS_OUTPUT.PUT_LINE('Executando a Limpeza! Aguarde! . . .');
    DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------->');
    DBMS_OUTPUT.PUT_LINE('.    ');
    DBMS_OUTPUT.PUT_LINE('.    ');
    --/
END;
/
exec DBMS_LOCK.SLEEP(15);
SELECT COUNT(*) FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'PBUM%';
PROMPT .
PROMPT .
COLUMN LOG_DATE FORMAT A40
COLUMN JOB_NAME FORMAT A40
COLUMN STATUS   FORMAT A11
SELECT log_date, job_name, STATUS FROM dba_scheduler_job_log WHERE JOB_NAME LIKE 'PBUM%' and JOB_NAME  LIKE '%&&v_sql_id%' AND log_date >= TRUNC(SYSDATE) ORDER BY log_date;
PROMPT .
PROMPT .

SET FEEDBACK ON;

Como Executar o Script

Para executar o script e limpar o SQLID da5aa5muh76r7, siga estes passos:

Abra uma conexão com o banco de dados: Conecte-se a qualquer uma das instâncias do RAC.
Execute o script: Use o comando abaixo, e quando solicitado informe o SQLID como parâmetro:

SQL> @flush_sqlid_rac.sql

+-------------------------------------------------------------------------------------------+
| https://github.com/dbasobrinho/g_gold/blob/main/flush_sqlid_rac.sql                       |
+-------------------------------------------------------------------------------------------+
| Script   : Remove SQLID da Shared Pool de Todas as Instancias    +-+-+-+-+-+-+-+-+-+-+-+  |
| Instancia: bieprd5                                               |d|b|a|s|o|b|r|i|n|h|o|  |
| Versao   : 1.0                                                   +-+-+-+-+-+-+-+-+-+-+-+  |
+-------------------------------------------------------------------------------------------+

.
.
-------------------------------------------------------->
Informe o SQL_ID: da5aa5muh76r7
-------------------------------------------------------->
.
.
JOB: "PBUM_da5aa5muh76r7_1" Plan Hash Value: 4110654183 SYS.DBMS_SHARED_POOL.PURGE ('0000004A978E0B40,4110654183','C');
JOB: "PBUM_da5aa5muh76r7_2" Plan Hash Value: 4110654183 SYS.DBMS_SHARED_POOL.PURGE ('0000004B39498550,4110654183','C');
JOB: "PBUM_da5aa5muh76r7_3" Plan Hash Value: 4110654183 SYS.DBMS_SHARED_POOL.PURGE ('0000004AD8B87D08,4110654183','C');
JOB: "PBUM_da5aa5muh76r7_4" Plan Hash Value: 4110654183 SYS.DBMS_SHARED_POOL.PURGE ('0000004B3393B448,4110654183','C');
JOB: "PBUM_da5aa5muh76r7_5" Plan Hash Value: 4110654183 SYS.DBMS_SHARED_POOL.PURGE ('0000004A763BABC0,4110654183','C');
.
.
-------------------------------------------------------->
Executando a Limpeza! Aguarde! . . .
-------------------------------------------------------->
.
.

  COUNT(*)
----------
         0
.
.

LOG_DATE                                |JOB_NAME                                |STATUS
----------------------------------------|----------------------------------------|-----------
24-MAY-24 10.47.14.428315 AM -03:00     |PBUM_da5aa5muh76r7_4                    |SUCCEEDED
24-MAY-24 10.47.15.304601 AM -03:00     |PBUM_da5aa5muh76r7_5                    |SUCCEEDED
24-MAY-24 10.47.17.006437 AM -03:00     |PBUM_da5aa5muh76r7_1                    |SUCCEEDED
24-MAY-24 10.47.18.495033 AM -03:00     |PBUM_da5aa5muh76r7_3                    |SUCCEEDED
24-MAY-24 10.47.18.820499 AM -03:00     |PBUM_da5aa5muh76r7_2                    |SUCCEEDED
.
.
SQL>

Explicação da Saída

Criação de Jobs: Para cada instância, o script cria um job com o nome "PBUM_{SQLID}_{INST_ID}" e a ação de purgar o plano de execução específico.
Atribuição de Instâncias: Os jobs são associados às instâncias corretas, garantindo que o purge seja executado na instância específica.
Feedback ao Usuário: O script fornece feedback sobre os jobs criados e suas ações.
Execução do Flush: Os jobs são automaticamente executados, garantindo que o plano de execução do SQLID seja purgado em todas as instâncias simultaneamente.
Status dos Jobs: Status sobre a execução dos JOBS.

Dicas Rápidas:

Utilize o script flush_sqlid_rac.sql para limpar SQLIDs específicos em todas as instâncias do seu ambiente RAC de forma rápida e eficiente.
Ao executar o script, certifique-se de passar o SQLID como parâmetro para garantir que o SQLID desejado seja limpo.
Verifique regularmente os SQLIDs em execução no seu ambiente e limpe aqueles que estão causando problemas de desempenho para evitar impactos negativos no sistema com um todo.

O script flush_sqlid_rac.sql representa uma solução eficaz para os desafios enfrentados por DBAs Oracle em ambientes RAC. Ao simplificar o processo de limpeza de SQLIDs em todas as instâncias do cluster, oferece uma abordagem ágil e confiável em comparação com métodos manuais. Ao eliminar a necessidade de conexões repetitivas e execuções individuais, o script não apenas economiza tempo, mas também reduz a margem de erro humano. O flush_sqlid_rac.sql se destaca como uma ferramenta indispensável para otimizar o gerenciamento de ambientes Oracle RAC e resolver rapidamente problemas de desempenho relacionados a SQLIDs específicos

#GuinaNaoTinhaDo #BóBó #CaceteDeAgulha #DBA #Otimização #FogoNoPavioOsManoInvadiu
🚀🚀🚀 👋👋👋👋

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.