Oracle Instance Tuning (Part 1)
Alireza Kamrani04/25/2025
🔵What is your strategies to discover and resolving performance issues on your database and any layers related to it?
🟠What are essential knowledges that you have know about steps to investigate and analysis performance issue for a Oracle instance?
🟡In this topic you can find a best practice to handling performance issue (How to discover, How to analysis, How to resolve them).
****************************
Instance Tuning Using Performance Views
After the initial configuration of a database, monitoring and tuning an instance regularly is important to eliminate any potential performance bottlenecks. This topic discusses the tuning process using Oracle V$ performance views.
🟪 I try to covering this target by explaining following sections in some continuous parts.
• Instance Tuning Steps
• Interpreting Oracle Database Statistics
• Wait Events Statistics
• Tuning Instance Recovery Performance: Fast-Start Fault Recovery
Instance Tuning Steps
These are the main steps in the Oracle performance method for instance tuning:
• Define the Problem
Get candid feedback from users about the scope of the performance problem.
• Examine the Host System and Examine the Oracle Database Statistics
• After obtaining a full set of operating system, database, and application statistics, examine the data for any evidence of performance problems.
• Consider the list of common performance errors to see whether the data gathered suggests that they are contributing to the problem.
• Build a conceptual model of what is happening on the system using the performance data gathered.
• Implement and Measure Change
Propose changes to be made and the expected result of implementing the changes. Then, implement the changes and measure application performance.
• Determine whether the performance objective defined in step 1 has been met. If not, then repeat steps 2 and 3 until the performance goals are met.
The remainder of this chapter discusses instance tuning using the Oracle Database dynamic performance views. However, Oracle recommends using Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) for statistics gathering, monitoring, and tuning due to the extended feature list.
Note:
If your site does not have AWR and ADDM features, then you can use Statspack to gather Oracle database instance statistics.
Define the Problem
It is vital to develop a good understanding of the purpose of the tuning exercise and the nature of the problem before attempting to implement a solution. Without this understanding, it is virtually impossible to implement effective changes. The data gathered during this stage helps determine the next step to take and what evidence to examine.
Gather the following data:
• Identify the performance objective.
What is the measure of acceptable performance? How many transactions an hour, or seconds, response time will meet the required performance level?
• Identify the scope of the problem.
What is affected by the slowdown? For example, is the whole instance slow? Is it a particular application, program, specific operation, or a single user?
• Identify the time frame when the problem occurs.
Is the problem only evident during peak hours? Does performance deteriorate over the course of the day? Was the slowdown gradual (over the space of months or weeks) or sudden?
• Quantify the slowdown.
This helps identify the extent of the problem and also acts as a measure for comparison when deciding whether changes implemented to fix the problem have actually made an improvement. Find a consistently reproducible measure of the response time or job run time. How much worse are the timings than when the program was running well?
• Identify any changes.
Identify what has changed since performance was acceptable. This may narrow the potential cause quickly. For example, has the operating system software, hardware, application software, or Oracle Database release been upgraded? Has more data been loaded into the system, or has the data volume or user population grown?
At the end of this phase, you should have a good understanding of the symptoms. If the symptoms can be identified as local to a program or set of programs, then the problem is handled in a different manner from instance-wide performance issues.
Examine the Host System
Look at the load on the database server and the database instance. Consider the operating system, the I/O subsystem, and network statistics, because examining these areas helps determine what might be worth further investigation. In multitier systems, also examine the application server middle-tier hosts.
Examining the host hardware often gives a strong indication of the bottleneck in the system. This determines which Oracle Database performance data could be useful for cross-reference and further diagnosis.
Data to examine includes the following:
• CPU Usage
• Identifying I/O Problems
• Identifying Network Issues
CPU Usage
If there is a significant amount of idle CPU, then there could be an I/O, application, or database bottleneck. Note that wait I/O should be considered as idle CPU.
If there is high CPU usage, then determine whether the CPU is being used effectively. Is the majority of CPU usage attributable to a small number of high-CPU using programs, or is the CPU consumed by an evenly distributed workload?
If a small number of high-usage programs use the CPU, then look at the programs to determine the cause. Check whether some processes alone consume the full power of one CPU. Depending on the process, this could indicate a CPU or process-bound workload that can be tackled by dividing or parallelizing process activity.
Non-Oracle Processes
If the programs are not Oracle programs, then identify whether they are legitimately requiring that amount of CPU. If so, determine whether their execution be delayed to off-peak hours. Identifying these CPU intensive processes can also help narrowing what specific activity, such as I/O, network, and paging, is consuming resources and how can it be related to the database workload.
Oracle Processes
If a small number of Oracle processes consumes most of the CPU resources, then use SQL_TRACE and TKPROF to identify the SQL or PL/SQL statements to see if a particular query or PL/SQL program unit can be tuned. For example, a SELECT statement could be CPU-intensive if its execution involves many reads of data in cache (logical reads) that could be avoided with better SQL optimization.
Oracle Database CPU Statistics
Oracle Database CPU statistics are available in several V$ views:
• V$SYSSTAT shows Oracle Database CPU usage for all sessions. The CPU used by this session statistic shows the aggregate CPU used by all sessions. The parse time cpu statistic shows the total CPU time used for parsing.
• V$SESSTAT shows Oracle Database CPU usage for each session. Use this view to determine which particular session is using the most CPU.
• V$RSRC_CONSUMER_GROUP shows CPU utilization statistics for each consumer group when the Oracle Database Resource Manager is running.
Interpreting CPU Statistics
It is important to recognize that CPU time and real time are distinct. With eight CPUs, for any given minute in real time, there are eight minutes of CPU time available. On Windows and UNIX, this can be either user time or system time (privileged mode on Windows). Thus, average CPU time utilized by all processes (threads) on the system could be greater than one minute for every one minute real time interval.
At any given moment, you know how much time Oracle Database has used on the system. So, if eight minutes are available and Oracle Database uses four minutes of that time, then you know that 50% of all CPU time is used by Oracle. If your process is not consuming that time, then some other process is. Identify the processes that are using CPU time, figure out why, and then attempt to tune them.
If the CPU usage is evenly distributed over many Oracle server processes, examine the V$SYS_TIME_MODEL view to help get a precise understanding of where most time is spent.
No comments:
Post a Comment