Home » SQL & PL/SQL » SQL & PL/SQL » oracle resource plan
oracle resource plan [message #251388] Fri, 13 July 2007 07:10 Go to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Dear ALL,
Anyone plz help me to oracle resource manager plan, I have created a plan but it is not working according to my requirement. any example script. I have to use it for single PC. when i run a script it always consume 100% CPU, I have to minimize this thing using reource plan. This is NOT very urgent, so please take your time.
I shall be very thankful to you

Thanks

[Updated on: Fri, 13 July 2007 07:57] by Moderator

Report message to a moderator

Re: oracle resource plan [message #251395 is a reply to message #251388] Fri, 13 July 2007 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Resource manager is ONLY active when CPU is used at 100%.
More, it is active only if each group requires 100% of CPU.
So your situation is normal.

Regards
Michel
Re: oracle resource plan [message #251415 is a reply to message #251395] Fri, 13 July 2007 09:02 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
but i want to use it i want to use a specific resource limit to for each group, i want that certain group use 75% of the cpu

thanks
Re: oracle resource plan [message #251418 is a reply to message #251415] Fri, 13 July 2007 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your plan and other groups.

Regards
Michel
Re: oracle resource plan [message #251478 is a reply to message #251415] Fri, 13 July 2007 14:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you want to do with the other 25% ?
Re: oracle resource plan [message #251683 is a reply to message #251478] Mon, 16 July 2007 01:29 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
The plan that i create is

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN => 'BOND', COMMENT => 'JAMES BOND');


EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP =>'RED EYE',COMMENT => 'G1');

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP =>'BLUE EYE',COMMENT => 'G2');


EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan =>'BOND', group_or_subplan =>'RED EYE',comment =>'G1', cpu_p1 =>50,cpu_p2=>0, parallel_degree_limit_p1 =>12);

EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan =>'BOND', group_or_subplan =>'BLUE EYE', comment =>'G2', cpu_p1 =>0,cpu_p2=>50, parallel_degree_limit_p1 =>6);

EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (plan =>'BOND', group_or_subplan =>'OTHER_GROUPS', comment =>'THIS IS DEFAULT', cpu_p1 =>0,cpu_p2=>0,cpu_p3=>100);

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'scott', 'RED EYE');
END;

