Hi All
I am not a DBA, but a unix administrator.
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
Server info:
HP Proliant ML350G3
1024MB Ram
1 x Intel Xeon 2.8GHz processor
Unixware 7.1.1
The DBA's have run a report which indicates an I/O problem.
Total Wait
wait Waits
Event Waits Timeouts Time (cs)
(ms) /txn
---------------------------- ------------ ---------- -----------
------ ------
log file sync 90,709 0 123,003
14 1.0
log file parallel write 90,719 0 121,555
13 1.0
Our redo logs are on a mirror.
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0
but no luck .....
The DBA's tried the upgrade 2 different ways
1. upgrade original database from 8.0.5 to 8.1.7.4
2. built brand new database from scratch then importing the data.
Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on
8.0.5, and how we can fix it.
thankyou
regards
Ruth 14 7956
> The DBA's tried the upgrade 2 different ways 1. upgrade original database from 8.0.5 to 8.1.7.4 2. built brand new database from scratch then importing the data.
Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on 8.0.5, and how we can fix it.
The first thing I would do is make sure that your statistics are up to
date on your tables... If you used step 1 above this should be the
case, but confirm this anyway.
Query user_tables for last_analzed and num_rows...
If these are up to date you need to run the process under 8.0.5 with
sql_trace enabled and then compare this with the same for 8.1.7.
Use tkprof to format the trace files and sort by
"prsela,fchela, exeela" to identify the heaviest SQL and determine the
difference between the 2 elapsed times.
If you get vastly different values for 'cpu time' and 'elapsed time'
then you may need to enable a 10046 (level 8) trace to identify the
wait events.
Good luck..
Matt
I would update the statistics on the DB and then go from there.
Mike rr****@goldcoas t.qld.gov.au (Ruth) wrote in message news:<7d******* *************** ***@posting.goo gle.com>... Hi All
I am not a DBA, but a unix administrator.
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our test server, we have noticed a big slow down in our application (acocobol). From ~10mins to ~45mins.
Server info: HP Proliant ML350G3 1024MB Ram 1 x Intel Xeon 2.8GHz processor Unixware 7.1.1
The DBA's have run a report which indicates an I/O problem.
Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ log file sync 90,709 0 123,003 14 1.0 log file parallel write 90,719 0 121,555 13 1.0 Our redo logs are on a mirror.
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0 but no luck .....
The DBA's tried the upgrade 2 different ways 1. upgrade original database from 8.0.5 to 8.1.7.4 2. built brand new database from scratch then importing the data.
Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on 8.0.5, and how we can fix it.
thankyou
regards Ruth
How about running some sql traces on some of the long running
processes? How about running statistics? The optimizer in 8.1.7
could be choosing a different plan than the 8.0.5 optimizer.
Pete's rr****@goldcoas t.qld.gov.au (Ruth) wrote in
news:7d******** *************** **@posting.goog le.com: Hi All
I am not a DBA, but a unix administrator.
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our test server, we have noticed a big slow down in our application (acocobol). From ~10mins to ~45mins.
Server info: HP Proliant ML350G3 1024MB Ram 1 x Intel Xeon 2.8GHz processor Unixware 7.1.1
The DBA's have run a report which indicates an I/O problem.
Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ log file sync 90,709 0 123,003 14 1.0 log file parallel write 90,719 0 121,555 13 1.0 Our redo logs are on a mirror.
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0 but no luck .....
The DBA's tried the upgrade 2 different ways 1. upgrade original database from 8.0.5 to 8.1.7.4 2. built brand new database from scratch then importing the data.
Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on 8.0.5, and how we can fix it.
thankyou
regards Ruth
You need more than the current wait events to diagnose the problem
accurately. Do you have the same report from prior to the upgrade? If you
are taking snapshots regularly you should. You need to see what changed
Other than that, the other suggestions are probably valid. Regather
statistics and see if that helps.
--
Chuck
Remove "_nospam" to reply by email rr****@goldcoas t.qld.gov.au (Ruth) wrote in message news:<7d******* *************** ***@posting.goo gle.com>... Hi All
I am not a DBA, but a unix administrator.
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our test server, we have noticed a big slow down in our application (acocobol). From ~10mins to ~45mins.
Server info: HP Proliant ML350G3 1024MB Ram 1 x Intel Xeon 2.8GHz processor Unixware 7.1.1
The DBA's have run a report which indicates an I/O problem.
Total Wait wait Waits Event Waits Timeouts Time (cs) (ms) /txn ---------------------------- ------------ ---------- ----------- ------ ------ log file sync 90,709 0 123,003 14 1.0 log file parallel write 90,719 0 121,555 13 1.0 Our redo logs are on a mirror.
We also have tried setting OPTIMIZER_FEATU RES_ENABLE=8.0. 0 but no luck .....
The DBA's tried the upgrade 2 different ways 1. upgrade original database from 8.0.5 to 8.1.7.4 2. built brand new database from scratch then importing the data.
Both ways have the same performance problem.
Does anyone have any idea why this is occurring on 8.1.7.4 and not on 8.0.5, and how we can fix it.
thankyou
regards Ruth
Ruth, based on experience I know that anytime you perform a major
Oracle RDBMS upgrade that a query or two may go to lunch. Find the
SQL that is not performing well and tune it.
The first step is updating the statistics and then compare job run
times to their pre-update job run times. Tune those that differ to
the worse.
HTH -- Mark D Powell --
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_numbe r, ae_animal_type, ae_t
ariff, ae_breed, ae_second_breed , ae_sex, ae_description, ae_nam
e, ae_balance, ae_other_id_typ e, ae_other_id, ae_other_date, ae_
microchip_id, ae_microchip_da te, ae_exempt, ae_exempt_statu s, ae
_nusiance, ae_nusiance_dat e, ae_nusiance_sta tus, 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_numbe r, ae_animal_type, ae_tariff, ae
_breed, ae_second_breed , ae_sex, ae_description, ae_name, ae_bal
ance, ae_other_id_typ e, ae_other_id, ae_other_date, ae_microchip
_id, ae_microchip_da te, ae_exempt, ae_exempt_statu s, 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_numbe r, ae_animal_type, ae_tariff, ae
_breed, ae_second_breed , ae_sex, ae_description, ae_name, ae_bal
ance, ae_other_id_typ e, ae_other_id, ae_other_date, ae_microchip
_id, ae_microchip_da te, ae_exempt, ae_exempt_statu s, 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_conc ession, 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_NUMBE R > :w0 )
ORDER BY AA_APPLIC_NUMBE R ASC
Jacek
Did you analyze before or after the post?If it were after the post
has there been any improvements?
Michael
> I've used oracle PERFSTAT tool to trace the job.
There are better ways to do a trace. The other posts referred to
this. Top 5 Wait Events Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- PX Deq: Execution Msg 1,378 272,554 52.29
This seems wierd to me. I thought your post said you have a single
processor. This looks like you are using parallel query. Maybe you
should get rid of your parallel query stuff.
log file sync 90,709 123,003 23.60 log file parallel write 90,719 121,555 23.32
These waits relate to writing updates. I've seen these waits be high
when I do a lot of commits. I.e. drop a bunch of empty tables.
Optimizing your queries wont help this.
Buffer Gets Executions Gets per Exec % Total Hash Value --------------- ------------ -------------- ------- ------------ 988,666 201,821 4.9 42.4 3375688107 SELECT ae_application_ no, ae_entity_numbe r, ae_animal_type, ae_t ariff, ae_breed, ae_second_breed , ae_sex, ae_description, ae_nam e, ae_balance, ae_other_id_typ e, ae_other_id, ae_other_date, ae_ microchip_id, ae_microchip_da te, ae_exempt, ae_exempt_statu s, ae _nusiance, ae_nusiance_dat e, ae_nusiance_sta tus, 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_numbe r, ae_animal_type, ae_tariff, ae _breed, ae_second_breed , ae_sex, ae_description, ae_name, ae_bal ance, ae_other_id_typ e, ae_other_id, ae_other_date, ae_microchip _id, ae_microchip_da te, ae_exempt, ae_exempt_statu s, 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_numbe r, ae_animal_type, ae_tariff, ae _breed, ae_second_breed , ae_sex, ae_description, ae_name, ae_bal ance, ae_other_id_typ e, ae_other_id, ae_other_date, ae_microchip _id, ae_microchip_da te, ae_exempt, ae_exempt_statu s, 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_conc ession, 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_NUMBE R > :w0 ) ORDER BY AA_APPLIC_NUMBE R ASC
These queries seem fine to me. I think you have something else going
on that you aren't catching in your statspack report. Try doing an
extended sql trace as others have suggested and use TKPROF to see
which SQL statements are causing the PX and log file waits.
Check out http://www.oracle.com/technology/ora...tech_perf.html
- Bobby This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Greg Lindstrom |
last post by:
Greetings-
I am running Python 2.3.3 on HP-UX and would like to access an Oracle 9i
database. Looking through the vaults, I see 4 or 5 modules that might work
and was wondering if anyone is currently hitting Oracle and what are you
using. I get to build a new system from the ground up and the powers that
be are allowing me to do it in Python. I'm quite happy.
Thanks for your help,
|
by: cpchan |
last post by:
If I want to upgrade the following system :
(1) Oracle 8i RDBMS
(2) Oracle Finance 11.5.2
to :
(1) Oracle 9i RDBMS
(2) Oracle Finance 11.5.8
|
by: cpchan |
last post by:
According the documentation from Metalink, the following files are required
for the Oracle Upgrade from 8.1.6 to 8.1.7 :
u0801060.sql
c0801060.sql
a0801060.sql
i0801060.sql
utlip.sql
However, after install 8.1.7, I could only find 4 of them, but no
|
by: susmita_ganguly |
last post by:
Hi
I am trying to upgrade from oracle 8i to oracle 9i on the same server
..I don't know much abt migration .
Can anyone help me out.
Thanks.
Susmita
|
by: David Gray |
last post by:
Hello Gurus,
SQL Server 2000
Windows 2003 Server, Standard Edition.
Firstly I'm not a SQL server DBA but have a little experience with
Oracle 9i and Oracle Rdb.
An application that I've inherited has started performing very very
slowly over the last few days, as far as I know there have been no
| |
by: Bill Thorne |
last post by:
We have a COM object that has been wrappered for use in .NET and which can
make calls which can take a while to execute. These are mainframe
integration calls that might perform a lot of data entry and gathering,
returning the results to the ASP.NET caller.
I have tried an AsyncPage class (implements IHttpAsyncHandler and uses
custom thread pool class from DevelopMentor.ThreadPool
-http://staff.develop.com/woodring); I have tried the...
|
by: Aykut Canturk |
last post by:
Dear friends,
I recently decided to move vb.net 2005 from vb6. my projects has average of
200 forms and 20 modules. I mostly write enterprise solution about
production automation like applications.
In VB6 it takes 15 to 20 second to open a project and almost less than 2
second to see the form after I click to project explorer. Compiling to EXE
file takes rather long time, about 45 seconds to 1 minute.
But in VB.net, to open a project...
|
by: yoram.ayalon |
last post by:
we are deploying a ASP.NET 2.0 web application (written in vb.net) to
a windows Server 2003 web edition in which .NET framework 2.0 has been
instaled. the application connects to an Oracle 9i database, and uses
system.date.oracleclient
our development machine windows runs XP, has VS 2005 installed.
our local testing machine runs windows 2003 enterprise edition, has VS
2005 installed.
|
by: Ruth |
last post by:
Hi All
I am not a DBA, but a unix administrator.
After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our
test server, we have noticed a big slow down in our application
(acocobol). From ~10mins to ~45mins.
Server info:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |