by Fred Tingle – Principal Architect
In Part One of this series, we discussed how to detect long-running programs and code to improve the performance of EBS Applications. For Part Two of this series, we will look at the OS for the Application and Database Tiers.
When looking at the Database Tier you can use the “top” command to see which PID is using the most CPU on the hardware. In the image below, the RED Box shows where to find the PID and OS user ID of the processes with the highest CPU levels.
By applying the below query in the database, it allows you to identify which query is causing high CPU utilization on the server.
SELECT
a.inst_id,
a.sid,
a.serial#,
a.logon_time,
a.last_call_et/60,
a.username,
a.sql_id,
a.status,
a.machine,
a.module,
a.program,
a.action,
a.event,
a.client_identifier, p.spid
FROM gv$session a, gv$process p
WHERE a.paddr = p.addr
AND p.spid in
(31229)
In the above SQL output find the SQL ID then go to Tuning Advisor in OEM->Database->your instance name and under Performance go to SQL and search SQL.
The above results show the two recommendations provided by the SQL Tuning Advisor. To address the database issues, the recommended indexes should be created; however, since the new indexes are standard tables, you will need to test overall system performance in non-production before implementing. A short-term alternative would be to pin the improved plan with the SQL profile provided. Once you pin the profile, you will need to cancel the program and restart it to see the new profile. Canceling the program needs to be done carefully since it could cause data corruption if done incorrectly.
Next, you can look at the Application Tier to see if there are any types of system load/performance issues. The Application Tier in the above screenshot is running the EBS Weblogic Admin tier, as well as OACORE/FORMS/OAFM, servers. We also run MWA telnet sessions from this server and any server slowness can impact the user’s experience which means the system isn’t working at the optimal level.
The image shown above displays the server where the application is running and functioning as a healthy system. You must learn what a healthy system looks like, so that you’re able to identify and address queries that cause high CPU utilization and impact overall performance.
Let’s Work Together to Achieve Peak Performance for your EBS Environment!
Learn more about detecting long-running programs and enhancing code performance by reaching out to the author or our office.
Fred Tingle – Principal Architect
Email: [email protected]
Cell: 563-607-1555
LinkedIn: linkedin.com/in/fred-tingle-2046857