HASH JOIN RIGHT OUTER Estimated To Return Only 1 Row When OPTIMIZER_DYNAMIC_SAMPLING Is Set To 11
(Doc ID 2549959.1)
Last updated on APRIL 09, 2020
Oracle Database - Enterprise Edition - Version 18.104.22.168 and later
Information in this document applies to any platform.
- OPTIMIZER_DYNAMIC_SAMPLING set to 11, a HASH JOIN RIGHT OUTER operation will be estimated to only return a single row, even though millions of rows are actually returned
- This hugely underestimates the overall cost of the execution plan, and causes severe performance problems
- The query starts performing huge numbers of buffer gets and never returns
- Using OPT_PARAM('optimizer_dynamic_sampling' 10) results in the plan being correctly costed and returns
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
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.