#!/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