SMART SCAN Doesn't Work Only On A Specific Instance Of RAC For a Same SQL Statement
(Doc ID 2952716.1)
Last updated on JUNE 11, 2023
Applies to:
Gen 2 Exadata Cloud at Customer - Version N/A and laterInformation in this document applies to any platform.
Symptoms
On : NA version, Database Performance Issues
ACTUAL BEHAVIOR
---------------
When a same SQL statement is executed in each instance of RAC, the elapsed times are different.
The elapsed time if the 2nd instance is 4 times greater than it of the 1st instance.
+Offending SQL statement
SELECT /*+ gather_plan_statistics FULL(T) PARALLEL(T 4) MONITOR */ *
FROM TB_OPT_MVMT_MST T
WHERE 1=1
AND
NOT EXISTS
( SELECT /*+ UNNEST HASH_SJ */ 1 FROM TB_MVMT@LK_NOIS B WHERE 1 = 1 AND B.MVMT_ID = T.MVMT_ID )
+ Result of the execution of the offending query
On 1st Instance : Elapsed Time : 28 rows returned, 7 mins 23 secs
On 2nd Instance: Elapsed Time : 28 rows returned, 31 mins 28 secs
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 |
| Cause |
| Solution |
| References |