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

简介：这个脚本创建一个简单的存储过程查看当前单实例的并行运行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(prefix||IO.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(prefix||SQ.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;
/