Shell: extract more from listener.log(分析监听日志)

统计一天内每小时的session请求数

# fgrep "13-JAN-2015 " anbob_listener.log  |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 }' |sort |uniq -c

 

指定的一小时每分钟session请求数

# fgrep "13-JAN-2015 11:" anbob_listener.log  |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c

 

指定的一小时每秒session请求数

# fgrep "13-JAN-2015 11:30" anbob_listener.log  |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 ":" $3 }' |sort |uniq -c

 

指定的一小时内每分钟连接创建失败数

#fgrep "11-JAN-2015 11:" anbob_listener.log |awk  '{ if ( $NF != 0 ) print $0 }'|awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c

 

指定的一小时内每IP请求数

#fgrep "11-JAN-2015 11:" anbob_listener.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort

 

指定的分钟内每IP请求数

#fgrep "11-JAN-2015 11:30" anbob_listener.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort 

 

全天每小时每个IP请求数

fgrep "09-JAN-2015 " anbob_listener.log|fgrep "establish"|awk -F* '{print $1 " " $3}'|awk -F= '{ print $1 " "  $4}'|sed -e 's/......$//g'| awk '{print $1 " " $2 " " $4}'|cut -b-14,21- |sort |uniq -c 

 

-- file: session_rpt.sql
-- Purpose: list of session information 
-- Author:      weejar
-- Copyright:   (c) ANBOB - http://www.anbob.com.com - All rights reserved.
prom
prom List of sessions (c)anbob.com
prom
select 'anbob.com' author,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') current_time,instance_name from v$instance
/
col sid form 99999
col serial# form 99999
col spid form a6
col program heading 'program' for a25 trunc
col username form a15
col osuser form a10
col idle form a30 heading "Idle"
col terminal form a12
col logon_time form a18
col machine for a15 trunc
col rn for 9999
col service_name for a30
set lines 150 pages 1000

break  on report
compute sum of cnt on report
select username,status,count(*) cnt from v$session group by username,status
/

select username,machine,count(*) cnt from v$session group by username,machine
/

select status,count(*) cnt from v$session group by status
/

select inst_id,service_name,count(*) cnt from gv$session group by  inst_id,service_name order by 1,2
/

ttitle - 
   center  'displays the top 50 longest idle times'  skip 2 

select  a.*
from (
  select sid,serial#,username,status, to_char(logon_time,'dd-mm-yy hh:mi:ss') logon_time
    , floor(last_call_et/3600)||' hours '
        || floor(mod(last_call_et,3600)/60)||' mins '
        || mod(mod(last_call_et,3600),60)||' secs' idle
    , machine ,row_number() over(order by last_call_et desc ) rn
  from v$session 
  where type='USER' ) a
where rn<= 50
/

ttitle off

column event heading 'wait event' for a30 trunc

ttitle -
   center  'displays active session'  skip 2

select sid,serial#,username,event,program,MACHINE,sql_id,BLOCKING_SESSION from v$session where status='ACTIVE' and username is not null;

ttitle off