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

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

/