Re: Temp usage on Linux vs solaris

  • From: Kumar Madduri <ksmadduri@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Mon, 20 Jun 2011 22:35:44 -0700

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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx>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@xxxxxxxxx>
>> *To:* oracle Freelists <Oracle-L@xxxxxxxxxxxxx>
>> *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
>

Other related posts: