Re: Poor performance after oracle 8i upgrade

> I've used oracle PERFSTAT tool to trace the job.

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

> Top 5 Wait Events
>  Wait % Total
> Event Waits Time (cs) Wt Time
> -------------------------------------------- ------------ ------------
> -------
> PX Deq: Execution Msg 1,378 272,554 52.29

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.

> log file sync 90,709 123,003 23.60
> log file parallel write 90,719 121,555 23.32

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.

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

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/oramag/oracle/04-jan/o14tech_perf.html

• Bobby