Basic Troubleshooting steps for long running requests

Basic Troubleshooting steps for long running Concurrent requests:

1. Verification of DB & Application Node and log files:

·         top

·         free -g

·         Check alert log of both database instances

·         Check the mount point if any breaches the threshold value

·         Check the application services using adstrtal.sh status service name

2. If loads are normal check Standard manger and queue status

·         go to System Administrator->concurrent->manager->administer

3. Check the conflict resolution manager’s queue if any.

·         go to System Administrator->concurrent->manager->administer

·         select CRM then request

·         If any request you find in CRM queue , it will automatically make it compatible to run with other request. If not, then check with the user who has submitted the request and clear that.

 3.1. To find long running concurrent request:

SELECT a.request_id,a.oracle_process_id "SPID",frt.responsibility_name,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name,a.description

,a.ARGUMENT_TEXT,b.node_name,b.db_instance,a.logfile_name,a.logfile_node_name,a.outfile_name,q.concurrent_queue_name,a.phase_code,a.status_code, a.completion_text

,actual_start_date,actual_completion_date,fu.user_name,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time

FROM APPLSYS.fnd_Concurrent_requests a2,APPLSYS.fnd_concurrent_programs c2 WHERE c2.concurrent_program_id = c.concurrent_program_id AND a2.concurrent_program_id = c2.concurrent_program_id

AND a2.program_application_id = c2.application_id AND a2.phase_code || '' = 'C') avg_mins,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours

FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b,applsys.fnd_concurrent_queues q,APPLSYS.fnd_concurrent_programs c,APPLSYS.fnd_concurrent_programs_tl ctl

,apps.fnd_user fu,apps.FND_RESPONSIBILITY_TL frt WHERE a.controlling_manager = b.concurrent_process_id AND a.concurrent_program_id = c.concurrent_program_id

AND a.program_application_id = c.application_id AND a.phase_code = 'R' AND a.status_code = 'R' AND b.queue_application_id = q.application_id AND b.concurrent_queue_id = q.concurrent_queue_id

AND ctl.concurrent_program_id = c.concurrent_program_id AND a.requested_by = fu.user_id AND a.responsibility_id = frt.responsibility_id ORDER BY a.actual_start_date DESC;

 

 4. Check the blocking sessions in system:

select
   blocking_session,
   sid,
   serial#,
   wait_class,

   seconds_in_wait
from
   gv$session

where
   blocking_session is not NULL

order by blocking_session;

·         If you find blocking session check with user who has submitted the request and clear the request upon confirmation with requestor.

·         ALTER SYSTEM KILL SESSION 'sid,serial#';

5. Run below command to find SPID, provide concurrent request ID when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = ‘R’;

6.1 Check and confirm SPID on Database Node
ps-ef | grep (SPID) eg : 1523

6.2. Set OSPID (1523 in my case) for ORADEBUG

SQL> oradebug setospid 1523

Step 6.3 : Enable trace for 10050 event with level 12 ( We can give any event number)

SQL> oradebug event 10046 trace name context forever, level 12

6.3. Locate Trace file as
SQL>oradebug tracefile_name

/u01/app/oracle/OTST/12.1.0/admin/OTST_ebdb01/udump/OTST_trace.trc

Wait for 5 minutes

6.4. Disable trace
SQL> oradebug event 10046 trace name context off

6.5. Create tkprof file like
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10

Note: Above only running currently concurrent program

6.6. Check DB alert log file and see any error ( ORA, Tablespace, archive log ) which could be related to issue

adrci

ADRCI: Release 12.1.0.2.0 - Production on Mon Feb 17 22:12:08 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/data01/app/oraprd"

adrci> show log

ADR Home = /data01/app/oraprd/diag/rdbms/oprd/OPRD1:

 

7. Generate the AWR report and compare between the interval of time

Collect Multiple AWR Reports: It's always suggested to have two AWR Reports, one for good time when database was performing well, second when performance is poor).This way we can easily compare good and bad report to find out the culprit.

·         sql> @$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

8.  SQL tuning advisor can be applied to tune the sql statements

SQL profile is a collection of additional statistical information stored in the data dictionary that helps the optimizer to generate the best plan for the query. SQL profile is managed through SQL_TUNE package of SQL tuning advisor. i.e when we run SQL tuning advisor against a query, The tuning optimizer creates a profile for an SQL statement called SQL profile which consists additional statistical information about that statement, and it gives the recommendation to accept that profile.

SQL PROFILE can be applied to below statements.

SELECT statements
UPDATE statements
INSERT statements (only with a SELECT clause)
DELETE statements
CREATE TABLE statements (only with the AS SELECT clause)
MERGE statements (the update or insert operations)

8.1. Run sql tuning advisor for sql_id=5dkrnbx1z8gb

set long 1000000000

Col recommendations for a200

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '5dkrnbx1z8gcb',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 9000,

                          task_name   => '5dkrnbx1z8gb_tuning_task_1',

                          description => 'Tuning task for statement 5dkrnbx1z8gb');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5dkrnbx1z8gb_tuning_task_1');

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('5dkrnbx1z8gb_tuning_task_1') AS recommendations FROM dual;

SET PAGESIZE 24

        

8.2 Go through the profile recommendation part of the report:

 DBMS_SQLTUNE.report_tunning:  task will generate the completed output of the advisory. If you go through the profile recommendation part, it will be as below.

 Recommendation (estimated benefit: we can see the difference multiple of your task) we can check this also through OEM console could be easier to see the % of benefits with the sql profile with multiple of your actual cost.

From the performance tuning advisor tab.

Search for sql_id and see the sql profile with the percentage if above 90% of benefit.

  ------------------------------------------

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => '456rnbx1z8gb_tuning_task_1', task_owner => 'SYS', replace =>TRUE);

8.3. Accept profile and see the performance of the query.

    execute dbms_sqltune.accept_sql_profile(task_name => '456rnbx1z8gb_tuning_task_1', task_owner => 'SYS', replace =>TRUE);

9. Gather stats and check when was it last run:

exec fnd_stats.verify_stats('schema', 'object_name');