![]() This can be used to decide which sessions to kill if the DB is running out of processes Select sql_id, count( *) from v$session where program like '%P0% ' group by sql_id Check what is the top SQL executed by parallel slaves Select addr,SPID,username,program,pga_alloc_mem / 1024 mem_alloc_Kb from v$process order by pga_alloc_mem ![]() serial # ||'' 'Īnd (event = 'latch: shared pool ' or event = 'library cache lock ') and s. Select 'alter system kill session ' ' ' || s. Kill all sessions waiting for specific events by a specific user Select 'alter system kill session ' ' ' || SID || ', ' || serial # ||'' immediate ' from gv$session where username='BAD_USER' and inst_id=1 generate commands to kill all sessions from a specific user on specific instance ![]() Select sid,blocking_session,username,sql_id,event,machine,osuser,program from v$session where sid = 491 Select sid,blocking_session,username,sql_id,event,machine,osuser,program from v$session where blocking_session > 0 Then find what the blocking session is doing Find all sessions that are blocked and which session is blocking them program, status,sql_text,COMMAND_TYPEįrom gv$session s,gv$process p, gv$sqlarea sa serial #,s.USERNAME,s.COMMAND,s.MACHINE,s.SQL_ADDRESS,s.SQL_HASH_VALUE Check what a specific session is doing: left outer join gV$SQL_BIND_CAPTURE sbc on sbc.ADDRESS = s.SQL_ADDRESS and s.inst_id=p.inst_id ![]() ,sbc.name,to_char(sbc.last_captured,'yyyy-mm-dd hh24:mi:ss'),sbc.value_string serial #,s.USERNAME,s.COMMAND,s.MACHINE,s.blocking_session Where statement and column lists can be modified by the case Flexible query to check what's currently running in the system Select event, count( *) from v$session group by event order by count( *) Cannot retrieve contributors at this time - Check what the sessions in our instance are waiting for ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |