Re: Poor performance after oracle 8i upgrade

Check Metalink for this. There are articles about this issue after an upgrade.

"Oxnard" <shankeyp_at_no-spam.comcast.net> wrote in message news:P6ydnQ0sx86nGNTcRVn-uQ_at_comcast.com...
> Had any luck yet?
>
> "Ruth" <rreeve_at_goldcoast.qld.gov.au> wrote in message
> news:7d088242.0409071533.7aecc8f0_at_posting.google.com...
> > 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
>
>
Received on Thu Sep 16 2004 - 16:20:54 CEST