KMC Data : All things Oracle

How to find blocked and blocking sessions

by Granville Bonyata on September 22, 2018

1) Find the session id (SID), serial# and instance (inst_id) of your session that is hanging. 2) Is your session being blocked? SELECT s.blocking_status, s.blocking_session, s.event FROM gv$session s WHERE s.sid = [789] AND s.serial# = [98543] AND s.inst_id = [1] If any one of these three is true: 1) The blocking_status is VALID or […]

Temp usage by session

by Granville Bonyata on March 30, 2018

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 […]

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 […]

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 /

Why Isn’t My Parallel Query Running in Parallel?

If everything is set correctly and the plan shows that it is parallel, Oracle may still not actually assign the full number of threads (or even any extra threads at all). Here is a simple query to get the reason that Oracle downgraded the degree of parallel for a query: select SID,sql_id,sql_exec_id, sql_exec_start, case otherstat_2_value […]

View history of query run time

SELECT TO_CHAR(s.end_interval_time, ‘MM/DD/YY HH24:MI’) end_interval_time, t.executions_delta executions, ROUND(T.ELAPSED_TIME_DELTA/1000000,2) elapsed_time_sec, ROUND((t.elapsed_time_delta/t.executions_delta)/1000000,3) elapsed_time_per_exec_sec, t.plan_hash_value, t.* FROM dba_hist_sqlstat t, dba_hist_snapshot s WHERE t.sql_id = ‘hjd56z95jdhw’ AND t.snap_id = s.snap_id AND t.executions_delta > 0 AND t.instance_number = s.instance_number AND s.end_interval_time > TRUNC(SYSDATE) – 7 ORDER BY s.end_interval_time, t.executions_delta / More here: https://docs.oracle.com/database/121/REFRN/GUID-F5A246E0-C04A-406C-9E10-AC26E7742F06.htm#REFRN23447

SQL Monitoring Report

One of the most useful tuning tools available is Oracle’s SQL Monitor Report with a rich amount of detail about a single execution of a query. There are a number of ways to get this. Here is a simple and direct way: 1) Add this hint to your query: /*+ MONITOR */ [Note: don’t include […]

See the contents of the buffer cache

If you’re looking for what is in the buffer cache, use this: select COUNT(*) ct, o.object_name, o.subobject_name, s.blocks from v$bh bh, dba_objects o, dba_segments s WHERE o.data_object_id = bh.objd AND o.owner like ‘SCHEMA_OWNER’ AND o.owner = s.owner AND o.object_name = s.segment_name AND NVL(o.subobject_name,’x’) = NVL(s.partition_name,’x’) AND s.blocks > 0 AND bh.status != ‘free’ GROUP BY […]

Gathering Stats When Loading Data

Introduced in 12c, in some situations we can gather stats online when the data is loaded without taking any extra time: 1) When doing a direct path load (the APPEND or PARALLEL hint uses direct path loads) 2) The table is empty before the load. If both those are true, use the hint GATHER_OPTIMIZER_STATISTICS and […]

Check this if you can’t kill a session

Sometimes a session is in v$session, but when you try to kill it you get an ORA-00030 Session Is does not exist. That session may be from a client that disconnected, and Oracle is now cleaning it up (i.e. rolling back whatever it had done). One way to check is to see if the session’s […]

← Previous Entries