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

Tuning through case study-- Oracle ASH

Application environment:

Operating system: RedHat EL55

Oracle: Oracle 10gR2

Case scenario:

SCOTT users do transaction processing, transaction has been completed!

16:42:14 SYS@ prod > conn scott/tigerConnected.16:42:20 SCOTT@ prod > begin16:42:23 2 for i in 1.. 1000000 loop16:42:27 3 execute immediate 'insert into T1 values (' | | I | |')'; 16:42:32 4 end loop;16:42:36 5 end;16:42:38 6 / PL/SQL procedure successfully completed.Elapsed: 008 SCOTT@ prod 35.7716 SCOTT@ prod > commit;Commit complete.

Generate an ASH report:

16:55:43 SYS@ prod > @? / rdbms/admin/ashrpt

Current Instance~ DB Id DB Name Inst Num Instance- 199802235 PROD 1 prodElapsed: 00:00:00.07Specify the Report Type~~~Enter 'html' for an HTML report Or 'text' for plain textDefaults to' html'Enter value for report_type: htmlType Specified: htmlInstances in this Workload Repository schema~~~~ DB Id Inst Num DB Name Instance Host--* 199802235 1 PROD prod Rh65Defaults to current databaseUsing database id: 199802235Defaults to current instanceUsing instance number: 1ASH Samples in this Workload Repository schema~Oldest ASH sample available: 15-Aug-14 11:55:37 [4625 mins in the past] Latest ASH sample available: 18-Aug-14 17:00:17 [1 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 HH24:-- Examples: 02 HH24: -] MI-- Examples:-- 1:15 ( SYSDATE-1 Hr 15 Mins)-25 (SYSDATE-25 Mins) Defaults to-15 minsEnter value for begin_time:-- 10Enter duration in minutes starting from begin time:Defaults to SYSDATE-begin_timePress Enter to analyze till current timeEnter value for duration: Report duration specified:Using 18-Aug-14 16:51:39 as report begin timeUsing 18-Aug-14 17:01:40 as report end timeSpecify 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:Defaults to NULL: (% and _ wildcards allowed) SQL report target specified:Specify WATI_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target: [Enter 'CPU' to investigate CPU usage] Defaults to NULL: (% and _ wildcards allowed) WAIT_CLASS report target Specified:Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:Defaults to NULL:SERVICE report target specified:Specify MODULE name (eg: from V$SESSION.MODULE) report target:Defaults to NULL: (% and _ wildcards allowed) MODULE report target specified:Specify ACTION name (eg: from V$SESSION.ACTION) report target:Defaults to NULL: (% and _ wildcards allowed) ACTION report target specified:Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:Defaults to NULL: (% and _ wildcards allowed ) CLIENT_ID report target specified:Specify the Report Name~~~The default report file name is ashrpt_1_0818_1704.html. To use this name,press to continue, otherwise enter an alternative.Enter value for report_name: ash4.html

View the generated report

View the ASH report through html:

Basic information

TOP event: User TOP EVENTS

TOP SQL

TOP SESSION

TOP OBJECTS

ASH description

ASH is based on V$SESSION and is sampled once a second to record events that active sessions are waiting for. Inactive sessions are not sampled, and sampling is done by the newly introduced background process MMNL.

The minimum value of ASH buffers is 1MB, and the maximum value does not exceed 30MB. Record data in memory. The expected value is to record the content of one hour.

ASH memory allocation:

SQL > select * from v$sgastat where name like'% ASH%'

POOL NAME BYTES

Shared pool ASH buffers 8388608

ASH report generation script: @? / rdbms/admin/ashrpt.sql

ASH memory record data is always limited, in order to save historical data, the introduction of automatic load information base (Automatic Workload Repository, AWR) is completed by the background process MMON. The ASH information is also collected and written to the AWR load library. Because there is not enough memory, the MMNL process writes the information to the AWR load library after the ASH is full. It is unacceptable for ASH to write out all of the data, so only 10% of the collected data is generally written and done using direct-path insert to minimize log generation and minimize database performance impact.

The ASH information in memory can be queried through V$ACTIVE_SESSION_HISTORY, while the ASH information written to the AWR load library can be queried through the basic table wrh$_active_session_history of AWR. Wrh$_active_session_history is a partition table, and Oracle will automatically clean up the data.

In general, when we are monitoring the database, if it is a problem that is currently occurring, we can use v$session+v$sqlarea to find the worst-performing SQL statements. If it happens within an hour, we can find out the SQL by generating an ASH report. If it is more than an hour or a few days, we can use the AWR report to find out the SQL statements that have most affected the system in the past few days. ADDM reports are based on the AWR library, and you can save ADDM reports for 30 days by default.

Related query attempts:

V$session (currently occurring)

V$session_wait (currently occurring)

V$session_wait_history (the last 10 wait events for the session)

V$active_session_history (ASH in memory collects information, the theory is 1 hour)

Wrh$_active_session_history (ASH information written to AWR library, the theory is more than 1 hour)

Dba_hist_active_sess_history (views generated according to wrh$_active_session_history)

three。 Example of ASH report generation

The ASH component generates ASH reports based on v$active_session_history views, and ASH reports, similar to statspack, provide the following information:

Top User Events

Top Background Events

Top Event P1/P2/P3 Values

Top Service/Module

Top Client IDs

Top SQL Command Types

Top SQL using literals

Top Blocking Sessions

Top DB Objects

Top DB Files

Top Latches

Activity Over Time

The report interval can be accurate to minutes, so ASH can provide more detailed information about historical sessions than STATSPACK or AWR, which can be used as a supplement to statspack or awr. ASH reports are generated through the @ $ORACLE_HOME/rdbms/admin/ashrpt.sql script, including both hmtl and text formats.

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.