SQLs to Monitor PeopleSoft Database

Important SQLs to monitor the Peoplesoft Database.

1) Monitor the database connections to track PeopleSoft User ID and its session information 

set linesize 200 

select p.spid, substr(s.osuser,1,10) osuser, substr(s.username,1,8) username, substr(s.program,1,24) program, substr(s.client_info,1,60) ClientInfo 

from v$session s, v$process p 

where s.paddr=p.addr and s.osuser is not null 

order by s.osuser

a.) To filter SQLPLUS sessions, append condition "upper(s.program) like '%SQLPLUS%'"

b.) To filter App server sessions,  append condition "upper(s.program) like '%PSAPPSRV%'"

c.) To filter Application designer sessions, append "upper(s.program) like '%PSIDE.EXE%'"

d.) To filter SQR programs condition "upper(s.program) like '%SQRW%'"

e.) To filter COBOL programs, monitor all sessions and identify programs with cobol report name like ptptedit report.

2) Identify the process ID including module and action processed by application server processes.

set linesize 200 

select p.spid, substr(s.osuser,1,10) osuser, substr(s.username,1,8) username, substr(s.program,1,24) program, substr(s.client_info,1,60) ClientInfo, substr(s.module,1,48) module, substr(s.action,1,32) action 

from v$session s, v$process p 

where s.paddr=p.addr and s.osuser is not null 

order by s.osuser; 

3) Monitor Locked objects in session

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

COLUMN owner FORMAT A20

COLUMN username FORMAT A20

COLUMN object_owner FORMAT A20

COLUMN object_name FORMAT A30

COLUMN locked_mode FORMAT A15

SELECT lo.session_id AS sid,

       s.serial#,

       NVL(lo.oracle_username, '(oracle)') AS username,

       o.owner AS object_owner,

       o.object_name,

       Decode(lo.locked_mode, 0, 'None',

                             1, 'Null (NULL)',

                             2, 'Row-S (SS)',

                             3, 'Row-X (SX)',

                             4, 'Share (S)',

                             5, 'S/Row-X (SSX)',

                             6, 'Exclusive (X)',

                             lo.locked_mode) locked_mode,

       lo.os_user_name

FROM   v$locked_object lo

       JOIN dba_objects o ON o.object_id = lo.object_id

       JOIN v$session s ON lo.session_id = s.sid

ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14

SET VERIFY ON

4) Monitor SQLs consuming most resources in oracle database.

SET LINESIZE 500

SET PAGESIZE 1000

SET VERIFY OFF

 col SQL_TEXT for a60

 col BUFFER_GETS for 99999999

 col DISK_READS for 99999999

 col EXECUTIONS for 99999999

 col sorts for 99999999

 col address for a30

SELECT *

FROM   (SELECT Substr(a.sql_text,1,50) sql_text,

               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, 

               a.buffer_gets, 

               a.disk_reads, 

               a.executions, 

               a.sorts,

               a.address,

a.sql_id

        FROM   v$sqlarea a

where a.sql_id in (select sql_id from v$session) 

        ORDER BY 2 DESC)

WHERE  rownum <= 10;

SET PAGESIZE 14

5) Generate grant script before you build any record in application designer, It is required since grant will be dropped after build / alter any record in Application designer.

select 'grant ' || privilege || ' on ' || table_name || ' to ' || grantee || ';' from user_tab_privs where table_name =  '<Table Name>'

6) Generate index script before you build record. It is required since existing indexes will be dropped after build / alter any record in Application designer.

select dbms_metadata.get_ddl('INDEX', 'index_name') from dual;