最近数据库碰到了连接紧张的问题,想通过一个脚本对数据库的session情况一目了然。
以下是自己写的脚本。实现的效果如下。
对session的总体情况,那些program主要在占用,每个用户所拥有的session(active,inactive,killed,sniped...)都能够一目了然。
STATUS CNT
-------- ----------
KILLED 553
SNIPED 2028
ACTIVE 44
INACTIVE 6097
----------
sum 8722
.
PROGRAM CNT STATUS
----------------------------------- ---------- --------
JDBC Thin Client 2316 INACTIVE
program1@machine01 (TNS V1-V3) 659 INACTIVE
program2@machine02 (TNS V1-V3) 572 INACTIVE
program4@machine01 (TNS V1-V3) 462 SNIPED
program3@machine01 (TNS V1-V3) 449 INACTIVE
program4@machine02 (TNS V1-V3) 391 INACTIVE
program1@machine01 (TNS V1-V3) 349 SNIPED
JDBC Thin Client 342 SNIPED
program2@machine01 (TNS V1-V3) 268 INACTIVE
----------
sum 5808
.
USERNAME TOTAL_CNT ACTIVE INACTIVE KILLED SNIPED program1 program2 program3
--------------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------- -----------
TESTUSER46 428 0 174 253 1 114 181 32
TESTUSER5 424 0 309 0 115 137 188 35
TESTUSER2 422 0 8 300 114 143 172 35
TESTUSER29 414 1 226 0 187 165 102 47
TESTUSER6 399 0 268 0 131 82 196 35
TESTUSER9 394 0 281 0 113 131 162 35
TESTUSER7 358 0 232 0 126 67 192 36
TESTUSER23 345 0 212 0 133 52 178 35
TESTUSER21 338 0 229 0 109 79 161 35
TESTUSER25 330 0 216 0 114 66 156 34
TSTAPP1 313 0 195 0 118 31 182 3
TESTUSER30 299 0 298 0 1 110 92 32
TESTUSER22 297 0 286 0 11 106 112 32
TSTAPP12 287 0 164 0 123 66 126 35
TESTUSER31 287 0 286 0 1 107 104 32
TESTUSER42 265 0 262 0 3 68 125 32
TESTUSER11 254 0 250 0 4 52 105 32
TESTUSER52 247 0 158 0 89 4 170 33
TSTAPP16 241 0 232 0 9 54 110 32
TSTAPP8 220 0 220 0 0 47 102 32
TSTAPP2 220 0 215 0 5 74 93 32
TESTUSER32 149 0 149 0 0 2 76 32
TESTUSER51 134 0 18 0 116 30 77 1
TESTUSER3 104 0 17 0 87 34 50 3
TESTUSER45 95 0 0 0 95 12 65 0
TESTUSER44 69 0 21 0 48 22 31 3
附上脚本内容:
sqlplus -s $DB_CONN_STR@$SH_DB_SID < set feed off
set verify off
set line 132
set pages 200
col username format a15
col sql_id format a20
col sql_address format a20
col machine format a30
col osuser format a15
col logon_time format a10
col program format a35
break on report
compute sum of cnt on report
select status,count(*) cnt from v\$session group by status;
prompt .
select program,cnt,status from (select program,count(*) cnt,status from v\$session group by program,status order by cnt desc) where rownum<10;
prompt .
select username,
sum(cnt) total_cnt,
sum(decode(status,'ACTIVE', cnt,0)) ACTIVE,
sum(decode(status,'INACTIVE', cnt,0)) INACTIVE,
sum(decode(status,'KILLED', cnt,0)) KILLED,
sum(decode(status,'SNIPED', cnt,0)) SNIPED,
$issue_program1 "JDBC Thin Client",
$issue_program2 "program1",
$issue_program3 "program2"
from (select program,username,status,count(*) cnt from V\$SESSION group by program,username,status )
group by username having sum(cnt)>50 order by total_cnt desc;
EOF
exit
以下是自己写的脚本。实现的效果如下。
对session的总体情况,那些program主要在占用,每个用户所拥有的session(active,inactive,killed,sniped...)都能够一目了然。
STATUS CNT
-------- ----------
KILLED 553
SNIPED 2028
ACTIVE 44
INACTIVE 6097
----------
sum 8722
.
PROGRAM CNT STATUS
----------------------------------- ---------- --------
JDBC Thin Client 2316 INACTIVE
program1@machine01 (TNS V1-V3) 659 INACTIVE
program2@machine02 (TNS V1-V3) 572 INACTIVE
program4@machine01 (TNS V1-V3) 462 SNIPED
program3@machine01 (TNS V1-V3) 449 INACTIVE
program4@machine02 (TNS V1-V3) 391 INACTIVE
program1@machine01 (TNS V1-V3) 349 SNIPED
JDBC Thin Client 342 SNIPED
program2@machine01 (TNS V1-V3) 268 INACTIVE
----------
sum 5808
.
USERNAME TOTAL_CNT ACTIVE INACTIVE KILLED SNIPED program1 program2 program3
--------------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------- -----------
TESTUSER46 428 0 174 253 1 114 181 32
TESTUSER5 424 0 309 0 115 137 188 35
TESTUSER2 422 0 8 300 114 143 172 35
TESTUSER29 414 1 226 0 187 165 102 47
TESTUSER6 399 0 268 0 131 82 196 35
TESTUSER9 394 0 281 0 113 131 162 35
TESTUSER7 358 0 232 0 126 67 192 36
TESTUSER23 345 0 212 0 133 52 178 35
TESTUSER21 338 0 229 0 109 79 161 35
TESTUSER25 330 0 216 0 114 66 156 34
TSTAPP1 313 0 195 0 118 31 182 3
TESTUSER30 299 0 298 0 1 110 92 32
TESTUSER22 297 0 286 0 11 106 112 32
TSTAPP12 287 0 164 0 123 66 126 35
TESTUSER31 287 0 286 0 1 107 104 32
TESTUSER42 265 0 262 0 3 68 125 32
TESTUSER11 254 0 250 0 4 52 105 32
TESTUSER52 247 0 158 0 89 4 170 33
TSTAPP16 241 0 232 0 9 54 110 32
TSTAPP8 220 0 220 0 0 47 102 32
TSTAPP2 220 0 215 0 5 74 93 32
TESTUSER32 149 0 149 0 0 2 76 32
TESTUSER51 134 0 18 0 116 30 77 1
TESTUSER3 104 0 17 0 87 34 50 3
TESTUSER45 95 0 0 0 95 12 65 0
TESTUSER44 69 0 21 0 48 22 31 3
附上脚本内容:
sqlplus -s $DB_CONN_STR@$SH_DB_SID < set feed off
set verify off
set line 132
set pages 200
col username format a15
col sql_id format a20
col sql_address format a20
col machine format a30
col osuser format a15
col logon_time format a10
col program format a35
break on report
compute sum of cnt on report
select status,count(*) cnt from v\$session group by status;
prompt .
select program,cnt,status from (select program,count(*) cnt,status from v\$session group by program,status order by cnt desc) where rownum<10;
prompt .
select username,
sum(cnt) total_cnt,
sum(decode(status,'ACTIVE', cnt,0)) ACTIVE,
sum(decode(status,'INACTIVE', cnt,0)) INACTIVE,
sum(decode(status,'KILLED', cnt,0)) KILLED,
sum(decode(status,'SNIPED', cnt,0)) SNIPED,
$issue_program1 "JDBC Thin Client",
$issue_program2 "program1",
$issue_program3 "program2"
from (select program,username,status,count(*) cnt from V\$SESSION group by program,username,status )
group by username having sum(cnt)>50 order by total_cnt desc;
EOF
exit
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-776143/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-776143/