| forcing hash join [message #57286] |
Mon, 02 June 2003 13:44  |
Ed Stevens
Messages: 1
Registered: June 2003
|
Junior Member |
|
|
Friday we added some application functions - new screens, reports, etc. , a few new tables - on an existing database. Friday 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 checked 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 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.
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.
Any ideas?
|
|
|
|
|
|
| Re: forcing hash join [message #57295 is a reply to message #57286] |
Tue, 03 June 2003 04:29  |
Kishor Bhalwankar
Messages: 33
Registered: February 2003
|
Member |
|
|
Hi
check out the last_analyzed of tables which are in
sql statement on both the databases. and try to
analyze it again.
i think this will help you
any comments/queries please welcome
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
