Performance Degradation after migrating to Oracle v 7.3.4.3

From: Guruju,Narendra <NGURUJU_at_cerner.com>
Date: Fri, 18 Feb 2000 13:26:22 -0600
Message-ID: <D15ED542E12BD3119FFE00805F6551F0078130B2@MAILWHQNEWS>

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 Fri Feb 18 2000 - 13:26:22 CST