Sir thanks a Lot.
Now it is absolutely clear to me

hi

from figure shown in
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dbrm.htm#12607 <code>

I have doubts

Postman group is assigned 40% CPU of 30% CPU assigned to maildb plan then users group and mail maint group are assigned 80% ad 20% of 40% CPU assigned to postman group (is my understanding right?).

For example if we have 20 CPUs in MYDBPLAN then 6 would be assigned to MAILDBPLAN, then from those 6, 1.2 would be assigned to POSTMAN GROUP and from that 1.2, it would be then divided again into two groups USERS GROUP and MAIL MAINT GROUP. My question is where is the other 60% of MAILDBPLAN assigned to?

In multilevel groups what does level mean? Does it mean Level 2 uses CPU assignment from Level 1, and Level 3 from Level and Level 4 from Level 3?

I dont get it :-(

It says MYDB_PLAN. MAILDB_PLAN receives 30 percent of the CPU at level 1 but it also says MAILDB_PLAN. MAIL_POSTMAN_GROUP receives 40% of the CPU at level 1, are these same level 1? Where is the other 60% assigned?

hi

I thought level 2 has to be below level 1, in the figure nowhere says that USER GROUP and MAIL MAINT GROUP is below any level 1?

If those two groups is using the 60% then if I look BUG DB PLAN using same criteria as before then where does BUG MAINT GROUP get 100% from????

It's even more confusing!

Hi

My understanding about level is if I assign 80% and 20% CPU to two sub plans then those become level 1 assigments.
Then if each sub plan has 2 consumer groups then those would be level 2. So in summary it would be


X PLAN
80% 20%
A PLAN B PLAN
40% 60% 10% 90%
V GROUP W GROUP Y GROUP Z GROUP

40% and 60% in V and W group are 40% and 60% of 80% of total CPU usage, this is what Text description of the illustration admin027.gif in same chapter suggests but in other figure it seems like talk about different things

Tom, is it true that the resurce utilization plan that we set up using resource manager kicks in only when the cpu utilization has reached a "peak" value? If so, is there any value for the peak in percentage of total CPU? Thanks.

"it tries to make sure the various consumer groups get their requisite slices over time."

suppose I have allocated 50% of CPU to group A, and no other is group is doing anything now, will group A only get 50%? or the resource manager will throttle group A only if there is not enough cpu for other groups? In otherwords when does resource manager kick in? Thanks.

Hello Tom,

Is there a way to manage I/O quotas between users the same way we can manage CPU with the resource manager?

This discussion is very interesting.

I have this issue: I would not like to divide my users is groups in fact. I would simply like that no single user gets more than -- say -- 70% of cpu when concurring with others. Hou could I accomplish that ? By creating a resource group for each user ?

Profiles do are not in order here I because, I far as I understand, they terminate the session exceeding the limit and I would like them to be scheduled, not aborted.

Tom,

If we want to clear all the resource settings that we made and set it back to default what are the steps.

Thanks.

Hello Tom,

Can I be sure from the following output of top that the CPU is used at 100% and that resource manager should have kicked in?


System: Thu Apr 29 16:31:41 2004
Load averages: 1.47, 1.41, 1.33
589 processes: 554 sleeping, 35 running
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 1.38 84.6% 0.0% 15.4% 0.0% 0.0% 0.0% 0.0% 0.0%
1 1.27 86.7% 0.0% 13.3% 0.0% 0.0% 0.0% 0.0% 0.0%
2 1.35 83.6% 0.0% 16.4% 0.0% 0.0% 0.0% 0.0% 0.0%
3 1.48 78.9% 0.0% 21.1% 0.0% 0.0% 0.0% 0.0% 0.0%
4 1.97 83.2% 0.0% 16.8% 0.0% 0.0% 0.0% 0.0% 0.0%
5 1.29 85.2% 0.0% 14.8% 0.0% 0.0% 0.0% 0.0% 0.0%
6 1.32 85.4% 0.0% 14.6% 0.0% 0.0% 0.0% 0.0% 0.0%
7 1.68 80.7% 0.0% 19.3% 0.0% 0.0% 0.0% 0.0% 0.0%
8 1.51 85.0% 0.0% 15.0% 0.0% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 1.47 83.6% 0.0% 16.4% 0.0% 0.0% 0.0% 0.0% 0.0%

Memory: 4121212K (1686148K) real, 4906236K (1938224K) virtual, 2592228K free Pa
ge# 1/148

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
2 ? 25251 oracle 241 20 67776K 3816K run 42:31 79.62 79.48 oracleBCUN
5 ? 25412 oracle 241 20 67776K 3560K run 41:37 78.81 78.67 oracleBCUN
6 ? 25507 oracle 241 20 67776K 3560K run 38:42 78.22 78.08 oracleBCUN

If so, how come my sessions are not waiting one after another?
Script:
SELECT
name
,active_sessions
,execution_waiters
,requests
,cpu_wait_time
,cpu_waits
,consumed_cpu_time
,yields
,queue_length
,current_undo_consumption
FROM v$rsrc_consumer_group
;

shows me:

Consumer Act Exec Wait CPU Time Queue UNDO
Group Sess Wtrs Reqs Time Waits Used Ylds Len Used
------------ ----- ----- ----- -------- -------- -------- ----- ------ ------
OLTP_INTERFA 7 0 116 0 0 1269928 0 0 0
CE

OLTP_MAINTEN 0 0 0 0 0 0 0 0 0
ANCE

MOVE_AND_PUR 0 0 0 0 0 0 0 0 0
GE

DATAGUARD 0 0 0 0 0 0 0 0 0
RPT_PRIORITY 0 0 0 0 0 0 0 0 0
RPT_USER 0 0 0 0 0 0 0 0 0
RPT_INTERFAC 0 0 0 0 0 0 0 0 0
E

OTHER_GROUPS 0 0 0 0 0 0 0 0 0
OLTP_USER 4 0 163 0 0 733055 0 0 0

As you can see no session is waiting. I run:

DECLARE
sqrtval NUMBER := 0;
BEGIN
FOR idx1 IN 1..1000000
LOOP
FOR idx2 IN 1..1000000
LOOP
SELECT (SQRT(idx2/idx1)) INTO sqrtval FROM DUAL;
END LOOP;
END LOOP;
END;
/

from all session. The plans are set like:
sys.dbms_resource_manager.create_consumer_group (
consumer_group => 'OLTP_INTERFACE'
,comment => 'OLTP user interface sessions');
sys.dbms_resource_manager.create_plan_directive (
plan => 'FUNCTIONAL'
,group_or_subplan => 'OLTP_INTERFACE'
,cpu_p1 => 20
,active_sess_pool_p1 => 400
,switch_estimate => FALSE
,max_est_exec_time => 120 );

sys.dbms_resource_manager.create_consumer_group (
consumer_group => 'OLTP_USER'
,comment => 'OLTP users that will have the highest priority and will require the most resources');
sys.dbms_resource_manager.create_plan_directive (
plan => 'FUNCTIONAL'
,group_or_subplan => 'OLTP_USER'
,cpu_p1 => 60
,active_sess_pool_p1 => 1600
,switch_estimate => FALSE
,max_est_exec_time => 30 );

To conclude my questions are:
Why aren't the sessions waiting?
How can I better test the implementation of the resource manager?

Thank you very much,

Hello Tom,

I managed to get them to wait by folowing your advice and using more sessions. But now the results don't make much sense to me. I get:

SELECT
name
,active_sessions
,execution_waiters
,requests
,cpu_wait_time
,cpu_waits
,consumed_cpu_time
,yields
,current_undo_consumption
FROM v$rsrc_consumer_group;

NAME |ACTIVE |EXECUTION|REQUESTS|CPU_WAIT| CPU |CONSUMED|YIELDS
|SESSIONS|WAITERS | |TIME | WAITS|CPU_TIME|
-----------------|--------|---------|--------|--------|------|--------|------
OLTP_INTERFACE | 24 | 0 | 131 | 2913 | 2719| 1245206| 1339
OTHER_GROUPS | 2 | 0 | 6 | 2 | 13| 992| 13
OLTP_USER | 29 | 0 | 188 | 2574 | 3582| 1518484| 1960

interface is set up in level 4 cpu 20% and user is level 1 60%. But they both use aproximately the same amount of CPU and they both wait for cpu.
First question:
Why is the level 1 group waiting? Isn't he supposed to get everythin he needs? the fact that he is pu_1 60% is cancelled by the fact that he is the only one active at level 1 isn't it? and how come the level 4 group gets cpu time when the level 1 is waiting? (ok that was more than 1 question ;0)
Can you please provide here an example of resource manager set up with multiple level cpu groups and queries that would demonstrate the effects of this set up in your relevant and eloquent manner that you got us used to?

Thank you very much,

Hello Tom,

I followed your recomandation and they worked (im not surpriesed and thank you very much) but now I have another problem.

I implemented the resource manager package but queries are running for lonnger 
than max_est_exec_time.
SQL> select PLAN, status, mandatory from dba_rsrc_plans where 
plan='FUNCTIONAL';

PLAN STATUS MAN 
---------------- --------- --- 
FUNCTIONAL ACTIVE NO 

select plan, group_or_subplan,type,switch_time,
switch_estimate,max_est_exec_time from dba_rsrc_plan_directives where 
plan='FUNCTIONAL'
FUNCTIONAL,MOVE_AND_PURGE ,CONSUMER_GROUP,10 ,TRUE,10
FUNCTIONAL,DATAGUARD ,CONSUMER_GROUP, ,TRUE,
FUNCTIONAL,RPT_PRIORITY ,CONSUMER_GROUP,60 ,TRUE,60
FUNCTIONAL,RPT_USER ,CONSUMER_GROUP,1800,TRUE,1800
FUNCTIONAL,RPT_INTERFACE ,CONSUMER_GROUP,600 ,TRUE,600
FUNCTIONAL,OTHER_GROUPS ,CONSUMER_GROUP, ,TRUE,
FUNCTIONAL,OLTP_INTERFACE ,CONSUMER_GROUP,120 ,TRUE,120
FUNCTIONAL,OLTP_MAINTENANCE,CONSUMER_GROUP,3600,TRUE,3600
FUNCTIONAL,OLTP_USER ,CONSUMER_GROUP,1 ,TRUE,1
As you can see for the oltp_user group I have a max_est_exec_time of 1(second).
SQL> select RESOURCE_CONSUMER_GROUP from v$session where username='DM210'; 

RESOURCE_CONSUMER_GROUP 
-------------------------------- 
OLTP_USER 
My user is part of that group as you can see above. And still:
SQL> set autotrace on
SQL> select count(*) from all_objects;
COUNT(*)
----------
9359
Elapsed: 00:00:10.75
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Text continued in next action...


04-MAY-04 16:29:20

Text continued from previous action...

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
74613 consistent gets
0 physical reads
0 redo size
492 bytes sent via SQL*Net to client
652 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
My query took 10 seconds.
Question 1:
It was supposed to be estimated and stopped if it was longer than 1 second 
correct? but the user does not have access to the explain plan tables. 
Question 2:
Does that influence the estimation? 
Question 3:
Do I have to grant specific rights to access dictionary or explain plan tables 
to user for which I want to use max_est_exec_time, or are there any parameters 
that have to be set for the estimation to occur?
Question 4:
Is there anything that I have to change to get the max_est_exec_time to be 
taken into account(because maybe it is estimated but not taken into account)?
Question 5:
Is there anything that I have to do to get the time to be estimated (because 
maybe the execution time is just not estimated)?
Thank you very much 

Hello,

Do we have to analyze the dictionary? It is the first time I hear this notion but I'm rather new to oracle. If there is such a need how come oracle doesn't do it itself, it has the functionality in dbms_analyze.
How do you do that, analyze schemas sys and system?

Thank you,

So in my case when 9i, dict not analyzed frmo first day, no point to analyze it now, resource manager max_est_exec_time wont work, correct?

From, </code> http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10739/dbrm.htm#sthref3176 <code>

<quote>If you want to control resource distribution within an instance, use the Database Resource Manager and turn off operating-system resource control.

If you have multiple instances on a node and you want to distribute resources among them, use operating-system resource control, not the Database Resource Manager.<quote>

Tom, does the above indicate that I should not consider using resource manager if my server contains multiple instances? Thanks.

Hi Tom,

Sometimes, while trying to create pending area, I get following error:
ERROR at line 1:
ORA-29370: pending area is already active
ORA-06512: at "SYS.DBMS_RMIN", line 56
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 244
ORA-06512: at line 1

How to know whether pending_area is already created or not?

"ERROR at line 1: ORA-07455: estimated execution time (9141 secs), exceeds limit (10 secs)"

Is there a way to find out the estimated execution time (9141 secs above) if I am NOT using Resource Manager? Or does that information kick in only when a resource plan is active? Even so, where is this information stored? i.e. Given a query, how can I find out the estimated execution time?

Thanks

Tom,

What do you think about "A reader" question : </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5955622922067#6043031275366 <code>

I cannot figure out what is the relation between sub-plan and CPU_P1...


Best regards,

Laly.

Correct, it is a statement.

But is how things are ?

Dear Tom,
I tried to reproduce the same case as mentioned by you in the answer. But I am not getting any error (as would have been expected). Please look throught the code and let me know what I might have done differently.

Thanks and regards,

A reader

......................
SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure resource_plan as
  2  begin
  3       dbms_resource_manager.create_pending_area;
  4       dbms_resource_manager.create_plan
  5       ( 'lvl1', comment => 'top level plan' );
  6       dbms_resource_manager.create_plan
  7        ( 'lvl2a', comment => 'second level plan' );
  8       dbms_resource_manager.create_plan
  9      ( 'lvl2b', comment => 'second level plan' );
 10         dbms_resource_manager.create_consumer_group
 11          ( consumer_group => 'demo', comment => 'yes it is a demo' );
 12          dbms_resource_manager.create_plan_directive
 13          ( plan => 'lvl2a',
 14            group_or_subplan => 'demo',
 15             comment => 'its a demo',
 16            MAX_EST_EXEC_TIME => 10 );
 17          dbms_resource_manager.create_plan_directive
 18          ( plan => 'lvl2b',
 19             group_or_subplan => 'demo',
 20             comment => 'its a demo',
 21             MAX_EST_EXEC_TIME => 100 );
 22          dbms_resource_manager.create_plan_directive
 23          ( plan => 'lvl1',
 24             group_or_subplan => 'lvl2a',
 25            comment => 'its a demo' );
 26          dbms_resource_manager.create_plan_directive
 27         ( plan => 'lvl1',
 28             group_or_subplan => 'lvl2b',
 29             comment => 'its a demo' );
 30          dbms_resource_manager.create_plan_directive
 31         ( plan => 'lvl1',
 32            group_or_subplan => 'OTHER_GROUPS',
 33           comment => 'its a demo' );
 34           dbms_resource_manager.create_plan_directive
 35           ( plan => 'lvl2a',
 36             group_or_subplan => 'OTHER_GROUPS',
 37             comment => 'its a demo' );
 38           dbms_resource_manager.create_plan_directive
 39           ( plan => 'lvl2b',
 40             group_or_subplan => 'OTHER_GROUPS',
 41             comment => 'its a demo' );
 42           dbms_resource_manager.validate_pending_area;
 43           dbms_resource_manager.submit_pending_area;
 44* END;
 45  /

Procedure created.


SQL> exec resource_plan;

PL/SQL procedure successfully completed.

SQL> create user low_priority identified by x;

User created.

SQL> grant connect, resource to low_priority;

Grant succeeded.



SQL> drop user low_priority;

User dropped.

SQL> create user low_priority identified by x;

User created.

SQL> grant connect, resource to low_priority;

Grant succeeded.

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group( 'low_priority
', 'demo', TRUE);

PL/SQL procedure successfully completed.

SQL>
SQL> exec         dbms_resource_manager.set_initial_consumer_group( 'low_priorit
y', 'demo' );

PL/SQL procedure successfully completed.

SQL> conn low_priority/x
Connected.
SQL> create table t as select * from all_objects;

Table created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL>  select count(*) from t;

  COUNT(*)
----------
     22977

SQL> select count(*) from t,t;

  COUNT(*)
----------
 527942529
-- This had generated error in your case 

well, i don't see this bit

ops$tkyte@ORA920.US.ORACLE.COM> alter system set resource_manager_plan = lvl1 
scope = memory;

System altered. 

Tom,

I have a database which two departments share. Now they want to find out how much resources (in terms of transaction time, CPU time ...) each department users are using within the database (for billing purpose only, not to limit any session's limit)

Any idea whether Oracle has this built-in capability? can resource manager serve the purpose?

thanks,

Tom,

Our Application is being developed overseas, design or coding of which we do not have any control, but are entrusted with Performace Issues. My question is:

We have one user ABC (Owns all objects), who can login to database using two different applications - OLTP as well as Reports. Is there anyway I can control the resources consumed by Reports during working hours (Performace degrades when users run ad-hoc reports). users can wait while these reports are run, but other Online users should not be effected.

Can I use Resource Manager (or) any other techniques you recommend, other than splitting the Servers?

Thanks for your help.

its an Internal (to the organisaztion) Project. Design & Development happens overseas - in North America, Performance (They call it administration) is our head-ache :(:(.

I am negotiating for better "Contracting" in terms of inputs I can provide from here.

I am looking forward to upgrading the DB to 10g (from 9iR2) - scheduled for sometime next year.

Tom,


Resource manager implementation steps: 

begin 
 dbms_resource_manager.create_pending_area(); 
end; 
/ 
--------------===== Step 1 Create Resource Manager Plan =====----------------------------- 
begin 
 dbms_resource_manager.create_plan( 
  plan => 'SINGLE_LEVEL_PLAN', 
  comment => 'Resource plan/method for Single level sample'); 
end; 
/ 
--------------===== Step 2 Create Resource Manager Consumer Groups =====----------------------- 
begin 
 dbms_resource_manager.create_consumer_group( 
  consumer_group => 'OLTP_Group', 
  comment => 'Resource consumer group/method for online users sessions'); 
end; 
/ 
--------------===== Step 3 Create Resource Manager Plan Directives =====------------------------- 
begin 
 dbms_resource_manager.create_plan_directive( 
  plan => 'SINGLE_LEVEL_PLAN', 
  group_or_subplan => 'OTHER_GROUPS', 
  comment => 'Online day users sessions at level 1', 
  cpu_p3 => 80, 
  parallel_degree_limit_p1 => 0,
  max_est_exec_time =>0); 
end; 
/ 

begin
  dbms_resource_manager.update_plan_directive( 
  plan => 'SINGLE_LEVEL_PLAN', 
  group_or_subplan => 'OTHER_GROUPS',
  new_comment => 'Online day users sessions at level 1', 
  new_cpu_p1 => 10, 
  new_parallel_degree_limit_p1 => 0,
  new_max_est_exec_time =>0); 
end;
/

begin 
 dbms_resource_manager.validate_pending_area(); 
end; 
/ 
begin 
 dbms_resource_manager.submit_pending_area(); 
end; 
/ 

I followed the above steps, but i am getting the status as NULL; when will i get it as active?

Next, we have to grant privileges in order to assign consumer groups to users: 

begin 
 dbms_resource_manager.create_pending_area(); 
end; 
/ 
----------===== Step 4 Grant switch privilege for resource consumer groups to users or roles =====---------- 
begin 
 dbms_resource_manager_privs.grant_switch_consumer_group( 
  grantee_name => 'HR', 
  consumer_group => 'OLTP_Group', 
  grant_option => FALSE); 
end; 
/ 
---------===== Step 5 Assign Users to Resource Manager Consumer Groups =====----------------------- 
begin 
 dbms_resource_manager.set_initial_consumer_group( 
  user => 'HR', 
  consumer_group => 'OLTP_Group'); 
end; 
/ 
begin 
 dbms_resource_manager.validate_pending_area(); 
end; 
/ 
begin 
 dbms_resource_manager.submit_pending_area(); 
end; 
/ 

The above scheme is used for assigning consumer-groups to users in all of the following samples. 

Let us have a look at the definitions: 

Resource Plans 

select plan, cpu_method, status from dba_rsrc_plans order by 1; 

PLAN                           CPU_METHOD                     STATUS
------------------------------ ------------------------------ -----------------------
INTERNAL_PLAN                  EMPHASIS
INTERNAL_QUIESCE               EMPHASIS
MYPLAN                         EMPHASIS
SINGLE_LEVEL_PLAN              EMPHASIS
SYSTEM_PLAN                    EMPHASIS


Please let me know , why it is not active?
but from other session i am able to get the required error message;




SQL> select count(*) from employees, employees,employees;
select count(*) from employees, employees,employees
                     *
ERROR at line 1:
ORA-07455: estimated execution time (30 secs), exceeds limit (0 secs)


Please help me in knowing why the status is not active.

Thanks,
Yakgna Kumar 

Tom,

Earlier you stated:
"The resource manager cannot manage resources across instances, only within an instance. It would not be useful in a multi-instance on a single server situation. "

Could you please clarify a little more what you mean?

If there is more than one database on a single host, and I implement resource manager on only one of these databases, and the other database is consuming 50% of the CPU, will I still be able to manage competing resources within the single database? Or would this be an exercise in futility?

Tom,

I forgot to ask how a mix of SHARED & DIRECT connections would be handled by the Resource Manager.

Thkx!

Thanks Tom, See you at Oracle Openworld.

Tom,

Is it possible to enforce a limit at the database level to limit number of rows returned to a session? (10g)
In our OLTP environment if a user runs unqualified query that brings in millions of rows, the middle tier runs out of memory causing problems. We would like to limit the number of rows returned to any session to say 500 rows. Is there a way to do this?

Tom,
I am on 10gr2. I am exploring setting up resource manager for multiple database running on the same server. The idea is that if optimizer estimates that a SQL will use more than a specified amount of resources, resource manager will abort the SQL. I have setup resource manager using grid control on test database and it seems to abort any SQL that exceeds my specified limit of 300 seconds. I have many questions but I will ask one by one.

My first question is that if resource manager aborts a SQL because of resource consumption, is this event logged anywhere? I checked but there is no entry in alert log. Similarly, if I setup resource manager to kill the session, there is no entry in alert log. Knowing that SQL was aborted or session killed would be a great help since I can fine tune the resource manager settings.

Thanks

Tom,
I do agree with what you say about not having multiple databases on one cluster but it is not by choice. There are various issues invloved.

We were recently hit by a situation where one database consumed all the CPU causing a major downtime for other database. We are trying to make use of Oracle features to get around the infrastructure limitations.

Our major concern is queries. The directive is that if we can achieve uptime of all database by aborting few badly written queries, we will do that while we work on tuning the bad code.

So far testing has been promising. The resource manager doesn't let any resource intensive query run at all. However, it doesn't log that it has stopped a query from running or killed a user session. Even if I am running a single database on one server, I would be faced with the same problem. If I do not know which query has been aborted by resource manager, I would not even know that there is a problem unless users started complaining. This would look bad.

Your expertise will greatly help.

Thanks

Tom,
Thanks for your reply. I am still curious as to why you say that resource manager will not help us? Other than queries, what else can cause database downtime due to high CPU utilization? I am not counting hardware failures.

Thanks

I understand that resource manager will not work across instances. There are 4 database on the cluster. 3 of them combined take up about 15% of CPU at peak. The 4th database takes up about 50%-60% CPU at peak. These numbers are from observation over a 8 months period. It is the 4th database that I am trying to curtail. Sometimes due to new versions of application that we deploy, some bad SQLs do sneak past the testing process. These SQLs tend to raise the CPU consumed by 4th database to 100%. This is when it takes the other 3 down. It is always the same pattern. I am not trying to go across database, just trying to manage one database.

The second question is that I have about 10,000 named database users in this database. New accounts are created and old ones are retired by administrators through a web based interface.

I want to switch these users to a custom limited resource consumer group between 8:00 am and 5:00 pm on weekdays. All other times, these users will remain in the default consumer group. How do I do that so I do not have to manage adding/deleting the usernames manually?

Thanks

Hi Tom,

I learnt a number of ways of using the resource manager from this thread. However, I have a question - Is it possible to control the execution of a stored procedure through resource manager. In my case the stored procedure is called in parellel from our application through JMS (Java Messaging System).

I know of an option of doing this is by changing the implementation to use dbms_scheduler (and using job class)in 10g rather than using JMS.

Is there any other way of doing this keep the JAMS implementation.

Many thanks
Nilanjan Ray

Hi Tom,

Apologies for not being absolutely clear in my last post.
There is a functionality (say 'X') which calls a stored procedure asynchronously. The asynchronous call is achieved through JMS. Multiple users can execute this functionality. Often the parameters which the users use for this functionality causes the procedure (obviously inside a package) execute for 5-6 minutes. This is further complicated by the fact that sometimes there could be multiple asynchronous instances of this procedure running at the same time.

A. What I would like to achieve with resource manager is that if any asynchronous execution of the procedure exceeds a specific execution time, it will switch to a different consumer group. Can you please suggest something to achieve this?

B.
One change which I can think of is: to change the implementation to Oracle queues.
1. Create a consumer group
2. Create a plan
3. create resource manager plan directives
4. create a job class.
5. Create a scheduled job using dbms_scheduluer and associate the job_class to the scheduled job. The job performs the dequeue and executes the procedure.

The plan directives will ensure that if the job execues more than 5 minutes it will switch consumer group to consume less resource.

Excellent as usual. Precise and simple.

If I am not bothering you too much, can you please give an example of using resource manager with tns service name. I did google for a while and searched the docs but could not locate any.

Many thanks
Ray

Hi,

Do you have the ability to change the stored proc?
Create a resource group not assigned to anyone but has the max execution time set. In the stored proc, use DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS at the start of the procedure call to switch the session to this group. At the end of the call (remember exception handling) switch it back to the default or whatever you had before.
Would this work?

Chris

Hi,
You can set the module/action of JMS and map them to the Resource Consumer Group and then set the consumer group switching & directives to cancel the sessions that exceeds the threshold.

if JMS can't set the module/action you can use dbms_application_info.set_module(module_name,action_name)
I was little bit successful in doing this for a ODP.Net application, but yet to fully achieve the result.

Hi,

You are saying "have your jms 'executor' connect to the database using a specific user OR using a specific TNS connect string. Then, just set up a resource manager policy for THAT user or THAT tns service. "

Could you please explain this how to do this with TNS service ?

Thanks
Deba

Hi Tom,

Is there any way to log information about a session killed by our resource manager plan?

Thanks.

Joaquin Gonzalez

Hello Tom,
I would like to know if using Oracle 10g Services and Resource Manager, I can limit resources used by rman while performing a backup.

Thanks for your comments.

Regards,
Loïc

#bYou meant this link?
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmconc1.htm # BRADV108
After removing the blanks in the link.

It says (among other things):

I/O Read Rate of Backups

By default, RMAN uses all available I/O bandwidth to read/write to disk. You can limit the I/O resources consumed by a backup job with the RATE option of the ALLOCATE CHANNEL or CONFIGURE CHANNEL commands. The RATE option specifies the maximum number of bytes for each second that RMAN reads on the channel.

For example, you can configure automatic channels to limit each channel to read 1 MB a second:

CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
CONFIGURE DEFAULT DEVICE TYPE TO sbt;
CONFIGURE CHANNEL DEVICE TYPE sbt RATE 1M;

In effect, the RATE option throttles RMAN so that a backup job does not consume excessive I/O bandwidth on the computer.

Hi Tom,

Based on the Oracle documentation this resource consumer group is used to do IO operations on behalf of Background Processes.

However, in the gv$session I am seeing Column "USERNAME" as my Name using the above mentioned RESOURCE CONSUMER GROUP with Program as oracle@sk1-dbdata-003.com (O005)”

Can you please explain what is this?

Thanks

Hello Tom,

We have a requirement to restrict users using more than 20% CPU. I have used the below script to create resource manager plan accordingly.

BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area();
DBMS_RESOURCE_MANAGER.create_pending_area();

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP =>'MY_RSRC_GROUP',COMMENT => 'MY TEST CONSUMER GRP');

DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN =>'MY_PLAN',COMMENT => 'TEST PLAN');

DBMS_RESOURCE_MANAGER.create_plan_directive
('my_plan','my_rsrc_group','Create Plan Directive for scott user',cpu_p1 => 20);

DBMS_RESOURCE_MANAGER.create_plan_directive
('my_plan','other_groups','Create Plan Directive for all othersers/groups',cpu_p2 => 100);

DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

SQL> select username, initial_rsrc_consumer_group FROM dba_users where username like 'SCOTT';

USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SCOTT DEFAULT_CONSUMER_GROUP

SQL> exec DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group('scott', 'my_rsrc_group', FALSE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_RESOURCE_MANAGER.set_initial_consumer_group('scott', 'my_rsrc_group');

PL/SQL procedure successfully completed.

SQL> select username, initial_rsrc_consumer_group FROM dba_users where username like 'SCOTT';

USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SCOTT MY_RSRC_GROUP

SQL> ALTER SYSTEM SET resource_manager_plan=my_plan scope=memory;


Now, when running the above sql query as scott user, the over all cpu utilization of server hits over 99%.
and, if you see PID: 5093 uses 100.2% CPU. How can we restrict them to use only 20% per process?


SQL> conn scott/tiger
Connected.
SQL> select count(*) from scott.all_objects, scott.all_objects;


From unix top
==============
top - 15:27:27 up 439 days, 3:06, 3 users, load average: 0.49, 0.38, 0.27
Tasks: 116 total, 3 running, 113 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0%us, 0.0%sy, 0.0%ni, 98.7%id, 1.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 :100.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3913648k total, 3883368k used, 30280k free, 255684k buffers
Swap: 8385920k total, 0k used, 8385920k free, 3234032k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5093 oracle 25 0 2237m 74m 65m R 100.2 2.0 0:37.13 oracle


Any recommendations?