Travellers Trail: October 2018

Wednesday 17 October 2018

SQL for RAC db

RAC sql RAC Instances, DB instances, instance status, List of all instances, with startup time, up till, host load.


col host_name for a16
select i.instance_number, i.host_name, i.instance_name, 
  to_char(startup_time, 'DD Mon YYYY HH24:Mi') startup_time, 
  floor(sysdate - startup_time) uptill, i.status, round(o.value, 3) value  
from gv$instance i, gv$osstat o  
where i.instance_number = o.inst_id 
  and o.stat_name = 'LOAD' order by instance_number;


-- e.g.
INSTANCE_NUMBER HOST_NAME        INSTANCE_NAME    STARTUP_TIME                   UPTILL STATUS            VALUE
--------------- ---------------- ---------------- -------------------------- ---------- ------------ ----------
              1 proddbx00101     dex011           12 Feb 2018 02:52                 247 OPEN             15.979
              2 proddbx00102     dex012           12 Feb 2018 05:25                 247 OPEN                 18
              3 proddbx00103     dex013           12 Feb 2018 07:23                 247 OPEN              21.59
              4 proddbx00104     dex014           12 Feb 2018 09:37                 247 OPEN             19.079

Current Transaction for RAC


set line 250
col "username_sid_inst" for a35
select s.username||'('||s.sid||', '||s.inst_id||')' username_sid_inst,s.sid,t.start_time,
t.used_ublk as "Block Count", round(t.used_ublk*8192/1024/1024,2) Size_MB 
from gv$transaction t,gv$session s where s.saddr=t.ses_Addr and t.inst_id=s.inst_id order by start_time;

Session Wait for RAC


set line 250
col username for a24
col event for a45
col 'FileId# Obj#' for a16
select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", 
  s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state  
from gv$session s, gv$session_wait sw 
where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 
order by s.inst_id, s.username;

Sessions connected for RAC


-- Group by User (Summary)
select s.username, count(*) from gv$session s 
group by s.username order by s.username;

-- Group by Instance, User
select s.inst_id, s.username, count(*)   
from gv$session s 
group by s.inst_id, s.username
order by s.inst_id, s.username;

Locks (Holder/Waiter) for RAC


column sess format A20
select substr(DECODE(request,0,'Holder: ','Waiter: ')||sid||' ('||inst_id||')',1,18) sess,
       id1, id2, lmode, request, type, to_char(to_date(ctime,'sssss'),'  hh24:mi:ss') time_h_m_s
 from gv$lock
where (id1, id2, type) IN
   (select id1, id2, type from gv$lock where request>0)
     order by id1, request, ctime desc;

Thursday 4 October 2018

PDB DBA user


create user  c##dbareadonly  identified by  urdbapa55 container=all;

grant create session to c##dbateam container=all;
grant set container to c##dbateam container=all;
grant select_catalog_role to c##dbateam container=all;

alter user c##dbateam set container_data=all container = current;

Above (last line is important, otherwise dba user can't select * from v$pdbs, can't select from pdb views)

Favorite SQL for session waits

Session waits


col "FileId# Obj#" for a18
col event for a42

select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state  
from gv$session s, gv$session_wait sw 
where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 order by s.inst_id, s.username

Other related, or common sql


-- Current Transaction
col "username_sid" for a35
select s.username||'('||s.sid||')' username_sid,s.sid,t.start_time,t.used_ublk as "Block Count", 
round(t.used_ublk*8192/1024/1024,2) Size_MB 
from v$transaction t,v$session s where s.saddr=t.ses_Addr order by start_time;


-- Long OPS
select sid, serial#, context, sofar, totalwork, target, sql_hash_value, 
round(sofar/totalwork*100,2) "%_COMPLETE", opname, to_char(start_time, 'DD Mon YYYY - HH24:MI') start_time, 
round(time_remaining/60) tr, to_char(last_update_time, 'DD Mon YYYY - HH24:MI') last_update_time 
from v$session_longops 
where totalwork != 0 and sofar <> totalwork 
order by start_time;

SQL from hash value, SQL hash


set line 200
select * from v$sql where hash_value = 9999999;

select sq.sql_text from v$sqltext_with_newlines sq 
where sq.hash_value = 9999999
order by sq.piece

SELECT s.username, sl.sid, sq.executions, sl.last_update_time, sl.sql_id, sl.sql_hash_value,
       opname, target, elapsed_seconds, time_remaining, sq.sql_fulltext
  FROM v$session_longops sl
 INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
 INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
 WHERE time_remaining > 0

SQL from pid / session from Process Id / Session Id


set line 200
col schemaname for a20
col host_name for a20
col program for a16
col pprogram for a20
col machine for a20
col com for  a10

-- PID version
select p.spid, s.schemaname, s.sid, s.serial#, s.status, s.machine, s.program, 
   p.program pprogram, s.sql_hash_value, s.sql_id, s.prev_sql_id, 
   decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 
   'PL/SQL EXE', command) com , i.host_name, i.instance_name 
from v$session s, v$process p, v$instance i
where s.paddr = p.addr 
   and p.spid = 22844;

-- SID version
col "Sid_Ser#_SqlHash"for a24
select p.spid, s.schemaname, s.sid||','||s.serial#||' ['||s.sql_hash_value||']' as "Sid_Ser#_SqlHash", s.status, 
   s.machine, s.program, p.program pprogram, s.sql_id, s.prev_sql_id, 
   decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 
   'PL/SQL EXE', command) com , i.host_name, i.instance_name 
from v$session s, v$process p, v$instance i
where s.paddr = p.addr 
   and s.sid = 2790;