OraFAQ Forum: Performance Tuning » Oracle10g 10.0.2.0.1 Performance Problems

Home » RDBMS Server » Performance Tuning » Oracle10g 10.0.2.0.1 Performance Problems

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle10g 10.0.2.0.1 Performance Problems [message #262482]

Mon, 27 August 2007 03:29

geonet
Messages: 9
Registered: August 2007

Junior Member

Hi everyone,
I got a problem with Oracle 10g.
One of our customers decided after years with Oracle 8i to migrate to 10g. Since that day we have massive performance problems.
For example: to load the data into the database with our programs, it formerly took round about 2 hours, now it takes 18 hours.
And we have no clue why ?
The data is the same (not more or less than before).
The machine is also the same (Sun Solaris Spark.
Any ideas where we should look are appreciated.

Thanks in advance,

Frank

ps: the database is not migrated it is build using 10g.

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262486 is a reply to message #262482]

Mon, 27 August 2007 03:41

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
did you generate statpack or awr report during loading period.

please generate and analyze.

regards
Taj

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262489 is a reply to message #262482]

Mon, 27 August 2007 03:58

geonet
Messages: 9
Registered: August 2007

Junior Member

Hi Taj,
Thank you very much for your answer,
but what are statpacks / awr and how can I generate one ?

regards
Frank

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262490 is a reply to message #262489]

Mon, 27 August 2007 04:01

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
About AWR report.
http://www.oracle.com/technology/pub/articles/10gdba/week6_10gdba.html

connect any DBA role granted user and run "awrrpt.sql" script.

located : $oracle_home/rdbms/admin/awrrpt.sql

In AWR report check TOP FIVE TIMED EVENTS and if possible send to me.

regards
Taj

[Updated on: Mon, 27 August 2007 04:02]

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262492 is a reply to message #262482]

Mon, 27 August 2007 04:17

geonet
Messages: 9
Registered: August 2007

Junior Member

Hi Taj,
Ok I'll clean up everything, restart the Load procedure and generate the AWR.
This could take some time Smile

Smile

regards
Frank

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262610 is a reply to message #262492]

Mon, 27 August 2007 21:16

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Wasn't the first production release of 10g 10.1.###

If you are using 10.0, does that mean you are a beta-test site?

You should consider upgrading to the latest 10.2 patch.

Ross Leishman

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262715 is a reply to message #262482]

Tue, 28 August 2007 02:21

geonet
Messages: 9
Registered: August 2007

Junior Member

Hi again,

@rleishman
Sorry my fault, it is indeed 10.1.0.2.0 ...

@Taj
I've created the report, but I don't know if I can send it to you.
I have to talk about that with my employer first.

regards,
Frank

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262718 is a reply to message #262715]

Tue, 28 August 2007 02:22

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Yes, you should first analyze your self statspack report.
if any problem and come back to us.

Regards
Taj

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262879 is a reply to message #262482]

Tue, 28 August 2007 07:24

geonet
Messages: 9
Registered: August 2007

Junior Member

Hi Taj,

I've read and checked the output from AWR twice.
And the most significant information I see is in the "Top 5 Events" that Oracle is a little busy doing "db file scattered read".

Event Waits Time(s) Percent Total DB Time Wait Class
db file scattered read 7,713,067 3,949 22.36 User I/O
Did I understand that right that oracle makes lots of random reads ? How can we avoid them ?

And now another point figured out by my employer:
Has oracle 10g any mechanisms that keep data for a timed period, which could be the cause for such a performance drop-down?
And how can we disable them ?

It seems as if oracle is doing something else than answering on our applications queries.

regards ,
frank

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262885 is a reply to message #262879]

Tue, 28 August 2007 07:43

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

db file scattered read 7,713,067 3,949 22.36 User I/O

what is your buffer cache size,cpu,phyiscal memory(RAM)

Has oracle 10g any mechanisms that keep data for a timed period, which could be the cause for such a performance drop-down?
Oracle maintain automatically with there retention policy and i don't think there is any impact on performance.

REgards
Taj

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262896 is a reply to message #262482]

Tue, 28 August 2007 08:09

geonet
Messages: 9
Registered: August 2007

Junior Member

Ok our system consists of the following:

CPU             : 450 MHz Sparc (don't know exactly)
PHYS RAM        : 384MB
BUFFER_CACHE    : 12MB
PGA TARGET SIZE : 24MB 
SHARED POOL     : 120MB
LARGE POOL      : 8MB
SGA SIZE        : 141,49 MB (144 MB MAX)

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262900 is a reply to message #262896]

Tue, 28 August 2007 08:15

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

It is your production database ???

or increase "db_cache_size"

regards
Taj

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262921 is a reply to message #262482]

Tue, 28 August 2007 08:58

geonet
Messages: 9
Registered: August 2007

Junior Member

no its our development system

I've increased buffers to 40MB now and I'm checking if it loads faster.

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262922 is a reply to message #262921]

Tue, 28 August 2007 08:59

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Yes, check it and let us know.

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262932 is a reply to message #262482]

Tue, 28 August 2007 09:49

geonet
Messages: 9
Registered: August 2007

Junior Member

no change, the program runs as slow as since we have oracle 10g .
Any other ideas, parameters etc. which we can change ?

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #262933 is a reply to message #262932]

Tue, 28 August 2007 09:52

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,

If you have 10g then login to OEM page and analyze PERFORMANCE page.

or

all over database performance is down or only one particular query taking too much time.
if only query then first trace that query and post here "execution plan,tkprof,10046 trace events"

regards
Taj

[Updated on: Wed, 12 September 2007 00:28]

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #263345 is a reply to message #262482]

Wed, 29 August 2007 15:18

zzasteven
Messages: 18
Registered: August 2007

Junior Member

Our clients are upgrading from 9 to 10g and we are having performance problems all over the place. No code change on out part and the processes are taking hours longer to run. The strange part is different clients are reporting different processes as slowing down. I am not a fan of oracle for this reason. All of the unknowns that they put out with each version of a database and new bugs.
We where able to inject new explain plans and put hits in our code and improved speed.. You might try that..
I still cannot believe that oracle has to be so hard at doing this. All it is a file management system? It’s not rocket science! Just burns me when our team has to spend time fixing oracle databases to run like they ran in the last version.

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #263820 is a reply to message #262482]

Fri, 31 August 2007 02:10

geonet
Messages: 9
Registered: August 2007

Junior Member

Yes and thats our Problem - we can't change code.
It must be something within Oracle that it turns out to be slower than 8i.

So to redefine the problem:
Are there any parameters or whatever, so that Oracle 10g behaves like 8i did?
It doesn't need to be faster than it was - it was ok - we just want the same results as under 8i.
A performance loss like ours ( 6 to 9 times slower ) would be unacceptable for both sides our customers and our self.

BTW: EM runs so slow on our machine also, you can click on a link and wait a long time (about 5 to 10 mins) to see a result.
About execution plan, tkprof and trace events... I would use them,
but i have too little (or even none) experience with them...

regards
Frank.

Report message to a moderator

Re: Oracle10g 10.0.2.0.1 Performance Problems [message #265273 is a reply to message #263820]

Wed, 05 September 2007 23:48

mson77
Messages: 208
Registered: August 2007
Location: Brazil

Senior Member

Hello geonet,

1) As Taj pointed... did you access 10G OEM page?
Something like: http://yourserver:1158/em (port# may change)

2) Can you run the commands below and post the results?

show parameter ga;
show parameter optimizer;

3) Can you show us the hit rate (%) on the memory cache? This information is shown on statspack/awr report. Maybe your server has low physical memory.

4) Did you update statistics?

Regards,

mson77

[Updated on: Wed, 05 September 2007 23:52]

Report message to a moderator

Previous Topic:	Can anyone sent me the good peformance & tuning pdf file for 10g
Next Topic:	Procedure Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 07:11:24 CST 2024