4) Address Line 1 Search Times Out In Pre-Prod. Existing Production Returns. Results In Around 5 Se (Doc ID 2628423.1)

Last updated on OCTOBER 06, 2022

Applies to:

Symptoms

On : 2.3.0.3.0 version, System Wide

ACTUAL BEHAVIOR
---------------
4) Address line 1 search times out in Pre-Prod. Existing Production returns. results in around 5 se


Configured a Pre-Production environment with server specs and topology identical to that intended in the new upgraded production environment.
While running some basic performance tests, we experienced huge differences in the time taken to perform certain tasks.
1) CDI Login takes around 90 seconds in Pre-Prod. Existing Production (2.1.0.6) takes around 10 to 12 seconds.
2) CDI Refresh takes around 60 seconds in Pre-Prod. Existing Production takes around 6 to 7 seconds.
3) Task Monitor Batch Process takes 10 hours to complete in Pre-Prod. Existing Production takes around 10 minutes.
4) Address line 1 search times out in Pre-Prod. Existing Production returns results in around 5 seconds.

1) The MWM installation guide (https://docs.oracle.com/cd/F11859_01/PDF/MWM_Server_Application_Installation_Guide_v2_3_0_3.pdf Page 128) states that the system should be configured with the Java option Djava.compiler=NONE. When this option is removed the Task Type Batch process runtime actually reduces to just 17 minutes.
2) The SQL used to return the Address Line 1 results has a database hint /*+ LEADING(T) */ . This causes the database to run a table scan which obviously when there are over a million records in the system takes a long time. Removing this hint causes the results to be returned in a couple of seconds.

Cause

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