Tuning Advisor on sql from backend

below are the steps to run Tuning Advisor on sql_id

DECLARE
  my_task_name VARCHAR2(30);
begin
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id',scope => 'COMPREHENSIVE',time_limit =>7200,task_name => 'STA:1t05z5qh0u707',description => 'description_ax');
end;

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'STA:1t05z5qh0u707');

SET LONG 10000
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('STA:1t05z5qh0u707') from dual;

Index on Table

Index on Table

select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.table_name = upper('&table_name')
order by a.table_name, a.index_name, a.column_position;

Close WF_notifications as part of clone process

Close WF_notifications as part of clone process

use the below commands to close open notification so that emails are not sent out of the non-prod environments

create table wf_notifications_bkp as select * from wf_notifications;
update applsys.wf_notifications set status ='CLOSED';
update applsys.wf_notifications set mail_status ='SENT' where mail_status ='MAIL';
commit;

determine which concurrent manager ran a specific concurrent request?

Determine which concurrent manager ran a specific concurrent request?

login as apps/****

select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&Request_id';



References:
Concurrent Processing – ANALYZEREQ.SQL Detailed Analysis of One Concurrent Request (Release 11 and up) [ID 134035.1]
How to Determine Which Manager Ran a Specific Concurrent Request? [ID 343575.1]
How Do I Find Out Which Concurrent Manager Processed A Concurrent Request/Program [ID 344011.1]

sga/pga memory usages of an Oracle instance

Query to find the the hourly  sga/pga memory usages of an Oracle instance..

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
   from DBA_HIST_SGASTAT
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
    from DBA_HIST_PGASTAT where name = 'total PGA allocated'
   group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
  and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
  and sn.snap_id=pga.snap_id
  and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.snap_id desc, sn.INSTANCE_NUMBER;

Analyze Indexes

ANALYZE INDEX PA.PA_TASKS_N1 COMPUTE STATISTICS;
ANALYZE INDEX PA.PA_TASKS_N1 VALIDATE STRUCTURE;
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

Knowing when to Rebuild Indexes

We must first get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command.

The VALIDATE STRUCTURE command can be safely executed without affecting the optimizer. The VALIDATE STRUCTURE command populates the SYS.INDEX_STATS table only. The SYS.INDEX_STATS table can be accessed with the public synonym INDEX_STATS. The INDEX_STATS table will only hold validation information for one index at a time. You will need to query this table before validating the structure of the next index.

There are two rules of thumb to help determine if the index needs to be rebuilt.

1)     If the index has height greater than four, rebuild the index.

2)     if the ratio of del_lf_row/lf_row*100 is greater than 20% then rebuild the index

Indexes on Tables

Find all Indexes on a Table

SELECT index_name, column_name, column_position
FROM dba_ind_columns
WHERE table_name like upper('&table_name')
ORDER BY index_name, column_position;

Find Active Users with responsibilities assigned in R122

Below Query can be used to find all active users and all the responsibilities assigned to them in Oracle EBusiness Suite R122


SELECT   fu.user_name, fu.start_date user_start_date,fu.description,
         fu.end_date user_end_date, per.GLOBAL_NAME employee_name,
         per.effective_start_date employee_start_date,
         per.effective_end_date employee_end_date, resp.responsibility_name,
         resp.start_date responsibility_start_date,
         resp.end_date responsibility_end_date
    FROM apps.fnd_user fu,
         (SELECT *
            FROM (SELECT x.*,
                         ROW_NUMBER () OVER (PARTITION BY person_id ORDER BY effective_end_date DESC)
                                                                            r
                    FROM apps.per_all_people_f x)
           WHERE r = 1) per,
         (SELECT fr.responsibility_name, fur.user_id, fur.start_date,
                 fur.end_date
            FROM apps.fnd_user_resp_groups_direct fur,
                 apps.fnd_responsibility_tl fr
           WHERE fur.responsibility_id = fr.responsibility_id
          UNION
          SELECT fr.responsibility_name, fur.user_id, fur.start_date,
                 fur.end_date
            FROM apps.fnd_user_resp_groups_indirect fur,
                 apps.fnd_responsibility_tl fr
           WHERE fur.responsibility_id = fr.responsibility_id) resp
   WHERE fu.employee_id = per.person_id(+)
     AND resp.user_id = fu.user_id
  --   and fu.user_name in ('XYZ','PQR')
 --    and fu.description like '%XYZ%'
     and  nvl(fu.end_date, sysdate+1) > Sysdate order by fu.user_name