Some importent querys from application monitoring

Finding time required for Gather Stats

If we run gather schema stats and want to the progression then we can check by the below query. 

SELECT *
FROM v$session_longops 
WHERE opname LIKE '%Gather Schema Statistics%';

For gather table stats the opname will be like Table Scan.
 
We can get the unique session id and then also we can check the time required.

SELECT
SID,SERIAL#,OPNAME,TOTALWORK,START_TIME,TIME_REMAINING,ELAPSED_SECONDS,message FROM  v$session_longops
WHERE   SID=6844;

Check the Concurrent requests which are running

Use the below query for finding all the concurrent requests which are running.

Select /*+ RULE */ substr(Concurrent_Queue_Name,1,12) Manager Name,
       Request_Id Request, User_Name,
       fpro.OS_PROCESS_ID OSprocess,
      fcr.oracle_process_id LocalProcess,
       substr(Concurrent_Program_Name,1,35) Program, Status_code,
       To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
       from apps.Fnd_Concurrent_Queues fcq, apps.Fnd_Concurrent_Requests fcr,
      apps.Fnd_Concurrent_Programs fcp, apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes fpro
       WHERE
       Phase_Code = 'R' And
       Status_Code <> 'W' And
       fcr.Controlling_Manager = Concurrent_Process_Id       And
      (fcq.Concurrent_Queue_Id = fpro.Concurrent_Queue_Id    And
       fcq.Application_Id      = fpro.Queue_Application_Id ) And
      (fcr.Concurrent_Program_Id = fcp.Concurrent_Program_Id And
       fcr.Program_Application_Id = fcp.Application_Id )     And
       fcr.Requested_By = User_Id
       order by Started

Compiling Objects in Database

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Terminating a Concurrent Requests

Concurrent Requests can be terminated from front end. But we can also terminate the Concurrent Request from the back end.

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id=&REQUEST_ID;

Note: Please remember to kill the process for that request first before terminating the concurrent request .

The related process Id for the Concurrent Requests can be determined by

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

 

Stopping Concurrent Manager

Concurrent manager can be stopped by running 

adcmctl.sh stop  <appsusername/password>

We can check if the Concurrent processes are still running by 

ps -ef |grep FNDLIBR 

If there are processes running the we can manually kill the processes.

kill -9 `ps -ef|grep FNDLIBR|awk '{print $2}'`

or

ps -ef|grep FNDLIBR|awk '{print $2}'|xargs kill -9

 

Getting current Session ID

• select sid from v$session where audsid = sys_context('userenv','sessionid');

• select distinct sid from v$mystat;

 

 

Thanks 

Srini