Saturday, November 17, 2007

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;

2 comments:

Unknown said...

Do we need any special permissions to use Kill Statement?

I liked the blog. Actually I had the similar question got asked in one of the interview, and there is one website www.itwisesolutions.com/PsftQuiz.html which is having tricky questions related to peoplesoft.

-Larry

Jeromy McMahon said...

You do need to have dba access in order to use the kill statement.

http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

http://www.idevelopment.info/data/Oracle/DBA_tips/Microsoft_Windows/WINDOWS_1.shtml

http://stackoverflow.com/questions/466963/is-it-possible-to-kill-a-single-query-in-oracle-without-killing-the-session