Re: Re:Perfomance decreased while performing a stored procedure

Something else which you should be aware of is that once one the query execution plan of for procedure has been determined it will not be recalculated, therefore if the data upon which it operates varies significantly then the procedure should be flushed from the shared pool.

For example, when the procedure is initially executed perhaps a full table scan represents the best way of solving the query contained within the procedure, but over time, as rows are added, this becomes less and less efficient.

Vinay Joshi wrote in message
<717BDE76D864D011870F00A0C9283F1E1396A1_at_exchange.pinnacle.co.uk>...
>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 Sat Feb 21 1998 - 00:00:00 CST