Peoplesoft perfomance

Monitoring Tools - Oracle Database


Written by David Vandiver
(Topic formerly known as "Active Sessions with SQL Statements")
This is a collection of SQL statements that you can run to see activity and performance on your PeopleSoft system.
You will need read permission to the following tables:
v$session
psoprdefn
v$sqlarea
v$sql_cursor
v$sql_bind_data
v%sql

The first SQL gives you active sessions, including the operator's name from the PSOPRDEFN table. There is also a "Kill Statement" built as one of the columns. This allows you to copy and paste the "Kill SQL" column and send it to the dba quickly.
-- Sessions and PSOPRDEFN

select nvl(b.oprdefndesc,'Blank'), a.CLIENT_INFO,a.OSUSER, a.machine, a.PROGRAM,a.LOCKWAIT, a.LOGON_TIME,'ALTER SYSTEM KILL SESSION ''' a.SID ',' a.SERIAL# ''';' "Kill SQL"from v$session a, psoprdefn bwhere status = 'ACTIVE'and a.USERNAME is not nulland substr(a.client_info, 1, 7) = b.oprid (+);


The last three are still being tweaked, but should work.

-- For statistics use:

SELECT * FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10;

--To get the SQL text for the above statistics use the following:

select t.SQL_TEXT from v$sqlarea t where t.HASH_VALUE in (SELECT hash_value FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10);

--To find the top ten run sql’s:

SELECT * FROM (SELECT hash_value,address,substr(sql_text,1,40) sql, buffer_gets, executions, buffer_gets/executions "Gets/Exec", sql_text "Full SQL Text" FROM V$SQLAREA WHERE buffer_gets > 100000 AND executions > 10 ORDER BY buffer_gets DESC)WHERE rownum <= 10;