Shulou(Shulou.com)06/01 Report--

The real-time monitoring of various states during the operation of the database and the capture of related performance data are very important to solve the performance problems and improve the running efficiency of the overall business system. In Oracle database, the real-time capture of relevant performance data is realized by ASH tools. By taking samples of active sessions every second, ASH provides the most direct and effective basis for analyzing performance problems at the most recent moment. This article mainly talks about the usage and use of ASH.

I. Overview of the history of active conversations

The Oracle v$active_session_history view provides a sample of the active session in the instance. The most detailed and complete performance data provided by this view can be used as first-hand evidence for locating performance faults. Any session that is not part of the idle waiting class is considered active when connected to the database. This includes any session on the CPU at the time of sampling.

The active session sample is stored in a circular buffer in SGA. As system activity increases, the number of seconds of session activity that can be stored in a circular buffer decreases. The time of the session sample is kept in the v$ view. The number of seconds of session activity shown in the v$ view is entirely dependent on database activity.

When an automatic workload information base (AWR) snapshot is created, the contents of the dynamic performance view v$active_session_history are flushed to disk. By capturing only the active session, it represents a set of manageable data whose size is directly related to the work being performed, not the number of sessions allowed on the system.

The data dictionary dba_hist_active_sess_history is the historical data of the view v$active_session_history. By default, the dba_hist_active_sess_history view collects information every ten seconds and stores it on disk. In other words, the data eventually saved to disk is 1x10 of the amount collected in real time. Accordingly, the data available for diagnosing performance is not as detailed and rich as v$active_session_history.

Active session data flow:

?? V$session (ASH Buffer)-> dba_hist_active_sess_history (AWR repository)

A sample of active session history usually includes the following:

The SQL identifier of the SQL statement

?? Object number, file number and block number

?? Wait for event identifiers and parameters

?? Session identifier and session serial number

?? Module and Action name

?? Client identifier of the session

?? Service hash identifier

?? Blocking session

II. Historical logic architecture diagram of active session

As shown in the figure above, ASH takes information samples from V$SESSION. Take a sample per second and read the specific structure data used by Oracle directly instead of using SQL, so this approach is more efficient.

ASH is designed as a scrolling buffer in memory, and previous information is overwritten when needed. Because the amount of data in the ASH buffer can be very large, it is unacceptable to flush it all to disk. A more efficient approach is to filter historical data and refresh it to the workload repository. This is done automatically through the manageability Monitor (MMON) process every 60 minutes and through the MMNL process whenever the buffer is full.

Note: ASH's memory comes from the system global zone (SGA), which is fixed during the life of the instance. It represents memory for each CPU 2 MB. The ASH cannot exceed 5% of the shared pool size, or 5% of the SGA_TARGET.

SQL III. ASM sampling example

As mentioned earlier, ASH represents the history of recent activities. The figure shows how to sample a session when it is active. Every second, the Oracle database server looks at the active sessions and records the events that those sessions are waiting for. Inactive sessions are not sampled. Sampling is very efficient because it directly accesses the internal structure of the Oracle database.

As shown in the figure above, the active session 1 Wait I Wait O and Wait Block are recorded in the v$active_session_history view.

Access to active session data

Check the current active session history: v$active_session_history

Check the active session history data: dba_hist_active_sess_history

Generate ASH report

Through the OEM diagnostic package performance page 5, generate ASH report SQL > @? / rdbms/admin/ashrpt.sqlCurrent Instance~ DB Id DB Name Inst Num Instance 6, ASH report structure

The ASH report structure, as shown in the following figure:

Top Events:

?? Parameter values for reported user events, backgrounds, events, and events

Load Profile:

?? Report top-level services / modules, top-level clients, identify SQL commands and execute SQL types

Top SQL:

?? Report SQL statements related to primary events, SQL related to rowsources, full SQL statements, SQL statements binding variables using

