1、进程
1.1、查看当前进程数
select count(*) from v$process; |
1.2、查看进程最大值
select value from v$parameter where name = 'processes'; show parameter processes; |
1.3、分别查看进程，会话的历史最大数和最大数
select resource_name,max_utilization,limit_value from v$resource_limit where resource_name in('processes','sessions'); |
1.4、查看连接oracle每台机器的连接数和状态
select machine,status,count(*) from gv$session group by machine,status order by status; |
1.5、查看oracle总的连接数以及活跃连接数
select t.INST_ID,count(*) count_all, sum(case when t.STATUS = 'ACTIVE' then 1 else 0 end) count_active from gv$session t group by t.INST_ID order by t.inst_id; |
2、会话
2.1、查看当前会话数
select count(*) from v$session; |
2.2、查看会话最大值
select value from v$parameter where name = 'sessions'; |
2.3、活动会话
--活动会话的sql语句 prompt Active session with sql text column USERNAME format a14 set linesize 200 column EVENT format a30 select /*+rule */ distinct ses.SID, ses.sql_hash_value, ses.USERNAME, pro.SPID "OS PID", substr(stx.sql_text,1,200) from V$SESSION ses ,V$SQL stx ,V$PROCESS pro where ses.paddr = pro.addr and ses.status = 'ACTIVE' and stx.hash_value = ses.sql_hash_value ; --活动会话的等待事件 select /*+rule */ sw.event,sw.wait_time,s.username,s.sid,s.serial#,s.SQL_HASH_VALUE from v$session s, v$session_wait sw where s.sid=sw.sid and s.USERNAME is not null and s.status = 'ACTIVE'; |
2.4、杀会话
set line 199 col event format a35 --杀某个SID会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1; --根据SQL_ID杀会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1; --根据等待事件杀会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1; --根据用户杀会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1; --kill所有LOCAL=NO进程 ps -ef|grep LOCAL=NO|grep $ORACLE_SID|grep -v grep|awk '{print $2}' |xargs kill -9 |
2.5、批量删除会话
实际生产中推荐两种方法批量删除会话：
1)ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
2)ps aux |grep "LOCAL=NO" |awk '{printf "%s\n", $2}' |xargs kill -9
--1、oracle中批处理删除外部连接session set linesize 999 col spid format A10 col username format A10 col program format A50 declare cursor del_cur is SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND' and s.username not like '%SYS%'; begin for cur in del_cur loop execute immediate ( 'alter system kill/DISCONNECT session '''||cur.sid || ','|| cur.SERIAL# ||''' '); end loop; end; / ---2、根据计算机名批量删除会话，具体删除条件可以自行调整上面的查询语句 declare cursor del_cur is select b.sid,b.serial# from v$session b where b.MACHINE = 'MACHINE_NAME' and b.STATUS = 'INACTIVE'; begin for cur in del_cur loop execute immediate ( 'alter system kill session/DISCONNECT '''||cur.sid || ','|| cur.SERIAL# ||''' '); end loop; end; / |
2.6、session_by_XX
set line 199 col username format a14 col event format a35 col module format a20 col spid format a8 col machine format a15 col B_SESS for a10 --根据等待事件查会话 SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6; --根据用户查会话 SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.username='&user_name' AND s.paddr = p.addr order by 6 --根据SQL_ID查会话 SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.sql_id='&sql_id' AND s.paddr = p.addr order by 6 --根据会话ID查会话详情 SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session b_sess,logon_time FROM v$session s, v$process p WHERE sid = '&sid' AND s.paddr = p.addr; --查询阻塞会话 select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session; --查询会话的对象信息 col OBJECT_NAME for a30 select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid; |
2.7、一键获得当前会话情况
可以获得会话基本信息、执行时间、执行sql、使用的临时表空间大小、undo大小和表空间等。
SELECT /* use_hash(sess,proc,undo,tmp) use_nl(s)*/ distinct sess.inst_id, sess.sid, sess.serial#, sess.username, substr(osuser, 1, 10) osuser, status, sess.process, proc.spid, sess.machine, sess.program, regexp_substr(NUMTODSINTERVAL(nvl((SYSDATE - SQL_EXEC_START) * 24 * 60 * 60, last_call_et), 'SECOND'), '+\d{2} \d{2}:\d{2}:\d{2}') running_sec, TEMP_MB, UNDO_MB, s.sql_id, TSPS.NAME TSPS, decode(sess.action, null, '', sess.action || ', ') || replace(s.sql_text, chr(13), ' ') sql FROM gv$session sess, gv$process proc, gv$sql s, (select ses_addr as saddr, sum(used_ublk / 128) UNDO_MB from v$transaction group by ses_addr) undo, (select session_addr as saddr, SESSION_NUM serial#, sum((blocks / 128)) TEMP_MB from gv$sort_usage group by session_addr, SESSION_NUM) tmp, (select inst_id, sid, serial#, event, t.name from gv$session ls, sys.file$ f, sys.ts$ t where status = 'ACTIVE' and ls.p1text in ('file number', 'file#') and ls.p1 = f.file# and f.ts# = t.ts#) tsps WHERE sess.inst_id = proc.inst_id(+) and sess.saddr = tmp.saddr(+) and sess.serial# = tmp.serial#(+) AND sess.status = 'ACTIVE' and sess.username is not null and sess.sid = tsps.sid(+) and sess.inst_id = tsps.inst_id(+) and sess.serial# = tsps.serial#(+) AND sess.paddr = proc.addr(+) and sess.sql_id = s.sql_id(+) and sess.saddr = undo.saddr(+) ORDER BY running_sec desc, 4, 1, 2, 3; |
2.8、查看客户端连接的IP信息
--1、利用 DBMS_SESSION 过程包 BEGIN DBMS_SESSION.set_identifier(SYS_CONTEXT('USERENV', 'IP_ADDRESS')); END; --2、创建触发器 create or replace trigger on_logon_trigger after logon on database begin dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) ); end; --3、在v$session的client_info列会记录其相应的IP信息 select username,machine,terminal,program,client_info,logon_time from v$session order by logon_time desc; |
2.9、监控并发查询
--gives an overview of all running parallel queries with all slaves.It shows the if a slave is waiting and for what event it waits. col username for a12 col "QC SID" for A6 col "SID" for A6 col "QC/Slave" for A8 col "Req. DOP" for 9999 col "Actual DOP" for 9999 col "Slaveset" for A8 col "Slave INST" for A9 col "QC INST" for A6 set pages 300 lines 300 col wait_event format a30 select decode(px.qcinst_id,NULL,username, ' - '||lower(substr(pp.SERVER_NAME, length(pp.SERVER_NAME)-4,4) ) )"Username", decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , to_char( px.server_set) "SlaveSet", to_char(s.sid) "SID", to_char(px.inst_id) "Slave INST", decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE, case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event , decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", to_char(px.qcinst_id) "QC INST", px.req_degree "Req. DOP", px.degree "Actual DOP" from gv$px_session px, gv$session s , gv$px_process pp, gv$session_wait sw where px.sid=s.sid (+) and px.serial#=s.serial#(+) and px.inst_id = s.inst_id(+) and px.sid = pp.sid (+) and px.serial#=pp.serial#(+) and sw.sid = s.sid and sw.inst_id = s.inst_id order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID, decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID / --sample output Username QC/Slave SlaveSet SID Slave INS STATE WAIT_EVENT QC SID QC INS Req. DOP Actual DOP ------------ -------- -------- ------ --------- -------- ------------------------------ ------ ------ -------- ---------- SCOTT QC 923 1 WAIT db file sequential read 923 - p003 (Slave) 1 935 1 WAIT PX Deq Credit: send blkd 923 1 4 4 - p001 (Slave) 1 961 1 WAIT PX Deq: Execution Msg 923 1 4 4 - p002 (Slave) 1 1035 1 WAIT PX Deq: Execution Msg 923 1 4 4 - p004 (Slave) 1 977 1 WAIT PX Deq Credit: send blkd 923 1 4 4 - p006 (Slave) 2 609 1 WAIT PX Deq: Execution Msg 923 1 4 4 - p007 (Slave) 2 642 1 WAIT PX Deq: Execution Msg 923 1 4 4 - p008 (Slave) 2 970 1 WAIT PX Deq: Execution Msg 923 1 4 4 - p005 (Slave) 2 953 1 WAIT PX Deq: Execution Msg 923 1 4 4 SCOTT QC 1003 1 WAIT SQL*Net message from client 1003 - p015 (Slave) 1 608 1 WAIT PX Deq Credit: send blkd 1003 1 8 8 - p011 (Slave) 1 639 1 WAIT PX Deq Credit: send blkd 1003 1 8 8 - p012 (Slave) 1 1115 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p000 (Slave) 1 1253 1 WAIT PX Deq Credit: send blkd 1003 1 8 8 - p010 (Slave) 1 1420 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p009 (Slave) 1 1421 1 WAIT PX Deq Credit: send blkd 1003 1 8 8 - p014 (Slave) 1 1417 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p013 (Slave) 1 1180 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p020 (Slave) 2 1422 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p023 (Slave) 2 1423 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p018 (Slave) 2 1424 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p021 (Slave) 2 1426 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p019 (Slave) 2 1428 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p016 (Slave) 2 1429 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p022 (Slave) 2 1427 1 WAIT PX Deq: Execution Msg 1003 1 8 8 - p017 (Slave) 2 1425 1 WAIT PX Deq: Execution Msg 1003 1 8 8 --shows for the PX Deq events the processes that are exchange data. set pages 300 lines 300 col wait_event format a30 select sw.SID as RCVSID, decode(pp.server_name, NULL, 'A QC', pp.server_name) as RCVR, sw.inst_id as RCVRINST, case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event , decode(bitand(p1, 65535), 65535, 'QC', 'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR, bitand(p1, 16711680) - 65535 as SNDRINST, decode(bitand(p1, 65535), 65535, ps.qcsid, (select sid from gv$px_process where server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and inst_id = bitand(sw.p1, 16711680) - 65535) ) as SNDRSID, decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE from gv$session_wait sw, gv$px_process pp, gv$px_session ps where sw.sid = pp.sid (+) and sw.inst_id = pp.inst_id (+) and sw.sid = ps.sid (+) and sw.inst_id = ps.inst_id (+) and p1text = 'sleeptime/senderid' and bitand(p1, 268435456) = 268435456 order by decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID), ps.QCSID, decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP), ps.SERVER_SET, ps.INST_ID / --sample output RCVSID RCVR RCVRINST WAIT_EVENT SNDR SNDRINST SNDRSID STATE ---------- ---- ---------- ------------------------------ ----- ---------- ---------- -------- 935 P003 1 PX Deq Credit: send blkd QC 1 923 WAIT 961 P001 1 PX Deq: Execution Msg QC 1 923 WAIT 977 P004 1 PX Deq Credit: send blkd QC 1 923 WAIT 1035 P002 1 PX Deq: Execution Msg QC 1 923 WAIT 609 P006 1 PX Deq: Execution Msg QC 1 923 WAIT 642 P007 1 PX Deq: Execution Msg QC 1 923 WAIT 970 P008 1 PX Deq: Execution Msg QC 1 923 WAIT 953 P005 1 PX Deq: Execution Msg QC 1 923 WAIT 608 P015 1 PX Deq Credit: send blkd QC 1 1003 WAIT 1180 P013 1 PX Deq: Execution Msg QC 1 1003 WAIT 1253 P000 1 PX Deq Credit: send blkd QC 1 1003 WAIT 1417 P014 1 PX Deq: Execution Msg QC 1 1003 WAIT 1421 P009 1 PX Deq Credit: send blkd QC 1 1003 WAIT 1420 P010 1 PX Deq: Execution Msg QC 1 1003 WAIT 1115 P012 1 PX Deq: Execution Msg QC 1 1003 WAIT 639 P011 1 PX Deq Credit: send blkd QC 1 1003 WAIT 1422 P020 1 PX Deq: Execution Msg QC 1 1003 WAIT 1423 P023 1 PX Deq: Execution Msg QC 1 1003 WAIT 1424 P018 1 PX Deq: Execution Msg QC 1 1003 WAIT 1425 P017 1 PX Deq: Execution Msg QC 1 1003 WAIT 1426 P021 1 PX Deq: Execution Msg QC 1 1003 WAIT 1427 P022 1 PX Deq: Execution Msg QC 1 1003 WAIT 1428 P019 1 PX Deq: Execution Msg QC 1 1003 WAIT 1429 P016 1 PX Deq: Execution Msg QC 1 1003 WAIT --shows for long running processes what are the slaves do. set pages 300 lines 300 col "Username" for a12 col "QC/Slave" for A8 col "Slaveset" for A8 col "Slave INST" for A9 col "QC SID" for A6 col "QC INST" for A6 col "operation_name" for A30 col "target" for A30 select decode(px.qcinst_id,NULL,username, ' - '||lower(substr(pp.SERVER_NAME, length(pp.SERVER_NAME)-4,4) ) )"Username", decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , to_char( px.server_set) "SlaveSet", to_char(px.inst_id) "Slave INST", substr(opname,1,30) operation_name, substr(target,1,30) target, sofar, totalwork, units, start_time, timestamp, decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", to_char(px.qcinst_id) "QC INST" from gv$px_session px, gv$px_process pp, gv$session_longops s where px.sid=s.sid and px.serial#=s.serial# and px.inst_id = s.inst_id and px.sid = pp.sid (+) and px.serial#=pp.serial#(+) order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID, decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID / --sample output Username QC/Slave SlaveSet Slave INS OPERATION_NAME TARGET SOFAR TOTALWORK UNITS START_TIM QC SID QC INS ------------ -------- -------- --------- ------------------------------ ------------------------------ ---------- ---------- -------------------------------- --------- ------ ------ SCOTT QC 1 Index Fast Full Scan EMP 680893 680893 Blocks 10-SEP-07 923 SCOTT QC 1 Index Fast Full Scan EMP 680893 680893 Blocks 10-SEP-07 923 - p003 (Slave) 1 1 Sort Output 21997 33383 Blocks 10-SEP-07 923 1 - p001 (Slave) 1 1 Sort Output 94196 94196 Blocks 10-SEP-07 923 1 - p011 (Slave) 1 1 Hash Join 589 589 Blocks 11 |
3、事务
在数据库中事务是工作的逻辑单元，一个事务是由一个或多个完成一组的相关行为的SQL语句组成，通过事务机制确保这一组SQL语句所作的操作要么都成功执行，完成整个工作单元操作，要么一个也不执行。如果要查询当前事务我们可以查询v$transaction表获得相关信息，查一段时间的事务数可以通过awr报告中的Transactions和user commits来得知
3.1、查询XX时间段每秒/每天事务数
select instance_number, metric_unit, trunc(begin_time) time, round(avg(average), 2) "Transactions Per Second", avg(average) * 60 * 60 * 24 "Transactions Per Day" from DBA_HIST_SYSMETRIC_SUMMARY where metric_unit = 'Transactions Per Second' and begin_time >= to_date('2020-03-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and begin_time < to_date('2020-03-28 14:00:00', 'yyyy-mm-dd hh24:mi:ss') group by instance_number, metric_unit, trunc(begin_time) order by instance_number; |
3.2、查询XX时间段某个用户的事务数
需要在2个时间段分别运行脚本，把执行结果相减，即可得出该时间段内的用户事务数
select s.USERNAME, sum(se.VALUE) "session transaction number", sum(sy.VALUE) " database transaction number" from v$session s, v$sesstat se, v$sysstat sy where s.sid = se.SID and se.STATISTIC# = sy.STATISTIC# and sy.NAME = 'user commits' and s.USERNAME = upper('&username') group by s.USERNAME; |
3.4、查询当前正在执行的事务
SELECT s.sid, s.serial#, s.event, a.sql_text, a.sql_fulltext, s.username, s.status, s.machine, s.terminal, s.program, a.executions, s.sql_id, p.spid, a.direct_writes FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s LEFT JOIN v$sqlarea a ON s.sql_id = a.sql_id INNER JOIN v$process p ON s.paddr = p.addr |
3.5、查询长事务
with transaction_details as ( select inst_id , ses_addr , sysdate - start_date as diff from gv$transaction) select s.username, to_char(trunc(t.diff)) || ' days, ' || to_char(trunc(mod(t.diff * 24,24))) || ' hours, ' || to_char(trunc(mod(t.diff * 24 * 60,24))) || ' minutes, ' || to_char(trunc(mod(t.diff * 24 * 60 * 60,60))) || ' seconds' as transaction_duration , s.program , s.terminal , s.status , s.sid , s.serial# from gv$session s, transaction_details t where s.inst_id = t.inst_id and s.saddr = t.ses_addr order by t.diff desc |
