Skip to Main Content
  • Questions

  • Is there any way to make the optimizer more or less prone to selecting a USE_HASH to join tables over a NETS_LOOP?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joe.

Asked: March 31, 2016 - 3:01 am UTC

Last updated: March 31, 2016 - 1:27 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi team,
I have a doubt in explain plan. We have found some SQL in our database that use NEST LOOPS to build relationship between tables, but did not performace well. So I have to use the 'use_hash' hint to force the optimizer to select USE_HASH as the way of join tables.

as we all know, it is a great idea that OPTIMIZER_INDEX_COST_ADJ tunes optimizer behavior for access path selection to be more or less index friendly, make the optimizer more or less prone to selecting an index access path over a full table scan. Without oracle hint, is there any way/parameter to make the optimizer more or less prone to selecting a USE_HASH to join tables over a NETS_LOOP?

and Connor said...

OPTIMIZER_INDEX_COST_ADJ is not a great idea... it's a bad idea :-)

If you want to manipulate things in this way, you are probably better off adjusting (or correcting) your system statistics.

SREADTIM
MREADTIM
MBRC

will influence the full scan versus index scan decisions of the optimizer, and hence indirectly influence hash joins over nested loop joins.

Also, check your 'optimizer_mode' at session and system level. If its been altered to any of the 'first_rows' variants, then nested loops will obviously be considered more attractive.

Hope this helps.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Joe Huang, March 31, 2016 - 5:05 am UTC

Thanks Connor,
OK, let go of OPTIMIZER_INDEX_COST_ADJ.
As you said, I've check our system, and both of the 'optimizer_mode' at session and system level is set to 'ALL_ROWS'. Is there's any other way to let the optimizer to take USE_HASH into consideration more?
Connor McDonald
March 31, 2016 - 6:35 am UTC

Not *directly* to my knowledge. You'd need to tinker with SREADTIM, MREADTIM , MBRC to make full scans more attractive, which will most probably make nested loops less attractive.

But I'll ask around

Plan Baslines

Narendra, March 31, 2016 - 11:53 am UTC

Hello Joe,

Is there any reason you want to achieve this by tweaking optimizer only? If this is for a sql, using SQL Plan Baseline or SQL Profile is one way to make the query use the desired plan.
Connor McDonald
March 31, 2016 - 1:27 pm UTC

Thanks for your input.

I *think* Joe wants a bias toward hash joins over nested loops, across the whole database...but for particular queries, then baselines and/or profiles would be a good match.

Disabling Nested Loop across whole Database

Narendra, March 31, 2016 - 3:30 pm UTC

Hello Conor,

I might be wrong but I can't imagine why would anyone want to disable nested loop at a database level. That would almost certainly have negative side-effects, not just for the application tables but for data dictionary access too. I believe nested loop join is the most fundamental/basic join method in Oracle (or probably in any RDBMS) and don't think there is a way to disable it

Joe Huang, April 05, 2016 - 7:48 am UTC

Connor and Narendra, thanks for your helps!
I don't mean to disable nested loops, I just want a bias toward hash joins over nested loops at database level. However, we did not find a way to archieve this yet. As you guys said, the *SQL Profiles* could solve my problem. It really works, and maybe it is much more safty in some case.
Thanks again.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.