Modern Oracle DBA

Find SID for session SELECT sys_context('USERENV', 'SID') FROM DUAL; EX:sid: 4179 select sid,serial#,username,program,status,event from v$session where sid='4179'; Enable Trace for session level begin dbms_monitor.session_trace_enable(session_id=>4179,serial_num=>59963,binds=>true,waits=>true); end; SQL> select spid from v$process where addr=(select paddr from v$session where sid=9219); SPID ------------------------ 12124222 There was file system issue during which alerts got triggered Disable trace begin dbms_monitor.session_trace_disable(session_id=>4179,serial_num=>59963,binds=>true,waits=>true); end;

IMPORTANT VIEWS: v$sql          v$sql show the statistics on the shared sql area. Long running sql queries will update every 5-seconds in this view. It is ease to find the impact of the long running program. v$sql_plan It contains the execution plan information for each child cursor loader in library cache. v$session v$session show the session information for each current session. v$process It will show the currently active process. v$session_wait It will display the current/Last wait for each session v$system_event It will show the total wait event dba_hist_sqltext It will show the text of the sql statements this view capture the information from v$sql. dba_hist_sql_plan It will show the execution plan information this view capture the information from v$sql_plan. ​ V$SESSION: Hash_Value: Every SQL statement is generating unique value in library cache. Example same SQL query running again Oracle find the hash value in library cache and exec