Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Poor performance after oracle 8i upgrade

Re: Poor performance after oracle 8i upgrade

From: Ruth <rreeve_at_goldcoast.qld.gov.au>
Date: 7 Sep 2004 16:33:04 -0700
Message-ID: <7d088242.0409071533.7aecc8f0@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 Tue Sep 07 2004 - 18:33:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US