Home » RDBMS Server » Performance Tuning » Procedure slow in Oracle 10g (Oracle Enterprise 10.2.0.3.0)
Procedure slow in Oracle 10g [message #300088] Thu, 14 February 2008 03:42 Go to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I wonder if I could ask for some guidance in an issue we are encountering with a performance issue.

We have a third party software with an Oracle backend, since upgrading from Oracle 9i to 10g we have noticed system performance issue when a certain routine is run. Our dba's have identified what is causing this slow down of the system, they also say that they have optimised the database as much as possible. However the problem remains. Our DBA's say that they can't do any more, the server is up to spec and the hardware is up to the job, our software supplier fail to recognise there is a problem as other customers have not reported this problem.

But the calls from users continue to happen when this process occurs.

So the point of this post is two fold:

1> I have read in passing, that Oracle 10g has more system overhead with its automatic tuning process's. Can anyone point me to anymore detailed reference regarding this, if this overhead exists, what can be done to alleviate the symptoms?

2> With the procedure that is causing the problem, which is wrapped so I cannot see the code behind it. What can I run against it to see what resources it is taking up.

With our installation the tables and indexes are analysed both by the application and by a script on a regular basis.

Any help our guidance in what to do next would be very much appreciated.

Many thanks

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #300094 is a reply to message #300088] Thu, 14 February 2008 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How to Identify Performance Problem and Bottleneck .

Regards
Michel

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #300278 is a reply to message #300094] Thu, 14 February 2008 20:29 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get the DBAs to trace the job.

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #300450 is a reply to message #300094] Fri, 15 February 2008 07:50 Go to previous messageGo to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I have looked through the article and its has some interesting features, which I will look at when the system runs slow.

I have since found out also that each dataset has multiple files attached instead of one.

Would this has a factor in perfomance also, the server the DB lives on is UNIX based.

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #300456 is a reply to message #300450] Fri, 15 February 2008 08:02 Go to previous messageGo to next message
alanm
Messages: 284
Registered: March 2005
Senior Member
hi,
when performance is bad if you run the unix command 'top' you may be able to which oracle process is using most resources.

the when you have the PID of the process try the following

--find out sql script from unix PID

SET LINES 80
SET PAGES 1000

select sql_text,s.username,s.osuser,s.machine
from v$sqlarea a,
v$process p, v$session s
where p.spid = &process_id
and p.addr = s.paddr
and a.address = s.sql_address;


Also have the dbas got uptodate statistics?

regards

Alan

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #300895 is a reply to message #300088] Mon, 18 February 2008 09:36 Go to previous messageGo to next message
optimal
Messages: 1
Registered: February 2008
Junior Member
Hi.

This situation is nothing else but expensive and annoying for everyone involved in your business.

It seems like you have a huge communication problem between your department, the dba and your software supplier.

Your dba should be aware of the fact that he is administering a system that should work for your business, not the other way around.

However, you already have a lot of information to pin-point the problem:
FACTS:
1. The application does not respond in a matter of time that
is optimal for your business.
2. You know which part of your business is suffering.
3. You know which part of the code is being used by your
business.
4. You know how to technically reproduce the problem

As written before: your DBA has a lot of information served on a silver plate. He could get the answer by "just" tracing while you execute. So, get your DBA to trace Smile

If you trace, you will not see the code itself but you will get a picture what this procedure is doing. When you get the trace data you can use it to proove to your software supplier that they need to adjust this part of the code.

There is a very helpful process for these kind of things if you have an understanding boss:
ESCALATION

Good luck!

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #301656 is a reply to message #300088] Thu, 21 February 2008 04:40 Go to previous messageGo to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I've had a loot at the 'Sampling.pdf' article and it makes mention of producing pretty analysis graphs in 10g Enterprise, are there any instructions on how to do this, i'm having difficulty generating the top 5 report as well, any pointers?

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #301890 is a reply to message #301656] Fri, 22 February 2008 03:08 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Just run a sql trace and tkprof.

post tkprof report.

Michael

Report message to a moderator

Re: Procedure slow in Oracle 10g [message #302106 is a reply to message #301656] Sat, 23 February 2008 11:16 Go to previous message
alanm
Messages: 284
Registered: March 2005
Senior Member
hi,
I have created an excel macro which can run through awr files on 10g databases. PM me if you would like me to run this on your database.

regards

Alan

Report message to a moderator

Previous Topic: statspack
Next Topic: Tune the SQL Qury
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 20:46:08 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.