Plz identify mistake in the plan
Re: oracle resource plan [message #251690 is a reply to message #251683] Mon, 16 July 2007 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each level must sum up to 100% CPU.

You should put all statements between BEGIN/END instead of one EXEC for each one.

Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: oracle resource plan [message #251691 is a reply to message #251690] Mon, 16 July 2007 01:48 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Dear Sir,
I dont understand ur point, the plan is using 100% cpu among two groups.i.e 50% each

Thanks
Re: oracle resource plan [message #251696 is a reply to message #251691] Mon, 16 July 2007 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I meant at each LEVEL (cpu_p1, cpu_p2, cpu_p3). It is not mandatory but it is easier what is doing the plan.
Giving onely one cpu=50 at level1 is equivalent to give it 100.

I don't see no where something relative to the 75% you want.
I don't see either your Oracle version.

Regards
Michel
Re: oracle resource plan [message #251728 is a reply to message #251690] Mon, 16 July 2007 05:49 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Dear Sir, Plz help me in making a successful plan. I need to create it.
I shall be very grateful to you.

thanks
Re: oracle resource plan [message #251744 is a reply to message #251696] Mon, 16 July 2007 08:10 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
I am using 10G. Now I want that when I map a group with user e.g. (scott). it will not cross 50% cpu.
75% is my requirement but I want to check it using different scenarios. I have map the scott with group Red Eye which is using 50%. but when i run a heavy querey it reaches 100%

thanks
Re: oracle resource plan [message #251747 is a reply to message #251744] Mon, 16 July 2007 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want it exceeds 50% you have to put him in a group with 50% with concurrent groups for the other 50% or in a lower priority group.

You have to define the other groups and their priority and resource consumption. You can't just say I want this user consumes less than 50% of CPU.
Resource manager is a global policy.

Regards
Michel

[Updated on: Mon, 16 July 2007 08:44]

Report message to a moderator

Re: oracle resource plan [message #251808 is a reply to message #251747] Mon, 16 July 2007 13:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Somehow I have the feeling, this whole thread boils down to Michel's first reply..
Do you think your server is used too heavily? Is that the reason for the plans? You want Oracle only to use max 50% of the server's cpu?
Re: oracle resource plan [message #251887 is a reply to message #251808] Tue, 17 July 2007 01:47 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Dear Frank
We have hundreds of data entry transactions that continuously use the db server. So the server is used too heavily that other application stop responding. I need to create a plan. Can u send me any sample or a file that help me.
Re: oracle resource plan [message #251889 is a reply to message #251747] Tue, 17 July 2007 01:54 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Could u plz give any example to clear ur point. Where can i set the priority according to my knowledge its a procedure provided by oracle in which we map priorities. Did u mean that
Re: oracle resource plan [message #251900 is a reply to message #251889] Tue, 17 July 2007 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Did u mean that

Waiting for the rest...

Regards
Michel
Re: oracle resource plan [message #251903 is a reply to message #251900] Tue, 17 July 2007 02:17 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
I mean that where can I set the priorities, any example or any helpful material for me
Re: oracle resource plan [message #251905 is a reply to message #251903] Tue, 17 July 2007 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The prime question is: Does resource manager can fit your requirements?
This is what we are trying to establish.
If not, the rest is irrelevant.

And the first condition is: does Oracle user processes within each group you want to create wish to use more than 100% CPU?

Regards
Michel
Re: oracle resource plan [message #251916 is a reply to message #251905] Tue, 17 July 2007 02:39 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
The Resource Manager well suit in our environment. I want to restrict oracle process for each group at application level to 50%. No matter what the user wishes. its our internal process or policy.
Re: oracle resource plan [message #251927 is a reply to message #251916] Tue, 17 July 2007 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can you say that Resource manager well suits to your environment when you don't know how to use it?

The proof you don't know what is resource manager and how it works is that your 50% requirement.
If you want of 50% then create 2 groups and assign them 50% but this will not guarantee they won't use more than 50%.

Regards
Michel
Re: oracle resource plan [message #251943 is a reply to message #251927] Tue, 17 July 2007 04:00 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Well I have created two groups but they are consuming full 100% cpu.

As far as Resource Manager is concern I have a very clear concept of Resource Manager. I told you that I want to check the cpu utilization by making different resource limit such as (50%,70% etc). Before implementing it on the real database server I want to check different scenarios whether it works or not. I think u not clearly understand my problem.

Plz see my plan again that i post and guide me accordingly

regards
Majid
Re: oracle resource plan [message #251950 is a reply to message #251943] Tue, 17 July 2007 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I don't understand your problem I can't post a plan accordingly.

Regards
Michel

Re: oracle resource plan [message #251954 is a reply to message #251950] Tue, 17 July 2007 04:20 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
I think u mind it. Ok very fine I say sorry but try to understand my problem, I want to implement a plan, thats why I post it because I am facing problems


regards
Majid
Re: oracle resource plan [message #251961 is a reply to message #251954] Tue, 17 July 2007 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to implement a plan

What I think is resource manager is most likely not the correct way to do it.
More it is useless.

Regards
Michel


Re: oracle resource plan [message #252156 is a reply to message #251961] Wed, 18 July 2007 00:45 Go to previous messageGo to next message
majid tahir
Messages: 39
Registered: July 2007
Location: Pakistan
Member
Dear Michel
If its useless than why oracle has added this feature. why?


Thanks
Re: oracle resource plan [message #252158 is a reply to message #252156] Wed, 18 July 2007 00:52 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless FOR YOUR CASE.
Not useless in general (although...).
And for the question why Oracle add something. It is more a marketing question.
For myself, I ask why Oracle delivers new features when it did not still fix the known bugs.

Regards
Michel
Previous Topic: query counting number of records
Next Topic: Ideal linesize and pagesize
Goto Forum:
  


Current Time: Thu Dec 08 16:08:42 CST 2016

Total time taken to generate the page: 0.08824 seconds