How to Detect Performance Issues on all Tiers of the EBS Environment – Part 1

by Fred Tingle – Principal Architect

In Part One of this series, we will discuss how to address performance issues by removing long-running programs and code. Long-running processes within the EBS database are a good indicator that a query or process can be tuned to help improve the performance of the EBS Application.  

Knowing how to look for long-running programs and code is important to addressing slowness in your system. You can look at the long-running code through a basic script that will give you the SQL ID, which will allow you to use Oracle Tuning Advisor (pack required), or you can use the SQL ID to pull the full SQL text from the SQL area:

 

1. Below is a query to determine if a query has run longer than your determined time for long-running processes:

select  

       a.inst_id,

       sqlarea.sql_id,

       a.sid,

       a.serial#,

       a.logon_time,

       a.last_call_et/60,

       a.username,

       a.status,

       a.machine,

       a.module,

       a.program,

       a.action,

       a.event,

       a.client_identifier,

       sql_text

from gv$sqlarea sqlarea, gv$session a

where a.sql_hash_value= sqlarea.hash_value(+)

and a.sql_address= sqlarea.address(+)

and a.username is not null

and status = ‘ACTIVE’

and a.last_call_et/60 > 30

order by sql_text 

 

       a. Check for locks and blocks to see if they are causing the long-running process.

SELECT DISTINCT S1.USERNAME || ‘@’ || S1.MACHINE|| ‘ ( INST=’ || S1.INST_ID || ‘ SID=’ || S1.SID  

                ||’ CLIENT= ‘||s1.client_identifier|| ‘ ) IS BLOCKING ‘|| S2.USERNAME || ‘@’  

                || S2.MACHINE || ‘ ( INST=’ || S1.INST_ID || ‘ SID=’ || S2.SID ||’ CLIENT= ‘ 

                ||s2.client_identifier|| ‘ ) ‘ AS BLOCKING_STATUS 

FROM GV$LOCK L1,  

     GV$SESSION S1,  

     GV$LOCK L2,  

     GV$SESSION S2 

WHERE S1.SID=L1.SID AND S2.SID=L2.SID 

AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID 

AND L1.BLOCK > 0 AND L2.REQUEST > 0 

AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2 

 

       b. If no locks or blocks are detected, the next step is to determine if the Optimizer is selecting the right execution plan by executing an EXPLAIN PLAN on SQL.

              i. In the below EXPLAIN PLAN, you can see that the code is written in such a way that unless there is a function index available, no index can be used for these columns. 

 

       c. If the Optimizer selects a decent execution plan, run the SQL ID through the Tuning Advisor to view its recommendations. 

              i. Below, you will see how I searched for the SQL ID and how Tuning Advisor brought back a recommendation to pursue. 

Let’s Work Together to Boost your EBS Performance 

Tuning EBS Performance has many aspects and addressing bad code is just one of the first steps to improving the performance of your EBS Database. To help you navigate these complex issues, contact the author directly or contact our office. 

Fred Tingle – Principal Architect 

Email: [email protected] 

Cell: 563-607-1555 

LinkedIn: linkedin.com/in/fred-tingle-2046857