una - Descubra qué proceso/consulta utiliza el tablespace temporal oracle

ver procesos en ejecucion oracle

Question

Preguntas frecuentes de Oracle define el espacio de tabla temporal de la siguiente manera:

Los espacios de tablas temporales se utilizan para administrar el espacio para las operaciones de clasificación de bases de datos y para almacenar tablas temporales globales. Por ejemplo, si une dos tablas grandes y Oracle no puede hacer la ordenación en la memoria, se asignará espacio en un espacio de tabla temporal para realizar la operación de clasificación.

Eso es genial, pero necesito más detalles sobre qué es exactamente el uso del espacio. Debido a las peculiaridades del diseño de la aplicación, la mayoría de las consultas realizan algún tipo de ordenamiento, por lo que debo restringirlo al ejecutable del cliente, la tabla de destino o la instrucción SQL.

Esencialmente, estoy buscando pistas para decirme con mayor precisión qué podría estar mal con esta (una aplicación bastante grande). Cualquier tipo de pista puede ser útil, siempre que sea más preciso que "ordenar".

Answer 1

No estoy seguro exactamente qué información tiene que entregar, pero al usar la siguiente consulta se le indicará qué programa / usuario / sesiones, etc. está utilizando actualmente su espacio temporal.

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = ''db_block_size''
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Una vez que averigüe qué sesión está causando el daño, eche un vistazo al SQL que se está ejecutando, y debe estar en el camino correcto.

Answer 2

Una regla de oro es que casi cualquier consulta que tome más de un segundo probablemente use algún espacio TEMP, y estos no son los justos que implican ORDER BYs sino también:

1. GROUP BYs (SORT GROUPBY antes de 10.2 y HASH GROUPBY de 10.2 en adelante)
2. HASH JOINs o MERGE JOINs
3. Global Temp Tables (obviamente)
4. Reconstrucciones de índices

Ocasionalmente, el espacio utilizado en los espacios de tablas temporales no es liberado por Oracle (bug / quirk), por lo que debe soltar manualmente un archivo del tablespace, soltarlo del sistema de archivos y crear otro.

Answer 3

Gracias a Michael OShea por su respuesta,

pero en caso de que tenga varias instancias de Oracle RAC, necesitará este ...

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.inst_ID
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = ''db_block_size''
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE=''TEMP2''
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

y esta es la secuencia de comandos para generar las sentencias kill: por favor revisa en qué sesiones estarás matando ...

SELECT  b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
       ''ALTER SYSTEM KILL SESSION ''''''||a.SID||'',''||a.SERIAL#||'',@''||a.inst_ID||'''''' IMMEDIATE;''
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = ''db_block_size''
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE=''TEMP''
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;