Processes and Sessions

 select INST_ID,resource_name, current_utilization,initial_allocation, max_utilization, limit_value

from gv$resource_limit where resource_name in ( 'sessions', 'processes');

select * from DBA_HIST_RESOURCE_LIMIT where resource_name in ('sessions','processes') where INSTANCE_NUMBER=1;

select

  s.instance_number,

  s.end_interval_time,

  rn.current_utilization

from

  dba_hist_snapshot s

  join dba_hist_resource_limit rn on (s.snap_id = rn.snap_id and s.instance_number = rn.instance_number)

where

  rn.resource_name = 'processes'

  and s.end_interval_time > sysdate - 300 and s.INSTANCE_NUMBER=1

order by

  s.end_interval_time;

https://docs.oracle.com/cd/E57185_01/HFMAG/apbs09s05.html

How to hold the concurrent requests

List out the Jobs which were already on HOLD

select request_id, phase_code, status_code from fnd_concurrent_requests where hold_flag = 'Y';



3.Create backup of the table with the jobs which were already ON HOLD ##############



create table jobs_already_on_hold as (select request_id, phase_code, status_code from fnd_concurrent_requests where hold_flag = 'Y');



select count(*) from jobs_already_on_hold;

select * from jobs_already_on_hold;



4.Now, Place the pending jobs on HOLD using the below update command.

update fnd_concurrent_requests set hold_flag = 'Y' where phase_code = 'P'  and hold_flag = 'N';

commit;

5.Release Jobs from HOLD

SQL> UPDATE fnd_concurrent_requests

   SET hold_flag = 'N'

WHERE     hold_flag = 'Y'

       AND request_id NOT IN (SELECT request_id FROM jobs_already_on_hold)

Please ensure to check the below query for every 5 minutes until it returns 0 rows.

  List the pending ,running and passed  requests

  

SQL> 

SELECT REQUEST_ID,

       PHASE_CODE,

       STATUS_CODE,

       HAS_SUB_REQUEST,

       IS_SUB_REQUEST,

       hold_flag,

       REQ_INFORMATION

  FROM apps.fnd_concurrent_requests

WHERE    (phase_code = 'P' AND hold_flag = 'N')

       OR phase_code = 'R'

       OR status_code = 'W';

sql pofile

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)

In this example, first we will run SQL tuning advisor against an sql_id , and then will do different operations on sql profile.

1. Run sql tuning advisor for sql_id=5dkrnbx1z8gcb

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  => 500,

                          task_name   => '5dkrnbx1z8gcb_tuning_task_1',

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

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

END;

/

 

 

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

 

 

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

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

SET PAGESIZE 24

Find the name of the sql_profile:

select name,status,force_matching from dba_sql_profiles  where task_id in ( select task_id from DBA_ADVISOR_TASKS where task_name ='5dkrnbx1z8gcb_tuning_task_1')

 

set pagesize 299

set lines 299

col name for a45

NAME                               STATUS   FOR

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

SYS_SQLPROF_01601b64332e0000       ENABLED  NO

Alter a profile,

disable a sql_profile using steps

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'SYS_SQLPROF_01601b64332e0000',

attribute_name => 'STATUS',

value => 'DISABLED');

END;

/

 

To enable to changed the value from DISABLED to ENABLED.

__________________

Dropping an SQL profile:

begin

DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_01601b64332e0000');

end;

/

source : https://dbaclass.com/article/sql-profile-oracle/