Improving Performance of E-Business Suite

Applications – Practical Tips from a DBA's Diary Session ID# 11573

Biju Thomas

OneNeck IT Services Enterprise Hosted Application Management and

Managed Services Provider

• Principal Solutions Architect with OneNeck IT Services

• More than 19 years of Oracle Database Experience

• Working with Oracle EBS databases and applications

past 7 years. Always like to approach EBS from the

database to application…

• EBS expertise in setting up new environments,

automation, cloning, troubleshooting and performance

tuning

• Author of Oracle11g OCA, co-author of Oracle10g, 9i,

8i OCP certification guides published by Sybex/Wiley.

About the Speaker

Message…

Do not shy away from exploring the database…

• Lot of unanswered questions at the EBS application side can be

answered at the database. Almost all of performance issues end

up in the database.

• Newer releases of databases collect large amount of

performance related information.

• Most of the time it is the custom code or incorrect profile options

affecting performance…

Agenda…

Finding expensive SQL – using AWR

Tying the SQL to an EBS job

SQL Queries to gather Concurrent Job information

Few major performance issues & resolution

Periodic database maintenance for Performance

Oracle provided periodic EBS maintenance jobs

Multiple Concurrent Manager lanes for performance

Upgrade database to 11gR2

• Automatic Workload Repository - Introduced in Oracle 10g

• AWR is automatically installed and enabled, snapshot collection is scheduled

• Information automatically populated by database – but need license to use

information.

• Baseline snapshots, compare two time periods

• Use DBA_HIST_ views

AWR – Quick Overview

• Default keeps 7 days information

– need to increase to at least a

month to help analysis

• Fully integrated in OEM

• Captures resource intensive

SQLs, Object usage statistics

• Pay attention to Tablespase I/O,

SGA/PGA sizing

recommendations!

@$ORACLE_HOME/rdbms/admin/awrrpt.sql, awrsqrpt.sql, awrddrpt.sql

The “Dirty” SQL

• High buffer gets – LIO

• Use DBA_HIST_SQLSTAT

• Captures the statistics information

from V$SQL

• Tie in the SQL_ID to

DBA_HIST_SQLTEXT for SQL

statement.

• Tie in the SQL_ID to

DBA_HIST_ACTIVE_SESS_HISTO

RY for User id and Program name.

• May also use OEM to find more

information on the SQL_ID.

SELECT * FROM (

SELECT sql_id,

module,

SUM (nvl(buffer_gets_delta,0)) bget,

SUM (nvl(executions_delta,0)) exec,

SUM (nvl(cpu_time_delta,0)) cput,

SUM (nvl(elapsed_time_delta,0)) elap,

SUM (nvl(rows_processed_delta,0)) rowsp,

SUM (nvl(sorts_delta,0)) sorts,

SUM (nvl(disk_reads_delta,0)) diskread

FROM dba_hist_sqlstat

WHERE dbid = V_DBID

AND instance_number = V_INST

AND snap_id between

V_SNAP_B and V_SNAP_E

GROUP BY module, sql_id

--ORDER BY bget/nvl(nullif(exec,0),1) desc)

ORDER BY bget desc)

WHERE ROWNUM < 21

“Dirty” SQL Example

Finding Concurrent Program • For all EBS standard programs, “MODULE” in AWR (v$session) is

the Concurrent Program Name.

• Details on the program and request run times can be obtained from

FND_CONCURRENT_PROGRAMS and

FND_CONCURRENT_REQUESTS using the MODULE.

More Info on SQL from OEM

Identifying Job Slowness

Issue Using ASH

• User complains concurrent job “Item Cost Import” ran longer and

provided request id.

• FND_CONCURRENT_REQUESTS has details including start and

end time.

• Find the concurrent program name of the request, and search ASH

for module during the timeframe.

The ASH Report

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

Identifying Session Details Using ASH

• ASH report also shows blocking sessions during the window selected.

Filtering the report for a specific session gives more details.

