My Oracle Support Banner

Query With Large IN Clause Causes Database Server To Become Unresponsive (Doc ID 2359974.1)

Last updated on FEBRUARY 16, 2018

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 15.2.13.0 to 15.2.16.2 [Release 15.2]
Information in this document applies to any platform.

Symptoms

DESCRIPTION OF THE LOSS OF FUNCTIONALITY
----------------------------------------------

In Oracle Primavera P6 WebAccess when running a tracer tool, How To Trace SQL And Java From The P6 Web Access Server (Doc ID 894429.1), we notice that the following query, with a large IN clause is consuming significant resources and causing delays in the software. The query takes the form of:

SELECT projwbs.proj_id,
  projwbs.wbs_id,
  projwbs.parent_wbs_id,
  projwbs.wbs_short_name,
  projwbs.obs_id,
  projwbs.proj_node_flag,
  project.project_flag,
  project.msp_managed_flag,
  project.orig_proj_id,
  projwbs.status_code,
  project.checkout_flag
  FROM projwbs, project
 WHERE project.proj_id = projwbs.proj_id
  AND ( ( (projwbs.proj_id >= 3306) AND (projwbs.proj_id <= 3320))
  OR ( (projwbs.proj_id >= 3756) AND (projwbs.proj_id <= 3767))
  OR ( (projwbs.proj_id >= 4034) AND (projwbs.proj_id <= 4043))
  OR ( (projwbs.proj_id >= 4071) AND (projwbs.proj_id <= 4090))
  OR ( (projwbs.proj_id >= 4095) AND (projwbs.proj_id <= 4119))
  OR ( (projwbs.proj_id >= 4121) AND (projwbs.proj_id <= 4131))
  OR ( (projwbs.proj_id >= 4149) AND (projwbs.proj_id <= 4166))
  OR ( (projwbs.proj_id >= 4188) AND (projwbs.proj_id <= 4197))
  ¿..Query continues to list OR and AND statements.




Changes

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.