On any database server, there is often only a limited amount of resources (read cpu, memory, i/o). Oracle database servers are often subjected to highly concurrent, resource consuming, queries issued by database client programs. This often leads to contention for cpu, memory and i/o resources on a server. Once you have way more processes on a system actively executing, compared to the number of cpu cores you have, at some point the scheduling of these processes becomes inefficient at the operating system level.
The oracle Database resource manager can be used to throttle the Number of oracle database sessions actively running on a database, or to limit the amount of cpu resources a session gets, or to limit the degree of parallelism each session gets among other things.
Below are the steps to setup a resource plan that
Limits, maximum Degree of Parallelism each session can have, at 64.
Limits the maximum number of Concurrent active sessions that can be present for a user to 2. (The other sessions get queued).
Create a Consumer Group
Login to Enterprise Manager, click on the “Server” tab.
Under the “Resource Manager” section, click on “Consumer Groups”
Click on the “Create” Button
Enter a Name and Description for the Consumer group
Click on the “Add” button to add a database user to the consumer group
Check the box to the right of the user you want to add and click on “Select”
Now click on the “Database Instance” bread crumb to return to the “Server” tab
Click on “Consumer Group Mappings”
Click the radio button on the row that has the value “Oracle User” in the “View” column.
Click on “Add Rule for Selected Type”
In the drop down list “Selected consumer group”, choose “DOP_GROUP”.
In the “Available Oracle User” list, click on the user you want to add to this “DOP_GROUP” group and click on “Move”
Click Ok.
Click on Apply
Click on the “Database Instance” Breadcrump to return to the “Server Tab”
Click on “Resource Plans”
Click on “Create” to create a new resource plan
From the table “Available Group/Subplans” choose “DOP_GROUP” and click on Move. Click Ok.
Click on the tab named “Parallelism”
For the DOP_GROUP set the maximum degree of parallelism to be 64
Click on the tab named “Session Pool”
Set the number of active sessions to “2” (Or any number you want (In the screen i have used 12))
Click “Ok”
In the “Resource Plans” screen, choose the DOP_PLAN, In the actions drop down list, choose “Activate” and click on Go.
Now the resource plan “DOP_PLAN” is active.
Let us say now we submit a bunch of queries (Let us say 20) simultaneously (That use parallel query)as the database user HR then
Only 2 of them will be allowed to run at the same time (The rest are queued)
Each session gets a maximum Degree of parallelism of 64