SQL Statements Having DISTINCT Run Slower After Upgrade To 12.2.0.1
(Doc ID 2493549.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
- Query performance degraded after upgrade the database from 11.2.0.3 to 12.2.0.1.
- SQL statements using DISTINCT in outer and inner query take long time in 12.2.0.1.
- View merging does not happen for SQL statements having DISTINCT keyword in outer and inner query.
- Parse operation takes more CPU time causing cursor: pin S wait on X waits in the database due to concurrency.
- Setting optimizer_features_enable to 12.1.0.2 or 11.2.0.4 does not help.
- Setting optimizer_features_enable to 11.2.0.3 only generates the good plan and the query runs faster.
Excerpt of Bad Plan(View Merging Not Happens) in 12.2.0.1:
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 300 | 93 (7)| 00:00:01 |
| 1 | HASH UNIQUE | | 50 | 300 | 93 (7)| 00:00:01 |
| 2 | VIEW | | 50 | 300 | 92 (6)| 00:00:01 |
| 3 | HASH UNIQUE | | 50 | 3350 | 92 (6)| 00:00:01 |
|* 4 | HASH JOIN | | 50 | 3350 | 91 (5)| 00:00:01 |
Excerpt of Good Plan in 11.2.0.3 (View Merging Happens):
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 2700 | 92 (6)| 00:00:01 |
| 1 | HASH UNIQUE | | 50 | 2700 | 92 (6)| 00:00:01 |
|* 2 | HASH JOIN | | 50 | 2700 | 91 (5)| 00:00:01 |
| 3 | JOIN FILTER CREATE | :BF0000 | 50 | 1750 | 61 (5)| 00:00:01 |
Changes
Database upgrade to 12.2.0.1.
Cause
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
| Symptoms |
| Changes |
| Cause |
| Solution |