Alireza Kamrani
🔵 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).
****** Part 2 ******
Identifying I/O Problems
An overly active I/O system can be evidenced by disk queue lengths greater than two, or disk service times that are over 20-30ms. If the I/O system is overly active, then check for potential hot spots that could benefit from distributing the I/O across more disks. Also identify whether the load can be reduced by lowering the resource requirements of the programs using those resources. If the I/O problems are caused by Oracle Database, then I/O tuning can begin. If Oracle Database is not consuming the available I/O resources, then identify the process that is using up the I/O. Determine why the process is using up the I/O, and then tune this process.
I/O problems can be identified using V$ views in Oracle Database and monitoring tools in the operating system, as described in the following sections:
• Identifying I/O Problems Using V$ Views
• Identifying I/O Problems Using Operating System Monitoring Tools
Identifying I/O Problems Using V$ Views
Check the Oracle wait event data in V$SYSTEM_EVENT to see whether the top wait events are I/O related. I/O related events include db file sequential read, db file scattered read, db file single write, db file parallel write, and log file parallel write. These are all events corresponding to I/Os performed against data files and log files. If any of these wait events correspond to high average time, then investigate the I/O contention.
Cross reference the host I/O system data with the I/O sections in the Automatic Repository report to identify hot data files and tablespaces. Also compare the I/O times reported by the operating system with the times reported by Oracle Database to see if they are consistent.
An I/O problem can also manifest itself with non-I/O related wait events. For example, the difficulty in finding a free buffer in the buffer cache or high wait times for logs to be flushed to disk can also be symptoms of an I/O problem.
Before investigating whether the I/O system should be reconfigured, determine if the load on the I/O system can be reduced.
To reduce I/O load caused by Oracle Database, examine the I/O statistics collected for all I/O calls made by the database using the following views:
• V$IOSTAT_CONSUMER_GROUP
The V$IOSTAT_CONSUMER_GROUP view captures I/O statistics for consumer groups. If Oracle Database Resource Manager is enabled, I/O statistics for all consumer groups that are part of the currently enabled resource plan are captured.
• V$IOSTAT_FILE
The V$IOSTAT_FILE view captures I/O statistics of database files that are or have been accessed.
🔷️The SMALL_SYNC_READ_LATENCY column displays the latency for single block synchronous reads (in milliseconds), which translates directly to the amount of time that clients need to wait before moving onto the next operation.
This defines the responsiveness of the storage subsystem based on the current load. If there is a high latency for critical data files, you may want to consider relocating these files to improve their service time.
To calculate latency statistics, timed_statistics must be set to TRUE.
TIMED_STATISTICS specifies whether statistics related to time are collected.
• true
The statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views.
• false
The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system.
Starting with release 11.1.0.7.0, the value of the TIMED_STATISTICS parameter cannot be set to false if the value of STATISTICS_LEVEL is set to TYPICAL or ALL.
On some systems with very fast timer access, Oracle might enable timing even if this parameter is set to false. On these systems, setting the parameter to true can sometimes produce more accurate statistics for long-running operations.
• V$IOSTAT_FUNCTION
The V$IOSTAT_FUNCTION view captures I/O statistics for database functions (such as the LGWR and DBWR).
🔶️An I/O can be issued by various Oracle processes with different functionalities. The top database functions are classified in the V$IOSTAT_FUNCTION view. In cases when there is a conflict of I/O functions, the I/O is placed in the bucket with the lower FUNCTION_ID.
🔶️For example, if XDB issues an I/O from the buffer cache, the I/O would be classified as an XDB I/O because it has a lower FUNCTION_ID value. Any unclassified function is placed in the Others bucket. You can display the FUNCTION_ID hierarchy by querying the V$IOSTAT_FUNCTION view:
select FUNCTION_ID, FUNCTION_NAME
from v$iostat_function
order by FUNCTION_ID;
FUNCTION_ID FUNCTION_NAME
----------- ------------------
0 RMAN
1 DBWR
2 LGWR
3 ARCH
4 XDB
5 Streams AQ
6 Data Pump
7 Recovery
8 Buffer Cache Reads
9 Direct Reads
10 Direct Writes
11 Others
🔶️These V$IOSTAT views contains I/O statistics for both single and multi block read and write operations. Single block operations are small I/Os that are less than or equal to 128 kilobytes. Multi block operations are large I/Os that are greater than 128 kilobytes. For each of these operations, the following statistics are collected:
• Identifier
• Total wait time (in milliseconds)
• Number of waits executed (for consumer groups and functions)
• Number of requests for each operation
• Number of single and multi block bytes read
• Number of single and multi block bytes written
You should also look at SQL statements that perform many physical reads by querying the V$SQLAREA view, or by reviewing the "SQL ordered by Reads" section of the Automatic Workload Repository report. Examine these statements to see how they can be tuned to reduce the number of I/Os.
To find more information you can search about the views V$IOSTAT_CONSUMER_GROUP, V$IOSTAT_FUNCTION, V$IOSTAT_FILE, and V$SQLAREA.
Identifying I/O Problems Using Operating System Monitoring Tools
Use operating system monitoring tools to determine what processes are running on the system as a whole and to monitor disk access to all files. Remember that disks holding data files and redo log files can also hold files that are not related to Oracle Database. Reduce any heavy access to disks that contain database files. You can monitor access to non-database files only through operating system facilities, rather than through the V$ views.
Utilities, such as sar -d (or iostat) on many UNIX systems and the administrative performance monitoring tool on Windows systems, examine I/O statistics for the entire system.
Identifying Network Issues
Using operating system utilities, look at the network round-trip ping time and the number of collisions. If the network is causing large delays in response time, then investigate possible causes.
To identify network I/O caused by remote access of database files, examine the V$IOSTAT_NETWORK view. This view contains network I/O statistics caused by accessing files on a remote database instance, including:
• Database client initiating the network I/O (such as RMAN and PLSQL)
• Number of read and write operations issued
• Number of kilobytes read and written
• Total wait time in milliseconds for read operations
• Total wait in milliseconds for write operations
After the cause of the network issue is identified, network load can be reduced by scheduling large data transfers to off-peak times, or by coding applications to batch requests to remote hosts, rather than accessing remote hosts once (or more) for one request.
Examine the Oracle Database Statistics
Examine Oracle Database statistics and cross-reference them with operating system statistics to ensure a consistent diagnosis of the problem. Operating system statistics can indicate a good place to begin tuning. However, if the goal is to tune the Oracle database instance, then look at the Oracle Database statistics to identify the resource bottleneck from a database perspective before implementing corrective action.
This section contains the following topics.
• Setting the Level of Statistics Collection
• Wait Events
• Dynamic Performance Views Containing Wait Event Statistics
• System Statistics
• Segment-Level Statistics
Setting the Level of Statistics Collection
Oracle Database provides the initialization parameter STATISTICS_LEVEL, which controls all major statistics collections or advisories in the database. This parameter sets the statistics collection level for the database.
Depending on the setting of STATISTICS_LEVEL, certain advisories or statistics are collected, as follows:
• BASIC: No advisories or statistics are collected. Monitoring and many automatic features are disabled. Oracle does not recommend this setting because it disables important Oracle Database features.
• TYPICAL: This is the default value and ensures collection for all major statistics while providing best overall database performance. This setting should be adequate for most environments.
• ALL: All of the advisories or statistics that are collected with the TYPICAL setting are included, plus timed operating system statistics and row source execution statistics.
No comments:
Post a Comment