?? Efficient diagnosis of performance problems, need to provide complete statistics available, like the doctor to the patient's palpation, to be able to correctly diagnose, and then prescribe the corresponding prescription. The Oracle database generates multiple types of cumulative statistics for systems, sessions, and individual SQL statements. This paper mainly describes the related concepts and statistical objects of Oracle performance statistics, in order to make better use of statistical information to lay the foundation for performance adjustment.
A few terms of performance statistic information?? Performance statistics depend on the accumulation of delta values, or increment values. The increment value reflects the trend, or amplitude, of a particular event during a particular time period. By comparing the difference between the accumulated value and the accumulated value at the end of the statistic at the beginning, it is possible to understand more clearly the resource overhead required for this value in different load periods of the system and to locate performance bottlenecks.
?? In the Oracle database, you can query for the cumulative status of the statistics by querying v$sesstat and V$sysstat. Since these 2 views are dynamic performance views, the data for these 2 views is collected only from the statistics since the instance was started. So, after the database shut down all the statistical information is lost swollen, do not worry, there is awr to save regularly.
?? A metric is another statistic type that is collected by an Oracle database. A measure is defined as the rate of change in some cumulative statistics. The rate can be measured on a variety of units, including time, transaction, or database calls. For example, a database call per second is a metric. Measures can be obtained from dynamic performance view queries. Where the value is average at a fairly small time interval, typically 60 seconds. The history of recent measures is in the v$ view, while some data is persisted by the awr snapshot.
?? Oracle samples the related waits that occur during the active session, collects the data into memory, can be accessed using the v$ view, or generates reports for fast or real-time positioning of performance issues. That part is known as Ash, the activity session history. This data is also written to the persistent storage by the AWR snapshot processing.
?? There is data that can be compared to the true nature of the problem. So in the Oracle database, we can put the activity session history data of its peak business load, or, more specifically, the AWR persisted data label, this label is called the baseline. So baselines are a baseline report on the overall performance of a database during a particular period of time, and once the performance problems or underperformance of subsequent databases are performed, by using baselines to compare performance statistics during performance issues, the difference between the two is reported, helping to locate the problem.
Second, system-level performance statistics?? Basic performance statistics of the operating system provide information on the use and performance of the main hardware components of the system, as well as the performance of the operating system itself. This information is critical for detecting potential resource consumption, such as CPU cycles and physical memory, as well as detecting poor performance of peripherals such as disk drives. Operating system statistics are important indicators of hardware and operating system performance. Mainly includes the following important information.
?? CPU utilization is the most important operating system statistic in the tuning process. The utilization of a single CPU in the entire system and multiprocessor systems should be obtained. Per-CPU utilization can detect single-threaded and scalability issues. Most operating systems display CPU time overhead in user space or kernel space, and these additional statistics can be used to better analyze the actual performance of the CPU.
?? In an Oracle database system that runs only a single application, typically, the business system runs database activity in user space. Active requests that serve the database, such as scheduling, synchronization, I/O, memory management, process/thread creation, and deletion, run in kernel mode (space). In a CPU-fully utilized system, a healthy Oracle database is typically running between 65% and 95% of the CPU overhead in the user space.
?? You can capture information about hardware and operating system levels by querying the V$osstat view, making it easier to determine problems with hardware-level resources. The V$sysmetric_history View retains the CPU usage metrics for the host within one hour, expressed in per-minute intervals. The V$ Sys_time_model View provides the number of CPUs that are used by the Oracle database. These two sets of statistics are more able to determine whether an Oracle database or other system activity is the cause of a CPU problem.
?? Virtual memory statistics are primarily used to determine whether there is a large number of paging or switching activities on the system. When a large number of paging and switching, the system performance will quickly decline. Memory statistics for a single process can detect if a memory leak is caused by a process that has failed to release memory.
?? The performance of the I/O subsystem determines or affects the performance of the database. Most operating systems provide some extended statistics on the disk. The most important disk statistics are the current response time and the length of the disk queue. These statistics show whether the disk is in the best condition, or whether the disk is working overtime.
?? Measure the normal performance of the I/O system, depending on the hardware used, the typical value of a single block read range is from 5 to 20 milliseconds. If the hardware displays a response time that is much higher than the normal performance value, then it behaves poorly or is overloaded. This is the bottleneck. If the disk queue starts to exceed two, then the disk is a potential bottleneck system.
?? The Oracle database provides some I/O statistics for I/O calls. These statistics are obtained in the following views.
?? V$iostat_consumer_group:
???? After the resource plan is enabled, the view captures I/O-related information, which is the statistics for all resource consumption groups. Database sample statistics accumulate hourly and store them in the awr.
?? V$iostat_file:
???? This view is based on disk file I/O statistics and is used primarily to show file access at the file level.
?? V$iostat_function:
???? This view is statistical data based on I/O database features such as LGWR and DBWR.
?? Used to determine whether the network or network interface is overloaded or not optimally executed. In today's network applications, network latency can be a significant part of the actual user response time. For this reason, these statistics are a key debugging tool.
?? V$iostat_network:
???? This view provides network-related statistical information queries
Iii. statistical information of the database?? Database statistics provide information about the type of load on the database and the internal and external resources used by the database.
?? The wait event is a statistic that is incremented by the server process or thread, which means that it must wait for an event to finish before it can continue processing until the current task is processed. Waiting for event data reveals various symptoms of problems that may affect performance, such as latch contention, buffer contention, and I/O contention.
?? To better analyze the different wait events, Oracle waits for the events to be categorized. Wait event classifications include: Administration, Application, cluster, commit, concurrency, configuration, idle, network, other, dispatch, system I/O, and user I/O.
?? Author:leshami
?? Blog:http://blog.csdn.net/leshami
?? The following list includes common examples of waits in some classes:
?? ?? 1. Application: Lock waits caused by row-level locking or explicit lock commands
?? ?? 2. Commit: Wait for redo log write acknowledgement after commit
?? ?? 3. Idle: Indicates a wait event where the session is inactive, such as a sql*net message from the client
?? ?? 4. Network: Waiting for data to be sent over the network
?? ?? 5. User I/O: Waiting block is read disk
?? Based on time model statistics, the statistical information of the time spent in the database is mainly based on the operation type. The most important time model statistics are database time, that is, DB times. The database time represents the total amount of time spent in database calls, which is the total indicator of the workload for the instance.
?? In Oracle CNC, each component has its own statistical data. Therefore, this component is compared to the overall DB time, which can be a good measure of the performance of this component, or the component occupies a percentage of the entire DB time. If you can make adjustments based on these expensive components, the entire DB time overhead will be less.
DB Time:
?? That is, the sum of all calls spent at the database level, including all sessions (foreground process) and non-idle waits, CPU time, etc.
?? DB time is a cumulative measurement of the times since the instance was started. Because the DB time is calculated from the time combination of all non-idle user sessions, the DB time may exceed the actual time elapsed since the instance started. For example, an instance that has been running for 30 minutes may have four active user sessions with a cumulative db time of approximately 120 minutes.
More about the time statistics model can be consulted: Oracle time model Statistics
?? Oracle samples The current database active session every second and saves its last 10 wait information to v$session_wait, while archiving it to the active session history table, v$session_wait_history. This is the active session history (ASH). This data is retained in the specific memory allocated from the SGA and is written in a circular manner by taking the LRU algorithm to periodically clean up that part of the cache. To put it simply, Ash's strategy is to save information for the active session in the waiting state, sample from V$session_wait per second, and save the sampled information in memory. Because the active session information in the SGA is stored in a circular buffer, the greater the system activity, the smaller the number of seconds that can be stored in the loop buffer. So this data needs to be written to disk periodically, and that is, the Mmon process periodically writes part of the data to the AWR. The policy used by AWR is to sample the V$active_session_history once per hour and save the information to disk for 8 days (11g default) and 8 days before the old record is overwritten. These sampling information is saved in the view wrh$_active_session_history. This sampling frequency (1 hours) and retention time (8 days) can be adjusted according to the actual situation.
?? The active session history typically includes the following data:
???? SQL statements and Sqlid
???? SQL plan identifier and hash value for SQL plan execution of SQL statements
???? SQL Execution Plan Information
???? Object number, file number, and block number
???? Wait for event identifiers and parameters
???? Conversation identifier and session sequence number
???? Module and action name
???? Client identifier of the session
???? Service Hash identifier
???? Consumer group identifiers
?? A large number of system-level and session-level statistics can be obtained from the V$sysstat and V$sesstat two views.
Oracle Automatic Performance statistics
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
