SQL With Left Outer Join is Slower and Showing Different Behavior In 12c (Doc ID 2284617.1)

Last updated on JULY 11, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.2.0.1 [Release 12.1 to 12.2]
Information in this document applies to any platform.
This is not a Wrong Results problem. The results in both 11g and 12c are correct. Applying a Profile or Outline does not necessarily change the behavior. Setting optimizer_features_enabled to a lower value such as 11.2.0.4 makes no difference to this changed behavior.

Symptoms

The following example SQL with a LEFT OUTER JOIN show the same plans in 11.2.0.3 and 12.1.0.2 but timings and behavior is different.

11g- It results in 5 seconds [Sleep once for all records]
12c - It results in 20 seconds [Sleep once for each record 4x5 = 20 secs]

The significant difference in this plan is that the number of Starts for line 6 is 4 (once for each of "1", "2", "3" and "4"). The use of the Lateral View can be eliminated but makes no difference. Since, as in this case, the FUNC_WAIT function waits 5 seconds and there are 4 starts of line 6 the SQL will wait for a total of 20 seconds (5 seconds x 4 Starts).

Changes

 There were no code changes from 11g to 12c. (12.1.0.2. and 12.2.01).

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