How to cut resources to a particular PROCEDURE ?

[vrcatherine]

Is there a way in oracle database with which i can
allocate system resources to a particular procedure.

I have some 100's of Procedures in the database
and i want to set the priority level to the
procedures when running it.

I have some few procedures which i want to allocate least
resources if they are running during day time.

--thanks
cathy

 

[jimbopalmer]

My understanding is, that if a low priority procedure were to get shares on tables but not CPU time to complete, the number of locks and latches would skyrocket. I would use extreme caution when prioritizing CPU time for any process which potenialy blocks other processes.

You could Queue them so they never start unless there is CPU time available, but I caution againist not letting them finish.

I tried to remain child-like, all I acheived was childish.

 

[ddrillich]

Cathy,

One way in Oracle to control system resources is via profiles. They have several configuration paramerts that might be useful in your situation, like CONNECT_TIME, CPU_PER_CALL, CPU_PER_SESSION, LOGICAL_READS_PER_CALL.
I'm not sure that it's exactly what you need, because it seems to be based on quotas that when exceeded the transactions are rolled back.

Please run the following query:
SELECT * FROM sys.dba_profiles
ORDER BY profile

Please look at:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/users.htm#6403

Cheers,
Dan

 

[vrcatherine]

Does the profiles works for OBJECTS

or is it user based ??

 

[jocave]

Profiles are user-based, not object-based.

 

[LKBrwnDBA]

ddrillich's idea may be to create profiles with limited/selected resources and create a user for each of these profiles.
Then you can execute the procedures under each these users.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jocave]

My understanding, though, is that profiles follow the user executing the procedure, not the procedure itself. Thus, if SUPER_USER and LOW_RESOURCE_USER both execute a procedure owned by SUPER_USER, their respective profiles will control the priority, not the profile of the object owner.

 

[LKBrwnDBA]

Correct, that is the idea of creating the special LOW_RESOURCE_USER to limit the resources on execution of the procedure.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb