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_FEATURES_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

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

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

Mike

rreeve (AT) goldcoast (DOT) qld.gov.au (Ruth) wrote in message news:<7d088242.0409062036.c87f0e1 (AT) posting (DOT) google.com>...
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

rreeve (AT) goldcoast (DOT) qld.gov.au (Ruth) wrote in
news:7d088242.0409062036.c87f0e1 (AT) posting (DOT) google.com:

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

rreeve (AT) goldcoast (DOT) qld.gov.au (Ruth) wrote in message news:<7d088242.0409062036.c87f0e1 (AT) posting (DOT) google.com>...
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 --

Hi all,

Thanks for your responses. I forward them on to the DBA's and this is
their reply

regards
Ruth

I've analyzed and validated all user oracle objects (tables, indexes)
with compute option.
The number of user objects (indexes and tables) before and after
migration is the same.
All Database object are VALID and number of extents are very small.
All user objects are in Local Manage Tablespaces.
I've used oracle PERFSTAT tool to trace the job.
This job is executing SQLs with hints and bind variables which will
not use Oracle Optimiser stats.
In Oracle 8.1.7 you can't force Oracle Optimizer to ignore SQL hints.
This option is in Oracle 9i.

There is output form report:

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.60 In-memory Sort %: 100.00
Library Hit %: 99.75 Soft Parse %: 90.56
Execute to Parse %: 99.65 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 93.62 % Non-Parse CPU: 99.70

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 10.87 12.44
% SQL with executions>1: 48.89 67.61
% Memory for SQL w/exec>1: 32.85 58.69

Top 5 Wait Events
 Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------
-------
Top 5 Wait Events
 Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------
-------
PX Deq: Execution Msg 1,378 272,554 52.29
log file sync 90,709 123,003 23.60
log file parallel write 90,719 121,555 23.32
control file parallel write 670 3,376 .65
db file sequential read 9,245 618 .12
-------------------------------------------------------------
^LWait Events for DB: LOAF Instance: LOAF Snaps: 35 -45
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
PX Deq: Execution Msg 1,378 1,334 272,554 1978 0.0
log file sync 90,709 0 123,003 14 1.0
log file parallel write 90,719
control file parallel write 670
db file sequential read 9,245
log file switch completion 3
PX qref latch 17
log file single write 6
process startup 4
SQL*Net more data to client 459
control file sequential read 94
PX Deq: Parse Reply 6
latch free 2
db file parallel write 1,257
file open 156
PX Deq: Execute Reply 18
PX Deq: Signal ACK 5
enqueue 5
Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
988,666 201,821 4.9 42.4 3375688107
SELECT ae_application_no, ae_entity_number, ae_animal_type, ae_t
ariff, ae_breed, ae_second_breed, ae_sex, ae_description, ae_nam
e, ae_balance, ae_other_id_type, ae_other_id, ae_other_date, ae_
microchip_id, ae_microchip_date, ae_exempt, ae_exempt_status, ae
_nusiance, ae_nusiance_date, ae_nusiance_status, ae_desexed, ae_

688,948 181,308 3.8 29.5 1365597534
SELECT /*+ INDEX_ASC (ANL_ENTITY iANL_ENTITY_0) Index Hint */ ae
_application_no, ae_entity_number, ae_animal_type, ae_tariff, ae
_breed, ae_second_breed, ae_sex, ae_description, ae_name, ae_bal
ance, ae_other_id_type, ae_other_id, ae_other_date, ae_microchip
_id, ae_microchip_date, ae_exempt, ae_exempt_status, ae_nusiance

272,336 90,685 3.0 11.7 1786727592
SELECT /*+ INDEX_ASC (ANL_ENTITY iANL_ENTITY_0) Index Hint */ ae
_application_no, ae_entity_number, ae_animal_type, ae_tariff, ae
_breed, ae_second_breed, ae_sex, ae_description, ae_name, ae_bal
ance, ae_other_id_type, ae_other_id, ae_other_date, ae_microchip
_id, ae_microchip_date, ae_exempt, ae_exempt_status, ae_nusiance


272,259 90,685 3.0 11.7 3296768686
SELECT /*+ INDEX_ASC (ANL_APPLIC iANL_APPLIC_0) Index Hint */ aa
_applic_number, aa_pension_concession, aa_contact_type, aa_conta
ct_entity, aa_status, aa_status_ymd, aa_creation_ymd, aa_modifie
d_ymd ,rowid FROM ANL_APPLIC WHERE ( AA_APPLIC_NUMBER > :w0 )
ORDER BY AA_APPLIC_NUMBER ASC


Jacek

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


Michael

There are better ways to do a trace. The other posts referred to
this.

This seems wierd to me. I thought your post said you have a single
processor. This looks like you are using parallel query. Maybe you
should get rid of your parallel query stuff.

These waits relate to writing updates. I've seen these waits be high
when I do a lot of commits. I.e. drop a bunch of empty tables.
Optimizing your queries wont help this.


These queries seem fine to me. I think you have something else going
on that you aren't catching in your statspack report. Try doing an
extended sql trace as others have suggested and use TKPROF to see
which SQL statements are causing the PX and log file waits.

Check out

http://www.oracle.com/technology/ora...tech_perf.html

- Bobby

rreeve (AT) goldcoast (DOT) qld.gov.au (Ruth) wrote in message news:<7d088242.0409071533.7aecc8f0 (AT) posting (DOT) google.com>...
Are they trying to convince you not to put log files on RAID-5?

http://metalink.oracle.com/metalink/...abase _id=NOT
http://download-west.oracle.com/docs...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/ar...=all&position=