March 2018

Temp usage by session

A useful query for getting temp usage by session: SELECT s.process, s.machine, s.username, s.inst_id, s.sid, s.serial#, s.sql_id, s.event,s.seconds_in_wait, s.module, s.action, temp.temp_gb, s.blocking_session, s.blocking_instance, s.program, s.logon_time FROM ( SELECT inst_id, session_adr, session_num, ts#, username, sql_id, sum(blocks*8192)/(1024*1024*1024) temp_gb FROM gv$sort_usage GROUP BY inst_id, session_addr, session_num, ts#, username, sql_id ) temp, gv$session s WHERE s.inst_id = temp.inst_id AND […]

Read more →

Why do DB file sequential reads happen with an FTS?

We had an interesting case in Oracle 12c where a multi-table join on a merge statement, running in Parallel (degree of parallel = 8), was doing a full table scan on a fairly large table, and was spending most of the query time on “db file sequential reads”. This isn’t supposed to happen on a […]

Read more →

How can I look at other sessions’ parameters?

Session variables can be found in gv$ses_optimizer_env. Use this query to find the values set for a particular user or session: select o.name, o.value, s.logon_time, s.prev_exec_start, s.status, s.sid, s.inst_id, s.username from gv$ses_optimizer_env o, gv$session s where o.inst_id = s.inst_id AND o.sid = s.sid and s.username = ‘APPLICATION USER’ order by s.logon_time desc /

Read more →