#!/bin/sh

# Copyright (c)  Corporation.  All rights reserved.
#
# DESCRIPTION
#
#    This script. requests the top cpu load operating system processes identifier(PID) ,
#    and then display the infomation about the sql statements on these pid.     
#
#   NOTES
#     Usually run as the oracle on AIX
#
#   MODIFIED   (MM/DD/YY)
#   xigua            03/18/08

#
# generate sql scripts
#
. $HOME/.profile

cd $HOME
cat > get.sql <-- 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 decode(sql_hash_value,0, prev_hash_value,sql_hash_value),
        decode(sql_address,0, prev_sql_addr, sql_address)
into :p_address,:p_hashvalue
from v\$session s
where s.paddr = (select addr
                 from v\$process p
                 where p.spid = :pid))
;
end;
/

--set sqlplus environment
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

#
# get top N cpu load pid,N default vaule is 5
#

N=5

if [ $# -ge 1 ]; then  

    if [ $# -eq 1 ]; then  

        N=$1

    else

        echo "Error, you had enter too may parameters. "

        echo "USAGE:sh  $(basename $0) N "

        exit 1

    fi

fi
ps aux |grep oracle${ORACLE_SID} | grep -v grep | sort -nr | head -${N} > 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

#
# remove the temp file
#
rm -f get.sql
rm -f pid.txt

---