Poor performance after oracle 8i upgrade

[Ruth]

Hi All

I am not a DBA, but a unix administrator.

After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.


Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1


The DBA's have run a report which indicates an I/O problem.

Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0



Our redo logs are on a mirror.


We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0
but no luck .....


The DBA's tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.

Both ways have the same performance problem.


Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.

thankyou

regards
Ruth

[Matt]

Re: Poor performance after oracle 8i upgrade

The DBA's tried the upgrade 2 different ways

1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.
>
Both ways have the same performance problem.
>
>
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.
>

The first thing I would do is make sure that your statistics are up to
date on your tables... If you used step 1 above this should be the
case, but confirm this anyway.

Query user_tables for last_analzed and num_rows...

If these are up to date you need to run the process under 8.0.5 with
sql_trace enabled and then compare this with the same for 8.1.7.

Use tkprof to format the trace files and sort by
"prsela,fchela, exeela" to identify the heaviest SQL and determine the
difference between the 2 elapsed times.

If you get vastly different values for 'cpu time' and 'elapsed time'
then you may need to enable a 10046 (level 8) trace to identify the
wait events.

Good luck..

Matt

[Tilwen]

Re: Poor performance after oracle 8i upgrade

I would update the statistics on the DB and then go from there.

Mike

[Pete's]

Re: Poor performance after oracle 8i upgrade

rreeve@goldcoas t.qld.gov.au (Ruth) wrote in message news:<7d088242. 0409062036.c87f 0e1@posting.goo gle.com>...

Hi All
>
I am not a DBA, but a unix administrator.
>
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
>
>
Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1
>
>
The DBA's have run a report which indicates an I/O problem.
>
Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0
>
>
>
Our redo logs are on a mirror.
>
>
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0
but no luck .....
>
>
The DBA's tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.
>
Both ways have the same performance problem.
>
>
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.
>
thankyou
>
regards
Ruth

How about running some sql traces on some of the long running
processes? How about running statistics? The optimizer in 8.1.7
could be choosing a different plan than the 8.0.5 optimizer.

Pete's

[Chuck]

Re: Poor performance after oracle 8i upgrade

rreeve@goldcoas t.qld.gov.au (Ruth) wrote in
news:7d088242.0 409062036.c87f0 e1@posting.goog le.com:

Hi All
>
I am not a DBA, but a unix administrator.
>
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
>
>
Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1
>
>
The DBA's have run a report which indicates an I/O problem.
>
Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0
>
>
>
Our redo logs are on a mirror.
>
>
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0
but no luck .....
>
>
The DBA's tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.
>
Both ways have the same performance problem.
>
>
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.
>
thankyou
>
regards
Ruth

You need more than the current wait events to diagnose the problem
accurately. Do you have the same report from prior to the upgrade? If you
are taking snapshots regularly you should. You need to see what changed

Other than that, the other suggestions are probably valid. Regather
statistics and see if that helps.

--
Chuck
Remove "_nospam" to reply by email

[Mark D Powell]

Re: Poor performance after oracle 8i upgrade

rreeve@goldcoas t.qld.gov.au (Ruth) wrote in message news:<7d088242. 0409062036.c87f 0e1@posting.goo gle.com>...

Hi All
>
I am not a DBA, but a unix administrator.
>
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
>
>
Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1
>
>
The DBA's have run a report which indicates an I/O problem.
>
Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0
>
>
>
Our redo logs are on a mirror.
>
>
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0
but no luck .....
>
>
The DBA's tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.
>
Both ways have the same performance problem.
>
>
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.
>
thankyou
>
regards
Ruth

Ruth, based on experience I know that anytime you perform a major
Oracle RDBMS upgrade that a query or two may go to lunch. Find the
SQL that is not performing well and tune it.

The first step is updating the statistics and then compare job run
times to their pre-update job run times. Tune those that differ to
the worse.

HTH -- Mark D Powell --

[michael ngong]

Re: Poor performance after oracle 8i upgrade

Did you analyze before or after the post?If it were after the post
has there been any improvements?


Michael

[Joel Garry]

Re: Poor performance after oracle 8i upgrade

rreeve@goldcoas t.qld.gov.au (Ruth) wrote in message news:<7d088242. 0409071533.7aec c8f0@posting.go ogle.com>...

Hi all,
>
Thanks for your responses. I forward them on to the DBA's and this is
their reply :)

Are they trying to convince you not to put log files on RAID-5?

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=34592.1&p_database_id=NOT

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#16481

A wild guess is the PX has something to do with RAC mumbling to
itself? Or are you using logical standby?

How come the DBA's are having the sysadmin deal with this? What else
have they told you?

jg
--
@home.com is bogus.
The franchise that wouldn't die:

http://www.nytimes.com/2004/08/31/arts/television/31trek.html?ei=5070&en=a6aaa1675a64719f&ex=1094788800&pagewanted=all&position=

[Ruth]

Re: Poor performance after oracle 8i upgrade

"Oxnard" <shankeyp@no-spam.comcast.ne twrote in message news:<P6ydnQ0sx 86nGNTcRVn-uQ@comcast.com> ...

Had any luck yet?
>

Welll....we have been in contact with the Application vendors again
and it now may be that the version of the application layer doesnt
support the upgraded version of oracle, which were not advised of
until AFTER we had problems post upgrade.


I will let you know when the vendor upgrades their end and lets us
know for sure.

Thanks for all your help everyone

kind regards
Ruth

[Oxnard]

Re: Poor performance after oracle 8i upgrade

I have been down that road a few times. Others in this thread have hit upon
what I would be looking for, which is the way Oracle's Optimizer/Engine is
approaching some of the SQL or PL/SQL. The result is the I/O problem. Do you
have access to the source code?

"Ruth" <rreeve@goldcoa st.qld.gov.auwr ote in message
news:7d088242.0 409162208.44778 e05@posting.goo gle.com...

"Oxnard" <shankeyp@no-spam.comcast.ne twrote in message

news:<P6ydnQ0sx 86nGNTcRVn-uQ@comcast.com> ...

Had any luck yet?

>
Welll....we have been in contact with the Application vendors again
and it now may be that the version of the application layer doesnt
support the upgraded version of oracle, which were not advised of
until AFTER we had problems post upgrade.
>
>
I will let you know when the vendor upgrades their end and lets us
know for sure.
>
Thanks for all your help everyone
>
kind regards
Ruth