监控当前并行查询运行状况脚本

简介：这个脚本创建一个简单的存储过程查看当前单实例的并行运行SQL状态，假如需要收集更多信息，可以修改此脚本。目前此脚本会统计每个会话的等待状态和物理I/O以及CPU信息。有助于在PX操作中找出繁忙和空闲的会话。适用范围：适用于任何平台上的7.3.X 到9.2.X的Oracle数据库。
脚本说明：必须用sys用户来执行这个脚本。使用下面命令创建存储过程： sqlplus sys/passWord @scriptfile 用sys用户给相应的用户执行此存储过程的权限： grant execute on pqstat to user 用户使用下面的命令执行脚本： set serveroutput on execute PQSTAT；给出所有PX会话的摘要信息 execute P QSTAT(1); 给出针对每个PX会话运行的当前SQL语句 execute PQSTAT(0,SID) 给出指定SID的PQ slaves的摘要信息 execute PQSTAT(1,SID) 给出上述所有信息并加上当前运行的SQL语句脚本内容： Create or Replace Procedure PqStat( detail number := 0, forsid number:=0 ) is
--
SepLine varchar2(80) := '-------------------------------';
nqueries number := 0;
doprint boolean;
--
-- Get Query Coordinators or a specific one (by SID)
--
Cursor QCcursor( insid number ) is
select distinct KXFPDPCPR , qc.SID
from X$KXFPDP qs, V$SESSION qc
where KXFPDPCPR!=hextoraw('0') and KXFPDPPRO!=hextoraw('0')
and bitand(KXFPDPFLG,16)=0 /* Slave not Idle */
and qc.paddr=qs.KXFPDPCPR
and (insid=0 or qc.sid=insid) ;
--
-- Get all local Query Slaves for a given QC
--
Cursor QScursor( creator raw ) is
select KXFPDPNAM, qs.KXFPDPPRO
from X$KXFPDP qs
where qs.KXFPDPCPR=creator
and KXFPDPPRO!=hextoraw('0') ;
--
-- Show Useful Stats for a session (CPU + Physical IO)
--
Procedure ShowPhys(prefix varchar2, insid number ) is
Cursor IOcursor is
select n.name, v.value
from v$sesstat v, v$statname n
where (n.name like 'physical%' or n.name like 'CPU used by this%')
and v.statistic#=n.statistic#
and v.sid=insid ;
i number:=0;
Begin
For IO in IOcursor
Loop
dbms_output.put_line(prefixIO.name'='IO.value);
i:=i+1;
End Loop;
dbms_output.put_line(' ');
End;
--
-- Show wait status of given session
--
Procedure ShowWait(prefix varchar2, insid number ) is
WaitInfo VarChar2(20);
Cursor SWcursor is
select *
from v$session_wait
where sid=insid ;
Cursor DQcursor(dqcode number) is
select indx'='reason
from X$KXFPSDS
where indx=dqcode ;
Begin
For SW in SWcursor
Loop
--
-- When we run this script on Versions later than 8.0.3
-- this IF clause never should be true.
-- But on the other this IF clause not hurt the script.
-- So I do not remove it.
--
if (SW.event='parallel query dequeue wait')
then
open DQcursor( SW.p1 );
fetch DQcursor into SW.event;
SW.p1text:=null;
SW.p1:=null;
close DQcursor;
end if;
if (SW.wait_time=0) then
dbms_output.put_line(prefix
'WAITING 'SW.seconds_in_wait's for "'
SW.event'" '
SW.p1text'='SW.p1' '
SW.p2text'='SW.p2' '
SW.p3text'='SW.p3);
else
dbms_output.put_line(prefix'RUNNING (wait seq#='SW.seq#')');
end if;
End Loop;
End;
--
-- Show current SQL statement for the given session
--
Procedure ShowSQL(prefix varchar2, addr raw, hash number ) is
Cursor SQLcursor is
select sql_text
from v$sqltext
where address=addr
and hash_value=hash
order by piece;
Begin
dbms_output.put_line(' ');
For SQ in SQLcursor
Loop
dbms_output.put_line(prefixSQ.sql_text);
End Loop;
dbms_output.put_line(' ');
End;
Procedure ShowSid(prefix varchar2, inpaddr raw ) is
Cursor SIDcursor is
select s.sid, spid, pid, c.terminal, s.process, osuser ,
s.username username, s.machine, s.sql_address,
s.sql_hash_value
from v$process c, v$session s
where c.addr=inpaddr
and c.addr=s.paddr ;
Begin
For SID in SIDcursor
Loop
dbms_output.put_line(prefix' Sid='SID.sid' ServerPid='SID.spid);
if (prefix='QC') then
dbms_output.put_line(' User='SID.username
' Client='SID.process' on 'SID.machine );
end if;
dbms_output.put_line(' ');
ShowPhys(' ',SID.sid);
ShowWait(' ',SID.sid);
if (detail>0) then
ShowSQL(' ', SID.sql_address, SID.sql_hash_value);
end if;
End Loop;
End;
Begin
dbms_output.enable(1000000);
dbms_output.put_line('Parallel Queries Running');
if (forsid!
=0) then
dbms_output.put_line(' for QC SID='forsid);
end if;
dbms_output.put_line(' ');
For QC in QCcursor( forsid )
Loop
doprint:=TRUE;
For QS in QScursor( QC.kxfpdpcpr )
Loop
If DoPrint Then
nqueries:=nqueries+1;
dbms_output.put_line(SepLine);
ShowSid('QC',QC.kxfpdpcpr );
DoPrint:=FALSE;
End If;
ShowSid(QS.kxfpdpnam,QS.kxfpdppro);
End Loop;
End Loop;
dbms_output.put_line(SepLine);
dbms_output.put_line(nqueries' Parallel Queries Found');
End;
/