AWR/ASH Not Substitute for 10046 Trace

• AWR cannot replace Session trace, but is a quick analysis tool for performance

issues or for trending.

• Favorite trace interpretation utility is “Trace Analyzer” – which is “tkprof on

steroids”

• Trace Analyzer reads a raw SQL Trace generated by standard SQL Trace or by

EVENT 10046 (Level 4, 8 or 12), and generates a detailed HTML report with

performance details, including bind values.

• Trace Analyzer is available from MetaLink, see note # 224270.1 for download

and install instructions.

My Most Used SQL Query SELECT DISTINCT Substr (fcpv.concurrent_program_name

|| ' - ' || fcpv.user_concurrent_program_name, 1, 60) program,

fu.user_name, fu.description who, fcr.request_id, fcr.parent_request_id,

fcr.requested_start_date, fcr.actual_start_date, fcr.actual_completion_date,

Numtodsinterval(fcr.actual_completion_date - fcr.actual_start_date, 'day') duration,

fcr.argument_text, phase_code, status_code,

Decode (fcr.phase_code, 'P', 'Pending', 'R', 'Running', 'C', 'Completed', phase_code) phase,

Decode (fcr.status_code, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal‘, 'D', 'Cancelled',

'E', 'Error', 'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager',

'P', 'Scheduled', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T', 'Terminating',

'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting') status,

fcr.completion_text

FROM apps.fnd_concurrent_requests fcr, apps.fnd_concurrent_programs_tl fcpt,

apps.fnd_concurrent_programs_vl fcpv, apps.fnd_user fu

WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id

AND ( fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id )

AND fcr.requested_by = fu.user_id

-- AND fcr.actual_start_date >= SYSDATE - 1

-- and to_date('04/02/2012 01:05:00','MM/DD/YYYY HH24:MI:SS') between fcr.actual_start_date and

fcr.actual_completion_date

AND fcpt.USER_CONCURRENT_PROGRAM_NAME LIKE 'Item Cost I%'

-- AND fcr.request_id =38664594

-- AND fcpv.Concurrent_Program_Name like 'INCOIN%'

-- and argument_text like '%MDS_MRP%'

-- and TRUNC((fcr.ACTUAL_COMPLETION_DATE - fcr.ACTUAL_START_DATE)*24) > 2/24

-- and fcr.phase_code = 'P'

-- and fcr.status_code in ('E')

-- and fcr.requested_by != 0

-- and fcr.actual_start_date is null

-- and fcr.requested_start_date < sysdate

ORDER BY fcr.ACTUAL_START_DATE desc

Variations of Favorite Query – Running

Requests with SQL and Session Lock Info SELECT fcr.Request_Id Request_id, fcr.parent_request_id parent_req,

fu.User_name, substr(fu.description, 1,30) user_desc, s.sid || ', ' || s.serial# SIDSERIAL,

fcpv.Concurrent_Program_Name|| ' - ' || Fcpv.User_Concurrent_Program_Name Program,

TO_CHAR (fcr.actual_start_date, 'mm/dd hh24:mi') start_time, fcr.requested_start_date,

numtodsinterval(sysdate-fcr.actual_start_date,'day') duration, fcr.argument_text,

p.spid, fpro.os_process_id, phase_code, status_code,

qt.user_concurrent_queue_name, s.lockwait, s.sql_id, s.module

FROM apps.Fnd_Concurrent_Queues Fcq, apps.fnd_concurrent_queues_tl qt,

apps.Fnd_Concurrent_Requests Fcr, apps.Fnd_Concurrent_Programs Fcp,

apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes Fpro,

v$session s, v$process p, apps.Fnd_Concurrent_Programs_Vl Fcpv

WHERE status_Code = 'R' AND s.paddr = p.addr

AND fcr.requested_by = user_id

AND fcq.application_id = qt.application_id

AND fcq.concurrent_queue_id = qt.concurrent_queue_id

