Oracle Log switch report

checkpoints per hour

Log switch report

set feed off;
set pagesize 10000;
set wrap off;


set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_logswitches.lst
ttitle left 'redolog file status from v$log' skip 2 select group#, sequence#, members, archived, status, first_time from v$log;
ttitle left 'number of logswitches per hour' skip 2
select to_char(first_time,'dd.mm.yyyy') day,
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'00' ,1,0)),'99')  "00",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'01' ,1,0)),'99')  "01", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'02' ,1,0)),'99')  "02",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'03' ,1,0)),'99')  "03",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'04' ,1,0)),'99')  "04",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'05' ,1,0)),'99')  "05", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'06' ,1,0)),'99')  "06", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'07' ,1,0)),'99')  "07",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'08' ,1,0)),'99')  "08",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'09' ,1,0)),'99')  "09", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'10' ,1,0)),'99')  "10",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'11' ,1,0)),'99')  "11",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'12' ,1,0)),'99')  "12",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'13' ,1,0)),'99')  "13", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'14' ,1,0)),'99')  "14", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'15' ,1,0)),'99')  "15",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'16', 1,0)),'99')  "16", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'17' ,1,0)),'99')  "17", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'18' ,1,0)),'99')  "18",
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'19' ,1,0)),'99')  "19", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'20' ,1,0)),'99')  "20", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'21' ,1,0)),'99')  "21", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'22' ,1,0)),'99')  "22", 
to_char(sum(decode(substr(to_char(first_time,'ddmmyyyy:hh24:mi'),10,2),'23' ,1,0)),'99')  "23" 
from v$log_history
group by to_char(first_time,'dd.mm.yyyy')
 /

spool off;



 @log



number of logswitches per hour

DAY        00  01  02  03  04  05  06  07  08  09  10  11  12  13  14  15  16  17  18  19  20  21  22  23
---------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
29.04.2011   0   0   0   0   0   0   1   0   0   0   0   0   0   0   9   0   0   0   0   0   0   0   2   0
07.05.2011   0   0   0   0   0   0   2   0   0   0   0   0   0   0   1   0   0   0   0   0   0   1   0   1
14.06.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   5   0   0   0   0   0   2   0
15.06.2011   0   0   0  13   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   1   0
07.07.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0
26.07.2011   0   0   0   0   0   0   0   1   1   0   0   3   0   2   6   5   1   0   0   0   0   0   0   0
04.08.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
21.08.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
31.08.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
21.09.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
03.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0
14.10.2011   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0
28.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0
10.05.2011   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   2   0
11.05.2011   2   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   2   0
21.05.2011   1   0   0   0   0   0   1   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   1   0
23.06.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
24.07.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0  11  12   0   0   0   0   0   0   0
14.08.2011   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0
24.09.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0
05.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
03.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   5   2   0   0   7   0   0   0   0
12.04.2011   1   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   2   0
08.05.2011   0   0   0   0   0   0   2   0   0   0   0   1   0   0   0   0   0   0   0   0   0   1   0   1
09.05.2011   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   1   1   1
13.05.2011   1   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0  18   0   0   0   0   0   3   0
20.06.2011   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0
03.07.2011   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
10.07.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0
01.08.2011   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
28.08.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
27.09.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
20.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0
03.05.2011   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   1   1
06.05.2011   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   1   1
16.05.2011   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   1   0
27.06.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0
15.07.2011   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
08.09.2011   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   1   0   0   0   0   0   0   0   0
11.09.2011   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
18.09.2011   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
12.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
31.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0
23.11.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
20.05.2011   1   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   1   0
16.06.2011   0   0   0   0   1   0   0   1   0   1   6   0   0   0   0   0   0   0   0   0   0   0   0   0
06.09.2011   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0
15.09.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
17.10.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
25.10.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
20.11.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
13.04.2011   1   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   2   0
17.05.2011   1   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   1   0
23.05.2011   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   1   0
25.05.2011   1   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   2   0
30.06.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0
17.08.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
23.10.2011   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
29.10.2011   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
02.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   2   0   0   0   0   0   0   0   0   0   0
07.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   3   0   0   0   0   0   0   0   0   0
10.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1
11.04.2011   1   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   2   0
02.05.2011   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   1   1
15.05.2011   0   0   0   0   0   1   2   0   0   0   2   0   0   0   0   0   0   0   1   0   0   0   0   0
18.05.2011   1   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   1   0
24.05.2011   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0
26.05.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
18.07.2011   1   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
11.08.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0
06.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1
13.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1
10.04.2011   0   0   0   0   0   0   2   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   1
30.04.2011   0   0   0   0   0   1   0   0   0   0   1   0   0   0   1   0   0   0   0   0   0   1   0   0
01.05.2011   0   0   0   0   0   0   1   0   0   0   0   0   1   0   1   0   0   0   1   0   0   0   1   0
04.05.2011   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   2   1
05.05.2011   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   1   1
12.05.2011   2   0   0   0   0   0   0   0   0   0   0   0   5   0   0   0   0   0   0   0   0   0   2   1
19.05.2011   1   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   1   0
22.05.2011   1   0   0   0   0   0   1   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   1   0
07.08.2011   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0
24.08.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1
10.10.2011   0   0   0   0   0   0   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0   0   0   0
16.11.2011   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   1

