監(jiān)控當前并行查詢運行狀況腳本
2024-07-21 02:32:39
供稿：網友
簡介：這個腳本創(chuàng)建一個簡單的存儲過程查看當前單實例的并行運行SQL狀態(tài)，假如需要收集更多信息，可以修改此腳本。目前此腳本會統(tǒng)計每個會話的等待狀態(tài)和物理I/O以及CPU信息。有助于在PX操作中找出繁忙和空閑的會話。 適用范圍：適用于任何平臺上的7.3.X 到9.2.X的Oracle數(shù)據(jù)庫。
腳本說明：必須用sys用戶來執(zhí)行這個腳本。使用下面命令創(chuàng)建存儲過程：sqlplus sys/passWord @scriptfile用sys用戶給相應的用戶執(zhí)行此存儲過程的權限：grant execute on pqstat to user用戶使用下面的命令執(zhí)行腳本：set serveroutput onexecute 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;
/
