SQL With Left Outer Join is Slower and Showing Different Behavior In 12c
Last updated on NOVEMBER 29, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [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 184.108.40.206 makes no difference to this changed behavior.
The following example SQL with a LEFT OUTER JOIN show the same plans in 220.127.116.11 and 18.104.22.168 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).
There were no code changes from 11g to 12c. (22.214.171.124. and 12.2.01).
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