awr,ash報告生成方法

1、在出現(xiàn)性能問題的時候，DBA通常都需要你生成，出現(xiàn)性能問題時間段的AWR或者ASH報告，所以還 是需要客戶維護人員平常學會收集AWR ASH報告收集也是比較簡單的一下為實例：1 AWR報告的收集$ sqlplus / as sysdba (首先在操作上以ORACLE用戶登錄，并且以SYS登錄數(shù)據(jù)庫) SQL*Plus: Release .0 - Production on Wed Dec 24 14:57:53 2008Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Databa

2、se 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL ?/rdbms/admin/awrrpt.sql;(執(zhí)行這個SQL就可以開始AWR報告收集) Current Instance DB Id DB Name Inst Num Instance3704746844 HS081 HS081Specify the Report Type Would you lik

3、e an HTML report, or a plain text report?Enter html for an HTML report, or text for plain textDefaults to htmlEnter value for report_type:(首先會提示你輸入生成報告類型，默認為HTML，所以回車就OK)Type Specified: htmlInstances in this Workload Repository schemaDB Id Inst Num DB Name Instance Host37047468442 HS08HS082hp102* 37

4、047468441 HS08HS081hp101Using 3704746844 for database IdUsing 1 for instance numberSpecify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing withoutspecifying a number lists all completed snapshots

5、.Enter value for num_days: 1（輸入快照查看的間隔，一般一天的就OK，實際根據(jù)DBA的要求來做，）Listing the last days Completed SnapshotsSnapInstance DB Name Snap Id Snap StartedHS081 HS08158 24 Dec 2008 00:0024 Dec 2008 01:0024 Dec 2008 02:0024 Dec 2008 03:0024 Dec 2008 04:0024 Dec 2008 05:0024 Dec 2008 06:0024 Dec 2008 07:0024 Dec

6、 2008 08:0024 Dec 2008 09:0024 Dec 2008 10:0024 Dec 2008 11:0024 Dec 2008 11:3424 Dec 2008 11:3924 Dec 2008 13:0024 Dec 2008 14:00Specify the Begin and End Snapshot IdsEnter value for begin_snap: 167Begin Snapshot Id specified: 167Enter value for end_snap: 169End Snapshot Id specified: 169定，此處為169)L

7、evel1111111111111111（首先輸入開始的快照ID，此處為167）（輸入快照結束ID，可以根據(jù)要求的時間確Specify the Report NameThe default report file name is awrrpt_1_167_169.html. To use this name, press to continue, otherwise enter an alternative.Enter value for report_name:（輸入報告的名字，默認的就可以，例如：awrrpt_1_167_169.html.實例 1 快照 167-169 的 AWR 報告所

8、以此處回車就OK，報 告生成在當前目錄下）因為現(xiàn)在的系統(tǒng)一般都是RAC，所以需要在各個節(jié)點下的AWR報告，如果你愿意登錄各個節(jié)點，可以使 用一下SQL?/rdbms/admin/awrrpti.sql，和上邊的區(qū)別就在這于，輸入報告類型后的部分Instances in this Workload Repository schemaDB Id Inst Num DB NameInstanceHost37047468442 HS08HS082hp102* 37047468441 HS08HS081hp101Enter value for dbid: 3704746844（會要求你輸入數(shù)據(jù)庫的DBI

9、D）Using 3704746844 for database IdEnter value for inst_num: 1（輸入數(shù)據(jù)庫實例NUM，一般就是2節(jié)點，所以一般就是輸入1或者2）剩下的部分沒啥區(qū)別Using 1 for instance number2 ASH報告的生成$ sqlplus / as sysdba （首先SYS用戶登錄數(shù)據(jù)庫）SQL*Plus: Release .0 - Production on Wed Dec 24 15:14:21 2008Copyright （c） 1982, 2005, Oracle. All rights reserved.C

10、onnected to:Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL ?/rdbms/admin/ashrpt.sql;（執(zhí)行這個腳本開始ASH報告生成）Current InstanceDB Id DB NameInst Num Instance3704746844 HS082 HS082Specify the Report

11、TypeEnter html for an HTML report, or text for plain textDefaults to htmlEnter value for report_type:（默認HTML,回車即可）Type Specified: htmlInstances in this Workload Repository schemaDB Id Inst Num DB NameInstanceHost37047468441 HS08HS081hp101* 37047468442 HS08HS082hp102Defaults to current databaseUsing

12、database id: 3704746844Defaults to current instanceUsing instance number: 2ASH Samples in this Workload Repository schema Oldest ASH sample available: 17-Dec-08 11:06:09 10329 mins in the pastLatest ASH sample available: 24-Dec-08 15:14:23 0 mins in the pastSpecify the timeframe. to generate the ASH

13、 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)Default

14、s to -15 minsEnter value for begin_time: 08/12/24 15:10 (輸入開始的時間，格式為：年/月/日 小時/分鐘/秒)Report begin time specified: 08/12/24 15:10Enter duration in minutes starting from begin time:Defaults to SYSDATE - begin_timePress Enter to analyze till current timeEnter value for duration: 08/12/24 15:12(輸入結束的時間，格式

15、同上，根據(jù)問題發(fā)生的時間段生成就好了)Report duration specified: 08/12/24 15:12declare *ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character to number conversion errorORA-06512: at line 12Using 12-Aug-24 15:10:00 as report begin timeUsing as report end timeSpecify Slot Width (using ashrpti.sql) for Act

16、ivity 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 wi

17、dth of-1 minute, if the source is V$ACTIVE_SESSION_HISTORY or-5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORYSpecify 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 gener

18、ate 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

19、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

20、 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 usageDefaults 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: (%