AME Concept in Oracle Apps R12

Let's take a closer look at the 5 key terminology used in AME concepts:

1. Attribute

2. Conditions

3. Approver Group

4. Action Types

5. Rule

Let us understand each terminology in detail.

Attribute: Attribute refers to a column name extracted from a transaction.

Examples of attributes:

Requisition Total, Item Category, Transaction requestor Person ID, Invoice Amount... So.On.

Condition: Using attributes,  if Condition are created.

If Requisition total > $1000 

Approval Group: when additional approver are required for particular conditions and to specify a dynamic SQL query for Additional approval groups are created.

Action Types : Action types helps to understand the type of approval hierarchy followed for example: Supervisory Level or Approval - Group Chain of authority.

Rule: Rule are created with combination of  Condition and Action type. For Example, if the following condition is true (If Requisition total > $1000) then following action type ( Follow Supervisory Level Hierarchy)

How to diagnose the performance issue in oracle apps R12.

1.  Trace - We run the trace based on particular activity. We require to generate trace based on particular activity like concurrent report, or form which is leading to time consumption. Trace can be run at the below level

- Trace can be run at respective Form Level

- Trace need to be enabled at Concurrent Define window.

- Trace fetching based on Request ID

Once trace file is generated. Reveiw the trace file to find out at which level there performance issue for a Particular SQL ID as is mentioned below.
                            ·         Parsing
                            ·         Fetching
                            ·         Execution

2. AWR Report: AWR Report will be run every 1 hour as scheduled by DBA team. For this report we will be able to find out Top 10 SQL ID which is causing performance issue. Based on SQL ID, we will generated

3. SQLT Report:  For particular SQL ID we have to generate SQLT Report.  Which will help us to identify the number indexes created on particular table or Query which is causing for SQL Execution issue.

Oracle Enterprise Management (OEM)

From Oracle Enterprise Management (OEM) window we will be able to find the load on the server. As per the graph below, Blue indicates high load on the server.

`

Blocking Queries:

select to_char(a.LOGON_TIME,'DDMonYY HH24:MM'),a.sid, a.serial#,c.NAME, a.STATUS,a.ACTION from V$SESSION a ,DBA_BLOCKERS b,AUDIT_ACTIONS c

where a.sid = b.holding_session and a.command = c.action;

select /*+ rule */ 'SID '||a.sid||' is blocking the sessions '||b.sid from v$lock a, v$lock b where a.block=1 and b.request >0;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine

                 FROM  v$locked_object a, v$session b,  dba_objects c

                 WHERE b.SID = a.session_id AND a.object_id = c.object_id  AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

  

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine FROM  v$locked_object a, v$session b,  dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine FROM  v$locked_object a, v$session b,  dba_objects c

WHERE b.SID = a.session_id AND a.object_id = c.object_id  AND b.sid in ( select holding_session from DBA_BLOCKERS ) ;

SELECT session_id  FROM DBA_LOCKS WHERE BLOCKING_OTHERS = 'Blocking';

select * from DBA_WAITERS;

select * from dba_blockers;

select * from v$lock where block=1;

SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess,inst_id,id1, id2,lmode,request, TYPE

FROM gv$LOCK  WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM gv$LOCK WHERE request > 0)

ORDER BY id1, request;

SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ','||'@'||''|| i.instance_number || ''';' a,'ps -ef |grep LOCAL=NO|grep ' || p.SPID SPID,

'kill -9 ' || p.SPID,s.status FROM gv$session s, gv$process p,gv$instance i WHERE ( (p.addr(+) = s.paddr) AND (p.inst_id(+) = s.inst_id)) AND s.sid = &sid;

SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE

|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '

|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' ||L1.ID1||' OBJ_NAME:'||O.OBJECT_NAME

AS BLOCKING_STATUS

FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2,DBA_OBJECTS O

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.ID1=OBJECT_ID

AND L1.ID1=O.OBJECT_ID

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

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

    SELECT

   l1.sid || ' is blocking ' || l2.sid blocking_sessions

FROM

   gv$lock l1, gv$lock l2

WHERE

   l1.block = 1 AND

   l2.request > 0 AND

   l1.id1 = l2.id1 AND

   l1.id2 = l2.id2;