Sql Statement in Procedure Jtf_tasks_pvt.Delete_task Leads to Performance Degration While Deleting Tasks (Doc ID 2114999.1)

Last updated on SEPTEMBER 04, 2016

Applies to:

Oracle Common Applications Calendar - Version 12.2.3 to 12.2.3 [Release 12.2]
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
---------------
There is an SQL statement in jtf_tasks_pvt.delete_task that leads to performance degradation while deleting tasks.

Statement in cursor c_parent_child of jtf_tasks_pvt.delete_task contains nonsense condition which always evaluates to FALSE and performs a full-table-scan on JTF_TASKS_B.

In package jtf_tasks_pvt, procedure delete_task, cursor c_parent_child in line 4558 is called. It contains the following statement:

 


The second statement of the "UNION ALL" references to jtf_tasks_b twice, but does not use any table-alias. Therefore, the subselect in the EXISTS-clause does not have any restriction to a specific task_id, and therefore has to scan the whole JTF_TASKS_B for any row where parent_task_id equals task_id - which does not exist since it is not possible for a task to be its parent. This leads to massive performance problems (since JTF_TASKS_B is quite huge) and obviously to wrong results since the lower part of the UNION ALL never returns a row.


The version of package body JTF_TASKS_PVT is
/* $Header: jtfvtktb.pls 120.22.12020000.7 2015/05/06 09:30:32 srguntur ship $ */


Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms