PL/SQL Procedure - Very Very Slow

[saw15]

Working with Oracle 8.1.7. Unix AIX

We are running a stored procedure that sets up a cursor using a select distinct from three relatively small tables. The cursor then goes through a series (15 to be exact) of if statements to identify what account the row belongs to using one table and 4 where statements (nothing real complex). Each one that matches is then stored in a variable counter.

With 6 other clients this procedure runs in less than 2 minutes, with this ONE client, it takes over 15 hours to complete, running at a snails pace. The dba has indicated that he has checked indexes as well as updated statistics. I am not a dba and am looking for anything someone can offer.

Is there any other possible explantion for this? Something simple we may be over looking like shared memory or something ?

Help or suggestions are much appreciated.

 

[areza123]

As per my understanding u have a particular client from which your SP is pretty slow.Your SP works fast with other clients,which means the problem is at the client end.Check out your windows host file if the clients are in Windows or equivalent for AIX clients.

 

[sem]

Besides IP-routing check his sqlnet.ora/tnsnames.ora files on wheather he's using dedicated or shared connection. Try also to use IP address instead of host name in tns alias to eliminate resolving overhead. You may also look for possible locking problems.

 

[RJSHA1]

Have a look at database tables such as v$session and v$lock to see if there is any locking activity going on when you run the job again. Also you could do an EXPLAIN PLAN on the code and see if there is any tuning that you need to do on the code, but from the sounds of things it may not be a database problrm.

Good luck

 

[saw15]

The file writes 2001bytes every 30 minutes, almost like clockwork.

The stored procedure is also being run from sqlplus on the Unix server, so there is no interference between Win & Unix.

The DBA just added some indexes, and it still runs and writes in the exact same manner. It seems like there is a control or limit.

Any other thoughts?

 

[carp]

You might want to check what else is running on the client that runs slowly. We get a select few users who frequently complain about how slow Oracle is. When we look at their machines, they are also running about twelve other applications, one of which is almost invariably downloading music from the internet - and they just don't see WHY things should take so darned long!
As previously mentioned, it probably isn't the database but the specific client machine setup.

 

[spenglerr]

actually do you have such little expensive things like
NOT IN or IN in your 4 where statements? If a list grows very large and you request it using NOT IN you can get exactly these problems with the request only for the user who gets the large excludeing-list.

 

[areza123]

If the same performance occurs running this SP on different clients then it is a problem in the SP and not a client end problem as I earlier mentioned....try using indexes with hints ...if u have a small set (range) of values on a particular column like for example Blood Group can be only A,B,etc ...try bitmap indexes instead !
If nothing else works remove the cursors...could u mail the sp to ali.reza@ushacomm.co.in ; can have a look at it and let u know.