Re: Performance Problem: Oracle 7.1.4 to Oracle 7.2.3
Date: 1996/10/04
Message-ID: <5336u5$cdk_at_hil-news-svc-3.compuserve.com>#1/1
rick_at_rio.myra.com (Rick Tessner) wrote:
>We've just upgraded from Oracle 7.1.4 to Oracle 7.2.3 and have experienced
>general performance degradation ever since. The degradation is somewhat
>inconsistent across applications. For some, there is a 20% slowdown while in
>others there is a slowdown by a factor of 2.5 (we suspect ones involving full table scans).
>Oracle patches for bugs 385066, 342755, and 333541 have been applied.
>We've checked the dictionary and library cache hit ratios and everything looks
>great (> 90% in most cases). We've also executed compared EXPLAIN results for
>one long running query, executed against Oracle 7.1.4 and 7.2.3, and both look
>the comparable.
>We initially didn't make any changes to the startup parms in the init.ora but
>have tweaked some parameters since the upgrade, with no apparent effect.
>The applications going against the database are primarily PeopleSoft 4.01 for
>online and MicroFocus Cobol 3.2 and SQR for batch. The online applications
>connect via SQL*Net V2.2.3. We're also running SNS V2. No MTS.
>Alerts have been opened with Oracle Tech Support and PeopleSoft Tech Support.
>One question that we have not been able to get an acceptable answer to is with
>regards to relinking any application executables so that they take advantage
>of any new libraries available with Oracle 7.2.3. We actually went ahead and
>relinked some SQRs with the 7.2.3 libraries, and unfortunately saw even worse
>performance.
>Additional Information: We're running with a 110 MB SGA, no apparent
>contention or locking issues, adequate memory on system. Info from system
>stats look okay.
>Any advice and recommendations would be appreciated.
>Please respond, if possible, by email to tchan_at_psercsmtp.pser.gov.bc.ca
>Thanks in advance.
Something to consider:
Assuming the ONLY thing has changed is the version (ie. CPU/memory use
the same, database file placement is the same, NO dramatic increase in
data size, chaining/migration the same, etc.):
EXPLAIN a fewl more long and medium queries for a good cross check.
LOOK at the OPTIMIZER_MODE parameter (RULE/CHOOSE) in init.ora -- the
default is CHOOSE.
"Rumor" says: don't use use the cost-based optimizer(choose) til 7.3.
If you ARE using the cost optimizer, statistics must reflect current
data volumes in the database via the ANALYZE command.
If the statistics don't match, CHOOSE may get confused and
if no statistics are found, RULE based is used.
Hope this helps...as usual, there's always one more thing to check. Received on Fri Oct 04 1996 - 00:00:00 CEST