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

Last updated on OCTOBER 13, 2022

Applies to:

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.

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.

Cause

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