Performance Degradation Occurs or Error "ORA-12537: Network Session: End of File" When the Database Contains a Large Amount of EPS Level Activity Code Values Drawing a Gantt Chart in Visualizer (Doc ID 2226769.1)

Last updated on MARCH 08, 2017

Applies to:

Primavera P6 Enterprise Project Portfolio Management - Version 16.2.2.0 and later
Information in this document applies to any platform.

Symptoms

ACTUAL BEHAVIOR
----------------------------------------------
Using Visualizer to draw a gantt chart, performance degradation occurs when the database contains a large amount of EPS level activity code values.

EXPECTED BEHAVIOR
----------------------------------------------
For improved performance (and/or for the noted error not to occur) when using Visualizer to draw a gantt chart against project(s) which contain a large amount of EPS level activity code values in the database.

STEPS
----------------------------------------------
The issue can be reproduced at will with the following steps:

Prereq required: Create a large amount of activity code values:

  1. Login to P6 Professional
  2. Select Enterprise, Activity Codes
  3. Select EPS Radio Button
  4. Select Modify and create an EPS activity code
  5. Add 500 code values to the EPS activity code created under step 4
  6. Select Modify, and copy/paste the activity code 90 times
    • Note, this should create 90 EPS activity codes, each code containing 500 distinct code values for a total of 45000 distinct EPS activity level code values created in the database.

Steps to reproduce the issue within visualizer:

  1. Login to Visualizer
  2. Select +, Gantt
  3. Select any project
  4. Select option to create a layout
  5. Select Draw
  6. Note the performance degradation which occurs.

ADDITIONAL INFORMATION
----------------------------------------------
#1
After configuring SQL Monitor with Visualizer, the following SQL statement execution time is resulting in the long delay to draw the gantt chart:

select taskactv.task_id, taskactv.actv_code_type_id, taskactv.actv_code_id,taskactv.proj_id from taskactv, actvcode, actvtype wheretaskactv.actv_code_type_id = actvtype.actv_code_type_id and actvcode.actv_code_type_id = actvtype.actv_code_type_id and
actvcode.actv_code_id = taskactv.actv_code_id and actvcode.delete_session_id is null and actvtype.delete_session_id is null AND (taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>) OR taskactv.actv_code_id IN (<LIST_OF_IDs>)) AND (taskactv.proj_id IN (93831))

The large "taskactv.actv_code_id IN" clause in the statement is not based on actual code value assignments. Rather, the large in clause is based simply on the amount of EPS level activity code values which exist in the DB. Each "taskactv.actv_code_id IN" clause contains 1000 distinct IDs, and since it is listed roughly 38 times there are roughly 38000 EPS Code Values which exist in the customer's DB.

#2
If the DB contains greater than 45000 EPS level activity code values and it can cause the drawing of the gantt to fail instead of seeing performance degradation when the sql statment is being executed.

The UI error is:  "errors occurred while opening projects. Data for these projects is not displayed"

The error in visualizer.log:
TIMESTAMP Information Exception Exception while opening projects: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12537: Network Session: End of file ---> OracleInternal.Network.NetworkException (0x000030F9): ORA-12537: Network Session: End of file
at OracleInternal.Network.ReaderStream.Read(OraBuf OB)
at OracleInternal.TTC.OraBufReader.GetDataFromNetwork()
at OracleInternal.TTC.OraBufReader.Read(Boolean bIgnoreData)
at OracleInternal.TTC.MarshallingEngine.UnmarshalUB1(Boolean bIgnoreData)
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean& bAllPureInputBinds, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List'1& implicitRSList, Boolean bLOBArrayFetchRequired)
at Oracle.ManagedDataAccess.Client.OracleException.HandleError(OracleTraceLevel level, OracleTraceTag tag, Exception ex)
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean& bAllPureInputBinds, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List'1& implicitRSList, Boolean bLOBArrayFetchRequired)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Primavera.Venus.Data.DbStore.OracleHydrator.GetReader(DbCommand cmd)
at Primavera.Venus.Data.DbStore.BaseSqlHydrator.<Query>d__e'1.MoveNext()
at Primavera.Venus.Infrastructure.Types.Entity.EntitySet'1.Load(IEnumerable'1 source)
at Primavera.Venus.Data.DataStore.LoadProjectEntities(IEnumerable'1 theProjects)
at Primavera.Venus.DataRepository.BaseDataserviceContext.OpenProjects(Int32[] ids, Int32 SessionId)
at Primavera.Venus.DataRepository.ProjectRepository.OpenProjects(Int32[] projectsToOpen, Boolean OpenBaselines)
at Primavera.Venus.Presentation.Views.Reports.ReportInitiationViewModel.ExecuteOpenProjects()

 



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