Query to check blocking and waiting sessions SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a WHERE c.waiting_session=a.sid and c.holding_session=b.sid To Find which session is blocking other sessions with some system info select s1.username || ‘@’ || s1.machine || ‘ [...]
Archive for the ‘Memory Architecture’ Category
28 Feb
PGA
Program Global Area (PGA) The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. The total PGA memory allocated by [...]
27 Feb
Recursive Calls
Recursive calls is a call(sql statement) that must be completed before the completion of user’s SQL statement. Basically, recursive calls are generated by Oracle internal sql statements to maintain changes to tables for internal processing. Reasons for Recursive Calls Recursive calls can be generated due to following reasons: Dictionary cache is too small resulting in [...]
27 Feb
SGA
Larger Pic Link A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. SGA information is shared among multiple users of oracle database instance, its sometimes called shared global area. Oracle automatically allocates memory to SGA when an instance starts up, and [...]
