Oracle E-Business Suite System Survey

 This post contains a list of system survey questionnaires that Oracle Applications 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 Apps dba or someone having similar role will be best person to provide the output.
Sql*plus login to production database with apps user is required.

 

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