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

This article will explain in detail what are the characteristics of ORACLE AWR and ASH, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The Story of ASH and AWR 1.1 about ASH

As we all know, when users perform operations in the ORACLE database, they must create corresponding connections and sessions, where all the current session information is stored in the dynamic performance view V$SESSION, through which DBA can view the actual actions performed by the user, or the current wait events, and so on. Usually this information is the key information in the tuning process, but once the connection is disconnected. The session information is removed from the V$SESSION and other related views at the same time, that is, the user executes the operation and leaves, and you (DBA), if you can't catch him right now, have no idea what it has done.

In the 10g version, ORACLE has added a new view (the others are several, but we still use session as the introduction): V$ACTIVE_SESSION_HISTORY, look at the name, it is the history of the active session, so even if the user operation is completed, he is not afraid to disconnect, because the situation of the session has been recorded, this feature is ASH, the full name is the same as the view name, that is: ACTIVE SESSION HISTORY.

Once a second, ASH collects information about currently non-idle waiting events, active, and session, and saves it in the V$ACTIVE_SESSION_HISTORY view. We (should and must) know that the dynamic performance view is actually a pile of virtual tables that exist in the SGA memory area constructed by ORACLE itself, that is, the ASH data is stored in memory, in fact. The space allocated by ORACLE to ASH is not infinite (not to mention the memory space managed by ORACLE itself is not infinite). To view the memory space available to ASH, you can use the following SQL:

SQL > select pool, name, bytes/1024/1024 Mb From v$sgastat where name like 'ASH%'

POOL NAME Mb

Shared pool ASH buffers 15.5

To put it bluntly, how much session information can be recorded in V$ACTIVE_SESSION_HISTORY depends on, on the one hand, the size of the database's SGA allocated to ASH buffers, and on the other hand, on the startup and shutdown of the database (the SGA memory area will be reconstructed when the database is restarted). These two factors restrict the ability of the conversation information that can be saved in V$ACTIVE_SESSION_HISTORY. As DBA, we certainly want ASH to retain as much information about the session as possible, but at present, it is impossible to achieve this by relying solely on V$ACTIVE_SESSION_HISTORY, so what should we do? Don't worry, ORACLE also provides AWR features, and the session information collected by ASH is saved to the hard disk as part of the snapshot information in AWR.

1.2 about AWR

AWR is a new feature of Oracle 10g version, which is called Automatic Workload Repository- automatic load Information Base. When it comes to this feature, we have to mention that Statspack,Statspack is an old man in the ORACLE world, but after all, he is old, and although his legs and feet are still flexible, he is not so efficient. In fashionable terms, he cannot adapt to the pace of development of the times, cannot keep up with the trend of the times, cannot maintain his advanced nature as an outstanding party member, and always insists on serving DBA with three watches. However, in the final analysis, Statspack still strives for the cause of the party and state all his life, and there are still powerful, prestigious and influential old people in the party, and there are still remaining heat to play. Although in terms of statistical real-time performance, it is no longer enough to meet the ever-changing needs of DBA, and still can not be taken down at once, so ORACLE adopted a gradual approach to launch AWR first, which is said to assist Statspack work. In fact, it is clear to the discerning eye. This is the newly appointed successor.

Tip:

For the study and use of Statspack, please refer to the relevant chapters of Sansi Notes: http://www.5ienet.com/note/html/stat/index.shtml.

AWR is the same as its predecessor Statspack in terms of career positioning, both are responsible for collecting, processing and maintaining performance statistics for checking and analyzing performance problems (even the format of the reports generated are very similar). The statistics generated by AWR can be viewed through the V$ view and DBA_* data dictionary, or the corresponding reports can be generated through scripts. When it comes to what aspects have been improved compared to Statspack,AWR, there are two main points: automatic + real-time, as for the slight improvement in function, it is just because AWR is young and sharp.

Tip:

In the 10g version, ORACLE pays more attention to using EM (Enterprise Manager) to manage various operations, but considering various factors such as performance and versatility, it is still mainly through script operations to use the functions of AWR and ASH.

So much for sharing about the characteristics of ORACLE AWR and ASH. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

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.