eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 我们应该如何爱护自己? | Blog首页 | EMC损坏硬盘更换及恢复过程 »

Oracle10gR2 New Feature:Generate ASH Reports

在Oracle10gR1中,Oracle引入了ASH的概念。在Oracle10gR2中,Oracle进一步强化了ASH的功能,增加了report能力。
ASH采样的是一段时间内系统内活动session的等待信息,通过这些信息,可以获得top wait events, top SQL, top SQL command types,top sessions等等重要信息。

Oracle通过ASH Report可以对特定的时间段进行分析,可以精确到天、小时或者分钟。
ashrpt类似statspack的功能,通过报表把采集的数据直观的展现出来。其本质仍然是得益于ASH的强大功能。
我们简单看一下v$active_session_history记录的历史信息:
SQL> select sample_id,sample_time,event from v$active_session_history
  2  where rownum <20;

 SAMPLE_ID SAMPLE_TIME                    EVENT
---------- ------------------------------ --------------------------------------------------
    803376 14-SEP-05 10.54.39.860 AM      control file parallel write
    803187 14-SEP-05 10.51.30.646 AM      control file sequential read
    803170 14-SEP-05 10.51.13.626 AM      db file parallel write
    803165 14-SEP-05 10.51.08.626 AM      control file parallel write
    803162 14-SEP-05 10.51.05.626 AM      control file parallel write
    802948 14-SEP-05 10.47.31.399 AM      control file parallel write
    802728 14-SEP-05 10.43.51.163 AM      control file parallel write
    802511 14-SEP-05 10.40.13.934 AM      control file parallel write
    802474 14-SEP-05 10.39.36.894 AM      db file sequential read
    802473 14-SEP-05 10.39.35.894 AM      db file sequential read
    802294 14-SEP-05 10.36.36.699 AM      control file parallel write
    802077 14-SEP-05 10.32.59.483 AM      control file parallel write
    801869 14-SEP-05 10.29.31.267 AM      control file parallel write
    801652 14-SEP-05 10.25.54.043 AM      control file parallel write
    801435 14-SEP-05 10.22.16.810 AM      control file parallel write
    801218 14-SEP-05 10.18.39.596 AM      control file parallel write
    801149 14-SEP-05 10.17.30.526 AM      control file sequential read
    801004 14-SEP-05 10.15.05.372 AM      control file parallel write
    800787 14-SEP-05 10.11.28.168 AM      control file parallel write

19 rows selected.



我们可以看到通过ASH,Oracle以更低粒度记录了活动session所产生的等待以及等待事件。
通过这些信息我们不仅可以分时段的观察数据库系统,甚至可以划出每个等待时间的趋势图来。这对于我们了解系统运行状况,对数据库进行实时或历史状态跟踪提供了巨大的帮助。

我们可以通过OEM来生成report,也可以通过Oracle新提供的一个脚本来完成这个工作,这个脚本是:

$ORACLE_HOME/rdbms/admin/ashrpt.sql

运行此脚本以后就会生成这样一个报告:
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 3965153484 DANALY              1 danaly


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 3965153484        1 DANALY       danaly       danaly.hurrr
                                                ay.com.cn


Defaults to current database

Using database id: 3965153484

Defaults to current instance

