Re: Managing multi-user database resouces?

dloganca_at_my-deja.com wrote in message <7kp9g2$o7b$1_at_nnrp1.deja.com>...

>How is it possible to manage an Oracle Database
>Server's resources, when it may not be "user
>driven" per se (i.e. you have apps servers in an
>N-tier architecture)?

Oracle does not differentiate between a "client" process from a traditional client (Win95) or from a N-tier app server. As far as Oracle is concerned, both processes are clients and they will be treated the same way.

>How do I ensure that a
>particular transaction or application server gets
>nn% of the transaction resources of the server?

Difficult. Not impossible though. You can assign more resources to a query by using for example parallel query (PQ). This could mean that a "normal client" gets a maximum of 2 PQ slaves, whereas a "app server client" get a max 4 or 6 PQs. But this opens up a whole can of worms. If there are only 10 PQs available and 5 clients running queries, the app server client is not going to get a single PQ.

Oracle allows you to define user profiles, but these do not govern actual resource -allocation-. It simply provides max accumulated resource -usage- controls.

>How is admin'd and how much control granularity
>do I have?

First reaction - more than with any other database I've worked with. But then I think you are implying something else.

Oracle is a basic server application. It does not "control" the CPU in anyway - for example, it will not bind process abc to CPU2 and process xyz to CPU3. It does not change the priority settings (nice() or whatever) of "lower priority" processes. I would think this is true of all database systems.

CPU usage and scheduling are and should remain a function of the operating system. To move that complexity from the kernel to database server level... well, that is not Oracle's (or any other rdbms for that matter) strength, nor focus. Even if this is done - the standard "controls" provided by the kernel for applications to control CPU allocation are coarse. They usually range from things like low priority, normal, high priority to "real time". The actual CPU allocation is still handled by the kernel. Sure, a high priority process is going to get more CPU slices than a low priority process, but how much depends on the kernel and what else are running at that point in time.

With a real time operating system.. well, that is another ballgame. And one in which Oracle disclaims everything and anything.

>I have stumbled across Solaris Resource Manager,
>but it is very user/apps server centric.

But then this is an operating system tool for an operating system. Oracle does allow you to control in great detail, Oracle specific "things" - i.e. view Oracle processes, see the stats of these processes, interrogate them to find out what they are doing and even killing them if required. As Oracle is not platform dependant or bound, it does not provide you with further access down to kernel level to control these Oracle processes at physical operating system level. Good case in point - NT treats Oracle processes very differently than UNIX - on NT these are real dinkum threads and on UNIX these are separate UNIX processes.

What I fail to understand though is why? Why should an app server be treated differently by Oracle than a normal/standard client? Why should Oracle make such a differentiation? And if there are real reasons, how should it be implemented?

An N-tier architecture is at the most basic level not any different than the standard client-server architecture IMHO. The difference is that in N-tier, a process/app can be both a client and a server at the same time. So I would think that the basic client-server method of dealing with client and server processes to be applicable to N-tier.

regards,
Billy Received on Wed Jun 23 1999 - 04:42:41 CDT