Probably the most common performance adjust needed for Windchill systems is the adding of more memory for Oracle. In memory access of Oracle blocks can be a 100 000x faster (fig #3) than retrieving from disk making the ensuring that Oracle is properly configured critical the overall health of a Windchill system. Having 'enough' memory will give the system the opportunity to generally perform well at the database level, although not all SQL statements will run quickly enough even when there is enough memory available.
How do you know if Oracle has been allocated enough memory for acceptable performance or your system is in need of tuning? The SQL below can be run from SQL*plus as the Oracle 'sys' or 'system' user and will output recommendations based on rule of thumb guidelines. If you are in doubt or have questions, opening a technical support case with the Windchill performance group and including either an AWR report, the output from gather_info_script, or System Configuration Collector (Windchill R10+) is the next step.
select name, block_size, ESTD_PCT_OF_DB_TIME_FOR_READS,
case
when (ESTD_PCT_OF_DB_TIME_FOR_READS >0 and ESTD_PCT_OF_DB_TIME_FOR_READS <10) then 'system is well configured'
when (ESTD_PCT_OF_DB_TIME_FOR_READS >10 and ESTD_PCT_OF_DB_TIME_FOR_READS <20) then 'system is ok but could benefit from memory and SQL tuning'
when (ESTD_PCT_OF_DB_TIME_FOR_READS >20 and ESTD_PCT_OF_DB_TIME_FOR_READS <40) then 'system is struggling, stability and performance are likely problems'
when (ESTD_PCT_OF_DB_TIME_FOR_READS >40) then 'system is under duress and immediate actions to tune the system are required'
end
from V$DB_CACHE_ADVICE where SIZE_FACTOR=1;
If the memory needs to be adjusted this will usually mean in 11g increasing memory_target and memory_max_target settings and restarting Oracle ( if memory_max_target has been increased). Adjusting adjusting Oracle 10g memory or when the sga_max_size is set to a non-default value complicates things a good explanation of these parameters can be found here.
A note of caution, whenever possible do not set the sga_target parameter to zero, but this only recommendation only applies when an sga_max_size value has been specified i.e. some value other than '0'. If sga_max_size=0, then setting sga_target=0 is also appropriate. When Oracle sees a sga_target of zero (regardless of the memory_target setting) it appears to try and shrink the SGA to be as small as possible, flushing SGA blocks from memory more aggressively than is desirable and harming performance. It is not clear if this same behavior occurs when memory_target is set to zero also, but it probably does.