AND USERENV ('lang') = qt.language

AND fcr.os_process_id = s.process AND fcr.Controlling_Manager = Concurrent_Process_Id

AND (fcq.concurrent_queue_id = fpro.concurrent_queue_id

AND fcq.application_id = fpro.queue_application_id)

AND (fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id)

AND (fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id)

ORDER BY fcr.actual_start_date;

Variations of Favorite Query – Runaway

Concurrent DB Sessions SELECT fcr.request_id, fu.user_name, p.spid, s.sid ||', ‘ || s.serial# sidserial, s.module,

Substr(fcpv.concurrent_program_name ||' - ' || fcpv.user_concurrent_program_name, 1, 46) program,

To_char(fcr.actual_start_date, 'mm/dd hh24:mi:ss') CM_Job_start_time,

To_char(s.logon_time, 'mm/dd hh24:mi:ss') Session_login_time,

phase_code, status_code, To_char(Trunc(SYSDATE) + ( SYSDATE - fcr.actual_start_date ), 'hh24:mi:ss') duration

FROM apps.fnd_concurrent_queues fcq,

apps.fnd_concurrent_queues_tl qt,

apps.fnd_concurrent_requests fcr,

apps.fnd_concurrent_programs fcp,

apps.fnd_user fu,

apps.fnd_concurrent_processes fpro,

v$session s,

v$process p,

apps.fnd_concurrent_programs_vl fcpv

WHERE phase_code = 'C' AND status_code = 'X' AND s.paddr = p.addr

AND fcr.requested_by = user_id

AND fcq.application_id = qt.application_id

AND fcq.concurrent_queue_id = qt.concurrent_queue_id

AND Userenv('lang') = qt.language

AND fcr.os_process_id = s.process

AND fcr.controlling_manager = concurrent_process_id

AND ( fcq.concurrent_queue_id = fpro.concurrent_queue_id

AND fcq.application_id = fpro.queue_application_id )

AND ( fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcr.program_application_id = fcp.application_id )

AND ( fcr.concurrent_program_id = fcpv.concurrent_program_id

AND fcr.program_application_id = fcpv.application_id )

and fcpv.concurrent_program_name = s.module ORDER BY fcr.actual_start_date;

Performance Issues Identified…

• Few performance issues identified and fixed using

AWR/ASH method…

Update Standard Costs

Calculate Plan Performance Indicators

MRP

Concurrent Debug

ATP

Custom Index / Table INITRANS

Perf Issue: Update Standard Costs

• Large LIO on BOM.CST_STD_COST_ADJ_VALUES

table noticed.

• Coming from CMCICU

• SQL happened to be typical optimizer statistics collection

• Every time program runs, 10% statistics on table was run – multiple

users run the program multiple times a day, on a table with over 80

million rows!

• Fixed issue by setting profile “CST: Cost update- Gather

Statistics” to “N”.

• Weekly GATHER and daily GATHER_AUTO statistics scheduled in

DB, hence this stat collection seemed unnecessary.

• Look for similar profiles like “AR: AutoInvoice Gather Statistics”.

SELECT

substr(pro1.user_profile_option_name,1,35) Profile,

decode(pov.level_id,

10001,'Site',

10002,'Application',

10003,'Resp',

10004,'User') Option_Level,

decode(pov.level_id,

10001,'Site',

10002,appl.application_short_name,

10003,resp.responsibility_name,

10004,u.user_name) Level_Value,

nvl(pov.profile_option_value,'Is Null') Profile_option_Value

FROM

apps.fnd_profile_option_values pov,

apps.fnd_responsibility_tl resp,

apps.fnd_application appl,

apps.fnd_user u,

apps.fnd_profile_options pro,

apps.fnd_profile_options_tl pro1

WHERE

lower(pro1.user_profile_option_name) like ('%sla%diag%')

and pro.profile_option_name = pro1.profile_option_name

and pro.profile_option_id = pov.profile_option_id

