This preview shows page 1. Sign up to view the full content.
Unformatted text preview: Tuning CPU Resources Begin by determining the amount of CPU resources the Oracle instance utilizes in three cases, when your system is: Idle At average workloads At peak workloads This example application has 100 users working 8 hours a day, for a total of 800 hours per day. Each user entering one transaction every 5 minutes translates into 9,600 transactions daily. Over an 8-hour period, the system must support 1,200 transactions per hour, which is an average of 20 transactions per minute. If the demand rate were constant, you could build a system to meet this average workload. However, usage patterns are not constant--and in this context, 20 transactions per minute can be understood as merely a minimum requirement. If the peak rate you need to achieve is 120 transactions per minute, you must configure a system that can support this peak workload. For this example, assume that at peak workload Oracle can use 90% of the CPU resource. For a period of average workload, then, Oracle use no more than about 15% of the available CPU resource as illustrated in the following equation: 20 tpm/120 tpm * 90% = 15% Where tpm is "transactions per minute". If the system requires 50% of the CPU resource to achieve 20 tpm, then a problem exists: the system cannot achieve 120 transactions per minute using 90% of the CPU. However, if you tuned this system so it achieves 20 tpm using only 15% of the CPU, then, assuming linear scalability, the system might achieve 120 transactions per minute using 90% of the CPU resources. Detecting and Solving CPU Problems
System CPU Utilization
Tools such as sar -u on many UNIX-based systems enable you to examine the level of CPU utilization on your entire system. CPU utilization in UNIX is described in statistics that show user time, system time, idle time, and time waiting for I/O. A CPU problem exists if idle time and time waiting for I/O are both close to zero (less than 5%) at a normal or low workload. V$SYSSTAT shows Oracle CPU usage for all sessions. The statistic "CPU Used" shows the aggregate CPU used by all sessions. V$SESSTAT shows Oracle CPU usage per session. You can use this view to determine which particular session is using the most CPU. neffective memory sharing among SQL statements can result in reparsing. Use the following procedure to determine whether reparsing is occurring: 1. Begin by checking V$SYSSTAT to see if parsing in general is a problem:
2. SELECT * FROM V$SYSSTAT 3. WHERE NAME IN ('parse time cpu', 'parse time elapsed', 'parse count (hard)'); The average wait time should be extremely low, approaching zero. (V$SYSSTAT also shows the average wait time per parse.) Query V$SQLAREA to find frequently reparsed statements:
SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA ORDER BY PARSE_CALLS; ...
View Full Document
This note was uploaded on 02/25/2010 for the course MAT 443 taught by Professor Roberts during the Winter '10 term at University of Arizona- Tucson.
- Winter '10