SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD1 READ WRITE
SQL> show parameter backg
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /us1101/app/oracle/product/11. 2.0/diag/rdbms/trace
SQL>
SQL> select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1); 2 3 4 5 6 7
COUNT(DISTINCTD.USER_NAME)
--------------------------
237
3. Run the following queries:
This will give the number of users on the system in the past 1 hour.
SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';
This will give the number of users on the system in the past 1 day.
SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';
This will show the activity in the last 15 minutes.
SQL> select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",
user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;
http://appsdbafix.blogspot.com/2013/02/number-of-users-connected-with-oracle.html
tail -10000 filename.log > filename_tail.log
head -10000 filename.log > filename_head.log
and then
we can give
> filename.log
Put jobs on hold
----------------
Put Jobs on Hold
<<< Check Putty Spool Log >>>
SQL> spool Spool_Jobs_on_hold_09JAN2021.txt;
SQL> SELECT SYSTIMESTAMP FROM DUAL;
1.Connect as apps user, Drop old table (jobs_already_on_hold)
SQL> DROP TABLE jobs_already_on_hold;
2. List out the Jobs which were already on HOLD
SQL> 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.
SQL>
CREATE TABLE jobs_already_on_hold
AS
(SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE hold_flag = 'Y');
4. Verify the count from backup table with the above query output
SQL> SELECT COUNT (*) FROM jobs_already_on_hold;
SQL> SELECT * FROM jobs_already_on_hold;
5.Now, Place the pending jobs on HOLD using the below update command.
SQL>
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P' AND hold_flag = 'N';
SQL> COMMIT;
6. After Putting the Jobs ON HOLD, now verify if any Running Requests with PAUSED Status.
If there are any Running requests with PAUSED status, please verify the child requests status of this Program.
If there were any put on HOLD as part of the putting Jobs on Hold process.
Release them from HOLD and process the requests.
7.Check again the pending scheduled program during the time frame 3 PM to 5 PM CST
8.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';
##############################################USE THIS FOR RUNNIG REQUESTS##############################
SQL> col USER_CONCURRENT_PROGRAM_NAME for a30
SQL> col REQUESTOR for a10
SELECT request_id, --parent_request_id,
user_concurrent_program_name,
--program,
--(select responsibility_name from apps.fnd_responsibility_vl fr where fr.responsibility_id = fcs.responsibility_id) responsibility_name,
-- program_short_name,
argument_text,
requestor,
phase_code,
status_code,
-- completion_text,
to_char (actual_start_date, 'DD-MON-YY HH24:MI:SS') actual_start_date,
to_char (actual_completion_date, 'DD-MON-YY HH24:MI:SS') actual_completion_date,
NUMTODSINTERVAL (
NVL (actual_completion_date, SYSDATE) - actual_start_date,
'day')
run_time,requested_start_date
FROM apps.fnd_conc_req_summary_v fcs
WHERE 1=1
--and program like 'Requisition%Import%'
--AND FCS.REQUEST_ID = 99819130
--AND REQUESTOR = 'RMUTTA'
and PHASE_CODE = 'R' -- Request Status
--AND STATUS_CODE <> 'Q'
order by run_time desc;
** once activity is completed and start the application services and perform the sanity checks and release the on hold jobs
#####################################################################CHECK BEFORE RELEASE################
- Release Jobs from HOLD
connect as apps user and perform the below:
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);
SQL> COMMIT;
=====Cancel requests manually============================
update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';
Release child requests
=====================
UPDATE fnd_concurrent_requests SET hold_flag = 'N' where REQUEST_ID='&REQUEST_ID';
SET LINES 200
SET PAGES 999
COLUMN INSTANCE_NAME FOR A20
SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"
FROM V$INSTANCE;
REM +======================================================================+
REM
REM File Name: reqhistory.sql
REM
REM Description:
REM Query To Check Concurrent Program Run History
REM
REM Notes:
REM Usage: sqlplus <apps_user/apps_passwd> @reqhistory.sql
REM
REM Input Required :
REM Number of days and User Concurrent Program Name
REM
REM +======================================================================+
ACCEPT NO_DAYS PROMPT "Enter Number of Days for History: ";
ACCEPT USER_CONC_PROG_NAME PROMPT "Enter User Concurrent Program Name: ";
clear columns
set lines 180
set pages 100
col Parameters for a20 WORD_WRAPPED
set pages 100
col "Conc Program Name" for a30 WORD_WRAPPED
col "Started at" for a20
col "Completed at" for a20
col "Username" for a10 WORD_WRAPPED
SELECT distinct t.user_concurrent_program_name "Conc Program Name",
r.REQUEST_ID "Request ID",
to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",
decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',
'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",
u.user_name "Username",
--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,
-- 2
-- ) "Runtime (in Minutes)"
round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate - &NO_DAYS)
--AND r.requested_by=22378
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name like '&USER_CONC_PROG_NAME'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;
undef NO_DAYS
undef USER_CONC_PROG_NAME
clear columns
set lines 180
set pages 50
col NODE_NAME for a15 WORD_WRAPPED
COL SUPPORT_DB for a12
COL SUPPORT_CP for a12
COL SUPPORT_ADMIN for a15
COL SUPPORT_FORMS for a15
COL SUPPORT_WEB for a12
COL HOST for a15 WORD_WRAPPED
Col STATUS for a10
col DOMAIN for a20 WORD_WRAPPED
select NODE_NAME,SUPPORT_DB,SUPPORT_CP,SUPPORT_ADMIN,SUPPORT_WEB,SUPPORT_FORMS,STATUS,HOST,DOMAIN
from apps.fnd_nodes;
Prepare Phase:
Why sometimes gather stats runs for longer time than normal.
There are many possible causes but the most common are:
a) Database/application process or processes are running which are updating the database. When GSS runs it will invalidate all of the stats on the object it is analyzing. If a process then trying to update or use that table it will start doing full table scan which will affect the overall database performance, which in turn impact GSS.
b) Large amount of data has been added to the database. The more records you have the longer the GSS will take to complete the process.
c) Recollecting CBO stats on tables that have not changed. If a table has 100 million rows then gathering stats on that table will take a long time, however if no or little changes are made then there is no need to delete all of the old stats and regather them (which is what you are doing)
d) Gathering statistics invalidates cursors which can hamper performance.( Unless you use the ‘No Invalidate’ option)
IMPORTANT TABLES/VIEWS RELATED TO GATHER SCHEMA STATISTICS
1) FND_STATS_HIST
To record the time taken for gathering the statistics for the different types of objects.
2) FND_HISTOGRAM_COLS
Gather Schema stats create the histogram for the specified columns in the tables.
IMPORTANT SCRIPTS RELATED TO GATHER SCHEMA STATISTICS
1) If the custom schema is not registered, it will not show up in the LOV for schema selection for the mentioned concurrent programs.
You can run the following statement to see which schemas are currently registered with the Ebusiness Suite:
select distinct(upper(oracle_username)) sname
from fnd_oracle_userid a, fnd_product_installations b
where a.oracle_id = b.oracle_id order by sname;
How to verify if the current gathered statistics are correct?
We use the Verify Stats report to determine whether the current statistics are accurate.
This report is a utility provided with FND_STATS, and can be run as follows:
SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats(‘schema’, ‘object_name’);
GATHER SCHEMA STATISTICS NOT RUNNING FOR CUSTOM MODULES. WHY?
When we submit Gather Schema Stats with Parameter ALL, concurrent request will complete successfully but custom schemas may not get analyzed.
Script to check if custom schema is analyzed :
Sql > select count(table_name) from dba_tables where last_analyzed is not null and owner= <custom_schema_name>;
Gather Schema Statistics program gathers statistics for all schemas , however it skips custom schemas registered in Oracle Applications.
Reason:
Whenever Custom schemas are registerd in Oracle Applications , the entries are done in 2 tables
ie FND_ORACLE_USERID and FND_APPLICATIONS_TL
However , when Gather schema statistics is submitted it uses the below query to get schema information
Sql > select distinct upper(oracle_username) sname
from fnd_oracle_userid a,
fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;
Note : When custom schemas are created the entry is not made in FND_PRODUCT_INSTALLATIONS and hence it is not picked up in the above query.
Solution :
Go to the Responsibility called Alert Manager and Navigate to the form -> Installations under Systems Menu.
Define custom application in this form. Go to the last record and make entry for custom applications. Once this is done , it will insert an entry in fnd_product_installations.
Submit Gather Schema stats and then query dba_tables and you will realize , stats are being gathered for custom schemas as well.
http://expertoracle.com/2013/02/01/gather-statistics-in-r12-and-11i/
