Performance Problem in Assignment Manager

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: Performance Problem in Assignment Manager

Symptoms:

the first Task created the assignment process is slow when we press the assignment button , but if we close the Task form and re-open it, then create a new task, the assignment process is quick.
Issue happened when launching into apps for the first time, Attempting to assign a task, It takes more than 30 seconds to pull the data.

The issue was originally raised for the attention of JTF team because the most expensive query is from calendar..

Log files:

SELECT E.START_DATE_TIME START_EXCEPT, E.END_DATE_TIME END_EXCEPT,
NVL(A.START_DATE_ACTIVE,:B1 ) START_ASSIGN, NVL(A.END_DATE_ACTIVE,:B2 )
END_ASSIGN
FROM
JTF_CAL_EXCEPTION_ASSIGN A ,JTF_CAL_EXCEPTIONS_B E WHERE A.CALENDAR_ID = :B3

AND A.EXCEPTION_ID = E.EXCEPTION_ID AND E.START_DATE_TIME <= :B2 AND E.END_DATE_TIME >= :B1 AND NVL(A.START_DATE_ACTIVE,:B2 ) <= :B2 AND NVL(A.END_DATE_ACTIVE,:B1 ) >= :B1 UNION ALL SELECT DECODE(:B5 ,'Y',
DECODE(T.TIMEZONE_ID,NULL, T.SCHEDULED_START_DATE,
NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(T.TIMEZONE_ID, :B4 ,
T.SCHEDULED_START_DATE), T.SCHEDULED_START_DATE) ), T.SCHEDULED_START_DATE)

START_EXCEPT, DECODE(:B5 ,'Y', DECODE(T.TIMEZONE_ID,NULL,
T.SCHEDULED_END_DATE, NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(T.TIMEZONE_ID,
:B4 , T.SCHEDULED_END_DATE), T.SCHEDULED_END_DATE) ), T.SCHEDULED_END_DATE)

END_EXCEPT, :B1 START_ASSIGN, :B2 END_ASSIGN FROM JTF_TASKS_B T,
JTF_TASK_ASSIGNMENTS A, JTF_TASK_STATUSES_B S WHERE A.RESOURCE_ID = :B7 AND

A.RESOURCE_TYPE_CODE = :B6 AND DECODE(:B5 ,'Y', DECODE(T.TIMEZONE_ID,NULL,
T.SCHEDULED_START_DATE, NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(T.TIMEZONE_ID,

:B4 , T.SCHEDULED_START_DATE), T.SCHEDULED_START_DATE) ),
T.SCHEDULED_START_DATE) <= (TRUNC(:B2 )+86399/86400) AND DECODE(:B5 ,'Y', DECODE(T.TIMEZONE_ID,NULL, T.SCHEDULED_END_DATE, NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(T.TIMEZONE_ID, :B4 , T.SCHEDULED_END_DATE), T.SCHEDULED_END_DATE) ), T.SCHEDULED_END_DATE) >=
TRUNC(:B1 ) AND S.TASK_STATUS_ID = A.ASSIGNMENT_STATUS_ID AND T.TASK_ID =
A.TASK_ID AND NVL(S.CANCELLED_FLAG,'N') <> 'Y' AND
NVL(S.COMPLETED_FLAG,'N')
<> 'Y' AND T.SCHEDULED_START_DATE <> T.SCHEDULED_END_DATE ORDER BY 1,2




call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.02 0.02 0 0 0
0
Execute 11 0.04 0.03 0 0 0
0
Fetch 11 0.36 13.67 2698 5574 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 23 0.42 13.72 2698 5574 0
0



For total of 11 execution it took 14 Secs.Don't think this is performance problem from any perspective.


Solution:





bug 10153795 - PERFORMANCE PROBLEM IN ASSIGNMENT MANAGER

create index on JTF.JTF_TASK_ASSIGNMENTS Table with following 3 columns (resource_id,resource_type_code,assignee_role) .

Get Oracle Certifications for all Exams
Free Online Exams.com