Oracle E-Business Suite System Survey

You can use $FND_TOP/patch/115/bin/txkInventory.pl perl script to generate a report of techstack components and their versions. This is described in metalink note 601736.1

Metalink Note 392782.1 contains a list of system survey questionnaires that Oracle Application development should know from the viewpoint of performance. This output will help in understanding h/w configuration and its related environment for Oracle E-Business Suite:

Oracle E-Business Suite System Survey

The first 6 questions should be provided manually and rest of them could be gathered by script. 

1. Hardware Information 
* DB sever 
- System Model : 
- OS version : 
- # CPUs : 
- CPU speed : 
- MEMORY : 
- Disk I/O : 
- Etc : 

* CM (Concurrent Manager) sever 
- System Model : 
- OS version : 
- # CPUs : 
- CPU speed : 
- MEMORY : 
- Disk I/O : 
- Etc : 

* AP (Application) sever 
- System Model : 
- OS version : 
- # CPUs : 
- CPU speed : 
- MEMORY : 
- Disk I/O : 
- Etc : 

2. environment information (# tiers, network setup, RAC etc..) 
3. # of total and avg. concurrent users during peak times. 
4. DB size (including version) 
5. modules being used. 
6. # of employees 

==== 

The following sql script will gather additional information 
from the production database. After sql*plus login with apps user, 
pl run system.sql and get the spool output file, system.txt 
under the current directory. 

--File name : system.sql 
spool system.txt 
set linesize 180 
set pagesize 600 

prompt Apps Version

SELECT release_name from fnd_product_groups;

prompt DB SERVER INFORMATION 
col host_name format a20 
COL VERSION FORMAT A15 
COL STATUS FORMAT A10 
COL THREAD# FORMAT 99 
select 
INST_ID, 
INSTANCE_NUMBER , 
INSTANCE_NAME , 
HOST_NAME , 
VERSION , 
STATUS , 
PARALLEL , 
THREAD# 
from gv$instance ; 

PROMPT MAJOR TABLE LIST 

select owner, table_name, num_rows, LAST_ANALYZED 
From dba_tables 
where table_name in ( 
'AP_INVOICES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL', 'AR_PAYMENT_SCHEDULES_ALL', 
'RA_CUSTOMER_TRX_ALL', 'RA_CUSTOMER_TRX_LINES_ALL' , 
'HZ_PARTIES', 'HZ_CUST_ACCOUNTS', 
'AS_SALES_LEADS', 'AS_ACCESSES_ALL_ALL', 
'BOM_STRUCTURES_B', 'BOM_COMPONENTS_B', 
'CS_INCIDENTS_ALL_B', 
'FA_ADJUSTMENTS', 'FA_DEPRN_DETAIL', 'FA_DEPRN_SUMMARY', 
'FND_USERS', 
'GL_JE_HEADERS', 'GL_JE_LINES', 
'MRP_GROSS_REQUIREMENTS', 'MRP_RECOMMENDATIONS', 'MRP_FULL_PEGGING', 
'MRP_BOM_COMPONENTS', 'MTL_MATERIAL_TRANSACTIONS', 
'MTL_TRANSACTION_ACCOUNTS', 'MTL_SYSTEM_ITEMS_B', 
'HR_ORGANIZATION_INFORMATION', 'HR_OPERATING_UNITS', 
'MTL_PARAMETERS', 
'OE_ORDER_HEADERS_ALL', 'OE_ORDER_LINES_ALL', 
'PO_HEADERS_ALL', 'PO_LINES_ALL', 'PO_VENDORS', 
'WF_ITEM_ACTIVITY_STATUSES', 'WF_ITEM_ATRIBUTE_VALUES', 
'WF_NOTIFICATIONS', 'WF_NOTIFICATION_ATTRIBUTES' , 
'WSH_DELIVERY_DETAILS' , 'WSH_DELIVERY_ASSIGNMENTS', 
'WSH_NEW_DELIVERIES', 'WSH_DELIVERY_LEGS', 
'WSH_TRIP_STOPS', 'WSH_TRIPS' ) 
order by table_name ; 


PROMPT number of daily concurrent requests. 

SELECT trunc(REQUESTED_START_DATE), count(*) 
FROM FND_CONCURRENT_REQUESTS 
WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate 
group by rollup(trunc(REQUESTED_START_DATE)) ; 


PROMPT Applications versions and family pack versions. 

COL APPLICATION_NAME FORMAT A60 
COL SHT_NAME FORMAT A10 
col PATCH_LEVEL FORMAT A20 
SELECT A.APPLICATION_SHORT_NAME SHT_NAME,T.APPLICATION_NAME, I.STATUS, 
NVL(I.PATCH_LEVEL, 'n/a') PATCH_LEVEL, I.DB_STATUS 
FROM FND_PRODUCT_INSTALLATIONS I, 
FND_APPLICATION A, 
FND_APPLICATION_TL T 
WHERE A.APPLICATION_ID = I.APPLICATION_ID 
AND A.APPLICATION_ID = T.APPLICATION_ID 
AND T.LANGUAGE = USERENV('LANG') 
ORDER BY 1 ; 


PROMPT Multi-org being used. 

select MULTI_ORG_FLAG org, MULTI_LINGUAL_FLAG lingual, MULTI_CURRENCY_FLAG currency 
from FND_PRODUCT_GROUPS ; 

PROMPT DB size with Tablespace 

set head on 
set pagesize 30 
select NVL(tablespace_name,'** Total **') "Tablespace Name", 
sum("allocated") "Allocated(M)", 
sum("used") "Used(M)", 
sum("free") "Free(M)", 
sum(df_cnt) "#(File)" 
from 
( 
select a.tablespace_name, trunc(b.assigned/1048576) "allocated", 
trunc((b.assigned-a.free)/1048576) "used", 
trunc(a.free/1048576) "free", 
df_cnt 
from 
( 
select tablespace_name, sum(bytes) free 
from dba_free_space 
group by tablespace_name ) a, 
( 
select tablespace_name, sum(bytes) assigned, count(*) df_cnt 
from dba_data_files 
group by tablespace_name ) b 
where a.tablespace_name = b.tablespace_name 
UNION ALL 
SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt 
from dba_temp_files 
group by tablespace_name 
) 
group by rollup(tablespace_name) ; 

spool off