 | |
Poor performance after oracle 8i upgrade
comp.databases.oracle comp.databases.oracle
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Poor performance after oracle 8i upgrade -
09-09-2004
, 09:24 AM
|
Hi all, Thanks for your responses. I forward them on to the DBA's and this is their reply  regards Ruth 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 |
ignore SQL hints. This option is in Oracle 9i."
If the DBA's have somehow disabled the use of hints then since the SQL
is hinted the CBO is not running the SQL as the hints dictate. That
would be a definite possible cause of the problem.
HTH -- Mark D Powell --
#12
| |||
| |||
|
|
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 |
#13
| |||
| |||
|
|
Had any luck yet? "Ruth" <rreeve (AT) goldcoast (DOT) qld.gov.au> wrote in message news:7d088242.0409071533.7aecc8f0 (AT) posting (DOT) 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 |
#14
| |||
| |||
|
|
Had any luck yet? |
and it now may be that the version of the application layer doesnt
support the upgraded version of oracle, which were not advised of
until AFTER we had problems post upgrade.
I will let you know when the vendor upgrades their end and lets us
know for sure.
Thanks for all your help everyone
kind regards
Ruth
#15
| |||
| |||
|
|
"Oxnard" <shankeyp (AT) no-spam (DOT) comcast.net> wrote Had any luck yet? Welll....we have been in contact with the Application vendors again and it now may be that the version of the application layer doesnt support the upgraded version of oracle, which were not advised of until AFTER we had problems post upgrade. I will let you know when the vendor upgrades their end and lets us know for sure. Thanks for all your help everyone kind regards Ruth |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.