Re: Performance Degradation after migrating to Oracle v 7.3.4.3

I cannot remember exactly but I suppose the 7.1.x default optimizer_mode was rule.

So insert a line

optimizer_mode = rule

in your init.ora and bounce your instance. You should get the same behaviour again.

Martin

"Guruju,Narendra" wrote:
>
> Hello all,
> We recently upgraded to oracle 7.3.4.3 from 7.1.3. This is a
> OLTP database. A query which used to take 0.2 seconds on 7.1 is taking
> 80 seconds on 7.3. The optimizer hint is set to RULE on both databases
> and the explain plan shows the execution plan to be same on both 7.1 and
> 7.3.4.3. The only change between the two databases is, in 7.3.4.3, we
> increased the shared pool size to twice that of in 7.1. The tkprof on
> 7.3.4.3 query shows an increase in fetch cycles
> SELECT MAX(C.CODE_CONFID_LEVEL_CD)INTO:b1:i1 FROM ROLE_CODE
> C,PAT_PROV_RELTN R WHERE R.PAT_ID=:b2 AND R.PROV_ID=:b3 AND
> R.PPR_ROLE_CD=C.CODE_VALUE AND R.PPR_STATUS_CD='AAA' AND(R.PPR_END_DT_TM
> IS NULL OR R.PPR_END_DT_TM>SYSDATE)
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0 0
> Execute 388 0.06 0.00 0 0 0 75465
> Fetch 388 0.95 77.34 5017 12727 0 388
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 777 1.01 77.34 5017 12727 0 75853
> Misses in library cache during parse: 0
> Optimizer goal: RULE
> Parsing user id: 15
>
> Are there any parameters that need to be modified to give any
> performance boost after the upgrade? Are there any changes that need to
> be made at VMS operating system level?
> RDBMS Version: 7.3.4.3
> Operating System and Version: Alpha VMS 7.1
> Thanks and Regards
> Narendra
Received on Mon Feb 21 2000 - 05:09:38 CST