Re: Temp usage on Linux vs solaris
Date: Mon, 20 Jun 2011 22:35:44 -0700
Message-ID: <BANLkTinNs_yCDAEaYmX_GAWQ+Rmw86u2HQ_at_mail.gmail.com>
Hi Greg
One change is in the way stats are collected. This is Oracle Apps 12.1.2 and we use fnd_stats.gather_schema_stats.
In solaris it was,
SCHEMA_NAME => ALL
Estimate Percent => 10
Degree => Null
Backup Flag => NoBackup
Restart Request ID => Null
History Mode => Last run
Gather Options => Gather
Modifications Threshold => Null
Invalidate Dependent Cursors => Y
in Linux it is
CHEMA_NAME => ALL
Estimate Percent => 10
Degree => Null
Backup Flag => NoBackup
Restart Request ID => Null
History Mode => Last run
Gather Options => Gather Auto
Modifications Threshold => 10
Invalidate Dependent Cursors => Y
But my understanding was this is better because it only collects stats for any table if more than 10% of the data changes and this reduced stats gathering from 9 hrs to 30 minutes.
To minimize issues with version differences we maintained the version of rdbms s (11.1.0.7) between solaris and linux and another change to init.ora was not to use memory_target and memory_max-target (that should not matter I hope in this particular case thoug h. We are using sga_target and sga_max_size )
Thank you
Kumar
On Mon, Jun20, 2011 at 5:31 PM, Greg Rahn <greg_at_structureddata.org> wrote
> The temp usage changing is more certainly from plans changing - nothing to
> do with the OS directly, so it would make more sense to focus on why that is
> -- stats, db version, parameters, etc.
>
>
> On Mon, Jun 20, 2011 at 4:17 PM, David Fitzjarrell <oratune_at_yahoo.com>wrote:
>
>> Are your SGA/memory settings the same on Linux as they were on Solaris?
>> To what values are sort_area_size and hash_area_size set? Have you
>> monitored who's using the TEMP space, how much they're using and what
>> statements are the largest offenders? Is the TEMP tablespace sized the same
>> on Linux as it was on Solaris? Have you traced sessions with strace?
>>
>> *From:* Kumar Madduri <ksmadduri_at_gmail.com>
>> *To:* oracle Freelists <Oracle-L_at_freelists.org>
>> *Sent:* Monday, June 20, 2011 2:26 PM
>> *Subject:* Temp usage on Linux vs solaris
>>
>> Hi
>> We recently migrated our ebiz solaris db to linux. We have some bad
>> queries in solaris which take up temp segment (I mean mor than what it
>> should take if the query was properly written in the first place).
>> Since we migrated to LInux, the queries are as bad as ever but we see more
>> ORA-1652 errors now than we were on Solaris.
>> We are trying to identify the sql_id and report that to developers. But
>> have any of you noticed increased temp space usage after migration to linux
>> from solaris.
>>
>> Thank you
>> Kumar
>>
>>
>>
>
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 21 2011 - 00:35:44 CDT