The main goal of the Database Resource Manager is to give the Oracle Database server more control over resource management decisions, thus circumventing problems resulting from inefficient operating system management.
This section contains the following topics:
What Problems Does the Database Resource Manager Address?
When database resource allocation decisions are left to the operating system, you may encounter the following problems:
Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
The operating system deschedules database servers while they hold latches, which is inefficient.
The operating system distributes resources equally among all active processes and is unable to prioritize one task over another.
How Does the Database Resource Manager Address These Problems?
The Oracle Database Resource Manager helps to overcome these problems by allowing the database more control over how machine resources are allocated. Specifically, using the Database Resource Manager, you can:
What Are the Elements of the Database Resource Manager?
The elements of database resource management, which you define through the Database Resource Manager packages, are described below.
Understanding Resource Plans
This section briefly introduces the concept of resource plans. Included are some illustrations of simple resource plans. More complex plans are included in the examples presented later, after it has been explained how to build and maintain the elements of the Database Resource Manager.
Resource plans specify the resource consumer groups belonging to the plan and contain directives for how resources are to be allocated among these groups. You use the DBMS _RESOURCE _MANAGER package to create and maintain these elements of the Database Resource Manager: resource plans, resource consumer groups, and resource plan directives. Plan information is stored in tables in the data dictionary. Several views are available for viewing plan data.
A Single-Level Resource Plan
The first illustration, is of a single -level plan, where the plan allocates resources among resource consumer groups. The Great Bread Company has a plan called GREAT _BREAD that allocates CPU resources among three resource consumer groups. Specifically, SALES is allotted 60% of the CPU time, MARKET is allotted 20%, and DEVELOP receives the remaining 20%.
A Simple Resource Management Plan
Oracle Database provides a procedure (CREATE_SIMPLE_PLAN) that enables you to quickly create a simple resource plan.
A Multilevel Resource Plan
In addition to containing resource consumer groups, a plan can contain subplans. Perhaps the Great Bread Company chooses to divide their CPU resource.The figure illustrates a plan schema, which contains a top plan (GREAT_BREAD) and all of its descendents.
A Multilevel Plan With Subplans
In this case, the GREAT _BREAD plan still allocates 20% of CPU resources to the consumer group MARKET. However, now it allocates CPU resources to subplans SALES_TEAM (60%), which in turn divides its share equally between the WHOLESALE and RETAIL groups, and DEVELOP_TEAM (20%), which in turn divides its resources equally between the BREAD and MUFFIN groups. It is possible for a subplan or consumer group to have more than one parent (owning plan), but there cannot be any loops in a plan schema. An example of a subplan having more that one parent would be if the Great Bread Company had a night plan and a day plan. Both the night plan and the day plan contain the sales subplan as a member, but perhaps with a different CPU resource allocation in each instance.
Resource Consumer Groups
Resource consumer groups are groups of users, or sessions, that are grouped together based on their processing needs. Resource plan directives, discussed next, specify how resources are allocated among consumer groups and subplans in a plan schema.
Resource Plan Directives
How resources are allocated to resource consumer groups is specified in resource allocation directives. The Database Resource Manager provides several means of allocating resources.
CPU Method This method enables you to specify how CPU resources are to be allocated among consumer groups or subplans. The multiple levels of CPU resource allocation (up to eight levels) provide a means of prioritizing CPU usage within a plan schema. Level 2 gets resources only after level 1 is unable to use all of its resources. Multiple levels not only provide a way of prioritizing, but they provide a way of explicitly specifying how all primary and leftover resources are to be used.
Active Session Pool with Queuing You can control the maximum number of concurrently active sessions allowed within a consumer group. This maximum designates the active session pool. When a session cannot be initiated because the pool is full, the session is placed into a queue. When an active session completes, the first session in the queue can then be scheduled for execution. You can also specify a timeout period after which a job in the execution queue (waiting for execution) will timeout, causing it to terminate with an error.
An entire parallel execution session is counted as one active session.
Degree of Parallelism Limit
Specifying a parallel degree limit enables you to control the maximum degree of parallelism for any operation within a consumer group.
Automatic Consumer Group Switching This method enables you to control resources by specifying criteria that, if met, causes the automatic switching of sessions to another consumer group. The criteria used to determine switching are:
The Database Resource Manager switches a running session to switch group if the session is active for more than switch time seconds. Active means that the session is running and consuming resources, not waiting idly for user input or waiting for CPU cycles. The session is allowed to continue running, even if the active session pool for the new group is full. Under these conditions a consumer group can have more sessions running than specified by its active session pool. Once the session finishes its operation and becomes idle, it is switched back to its original group.
If use estimate is set to TRUE, the Database Resource Manager uses a predicted estimate of how long the operation will take to complete. If the database estimate is longer than the value specified as the switch time, then the database switches the session before execution starts. If this parameter is not set, the operation starts normally and only switches groups when other switch criteria are met.
Switch time in call is useful for three -tier applications where the middle tier server is using session pooling. At the end of every top call, a session is switched back to its original consumer group--that is, the group it would be in had it just logged in. A top call in PL/SQL is an entire PL/SQL block being treated as one call. A top call in SQL is an individual SQL statement issued separately by the client being treated as a one call.
You cannot specify both switch time in call and switch time.
Canceling SQL and Terminating Sessions
You can also specify directives to cancel long-running SQL queries or to terminate long -running sessions. You specify this by setting CANCEL _SQL or KILL _SESSION as the switch group.
Execution Time Limit
You can specify a maximum execution time allowed for an operation. If the database estimates that an operation will run longer than the specified maximum execution time, the operation is terminated with an error. This error can be trapped and the operation rescheduled.
You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current DML statement generating the redo is terminated. No other members of the consumer group can perform further data manipulation until undo space is freed from the pool.
Idle Time Limit
You can specify an amount of time that a session can be idle, after which it will be terminated. You can further restrict such termination to only sessions that are blocking other sessions.
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 10g Tutorial
Overview Of Administering An Oracle Database
Creating An Oracle Database
Starting Up And Shutting Down
Managing Oracle Database Processes
Managing Control Files
Managing The Redo Log
Managing Archived Redo Logs
Managing Datafiles And Tempfiles
Managing The Undo Tablespace
Using Oracle-managed Files
Using Automatic Storage Management
Managing Space For Schema Objects
Managing Partitioned Tables And Indexes
Managing Hash Clusters
Managing Views, Sequences, And Synonyms
General Management Of Schema Objects
Detecting And Repairing Data Block Corruption
Managing Users And Securing The Database
Managing Automatic System Tasks Using The Maintenance Window
Using The Database Resource Manager
Moving From Dbms_job To Dbms_scheduler
Overview Of Scheduler Concepts
Using The Scheduler
Administering The Scheduler
Distributed Database Concepts
Managing A Distributed Database
Developing Applications For A Distributed Database System
Distributed Transactions Concepts
Managing Distributed Transactions
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.