--and lower(pov.profile_option_value) like '%desdb2%'

--and resp.responsibility_name like '%General%Ledger%'

and pov.level_value = resp.responsibility_id (+)

and pov.level_value = appl.application_id (+)

and pov.level_value = u.user_id (+)

order by 1,2;

Perf Issue: Calculate Plan

Performance Indicators

• Large LIO on MRP.MRP_BIS_INV_DETAIL table.

• Several deletes and inserts on the table multiple times a day. Table

had multiple million rows.

• Coming from module MRPCBIS

• Per metalink Note 783088.1 this job is no longer needed in Master

Scheduling / MRP.

• Turn off calculation using profile “MRP: Calculate Plan

Performance” to “No”.

• Truncate tables MRP_BIS_INV_DETAIL and

MRP_BIS_RES_SUMMARY.

Perf Issue: Materials Requirement

Planning • The purpose of material planning is to balance material supply to

demand by simulating the future – very critical process in some of our

environments.

• Performance improved by setting the following profile values…

• MRP:Planning Manager Batch Size = 1000

• MRP:Planning Manager Max Workers = 10

• MRP:Purge Batch Size = 600,000

• MRP:Snapshot Workers = 10

• MRP specialized manager should have at least “(2 x <Snapshot

Workers>) + 6” processes.

Perf Issue: Debug and Trace

• There were many profiles at site level set to “debug” mode.

• Revealed by large number of I/O against

FND_CONCURRENT_DEBUG_INFO and FND_LOG_MESSAGES

• Turn off tracing and debugging in production unless troubleshooting

an issue

• Few debugs that were enabled:

• Account Generator:Run in Debug Mode

• FND: Diagnostics

• FND: Debug Log Enabled

• INV:Debug Level

• WSH: Debug Level

• Concurrent programs running with trace enabled select a.concurrent_program_name ,a.enable_trace

from FND_concurrent_programs a

where a.enable_trace='Y'

Perf Issue: Available to Promise

• ATP jobs are part of supply chain planning.

• Refresh Collection Snapshots jobs run forever

• Large number of rows spotted in MLOG$ tables

• Snapshot refreshes were not deleting rows from MLOG$ tables even

after a full/incremental refresh

• Identified dependent materialized views for MLOG$ with large

number of rows

• Many snapshots (mv) are not used/refreshed for a long time.

• Manually remove them from registered snapshots

• Also, truncating the MLOG$ tables and locking statistics on them

helped

Perf Issue: Identifying and

Clearing Unused MV Reference

• Identify materialized view last refresh time SELECT r.NAME snapname, snapid,

l.log_table, snaptime

FROM sys.slog$ s, dba_registered_snapshots r,

(SELECT log_owner, master, log_table from dba_mview_logs

where log_table in ('&mlogtables' )) L

WHERE s.snapid=r.snapshot_id(+) AND

s.mowner = l.log_owner AND

s.MASTER = l.master

order by snaptime desc;

• If MV no longer used, purge and unregister. exec DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (<snapid>);

• Now MV refreshes delete rows from MLOG$ tables.

• MV will be registered again when FULL refresh is done.

Perf Issue: INITRANS

• AWR report showed several wait events on “Read by

another session” or “Buffer busy waits”

• Almost all of the tables/indexes involved in this wait

were custom tables/indexes.

• By default most EBS tables/indexes have INITRANS

value of 10 and 11. These custom tables/indexes were

created with default value of 2.

• Rebuild the indexes and reorganize tables with

appropriate INITTRANS • Note: DB is not using ASSM. The INITRANS setting controls Initial Transaction Slots (ITLs). A

transaction slot is required for any session that needs to modify a block in an object.

Maintenance: Purge and

Cleanup Jobs • EBS 11i and R12 provide several concurrent jobs for application and

database maintenance to keep it running healthy.

• Commonly scheduled maintenance jobs • Gather Schema Statistics - If you are on 11gR2, you may estimate percent 0%,

