Temporary Tablespace : Identificando consultas consumindo uma Tablespace Temporária
Recentemente nos deparamos com uma lentidão anormal no ambiente, analisando de maneira geral o database identificamos o WAIT_EVENT na GV$SESSION com "statement suspended, wait error to be cleared" em várias sessões identificando algo generalizado e no alert.log o erro "ORA-01652 - Unable to extend temp segment by 128 MB ".
Dessa forma o problema estava claro. A tablespace TEMP está totalmente consumida e era necessário o resize, correto ? Na verdade não. Dessa forma seguimos na identificação da(s) consulta(s) ofensoras(s) buscando a causa raiz do problema, simplesmente aumentar a tablespace resolveria o problema momentaneamente pois a query ofesora não seria analisada e provavelmente consumiria o espaço adicional colocado e teriamos uma nova incidência do problema.
Para entendermos melhor, o oracle usa espaços de tabela temporários como áreas de trabalho para tarefas como operações de classificação para usuários e classificação durante a criação do índice, conhecidos como Sort Operations.
Sort Operations: Group by, order by, distincts, create index, table partitions e etc.
Dessa forma o nosso foco era identificar essa sessão (uma ou mais) consumindo a TEMP, evidenciar suas origem, host, o user, o SQL_ID e etc, matar a sessão para normalizar o ambiente e realizar uma análise posterior e mais profunda para identificar pontos de melhora da query evitando que isso ocorra novamente.
O script abaixo mostra como identificar consultas realizando operações de sort:
SET LINES 300; SET PAGES 5000; COLUMN USERNAME FOR A28; COLUMN MACHINE FOR A33; COLUMN LOCKWAIT FOR A8; COLUMN PROGRAM FOR A30 TRUNC; COLUMN EVENT FOR A30; COLUMN OSUSER FOR A20; COLUMN SCHEMANAME FOR A30; COLUMN MODULE FOR A35; COLUMN SID_SERIAL FOR A25; --COLUMN TEMP_SIZE FOR A15; COLUMN TEMP_SIZE HEADING "Temp Size (GB)" FORMAT A15; COLUMN TABLESPACE FORMAT A15; COLUMN LAST_CALL_ET FORMAT A15; SELECT B.TABLESPACE, ROUND(((B.BLOCKS*P.VALUE)/1024/1024/1024),2)||' GB' AS TEMP_SIZE, A.SID||','||A.SERIAL#||','||A.INST_ID AS SID_SERIAL, NVL(A.USERNAME, '(oracle)') AS USERNAME, A.OSUSER, A.SQL_ID, A.PROGRAM, A.EVENT, A.STATUS, TRUNC(A.LAST_CALL_ET / (60 * 60 * 24)) || 'D ' || TRUNC(MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24) || 'H ' || TRUNC(MOD((MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24), TRUNC(MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24)) * 60) || 'M ' || TRUNC(MOD((MOD((MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24), TRUNC(MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24)) * 60), (TRUNC(MOD((MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24), TRUNC(MOD(A.LAST_CALL_ET / (60 * 60 * 24), TRUNC(A.LAST_CALL_ET / (60 * 60 * 24))) * 24)) * 60))) * 60) || 'S ' AS LAST_CALL_ET FROM GV$SESSION A, GV$SORT_USAGE B, GV$PARAMETER P WHERE P.NAME = 'db_block_size' AND A.SADDR = B.SESSION_ADDR AND A.INST_ID=B.INST_ID AND A.INST_ID=P.INST_ID AND A.SQL_ID IS NOT NULL --AND ROUND(((B.BLOCKS*P.VALUE)/1024/1024/1024),2) > '0.02' ORDER BY B.TABLESPACE, B.BLOCKS desc;
Outro script útil que mostra a data de Logon da sessão:
SET LINES 9999
SET PAGES 100
COL DATA FORMAT A20
COL USERNAME FORMAT A15
COL INST_ID FORMAT 99
COL SQL_ID FORMAT a15
COL osuser FORMAT A25
COL EVENT FORMAT a30 trunc
COL temp_size HEADING "Temp Size (MB)" FORMAT 999,999,999
break on report on tablespace
compute avg sum label "Total : " of temp_size on report
SELECT b.tablespace,
TO_CHAR(LOGON_TIME,'DD/MM/YYYY HH24:MI:SS') AS LOGON_TIME,
NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
a.inst_id,
a.sql_id,
a.status,
a.event,
ROUND( sum((b.blocks*p.value)/1024/1024),2) AS temp_size
FROM gv$session a, gv$sort_usage b, gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id
AND a.inst_id=p.inst_id
group by NVL(a.username, '(oracle)'),
SID, a.osuser,
b.tablespace,
a.sid,
a.inst_id,
a.sql_id,
a.serial#,
a.status,
a.event,
TO_CHAR(LOGON_TIME,'DD/MM/YYYY HH24:MI:SS')
ORDER BY temp_size desc;
TOPPP
ResponderExcluir