Dav_笔记2-管理自动负载存储库 之 AWR
1.提取AWR数据：
@$ORACLE_HOME/rdbms/admin/awrextr.sql
2.指定指向导出转储文件所在目录的目录对象：
Enter value for directory_name: DATA_PUMP_DIR
在这个例子中，选择了目录对象DATA_PUMP_DIR。
3.Specify the number of days for which you want to list snapshot IDs.
Enter value for num_days: 2
4.Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot ID:
Enter value for begin_snap: 30
Enter value for end_snap: 40
5.A list of directory objects is displayed.
Specify the directory object pointing to the directory where the export dump file will be stored:
Enter value for directory_name: DATA_PUMP_DIR
6.Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
Enter value for file_name: awrdata_30_40
■加载AWR数据
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrload.sql
2.Specify the directory object pointing to the directory where the export dump file is located:
Enter value for directory_name: DATA_PUMP_DIR
3.Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
Enter value for file_name: awrdata_30_40
4.Specify the name of the staging schema where the AWR data will be loaded:
Enter value for schema_name: AWR_STAGE
5.Specify the default tablespace for the staging schema:
Enter value for default_tablespace: SYSAUX
6.为临时模式指定临时表空间：
Enter value for temporary_tablespace:：TEMP
在这个例子中，选择了TEMP表空间。
7.将在加载AWR数据的位置创建名为AWR_STAGE的分段模式。 将AWR数据加载到AWR_STAGE模式后，数据将被传输到SYS模式中的AWR表中：
Using Automatic Workload Repository Views
通常，您可以通过Oracle Enterprise Manager或AWR报告查看AWR数据。 但是，您也可以使用以下视图查看统计信息：
■V$ACTIVE_SESSION_HISTORY
该视图显示活动的数据库会话活动，每秒采样一次。 请参阅第5-3页的“活动会话历史记录”。
■V$metric 视图提供度量数据来跟踪系统的性能
度量视图被组织成各种组，如事件，事件类，系统，会话，服务，文件和表空间度量。 这些组在V $ METRICGROUP视图中标识。
■DBA_HIST
DBA_HIST视图显示存储在数据库中的历史数据。 这组意见包括：
■DBA_HIST_ACTIVE_SESS_HISTORY
显示最近系统活动的内存活动会话历史记录内容的历史记录
■DBA_HIST_BASELINE
显示有关系统上捕获的基准的信息，例如每个基准的时间范围和基准类型
■DBA_HIST_BASELINE_DETAILS
显示有关特定基线的详细信息
■DBA_HIST_BASELINE_TEMPLATE
显示有关系统用于生成基准的基准模板的信息
■DBA_HIST_DATABASE_INSTANCE
显示有关数据库环境的信息
■DBA_HIST_DB_CACHE_ADVICE
显示对应于每行的高速缓存大小的物理读取数的历史预测
■DBA_HIST_DISPATCHER
显示快照时每个调度程序进程的历史信息
■DBA_HIST_DYN_REMASTER_STATS
显示有关动态remastering过程的统计信息
■DBA_HIST_IOSTAT_DETAIL
显示按文件类型和函数汇总的历史I / O统计信息
■DBA_HIST_SHARED_SERVER_SUMMARY
显示共享服务器的历史信息，例如共享服务器活动，公共队列和调度程序队列
■DBA_HIST_SNAPSHOT
显示有关系统中快照的信息
■DBA_HIST_SQL_PLAN
显示SQL执行计划
■DBA_HIST_WR_CONTROL
显示用于控制AWR的设置
To generate an AWR report:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
To generate an AWR report on a specific database instance:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
To generate an AWR report on a specific database instance in an Oracle RAC environment:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrgrpti.sql
To generate an AWR report for a particular SQL statement:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
To generate an AWR report for a particular SQL statement on a specified database instance:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
Generating Automatic Workload Repository Compare Periods Reports
AWR报告显示两个快照（或两个时间点）之间的AWR数据，而AWR比较期间报告显示两个时间段（或两个AWR报告，相当于四个快照）之间的差异。
使用“AWR比较周期”报告有助于识别两个时间段之间不同的详细性能属性和配置设置。
例如，如果应用程序工作负载在下午10点和每晚的午夜之间已知是稳定的，但是在星期四的表现在晚上十点和下午11点之间表现不佳，产生周四下午10点的到下午11点和星期三下午10点到下午11点AWR比较期报告。应该确定在这些时间段内不同的配置设置，工作负载配置文件和统计信息。根据差异，您可以更轻松地诊断性能下降的原因。
为AWR比较期报告选择的两个时间段可以具有不同的持续时间，因为报告通过在每个时间段花费在数据库上的时间量来统计统计量，并呈现按期间之间的最大差异排序的统计数据。
AWR比较期报告分为多个部分。 HTML报告包含可用于在部分之间快速导航的链接。
报告内容包含系统对所选快照范围的工作负载配置文件。
生成AWR比较期报告的主要界面是Oracle企业管理器。
如有可能，您应该使用Oracle Enterprise Manager生成AWR比较期报告，如Oracle数据库2日+性能调优指南中所述。
如果Oracle企业管理器不可用，则可以通过运行SQL脚本生成AWR比较期报告，如以下各节所述：
To generate an AWR Compare Periods report:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql
2.Specify whether you want an HTML or a text report:
Enter value for report_type: html
In this example, an HTML report is chosen.
3.Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 2
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
4.Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
5.Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 1
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
6.Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
7.Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated.
To generate an AWR Compare Periods report in an Oracle RAC environment:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql
2.Specify whether you want an HTML or a text report:
Enter value for report_type: html
In this example, an HTML report is chosen.
3.Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 2
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
4.Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
5.Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 1
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
6.Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
7.Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrracdiff_1st_1_2nd_1.html
To generate an AWR Compare Periods report on a specified database instance:
1.At the SQL prompt, enter:
@$ORACLE_HOME/rdbms/admin/awrddrpi.sql
2.Specify whether you want an HTML or a text report:
Enter value for report_type: text
In this example, a text report is chosen.
3.A list of available database identifiers and instance numbers are displayed:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3309173529 1 MAIN main examp1690
3309173529 1 TINT251 tint251 samp251
Enter the values for the database identifier (dbid) and instance number (inst_num) for the first time period:
Enter value for dbid: 3309173529
Using 3309173529 for Database Id for the first pair of snapshots
Enter value for inst_num: 1
Using 1 for Instance Number for the first pair of snapshots
4.Specify the number of days for which you want to list snapshot IDs in the first time period.
Enter value for num_days: 2
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.
5.Specify a beginning and ending snapshot ID for the first time period:
Enter value for begin_snap: 102
Enter value for end_snap: 103
In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.
6.Enter the values for the database identifier (dbid) and instance number (inst_num) for the second time period:
Enter value for dbid2: 3309173529
Using 3309173529 for Database Id for the second pair of snapshots
Enter value for inst_num2: 1
Using 1 for Instance Number for the second pair of snapshots
7.Specify the number of days for which you want to list snapshot IDs in the second time period.
Enter value for num_days2: 1
A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.
8.Specify a beginning and ending snapshot ID for the second time period:
Enter value for begin_snap2: 126
Enter value for end_snap2: 127
In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.
9.Enter a report name, or accept the default report name:
Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt
In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated on the database instance with a database ID value of 3309173529.
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/Dav_2099/article/details/129701684
原文地址：https://blog.csdn.net/Dav_2099/article/details/140729754
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！