Using Oracle Database Resource Manager

Oracle Database Resource Manager

Oracle Database Resource Manager allows you to

• Distribute CPU resources among different classes of users
• Automatically terminate or prevent ‘runaway’ queries
• Control the degree of parallelism
• Limit the undo space used by a class of users
• Limit the amount of time a session can be idle

Consumer Groups

• Sessions are grouped into consumer groups based on their processing needs
  • Use case #1: High and Low priority consumer groups
  • Use case #2: Customer_Service, Sales, Marketing consumer groups
• Sessions are mapped to consumer groups
  • With pre-configured rules, using session attributes such as
    • User name
    • Service, module, action
    • Client program name, OS user name, host name
  • Explicitly, using a PL/SQL procedure

Resource Manager Views

• v$rsrc_plan gives the currently running plan
• v$rsrc_consumer_group gives current performance statistics per group
• Important statistics from v$rsrc_consumer_group:
  • consumed_cpu_time – the total amount of time running
  • cpu_wait_time – the time spent waiting to run
  • active_sessions – the number of sessions in calls

Resource Manager Historical Views

• There are several views that give historical aggregations of v$rsrc_consumer_group
• v$rsrcmgrmetric gives data over the past minute
• v$rsrcmgrmetric_history gives data for the past 60 minutes in one minute intervals
• v$rsrc_cons_group_history gives information from v$rsrc_consumer_group for the current plan as well as previous plans
• More information available in the Database Administrator’s Guide Chapter 25

AWR Report Information

• The “resmgr: cpu quantum” wait event occurs when a process is waiting to be scheduled by Resource Manager
• A high number of occurrences of this event under load does not indicate performance degradation. It is analogous to counting time spent on the OS queue.
• Other wait events may indicate problems that would affect Resource Manager adherence to the plan

Non-CPU performance limitations

• If a load is not CPU bound, results will vary
• For example, if a load has resource contention issues, then fewer sessions will be available to run
• This may make it unavailable for the lottery
• Diagnosis:
  • select consumer_group_name, cpu_wait_time, consumed_cpu_time from v$rsrcmgrmetric_history
  • Compare the ratios of consumed_cpu_time to the plan
  • If cpu_wait_time is small, then this indicates an issue

Input/Output

• Just like OS CPU management, the OS handles I/Os without regard to consumer group
• Queries or processes that do a large amount of I/O will be limited by this factor (altering plan adherence)
• Diagnosis:
  • Same as with Non-CPU Performance Limitations
  • Also, consult the wait events in the AWR report
  • Measure load/cpu usage on the machine

Recycling Time

• A client spends some time between calls
• This presents some time where the client is not available to participate in the lottery
• Recycling time could mean that several users are unable to fully use a single processor.
• Diagnosis:
  • Select name, active_sessions from v$rsrc_consumer_group
  • Measure load/cpu usage on the machine

© 2010, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Oracle Enterprise Manager 10gr4 Diagnose and Tune Your Oracle Database Performance Problems Faster
2. Oracle Solaris 11 Express:Network Virtualization and Resource Control
3. Enterprise Manager 10g Sales and Partner Training Database Management Solutions
4. How to Identify Resource Intensive SQL for Tuning
5. Oracle Database 11g: Server Manageability Student Guide
6. Oracle Real Application Testing for Earlier Releases of Oracle Database
7. I/O Resource Management with Exadata
8. Enterprise Manager 11g Technical Preview:Application Testing Suite 9.1