How to force hash and merge joins

ubject: How to force hash and merge joins

Developers put a new system into production yesterday. (By new system, I mean additional application functions - new screens, reports, etc. , a few new tables - on an existing database). Last night I got a call from the lead developer that the reports were running so long the web front end was timing out. He had isolated the queries and run them in SQLPlus and found they were running 2 to 5 minutes in production and 2 seconds or less in test.

Platforms: Test is running 8.1.7 SE on Win2k. Production is running 8.0.5 SE on NT. Yes, I know we've got apples and oranges, but read on. I don't think that is the key.

We loaded the production data into the test db and ran stats to ensure same data volumes and stats. Then we ran explain plans on both systems. On production (the poor performer) it yielded a full table scan within a 3-deep nested loop. On the test system, it yielded a full table scan within a hash join and a Cartesian merge join.

At this point we check v$parameter for any differences that might impact the plan. We found that on the test (fast) system HASH_AREA_SIZE was 1638400, while on the prod system (slow) it was zero. AHAH! We said. Logged on to the prod system, altered session to set HASH_AREA_SIZE=1638400, and ran the explain plan. No joy . . . still yielding a 3-deep nested loop. I have also compared index structures between both systems and found them to be identical.

I am not posting the SQL or explain plans because (they are pretty involved and tend to line wrap so badly (when posted) as to make them very difficult to read. Also, I really think I'm asking a broader question that goes beyond the specific query - how to influence a plan toward hash and merge joins instead of nested loops.

While there may be things that could be done with the query itself, the fact that we are able to get a fast performing hash join on the one system leads me to think that there's something I can do as a DBA to force it on the other system.

I'm going home to study my Kyte and Harrison books, but wanted to get this out in hopes of eliciting some response in time to start cracking on it first thing Monday morning. Received on Sat May 31 2003 - 17:13:54 CDT