Tuesday, May 3, 2011

Development DBA cheat sheet

-- Finding a session information
select s.machine  as "Client Machine",
       s.osuser   as "Client OS User",
       s.program  as "Client Program",
       s.username as "Database User",
       p.spid     as "Server Process ID"
  from v$session s,
       v$process p
 where s.sid     = 358
   and s.serial# = 560
   and p.addr    = s.paddr;

-- Finding a session information, RAC 
select s.machine       as "Client Machine",
       s.osuser        as "Client OS User",
       s.program       as "Client Program",
       s.username      as "Database User",
       p.spid          as "Server Process ID",
       i.instance_name as "Database Instance",
       i.host_name     as "Server Hostname"
  from gv$session  s,
       gv$process  p,
       gv$instance i
 where s.sid     = 269
   and s.serial# = 6360
   and p.addr    = s.paddr
   and p.inst_id = s.inst_id
   and i.inst_id = s.inst_id;

-- Finding blocking sessions
select blocker.sid      as "Blocking SID",
       blocker.serial#  as "Blocking Serial#",
       blocker.username as "Blocking User",
       waiter.sid       as "Waiting SID",
       waiter.serial#   as "Waiting Serial#",
       waiter.username  as "Waiting User"
  from v$session blocker,
       v$session waiter
 where waiter.blocking_session = blocker.sid
   and waiter.blocking_session_status = 'VALID';

-- Get all remote sessions
  from gv$session 
 where MACHINE not in (select HOST_NAME from gv$instance);

-- Killing a session
alter system kill session 'sid,serial#';
-- Killing a session, RAC
alter system kill session 'sid,serial#,@inst_id';

-- Finding current session's PID
select distinct sid from v$mystat;
select sys_context('USERENV','SID') from dual;

-- Tracing current session
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set sql_trace=true;
-- Stop tracing
alter session set sql_trace=false;

-- Tracing current session, another method
exec dbms_monitor.session_trace_enable(binds => true);
-- Stop tracing
exec dbms_monitor.session_trace_disable;

-- Tracing a different session
exec dbms_monitor.session_trace_enable( session_id => 358,
                                        serial_num => 560,
                                        waits      => true,
                                        binds      => true);
-- Stop tracing
exec dbms_monitor.session_trace_disable(358, 560);

-- Tracing a user's session using trigger
create or replace trigger set_user_trace
 after logon on database
 when (user = 'MURTY')
  execute immediate 'alter session set timed_statistics=true';
  execute immediate 'alter session set max_dump_file_size=unlimited';
  execute immediate 'alter session set sql_trace=true';

drop trigger set_user_trace;

-- Setting a session identifier
exec dbms_session.set_identifier('Trace_Me');
-- Finding SID, SERIAL# using a session identifier
select SID, SERIAL# from v$session where CLIENT_IDENTIFIER = 'Trace_Me';
-- Tracing using a session identifier
exec dbms_monitor.client_id_trace_enable(client_id => 'Trace_Me', waits => true, binds => true);
-- Stop tracing
exec dbms_monitor.client_id_trace_disable(client_id => 'Trace_Me');

--Finding trace file name and path using session ID, RAC & Non-RAC
       pa.VALUE || '/' || i.INSTANCE_NAME ||  '_ora_' || pr.SPID || '.trc' as TRACE_FILE
  from gv$session   s,
       gv$process   pr,
       gv$parameter pa,
       gv$instance  i
 where s.SID = 358 and s.SERIAL# = 560 -- s.CLIENT_IDENTIFIER = 'Trace_Me'
  and i.INST_ID   = s.INST_ID
  and pa.INST_ID  = s.INST_ID
  and pa.NAME     = 'user_dump_dest'
  and pr.INST_ID  = s.INST_ID
  and pr.ADDR     = s.PADDR;

-- Explain plan for a query
explain plan for select * from tab1;
select * from table(dbms_xplan.display);

-- Explain plan for just executed query (need to be serveroutput off)
set serveroutput off;
select * from tab1;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

Consolidating trace files

 $ trcsess output=a.trc session=358.560 mydb_ora_555.trc mydb_ora_666.trc

Using Client ID
 $ trcsess output=a.trc clientid=Trace_Me mydb_ora_555.trc mydb_ora_666.trc

Wild card in input files
 $ trcsess output=a.trc session=358.560 mydb*

Using tkprof

Recording SQLs
 $ tkprof a.trc record=rec.sql output=tk.txt

Output with execution plan
 $ tkprof a.trc explain=scott/tiger@mydb output=tk.txt

No comments:

Post a Comment