Re: Improving Performance of Hash Joins
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 26 Dec 2008 11:57:56 +0100
Message-ID: <6rjrlmF20olaU2@mid.individual.net>
>> I am on Oracle 10.2.0.3 on Windows 2003 32 bit. I have /3GB switch set
>> on and have a value of sga_target set to 2.x GB,
>>
>> I am using a third party package and many queries are doing hash
>> joins. I cannot modify any queries because of no source code available
>> to me. I know in Oracle9i, there was some init.ora parameter for
>> specifying memory used by hash joins which has been taken away in 10G.
>> What are my options (if any) to improve performance of this hash joins
>> which for some queries take 2-3 minutes.
Date: Fri, 26 Dec 2008 11:57:56 +0100
Message-ID: <6rjrlmF20olaU2@mid.individual.net>
On 25.12.2008 23:06, Mark D Powell wrote:
> On Dec 25, 3:04 pm, zigzag..._at_yahoo.com wrote:
>> I am on Oracle 10.2.0.3 on Windows 2003 32 bit. I have /3GB switch set
>> on and have a value of sga_target set to 2.x GB,
>>
>> I am using a third party package and many queries are doing hash
>> joins. I cannot modify any queries because of no source code available
>> to me. I know in Oracle9i, there was some init.ora parameter for
>> specifying memory used by hash joins which has been taken away in 10G.
>> What are my options (if any) to improve performance of this hash joins
>> which for some queries take 2-3 minutes.
Are schema and system statistics up to date? Is it a bad plan or is the plan good and it just takes too long?
> By the way on a 9.2 system you can potentially use the OUTLINE feature > to provide hints to the vendor SQL without ever having to change the > source.
If I understood him correct he is on 10g now - which should still have stored outlines AFAIK.
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without endReceived on Fri Dec 26 2008 - 04:57:56 CST