SQL With Left Outer Join is Slower and Showing Different Behavior In 12c
(Doc ID 2284617.1)
Last updated on MARCH 28, 2019
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 12.1 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
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).
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