Top PL/SQL Procedures:

?? Listed PL/SQL programs, the highest percentage of sampling session activity

Top Java Workload:

?? Describes the active java program during the sampling session

Top Sessions:

?? Primary session, blocking session and parallel related session

Top Objects/Files/Latches:

?? Report objects, files, or latches

Activity Over Time:

?? The first three waiting events are reported for 10 time reporting periods of the same size, and the report allows you to see very detailed activities at the last minute.

7. ASH report analysis 1. Header information:

Note: DataSource can have two sources, one is V$ACTIVE_SESSION_HISTORY, the other is DBA_HIST_ACTIVE_SESS_HISTORY2, the primary wait event

The primary waiting event part describes the primary waiting events generated by the user, background, etc. in the sampled session activity, which means that these events are the source of performance problems during the sampling period.

The primary wait event consists of the following sections:

(1) Top User Events primary user event

The primary user event, also known as the foreground wait event, shows that the user process wait event accounts for a high percentage of the sampling session activity.

(2) the primary background event of Top Background Events

This information shows the background process wait events that account for a high percentage of sampled session activity.

(3) Top Event P1/P2/P3 Values first waits for the event parameter P1/P2/P3

This information shows the parameter values of the wait events, which account for a high percentage of the sampled session activity, which are sorted by the percentage of the total wait time (% Event). For each waiting event p1, the value of p2, p3, and the waiting event parameter parameter 1, the waiting parameter 2, the waiting parameter 3, are associated with the three columns, namely, file number, block number, and set-id#

As shown in the above figure, the main events in the current database are

Free buffer waits

?? The server process scans the LRU list for free buffers (for example, when reading blocks from disk, or constructing consistent read Cr blocks, etc.). After scanning to a threshold, if the server process cannot find the available buffer, it asks DBWR to write the dirty buffer from the LRU list to disk and wait until the buffer is released. The wait before DBWR writes out a dirty buffer to release is called free buffer waits. There are usually two main situations that lead to free buffer waits, one is that DBWR is not fast enough to write dirty blocks, and the other is that the Buffer cache is too small.

Write complete waits

While DBWR records the dirty buffer to disk and occupies the buffer lock for the buffer in exclusive mode, other processes reading or modifying the buffer need to wait for the work to finish and wait for the write complete waits event.

Buffer busy waits

?? The buffer is busy waiting, and the two main situations in which this event occurs are: another session is reading a block into the buffer, and another session holds the buffer we requested in an incompatible manner. 3 、 Load Profile

4 、 Top SQL

5. Complete SQL list

6. The first conversation

7. primary object, file, bolt

8. Time-sharing activities

This section will show the active waiting events according to different time segments during the reporting period.

As shown in the figure above, the activity over time is divided into eight periods, and the first three wait events appear in each period.

As you can see from the figure above, basically, there are three waiting events related to buffer during the whole sampling period. There is no obvious sharp wave in% event.

The following is a description of each column

Column description

Duration of the slot time (duration) period

The number of sessions sampled by solt count in the period

Event's top three wait events in the time period

The number of waiting events that were sampled by event count ash

% percentage of waiting events sampled by event ash during the entire analysis period 9. Preliminary conclusions of the report

The main results are as follows: 1) during the whole sampling period, OLTP is characterized by a large number of DML operations.

2) the primary wait event is Buffer-related, so it is easy to think of adding Buffer size, but it can be seen from the report header that Buffer only occupies 23.5%

3) for primary wait events, DBWR may be adjusted and optimized, such as increasing the number of DBWn processes, speeding up writes, and optimizing disk IO

4) optimize checkpoints to speed up data writing to disk

5) optimize SQL to reduce excessive IO load, and you can also consider optimizing the package and stored procedure where SQL is located.

6) partitioning and index separation of hot objects, reverse index design, etc.

Transfer to: http://blog.csdn.net/leshami/article/details/73526881

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.