ASH及ASH报告
V$ACTIVE_SESSION_HISTORY数据字典的解释
V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait
classes.
This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.
V$ACTIVE_SESSION_HISTORY在数据库中显示采样的会话活动。 它包含一秒钟执行的活动数据库会话的快照。 数据库会话被认为是活动的，如果它在CPU上或正在等待不属于空闲等待类的事件。 有关等待类的更多信息，请参阅V$EVENT_NAME视图。
此视图包含每个样本每个活动会话的一行，并返回最新的会话示例行。 描述活动会话历史中的会话的大多数列存在于V$SESSION视图中。
ASH的官方文档解释
The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.
Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.
As part of the AWR snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Because the content of this V$ view can get quite large during heavy system activity, only a portion of the session samples is written to disk.
By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed rather than the number of sessions allowed on the system. Using ASH enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. ASH also contains execution plan information for each captured SQL statement. You can use this information to identify which part of SQL execution contributed most to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:
SQL identifier of SQL statement
SQL plan identifier and hash value of the SQL plan used to execute the SQL statement
SQL execution plan information
Object number, file number, and block number
Wait event identifier and parameters
Session identifier and session serial number
Module and action name
Client identifier of the session
Service hash identifier
Consumer group identifier
V$ACTIVE_SESSION_HISTORY视图提供了实例中的抽样会话活动。活动会话每秒采样一次，并存储在SGA中的循环缓冲区中。连接到数据库并等待不属于空闲等待类的事件的任何会话都被视为活动会话。这包括在采样时在CPU上的任何会话。
每个会话示例是一组行，V$ACTIVE_SESSION_HISTORY视图为每个样本的每个活动会话返回一行，首先返回最新的会话示例行。因为活动会话样本存储在SGA中的循环缓冲区中，系统活动越大，可以存储在循环缓冲区中的会话活动的秒数越小。这意味着在V$视图中显示会话示例的持续时间，或V$视图中显示的会话活动的秒数完全取决于数据库活动。
作为AWR快照的一部分，V$ACTIVE_SESSION_HISTORY的内容也被刷新到磁盘。因为在繁重的系统活动期间，这个V$视图的内容可能会变得相当大，所以只有一部分会话样本被写入磁盘。
通过仅捕获活动会话，可管理的一组数据以与正在执行的工作直接相关的大小而不是系统允许的会话数来表示。通过使用ASH，您可以检查并对V$ ACTIVE_SESSION_HISTORY视图中的当前数据和DBA_HIST_ACTIVE_SESS_HISTORY视图中的历史数据进行详细分析，通常不需要重播工作负载来收集其他性能跟踪信息。 ASH还包含每个捕获的SQL语句的执行计划信息。您可以使用此信息来确定哪些部分的SQL执行对SQL消耗的时间贡献最大。 ASH中存在的数据可以按照其捕获的各种维度进行汇总，包括以下内容：
SQL语句的SQL标识符
用于执行SQL语句的SQL计划的SQL计划标识符和哈希值
SQL执行计划信息
对象号，文件号和块号
等待事件标识符和参数
会话标识符和会话序列号
模块和动作名称
会话的客户端标识符
服务哈希标识符
消费者组标识符
个人理解：
ASH以V$SESSION为基础，记录活动会话等待的事件，不活动的会话不会采样。每秒(默认,受隐含参数_ash_sampling_interval影响)对活动会话进行一次抽样采集，采集到的信息储存于SGA中即对应v$active_session_history视图的信息。ASH信息量的多少，保留时间的长短都与数据库的活跃性相关。而分配给ASH的内存空间是有限的，当所分配空间占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。这样，对于长期检测oracle的性能是不可能的。所以Oracle提供了长期保留ASH信息的方法，这就是AWR，不过ASH全部写入AWR是不可接受的，据说只写入收集的10%的数据量，写AWR由后台进程MMON、MMNL共同完成。
ASH其实就是v$active_session_history中数据，实验过v$active_session_history中数据每时每刻都不一样，且重启后之前的数据都没有了
ASH报告的数据不是来自snapshot，但是时间期间可以跨越历史snapshot的多天期限
V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME的时间可以是好几天以前，V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME只要对应的V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME.SESSION_ID还在V$SESSION中
ASH报告的生成方法
ASH报告生成方法为SQL>@?/rdbms/admin/ashrpt.sql，begin time可以比V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME的时间还早，比如V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME最早一个是3天前，begin time可以指向4天前-96:30，持续时间到现在,也就是4天内的数据一样可以收集到且有效，而不是说只能收集到V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME范围的3天内的数据，3天前到4天前之间的数据是空白，4天内的报告和3天内的报告是一样。
V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn't belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait
classes.
This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.
V$ACTIVE_SESSION_HISTORY在数据库中显示采样的会话活动。 它包含一秒钟执行的活动数据库会话的快照。 数据库会话被认为是活动的，如果它在CPU上或正在等待不属于空闲等待类的事件。 有关等待类的更多信息，请参阅V$EVENT_NAME视图。
此视图包含每个样本每个活动会话的一行，并返回最新的会话示例行。 描述活动会话历史中的会话的大多数列存在于V$SESSION视图中。
ASH的官方文档解释
The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.
Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.
As part of the AWR snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Because the content of this V$ view can get quite large during heavy system activity, only a portion of the session samples is written to disk.
By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed rather than the number of sessions allowed on the system. Using ASH enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. ASH also contains execution plan information for each captured SQL statement. You can use this information to identify which part of SQL execution contributed most to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:
SQL identifier of SQL statement
SQL plan identifier and hash value of the SQL plan used to execute the SQL statement
SQL execution plan information
Object number, file number, and block number
Wait event identifier and parameters
Session identifier and session serial number
Module and action name
Client identifier of the session
Service hash identifier
Consumer group identifier
V$ACTIVE_SESSION_HISTORY视图提供了实例中的抽样会话活动。活动会话每秒采样一次，并存储在SGA中的循环缓冲区中。连接到数据库并等待不属于空闲等待类的事件的任何会话都被视为活动会话。这包括在采样时在CPU上的任何会话。
每个会话示例是一组行，V$ACTIVE_SESSION_HISTORY视图为每个样本的每个活动会话返回一行，首先返回最新的会话示例行。因为活动会话样本存储在SGA中的循环缓冲区中，系统活动越大，可以存储在循环缓冲区中的会话活动的秒数越小。这意味着在V$视图中显示会话示例的持续时间，或V$视图中显示的会话活动的秒数完全取决于数据库活动。
作为AWR快照的一部分，V$ACTIVE_SESSION_HISTORY的内容也被刷新到磁盘。因为在繁重的系统活动期间，这个V$视图的内容可能会变得相当大，所以只有一部分会话样本被写入磁盘。
通过仅捕获活动会话，可管理的一组数据以与正在执行的工作直接相关的大小而不是系统允许的会话数来表示。通过使用ASH，您可以检查并对V$ ACTIVE_SESSION_HISTORY视图中的当前数据和DBA_HIST_ACTIVE_SESS_HISTORY视图中的历史数据进行详细分析，通常不需要重播工作负载来收集其他性能跟踪信息。 ASH还包含每个捕获的SQL语句的执行计划信息。您可以使用此信息来确定哪些部分的SQL执行对SQL消耗的时间贡献最大。 ASH中存在的数据可以按照其捕获的各种维度进行汇总，包括以下内容：
SQL语句的SQL标识符
用于执行SQL语句的SQL计划的SQL计划标识符和哈希值
SQL执行计划信息
对象号，文件号和块号
等待事件标识符和参数
会话标识符和会话序列号
模块和动作名称
会话的客户端标识符
服务哈希标识符
消费者组标识符
个人理解：
ASH以V$SESSION为基础，记录活动会话等待的事件，不活动的会话不会采样。每秒(默认,受隐含参数_ash_sampling_interval影响)对活动会话进行一次抽样采集，采集到的信息储存于SGA中即对应v$active_session_history视图的信息。ASH信息量的多少，保留时间的长短都与数据库的活跃性相关。而分配给ASH的内存空间是有限的，当所分配空间占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。这样，对于长期检测oracle的性能是不可能的。所以Oracle提供了长期保留ASH信息的方法，这就是AWR，不过ASH全部写入AWR是不可接受的，据说只写入收集的10%的数据量，写AWR由后台进程MMON、MMNL共同完成。
ASH其实就是v$active_session_history中数据，实验过v$active_session_history中数据每时每刻都不一样，且重启后之前的数据都没有了
ASH报告的数据不是来自snapshot，但是时间期间可以跨越历史snapshot的多天期限
V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME的时间可以是好几天以前，V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME只要对应的V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME.SESSION_ID还在V$SESSION中
ASH报告的生成方法
ASH报告生成方法为SQL>@?/rdbms/admin/ashrpt.sql，begin time可以比V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME的时间还早，比如V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME最早一个是3天前，begin time可以指向4天前-96:30，持续时间到现在,也就是4天内的数据一样可以收集到且有效，而不是说只能收集到V$ACTIVE_SESSION_HISTORY.SAMPLE_TIME范围的3天内的数据，3天前到4天前之间的数据是空白，4天内的报告和3天内的报告是一样。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2146029/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
ADDM报告
请登录后发表评论
登录
全部评论
