Re: Poor performance after oracle 8i upgrade
Date: 7 Sep 2004 16:33:04 -0700
Message-ID: <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