Unix Pid to Oracle  Sql ..

Unix Command 

top -U oracle -d 5 -n 25
iotop
iotop -user oracle
alias topmem='ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head'

SET LINESIZE 80 HEADING OFF FEEDBACK OFF
SELECT
RPAD('USERNAME : ' || s.username,80) ||
RPAD('OSUSER   : ' || s.osuser,  80) ||
RPAD('PROGRAM  : ' || s.program, 80) ||
RPAD('SPID     : ' || p.spid,    80) ||
RPAD('SID      : ' || s.sid,     80) ||
RPAD('SERIAL#  : ' || s.serial#, 80) ||
RPAD('MACHINE  : ' || s.machine, 80) ||
RPAD('TERMINAL : ' || s.terminal,80) ||
RPAD('SQL TEXT : ' || q.sql_text,80)
FROM v$session s
,v$process     p
,v$sql         q
WHERE s.paddr        = p.addr
AND p.spid           = '&PID_FROM_OS'
AND s.sql_address    = q.address(+)
AND s.sql_hash_value = q.hash_value(+);
  

@pid
Enter value for pid_from_os: 10165
old  15: AND p.spid = '&PID_FROM_OS'
new  15: AND p.spid = '10165'

USERNAME :
OSUSER   : oracle
PROGRAM  : oracle@apt-amd-02 (VKTM)
SPID     : 10165
SID      : 3
SERIAL#  : 1
MACHINE  : apt-amd-02
TERMINAL : UNKNOWN
SQL TEXT :

*****************************************

SET LINES 200 PAGES 0 HEAD OFF LONG 100000
COL dummy_value NOPRINT
--
SELECT 'dummy1' dummy_value,
'USERNAME   : ' || s.username       || CHR(10) ||
'SCHEMA     : ' || s.schemaname     || CHR(10) ||
'OSUSER     : ' || s.osuser        || CHR(10) ||
'MODULE     : ' || s.program        || CHR(10) ||
'ACTION     : ' || s.schemaname       || CHR(10) ||
'CLIENT INFO   : ' || s.osuser        || CHR(10) ||
'PROGRAM   : ' || s.program        || CHR(10) ||
'MACHINE   : ' || s.machine        || CHR(10) ||
'TYPE    : ' || s.type         || CHR(10) ||
'TERMINAL   : ' || s.terminal        || CHR(10) ||
'CPU     : ' || q.cpu_time/1000000      || CHR(10) ||
'ELAPSED_TIME  : ' || q.elapsed_time/1000000   || CHR(10) ||
'BUFFER_GETS   : ' || q.buffer_gets       || CHR(10) ||
'SQL_ID   : ' || q.sql_id        || CHR(10) ||
'CHILD_NUM   : ' || q.child_number       || CHR(10) ||
'STATUS   : ' || s.status            || CHR(10) ||
'SPID    : ' || p.spid         || CHR(10) ||
'SID     : ' || s.sid         || CHR(10) ||
'SERIAL#   : ' || s.serial#        || CHR(10) ||
'KILL STRING   : ' || '''' || s.sid || ',' || s.serial# || ''''  || CHR(10) ||
'START_TIME   : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi')  || CHR(10) ||
'SQL_TEXT   : ' || q.sql_fulltext
FROM v$session s
JOIN v$process p ON (s.paddr = p.addr)
LEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)
WHERE 1=1 
AND s.username IS NOT NULL -- eliminates background procs
AND NVL(q.sql_text,'x') NOT LIKE '%dummy1%' -- eliminates this query from output
AND p.spid = '&PID_FROM_OS'
ORDER BY q.cpu_time;