#!/bin/sh
. $HOME/.profile
cd $HOME
cat > get.sql <Rem
Rem Copyright (c) xigua Corporation. All rights reserved.
Rem
Rem NAME
Rem get.sql
Rem
Rem DESCRIPTION
Rem
Rem This script. requests operating system process identifier which
Rem has higher cpu.
Rem
Rem NOTES
Rem Usually run as the sys
Rem
Rem MODIFIED (MM/DD/YY)
Rem niyl 03/06/08
-- Set up binds for pid,p_address and p_hashvalue
variable pid number;
variable p_address varchar2(30);
variable p_hashvalue varchar2(30);
-- Get the value of pid
begin
:pid := &pid;
end;
/
-- Get the value of p_address and p_hashvalue
begin
select /*+ ORDERED */ address,hash_value into :p_address,:p_hashvalue FROM v\$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value),
DECODE (sql_hash_value,0, prev_sql_addr, sql_address)
FROM v\$session b
WHERE b.paddr = (SELECT addr
FROM v\$process c
WHERE c.spid = :pid))
AND piece=0;
end;
/
clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 80 linesize 80 newpage 1 recsep off;
set trimspool on trimout on;
set heading on;
Prompt
Prompt The session information about the process
Prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col "LOGIN" for a10
col "CLIENT MACHINE" for a15
col program for a30
select s.sid,s.serial#,s.username "LOGIN",s.machine "CLIENT MACHINE",p.program
from v\$session s,v\$process p
where s.paddr = p.addr
and p.spid = :pid ;
Prompt
Prompt The SQL statement execute on the process
Prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select sql_text from v\$sqltext
where address = :p_address
and hash_value = :p_hashvalue
order by piece;
Prompt
Prompt The Execution plan for the SQL statement
Prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col id for 999
col parent_id for 999
col "Execution plan" for a40
SELECT id,parent_id,LPAD(' ',4*(LEVEL-1))||operation||' '||options||' '||object_name "Execution plan",
cardinality "rows produced"
FROM (
SELECT * FROM v\$sql_plan
WHERE address = :p_address
AND hash_value = :p_hashvalue
)
CONNECT BY PRIOR id = parent_id
START WITH id = 0;
!SC
ps aux |grep oracle${ORACLE_SID} | grep -v grep | sort -nr | head -5 > pid.txt
for pidnum in ` cat ./pid.txt |awk '{print $2}'`
do
sqlplus -s '/as sysdba' <clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 80 linesize 80 newpage 1 recsep off;
set trimspool on trimout on;
set heading on;
Prompt
Prompt The operation system process identifies : $pidnum
Prompt
@get.sql
$pidnum
!EOF
done
rm -f get.sql
rm -f pid.txt
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92530/viewspace-201612/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/92530/viewspace-201612/