Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Poor performance after oracle 8i upgrade
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
-------------------------------------------- ------------ ------------
Wait % Total
-------------------------------------------- ------------ ------------
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------------- ------
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 Tue Sep 07 2004 - 18:33:04 CDT