Monitor and instrument long runing query

With this query (compatible Oracle RAC) using the v$session_longops view, you can view any SQL statement that executes for more than 6 seconds. select s.inst_id, SQL.SQL_TEXT as « OPERATION », to_char(START_TIME, ‘dd/mm/yyyy hh24:mi:ss’) Start_Time, to_char(LAST_UPDATE_TIME, ‘dd/mm/yyyy hh24:mi:ss’) Last_Update_Time, round(TIME_REMAINING/60,1) as « MINUTES_REMAINING », round((SOFAR/TOTALWORK) * 100,2) as PCT_DONE from gv$session s, gv$sqlarea sql, gv$session_longops op where s.sid=op.sid and s.sql_id […]

Latch contention

Library cache latch contention is typically caused by NOT using bind variables. It is due to excessive parsing of statements. Cache buffers chain latches contention is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any […]