thus Oracle will use automatic sampling.

• Purge Concurrent Request and/or Manager Data

• Consider Scheduling these maintenance jobs • Purge Debug Log and System Alerts

• Purge Signon Audit data

• Purge Obsolete Workflow Runtime Data

• Purge Obsolete Generic File Manager Data

• JTF Item InterMedia Index Sync Operation

• Consider multiple “Purge Concurrent Request” jobs • In addition to “Entity=All” with “Mode=Age”, schedule additional jobs with

ModeValue=1 to remove request logs for programs that run often, especially

several times a day.

• Collect 100% stats on FND_CONCURRENT_REQUESTS Daily

Back to Basics – Quick

Refresher on PCT USED/FREE

• Since the purge and cleanup jobs

delete rows from the tables, and

thousands of rows are added daily to

table, reorganizing the tables reduce

the blocks used as well as improve

performance.

• As a practice, we rebuild indexes on

these tables quarterly and reorganize

these tables annually.

• Many _INTERFACE tables also

candidates for reorg.

Indexes [Tables] for Reorg

Consideration

• Tables where purge jobs act

• FND_CONCURRENT_REQUESTS

• FND_LOGINS

• FND_LOGIN_RESPONSIBILITIES

• WF_ITEM_ATTRIBUTE_VALUES

• WF_ITEM_ACTIVITY_STATUSES

• FND_STATS_HIST

• FND_LOG_MESSAGES

• Tables where data is deleted and

inserted often

• MRP, QP Schema Indexes

• _INTERFACE tables

• CTXSYS tables

• DR$WAITING

• DR$PENDING

• MLOG$ tables

• If the size is larger

• Custom tables

• Temporary data

• Staging data

The reorg recommendation may be against Oracle’s best

practice and some experts do not believe reorg adds value.

Test in your environment …

Multiple Standard Concurrent

Managers

• Multiple Standard Managers to divide the jobs –

Request Processing Managers

• At least 3 per Concurrent Manager – Set processes

appropriate to load, but cache size at least twice

processes.

• Fast : Requests that complete under a minute – sleep value 10

secs

• Long: Requests that take longer than 10 minutes – sleep value

60 secs.

• Default: Default queue – sleep value 30 secs

• Conflict Resolution Manager Sleep time : change to low

value from default 60 seconds.

Upgrade Database to 11gR2

• If still using 10g or 11gR1 database with 11.5.10.2 or 12.x EBS, upgrade

database to 11gR2 latest patch set.

• Excellent performance improvement in many areas after upgrade. Sufferers

may be Custom programs and reports!

• Remember to run dictionary stats, fixed stats in addition to Schema Stats after

the upgrade.

• Review Oracle E-Business Suite Recommended Performance Patches [ID

244040.1]

• Few Metalink Notes and DB Patches:

Interoperability Notes Oracle EBS 11i with Oracle Database 11gR2 (11.2.0) [ID 881505.1]

Interoperability Notes EBS 12.0 and 12.1 with Database 11gR2 [ID 1058763.1]

• "Database Patch Set Update : 11.2.0.3.x – Latest PSU

• Apply all recommended interoperability patches – on DB and EBS

• Following patches may be part of latest PSU, but we applied to fix issues…

• Patch 14571027: NATIVE COMPILED FILES FOR PL/SQL ON 11G ARE SIGNIFICANTLY

LARGER THAN 10G

• Patch 14013094: PARTITION ORDER IN INDEX DEFINITION IS DIFFERENT THAN TABLE

PARTITION

• Patch 13059165: UTLRP DOES NOT REPORT INVALID OBJECTS

• Patch 12834800: ORA-7445 [qkxrPXformUnm] with positional ORDER BY or GROUP BY with

function based index

Questions?

Comments?

THANK YOU!!!

Biju Thomas

Biju.thomas@OneNeck.com

Tweets: @biju_thomas

PLEASE COMPLETE FEEDBACK FORM!