Re: HELP: Stored Procedure performance question

From: Andrew Babb <andrewb_at_mail.com>
Date: Fri, 14 May 1999 08:54:14 +0800
Message-ID: <373B7436.30CBB156_at_mail.com>


Hi,

[Quoted] The server side logic will scale, since the PL/SQL block can do everything you are wanting, and it is compiled code.

Also, the code base will exist only once within the Oracle SGA, since it is common.

Each user will have there own copy of the Private Data area, which might give you some problems if you have 10,000's of users. The PGA has to exist in memory.

I think the questions you need to ask yourself are; 1. Can the client wait while the database perform these relationship changes on line, (does the SLA state that processing must be back to the client in 3 seconds, if YES then it might be difficult to complete the logic within the time frame, and therefore you need to queue) 2. Can the application allow me to make these relationship changes in a deferred mode, (if NO then you MUST change inline) 3. Other similar questions.......

If you do decide to defer, then I would suggest an intelligent and reliable queuing system, maybe Oracle AQ in 8i, or consider a self designed / developed queuing system.

Hope this helps,
Andrew

Furkan Khan wrote:

> Folks, I need to build a system that sends out notifications
> when a relation changes in the database. This has to be
> highly scalable, i.e., millions of hits per day. Some of these
> relation's are quite expensive, i.e., they may require joins.
>
> I would like to know the pros/cons of using :
>
> a. heavy server logic processing, i.e., heavy stored procedures
> as triggers that identify the changed relation - db server
> is doing most of the application logic.
>
> b. light server logic, i.e., minimal logic in a trigger. The trigger
> sends info to a event consumer client (multiple of them) which
> in turn do the processing.
>
> Basically, I want to know how expensive are stored procedures
> in oracle instances. Can I do everything in stored procedures/triggers
> and still get the salability to serve millions of transactions. Won't
> the
> heavy server side processing become a bottle-neck ?? Has anyone done
> any benchmarks on stored procedure logic and degradation of transaction
> processing throughput ??
>
> Also how well does the Oracle Advanced Queuing infrastructure scale ??
>
> Any advice/pointers appreciated.
>
> Thanks in advance.
>
> Regards.
> Furkan
Received on Fri May 14 1999 - 02:54:14 CEST

Original text of this message