Using instance number: 1


ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  12-Sep-05 19:36:11   [   2342 mins in the past]
Latest ASH sample available:  12-Sep-06 19:33:24   [ ###### 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/23/03 14:30:15
--                  02/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 value for begin_time: -25
Report begin time specified: -25

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 
Report duration specified:

Using 14-Sep-05 10:13:59 as report begin time
Using 14-Sep-05 10:39:26 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:
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_0914_1039.txt.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name: 

Using the report name ashrpt_1_0914_1039.txt

Summary of All User Input
-------------------------
Format         : TEXT
DB Id          : 3965153484
Inst num       : 1
Begin time     : 14-Sep-05 10:13:59
End time       : 14-Sep-05 10:39:26
Slot width     : Default
Report targets : 0
Report name    : ashrpt_1_0914_1039.txt


ASH Report For DANALY/danaly

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
DANALY        3965153484 danaly              1 10.2.0.1.0  NO  danaly.hurrr

CPUs           SGA Size       Buffer Cache        Shared Pool    ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
   4        900M (100%)       632M (70.2%)       231M (25.6%)        8.0M (0.9%)


          Analysis Begin Time:   14-Sep-05 10:13:59
            Analysis End Time:   14-Sep-05 10:39:26
                 Elapsed Time:        25.5 (mins)
                 Sample Count:           8
      Average Active Sessions:        0.01
  Avg. Active Session per CPU:        0.00
                Report Target:   None specified

Top User Events                DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top Background Events          DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                                                               Avg Active
Event                               Event Class     % Activity   Sessions
----------------------------------- --------------- ---------- ----------
control file parallel write         System I/O           87.50       0.00
control file sequential read        System I/O           12.50       0.00
          -------------------------------------------------------------

Top Event P1/P2/P3 Values      DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

Event                          % Event  P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1                Parameter 2                Parameter 3
-------------------------- -------------------------- --------------------------
control file parallel write      87.50                   "2","3","2"      87.50
files                      block#                     requests

control file sequential read     12.50                   "0","1","1"      12.50
file#                      block#                     blocks

          -------------------------------------------------------------

Top Service/Module             DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

Service        Module                   % Activity Action               % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED                      100.00 UNNAMED                100.00
          -------------------------------------------------------------

Top Client IDs                 DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top SQL Command Types          DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------


Top SQL Statements             DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top SQL using literals         DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------


Top Sessions                   DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)
-> '# Samples Active' shows the number of ASH samples in which the session
      was found waiting for that particular event. The percentage shown
      in this column is calculated with respect to wall clock time
      and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
      when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
      the PQ slave activity into the session issuing the PQ. Refer to
      the 'Top Sessions running PQs' section for such statistics.

   Sid, Serial# % Activity Event                             % Event
--------------- ---------- ------------------------------ ----------
User                 Program                          # Samples Active     XIDs
-------------------- ------------------------------ ------------------ --------
      165,    1      87.50 control file parallel write         87.50
SYS                  oracle@danaly....com.cn (CKPT)     7/1,527 [  0%]        0

      153,    3      12.50 control file sequential read        12.50
SYS                  oracle@danaly....com.cn (ARC0)     1/1,527 [  0%]        0

          -------------------------------------------------------------

Top Blocking Sessions          DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------


Top Sessions running PQs      DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top DB Objects                 DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top DB Files                   DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top Latches                    DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)

                  No data exists for this section of the report.
          -------------------------------------------------------------


Activity Over Time             DB/Inst: DANALY/danaly  (Sep 14 10:13 to 10:39)
-> Analysis period is divided into smaller time slots
-> Top 3 events are reported in each of those slots
-> 'Slot Count' shows the number of ASH samples in that slot
-> 'Event Count' shows the number of ASH samples waiting for
   that event in that slot
-> '% Event' is 'Event Count' over all ASH samples in the analysis period

                         Slot                                   Event
Slot Time (Duration)    Count Event                             Count % Event
-------------------- -------- ------------------------------ -------- -------
10:15:00   (3.0 min)        2 control file parallel write           1   12.50
                              control file sequential read          1   12.50
10:18:00   (3.0 min)        1 control file parallel write           1   12.50
10:21:00   (3.0 min)        1 control file parallel write           1   12.50
10:24:00   (3.0 min)        1 control file parallel write           1   12.50
10:27:00   (3.0 min)        1 control file parallel write           1   12.50
10:30:00   (3.0 min)        1 control file parallel write           1   12.50
10:36:00   (3.0 min)        1 control file parallel write           1   12.50
          -------------------------------------------------------------

End of Report
Report written to ashrpt_1_0914_1039.txt

当然怎样分析和使用这个report仍然要靠对数据库的深入了解才行。

历史上的今天...
    >> 2015-09-14文章:
    >> 2011-09-14文章:
    >> 2009-09-14文章:
    >> 2008-09-14文章:
    >> 2006-09-14文章:
           高级复制ORA-25207错误解决
    >> 2004-09-14文章:

无觅

By eygle on 2005-09-14 11:18 | Comments (2) | Oracle12c/11g | 432 |

2 Comments

勤奋的eygle啊
下周我回北京,该给我接风洗尘啦

你丫的生活过的滋润的很!


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com