When a SQR is run, SESSIONIDNUM contains the ProcessID of the PSSQR wrapper program which calls the SQR program. For Application Engine programs, the SERVERID of PSAESRV is stored in PSPRCSQUE.SESSIONIDNUM and not the PID of the server process.


set pages 500
set line 180
col MACHINE for a10
col PROGRAM for a30
col OSUSER for a10
col USERNAME for a10
col sql_text for a65
col a.sid for a5
col a.serial# for a5
select c.sql_text,a.username,a.osuser,a.sid,a.serial#,a.program,a.machine,a.process "APPPROC" ,
b.spid "DB PROC",hash_value
from v$session a , v$process b , v$sqlarea c
where a.sql_address=c.address (+) and
a.paddr=b.addr and
b.spid='&1'


select s.sid, s.program, s.terminal, machine, osuser, type, module,
to_char(logon_time, 'dd/mm/yyyy hh24:mi') logon_time,
pga_used_mem/1024/1024 pga_used_mem_MB,
pga_alloc_mem/1024/1024 pga_alloc_mem_MB,
pga_max_mem/1024/1024 pga_max_mem_MB
from v$process p, v$session s
where  s.paddr = p.addr
and spid =&pid;

To find the SQL ID prcs instance
set long 900
set page 900
column prcsinstance format a20
column sql_id format a10
select b.sid,b.serial#,sql_id,a.prcsinstance,a.prcstype,a.prcsname,to_char(rqstdttm,'mm-dd-yy hh24:mi:ss') Start_Date,oprid,decode(runstatus,1,'Cancel',
           2, 'Delete',
           3, 'Error',
           4, 'Hold',
           5, 'Queued',
           6, 'Initiated',
           7, 'Processing',
           8, 'Cancelled',
           9, 'Success',
           10,'No Success',
           11,'Posted',
           12,'Not Posted',
           13,'Resend',
           14,'Posting',
           15,'Generated') "Status",
origprcsinstance
from SYSADM.PSPRCSQUE a,gv$session b
where runstatus='7'
and b.process=to_char(a.sessionidnum)
and b.client_info is not null
/

Current DDL Module statement on production:

People tools-> utilities-> 

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME], estimate_percent=>1, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',cascade=>TRUE);

Recommended without histrogram 

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME], estimate_percent=>30, method_opt=> 'FOR ALL COLUMNS SIZE 1',cascade=>TRUE, DEGREE=>6);

STAT Update

exec dbms_stats.gather_table_stats(ownname=>'SYSADM',tabname=>'PS_GP_GRP_LIST_RUN',estimate_percent=>10,degree=>8,cascade=>true,no_invalidate=>false);