LinkedIn pixel

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. 

 

EBS

 

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) 

 

OS Sql

 

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. 

 

SQL Tuning Advisor search

SQL Tuning Advisor results

 

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. 

 

AppTier

 

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