My Oracle Support Banner

E1: DB: Database Query Performance (Doc ID 2540887.1)

Last updated on DECEMBER 07, 2021

Applies to:

JD Edwards EnterpriseOne Tools - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

There have been issues when some databases prefer to utilize one specific scanning method over another. This document hopes to go over more detail on the specifics.

 When running a report or UBE, a database connection (or more) may be opened to retrieve information.

 

The report may be delayed on a specific query or timeout, which is usually best identified based off the debug log of the specific request. If the user is from the web, then it can often be identified through Server Manager Console to identify what Enterprise Server connection they are utilizing.

If the report continues, the database may report a deadlock as in an example the first DB connection is still locking F0911.

This occurs when there is a small number of records in F0911, or no index to be used. The problem occurs when table scan seeking is being utilized.

 As a product, our submission of SQL queries is interpreted by intermediaries, primarily the database driver, ODBC or JDBC depending on connection, this helps the product communicate the query to the database

So for each database we communicate with a driver is utilized based on the code it is using, primarily C and Java, depending on the connection needed.

Then the database has to respond to the driver request. It should be utilizing either historical data or estimate algorithms to access the table with it's preferred algorithm.

 

This may be why the database uses table seek vs index scan as the index may be fragmented over user and require maintenance or the table scan may be preferred by the database.

The database is responsible for searching within itself in the manner it is design to maintain consistency and performance, it may trigger off of other variables such as indexes or algorithm requests

Currently, there is a workaround to supply a number of dummy records in the F0911, which causes a different algorithm to be used when accessing the table. This currently forces use of an index seek algorithm.

The usage of a custom index has also prevented this, however the database administrator should be consulted before adding indexes.

for SQL Server: CREATE NONCLUSTERED INDEX   SNC_CUSTOM_F0911_PERFORMANCE_01   ON      CPDTA.F0911 (GLICU, GLICUT, GLPOST, GLCO);

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!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.