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 - 07:25:00 CDT
