Andre Spadini
André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Uso TEMP

Oracle: Uso TEMP

Consumo da Tempfile no Oracle Database.

Andre Spadini's photo
Andre Spadini
·Jan 12, 2022·

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Segue abaixo query para analisar a query que está consumindo mais a temp

SET LINES 300;
SET PAGES 5000;
COLUMN USERNAME FOR A23;
COLUMN MACHINE FOR A33;
COLUMN LOCKWAIT FOR A8;
COLUMN PROGRAM FOR A45;
COLUMN EVENT FOR A35;
COLUMN OSUSER FOR A20;
COLUMN SCHEMANAME FOR A30;
COLUMN MODULE FOR A35;
COLUMN SID_SERIAL FOR A25;
COLUMN TEMP_SIZE FOR A15;
COLUMN TABLESPACE FORMAT A15;

--TEMP 
SELECT B.TABLESPACE,
       ROUND(((B.BLOCKS*P.VALUE)/1024/1024/1024),2)||' GB' AS TEMP_SIZE,
       A.MACHINE AS MACHINE,
       A.SID||','||A.SERIAL# AS SID_SERIAL,
       NVL(A.USERNAME, '(oracle)') AS USERNAME,
       A.PROGRAM,
       A.STATUS,
       A.SQL_ID
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    ROUND(((B.BLOCKS*P.VALUE)/1024/1024/1024),2) > '0.02'
ORDER BY B.TABLESPACE, B.BLOCKS desc

Output:

TABLESPACE      TEMP_SIZE       MACHINE                           SID_SERIAL                USERNAME                PROGRAM                                       STATUS   SQL_ID
--------------- --------------- --------------------------------- ------------------------- ----------------------- --------------------------------------------- -------- -------------
TEMP            19.05 GB        WORKGROUP\BES-LINX-PROD           3960,44182                CNP                     ApolloServerApp.exe                           ACTIVE   a7xtpuc3ghzky
TEMP            .08 GB          WORKGROUP\BES-LINX-PROD           809,23608                 CNP                     ApolloServerApp.exe                           ACTIVE   a7xtpuc3ghzky
 
Share this