How to generate ASH report manually 03/26 Update SLTechnology News&Howtos

How to generate ASH report manually

2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

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

In this issue, the editor will bring you about how to generate the ASH report by hand. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

We have discussed the manual method of AWR report (http://space.itpub.net/17203031/viewspace-700471) before. With the timing snapshot function of AWR report, we can selectively select the time period system data for analysis, and finally obtain the comprehensive diagnosis. In addition to the AWR report, two tools, ASH and ADDM, have been introduced in the Oracle10g version for fine-grained analysis of the system and automation of expert optimization schemes. Among them, ASH (Active Session History) is a more fine-grained tool than AWR to analyze system sessions and find out where the front-end applications are blocked.

For Oracle databases, most requests occur in the form of session session. In general, the impact of a single session on system performance is very small, especially in OLTP systems. If the performance of the system is caused by the session, most of the important performance is that the front-end application is unresponsive and has no effective connection, and the system shows the information that the session executes SQL or waits for events. At the same time, there is a high consumption of specific Server Process.

When a session causes performance problems or high consumption, we can use v$session, v$session_wait and other views to locate the problem caused by the SQL statement of the session and deal with it in a timely manner. But in practice, it is rare to cause a blocked session. Once the session ends or is unterminated, the information will be removed from the v$session and v$session_wait. At this point, if you need to observe the performance, you need to use ASH (Active Session History) reports.

ASH is one of a series of performance monitoring reports introduced in Oracle 10g. The principle of ASH is similar to that of AWR in that it adopts the principle of performance image collection. It's just that ASH collects more frequently, about once a second, so that information about expensive sessions can be preserved as much as possible. The second is that in terms of collection scope, ASH does not collect as widely as AWR, but focuses on conversations, key SQL, and Top activities. Unlike AWR, which is saved for about a month by default, ASH saves for a relatively short period of time, ranging from a few days to weeks. However, it is sufficient for temporary performance analysis or critical business period analysis.

After Oracle10g, you can use OEM's visualization tools to generate ASH or view key Top information. But when the system does not have OEM to use or does not have OEM installed at all, it is a good way to generate ASH reports by hand. This article describes how to manually generate ASH reports as records.

1. Prepare the environment and call the execution script

In the AWR hand-generated article, the author chose to generate the report file on the Linux side of the Oracle server, and then use the FTP tool to download it to the local. This time, the client script is used for generation, and to ensure consistency, use the same client script as the server as much as possible.

Navigate to the appropriate directory in the command line tool, and then start the Sqlplus tool.

SQL*Plus: Release 10.2.0.1.0-Production on Saturday July 16 21:23:06 2011

SQL > conn sys/oracle@orcl as sysdba

Connected.

SQL >

SQL > select dbid, name,log_mode from v$database

DBID NAME LOG_MODE

1192098220 ORCL NOARCHIVELOG

The generation script ashrpt.sql is then called. If you are using the Sqlplus tool, you can use it directly. To refer to the local $ORACLE_HOME directory of Oracle.

SQL > @? / rdbms/admin/ashrpt.sql

If you are using tools such as PL/SQL Developer, you need to specify the directory location directly. The role of the signifier no longer exists.

After invoking the script, you need to enter associated parameter information.

2. Enter relevant parameters

ü input report type

Like AWR, ASH provides a choice of reports in both txt and html formats.

SQL > @? / rdbms/admin/ashrpt.sql

Current Instance

DB Id DB Name Inst Num Instance

1192098220 ORCL 1 orcl

Specify the Report Type

~ ~ ~

Enter 'html' for an HTML report, or' text' for plain text

Defaults to 'html'

Enter a value for report_type:

The first step of the script requires you to enter the type of report generated, which defaults to html. If you want to generate the txt format, type text.

Inputting analysis period information

ASH is a time analysis report that requires the user to enter the start point of the time range for analysis.

Type Specified: html

Instances in this Workload Repository schema

DB Id Inst Num DB Name Instance Host

* 1192098220 1 ORCL orcl WWW-0E6111DF

F74

Defaults to current database

Using database id: 1192098220

Defaults to current instance

Using instance number: 1

ASH Samples in this Workload Repository schema

Oldest ASH sample available: 13-July-11 19:32:48 [4435 mins in the past]

Latest ASH sample available: 16-July-11 21:25:54 [2 mins in the past]

Specify the timeframe. To generate the ASH report

~ ~ ~

Enter begin time for report:

-- Valid input formats:

-- To specify absolute begin time:

-- [MM/ DD [/ YY]] HH24:MI [: SS]

-- Examples: 02 Compact 23 Universe 03 14:30:15

-- 02Compare 23 14:30:15

-- 14:30:15

-- 14:30

-- To specify relative begin time: (start with'- 'sign)

-[HH24:] MI

-- Examples:-1:15 (SYSDATE-1 Hr 15 Mins)

-25 (SYSDATE-25 Mins)

Defaults to-15 mins

Enter a value for begin_time:

Because the granularity of ASH analysis is at the level of seconds, the time period entered is fine-grained in seconds. The time granularity entered by Begin_time can be done in two ways, depending on what is prompted in the script. One is the absolute start time (absolute begin time), including the year, month, day, hour, minute and second, where omitting part of the information appropriately means using the current corresponding information. The other is the relative start time (relative begin time), which starts with a "-" sign to indicate how long before the current time.

Enter a value for begin_time: 21:15:00

Report begin time specified: 21:15:00

Here we want to analyze the five-minute information that began at 21:15:00.

After entering the start time, you can define the analysis duration value.

/ / enter the duration in minutes

Enter duration in minutes starting from begin time:

Defaults to SYSDATE-begin_time

Press Enter to analyze till current time

Enter a value for duration:

Enter a value for duration: 5

Report duration specified: 5

Other relevant specified parameters

The analysis in the ASH report includes a lot of content, and sometimes we may want the analysis to be more targeted. For example, specific SQL sentence analysis, specific session information analysis and so on.

Using 16-July-11 21:15:00 as report begin time

Using 16-July-11 21:20:00 as report end time

Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section

-- Explanation:

-- In the 'Activity Over Time' section of the ASH report

-- the analysis period is divided into smaller slots

-- and top wait events are reported in each of those slots.

-- Default:

-- The analysis period will be automatically split upto 10 slots

-- complying to a minimum slot width of

-1 minute, if the source is V$ACTIVE_SESSION_HISTORY or

5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.

Specify Slot Width in seconds to use in the 'Activity Over Time' section:

Defaults to a value as explained above:

Slot Width specified:

Specify Report Targets (using ashrpti.sql) to generate the ASH report

-- Explanation:

-ASH Report can accept "Report Targets"

-- like a particular SQL statement, or a particular SESSION

-- to generate the report on. If one or more report targets are

-- specified, then the data used to generate the report will only be

-- the ASH samples that pertain to ALL the specified report targets.

-- Default:

-- If none of the report targets are specified

-- then the target defaults to all activity in the database instance.

Specify SESSION_ID (eg: from V$SESSION.SID) report target:

Defaults to NULL:

SESSION report target specified:

Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: