Dúvida

Qual comando pode ser utilizado para localizar Locks em base de dados Oracle?

Solução

Importante

• O comando descrito abaixo é um exemplo de comando utilizado para coleta de Locks na base de dados (em tempo real) pela equipe de DBAs da Senior.
• A responsabilidade de execução deste comando é do DBA da sua empresa. Ele também poderá utilizar outros comandos para coleta dessa informação.
• O retorno deste select já exemplifica o comando que poderá ser utilizado para "matar" a sessão que aparecer como retorno do select (o comando será mostrado para cada linha retornada no select e iniciará sempre como ALTER SYSTEM KILL).

Para localizar locks em base de dados Oracle o seu DBA poderá utilizar o comando abaixo:

--SE FOR EXECUTAR O COMANDO NO SQLPLUS, ESSA PARTE ABAIXO É PARA FORMATAR A SAÍDA. NÃO PRECISA COLOCAR ISSO SE FOR EXECUTADO EM UMA FERRAMENTA TIPO SQLDEVELOPER
              SET LINESIZE 600 
              SET PAGESIZE 1000 
              COL LEV1 FORMAT A15 
              COL MIN_IN_WAIT FORMAT '999,990.00' 
              COL USERNAME FORMAT A15 
              COL OSUSER FORMAT A20 
              COL EVENT FORMAT A30 
              COL MACHINE FORMAT A20
              COL PROGRAMA FORMAT A20 
              COL COMANDO FORMAT A60
              COL IN_WAIT FORMAT A20

-- A PARTE ABAIXO É DE FATO O COMANDO PARA LOCALIZAÇÃO DOS LOCKS
              WITH A AS
              (SELECT LEVEL LEV, CONNECT_BY_ROOT COL1 BLOCKER, COL1 LEV1
                            FROM ((SELECT INST_ID || ',' || SID COL1,
                                                                         EVENT,
                                                                         SQL_ID,
                                                                         PREV_SQL_ID,
                                                                         PROGRAM,
                                                                         BLOCKING_INSTANCE || ',' || BLOCKING_SESSION COL2,
                                                                         SECONDS_IN_WAIT
                                                         FROM GV$SESSION))
                CONNECT BY NOCYCLE PRIOR COL2 = COL1
                 START WITH COL2 IN (SELECT BLOCKING_INSTANCE || ',' || BLOCKING_SESSION
                                                                                                   FROM GV$SESSION
                                                                                                   WHERE BLOCKING_SESSION IS NOT NULL)),
              B AS
              (SELECT DISTINCT LEV1
                            FROM A
                 WHERE (A.LEV, A.BLOCKER) IN
                                          (SELECT MAX(LEV), BLOCKER FROM A GROUP BY BLOCKER))
              SELECT LPAD(' ', 3 * (LEVEL - 1)) || COL1 LEV1,
                               SUBSTR(EVENT,1,20) EVENT,
                                          SQL_ID,
                                          PREV_SQL_ID,
                                          ROUND(SECONDS_IN_WAIT / 60) MIN_IN_WAIT,
                                          LPAD(TRUNC(SECONDS_IN_WAIT/60/60),2,'0') || ':'|| LPAD(TRUNC(MOD(SECONDS_IN_WAIT,3600)/60),2,'0') || ':' || LPAD(MOD(MOD(SECONDS_IN_WAIT,3600),60),2,'0')   IN_WAIT ,
                                          TO_CHAR(USERNAME) USERNAME,
                                          OSUSER,
                                          MACHINE,
                                          PROGRAMA
                               ,' ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID  || ''' IMMEDIATE; ' COMANDO
                               --,' ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''' IMMEDIATE; ' COMANDO
                               --,'EXEC RDSADMIN.RDSADMIN_UTIL.KILL(SID => '|| SID ||' , SERIAL => '|| SERIAL# ||' ,METHOD => ''IMMEDIATE'');'
                               --, 'EXEC SYS.ECT_KILL_SESSION('|| INST_ID || ',' || SID ||',' || SERIAL# ||')' 
                               -- ,SID,SERIAL#,INST_ID
                FROM ((SELECT INST_ID || ',' || SID COL1,
                                                                       EVENT,
                                                                       SQL_ID,
                                                                       PREV_SQL_ID,
                                                                       PROGRAM,
                                                                       BLOCKING_INSTANCE || ',' || BLOCKING_SESSION COL2,
                                                                       SECONDS_IN_WAIT,
                                                                       USERNAME,
                                                                       OSUSER,
                                                                       MACHINE,
                                                                       PROGRAM PROGRAMA,
                                                                       SERIAL#,
                                                                       SID,
                                                                       INST_ID                                          
                                             FROM GV$SESSION ))
              CONNECT BY NOCYCLE PRIOR COL1 = COL2
              START WITH COL1 IN (SELECT LEV1 FROM B);

Observação

Para mais informações sobre o questões relacionadas a Performance/Lentidão/Travamentos do Gestão Empresarial | ERP, consulte o artigo ERP - Performance/Lentidão/Travamentos - Onde é possível encontrar informações diversas sobre questões relacionadas a Performance/Lentidão/Travamentos (índice).