Hi Guys,
Please check my below question, if asked before please just post the link that I can analyze.
Currently I am checking temp usage of queries which running on Oracle 11gR2, main goal is to monitor queries which use much more temp to avoid errors due to insufficient temp usage.
But query which I am using for this purpose return me space in gb which sometimes more than total TEMP space.
I am sharing in here the query which I am using, please comment which part can be wrong.
SELECT
S.sid,
SUM(T.blocks) * TBS.block_size / 1024 / 1024 / 1024 gb_used,
S.serial# sid_serial,
s.INST_ID,
S.username,
S.osuser,
s.status,
P.spid,
S.module,
P.program,
T.tablespace,
q.sql_text,
nvl(S.sql_id, S.PREV_SQL_ID) sql_id
FROM
gv$sort_usage T,
gv$session S,
dba_tablespaces TBS,
gv$process P,
gv$sql q
WHERE
T.session_addr = S.saddr
AND t.INST_ID = S.INST_ID
AND S.paddr = P.addr
AND S.INST_ID = P.INST_ID
AND nvl(S.sql_id, S.PREV_SQL_ID) = q.sql_id
AND T.tablespace = TBS.tablespace_name
AND S.STATUS = 'ACTIVE'
GROUP BY
S.sid,
S.serial#,
S.INST_ID,
S.username,
S.osuser,
P.spid,
S.status,
S.module,
P.program,
TBS.block_size,
T.tablespace,
q.sql_text,
nvl(S.sql_id, S.PREV_SQL_ID)
HAVING SUM(T.blocks) * TBS.block_size / 1024 / 1024 / 1024 > 2
order by 2 desc;
TEMP space is 180GB but sometimes this query shows me that gb_used is 200+ gb, and when I check gv$sort_usage or V$TEMPSEG_USAGE - actually I can see that 1 sql_id has several records and these records have different blocks which is used for sorting, if we take into account that these record in gv$sort_usage is historical and due to historical data I am summing in the query I got more than current TEMP usage then it is ok but there is no any column to define in gv$sort_usage which is historical or not and I think records are not historical as this is dynamic view cannot be historical, right?
So in this case please help me understand why sum of segments is more than TEMP tablespace size when I run above query?
Thanks beforehand,
AbdulAli Aliyev