Re:Perfomance decreased while performing a stored procedure

Without looking at the stored procedure, tables and indexes, pl/sql code it is very difficult to say why it is performing very slowly. However there are a few things that you can look at: Is the procedure loaded completely in memory(Shared pool), if so, are any tables (or rows) being locked which makes other users wait ? What about excessive swapping and paging. Do you shut down your database everynight and restart every morning ? Does your developers follow any coding standards (i.e. on SQL queries, or stored procedures, packages )
Is there a lot of swapping going on in the buffer cache and the shared pool ?

There are a number of possibilities. But 99% of the time it is always a badly written SQL statement that causes problems.

Vinay Joshi
vjoshi_at_pinnacle.co.uk
Pinnacle Insurance Plc
DBA/Support

> -----Original Message-----
> From: Gerhard Anton Bartosch [SMTP:Gerhard.Bartosch_at_bull.net]
> Posted At: 20 February 1998 09:53
> Posted To: server
> Conversation: Perfomance decreased while performing a stored procedure
> Subject: Perfomance decreased while performing a stored procedure
>
> Since several weeks our customer has performance problems while
> performing one
> definitive stored procedure.
>
> Oracle version is 7.1.3
> PL/SQL is 2.1.3
>
> Has somebody an idea, what can be the reason for such an behaviour
> change.
> The customer's DBA promised, that their has not be done any change in
> the database.
>
> Regards,
> Gerhard A. Bartosch
Received on Fri Feb 20 1998 - 00:00:00 CST