130521建立AWR及AWR管理

来源：互联网发布：c语言指针编程题经典编辑：程序博客网时间：2024/05/20 09:22

1.创建snapshot

连接到:                    OracleDatabase 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production SQL>select dbms_workload_repository.create_snapshot() as "snap_id" fromdual;    snap_id----------       197

到em中查找该snapshot

点击进去发现错误，怀疑是节点刚刚起来的原因：

重建新的snapshot：

SQL>select dbms_workload_repository.create_snapshot() as "snap_id" fromdual;    snap_id----------       198

2. 通过存储过程删除snapshot

SQL>begin  2 dbms_workload_repository.drop_snapshot_range(low_snap_id=>197,high_snap_id=>197);  3  end;  4  /

PL/SQL 过程已成功完成。

回到em中，已经不存在197的snapshot

此时点击198，依然报错：

证明snapshot是必须存在2个，相互对比看；

3. 报告中的elapsed time 报告逝去时间比较关键，通常情况下，需要看1个小时内的系统情况；

需要通过dbms_workload_repository包中的函数进行修改，经过查询包中有modify_snapshot_settings函数

查询文档

Modifying Snapshot Settings

You can adjust the interval and retention of snapshot generation for aspecified database Id, but note that this can affect the precision of theOracle diagnostic tools.

The INTERVAL setting affects how often in minutesthat snapshots are automatically generated. The RETENTION setting affects how long in minutes that snapshots are stored in theworkload repository. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention =>43200,

interval => 30, dbid =>3310949047);

END;

In this example, the retention period is specified as 43200 minutes (30days) and the interval between each snapshot is specified as 30 minutes. IfNULL is specified, the existing value is preserved. The optional databaseidentifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. You cancheck the current settings for your database instance with the DBA_HIST_WR_CONTROL view.

此处对elapse 理解有误，之前以为是报告执行消耗的时间，实际为两个snapshot之间的间隔时间duration；

The snapshotdetails

This is normallyshown at the beginning of the report.

Snap Id Snap Time Sessions Curs/Sess

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

Begin Snap:4413 17-May-12 09:22:34 28 3.0

End Snap:4414 17-May-12 09:43:35 29 3.3

Elapsed: 21.02 (mins)

DB Time: 21.78 (mins)

Note that unlikethe hourly usual sample, this sampling took 21 minutes to complete. Thesnapshots in this case were taken immediately before and after running thePL/SQL block.

Elapsed (time) isjust the interval of time between the start and end snapshots. Anotherimportant quantity is database DB Time which is the sum ofworking sessions’ time. So DB Time = sumof database CPU time + waits. In systems with multiple concurrentactive sessions DB Time can be larger than the elapsed time.This is because DB Time is a sum over all active sessionsthat are using CPU(s) or waiting for an event. Note that Backgroundprocesses are not included in that.

正常情况下系统每一个小时生成一次AWR报告，如果需要修改通过dbms_workload_repository.modify_snapshot_settings函数进行修改

之后查看系统每隔一小时自动生成的报告，可以看出elapse time=1小时，前后运行时间相隔一小时

4. 通过脚本预估AWR对sysaux表空间的数据空间占用；utlsyxsz.sql -Utility script for SYSAUX Size

SQL> @D:\app\calvin\product\11.2.0\test\RDBMS\ADMIN\utlsyxsz.sql  This script estimates the space required for the SYSAUX tablespace.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Specify the Report File Name~~~~~~~~~~~~~~~~~~~~~~~~~~~~The default report file name is utlsyxsz.txt.  To use this name,press <return> to continue, otherwise enter an alternative. 输入 report_name 的值:  1 Using the report name 1~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SYSAUX Size Estimation Report~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Estimated at11:38:25 on 5月  22, 2013 ( 星期三 ) in Timezone+08:00  DB_NAME     HOST_PLATFORM                             INSTSTARTUP_TIME      PA----------- ---------------------------------------- ---------------------- --* TEST      HOMEPC - MicrosoftWindows x86 64-bit        1 10:41:32(05/22)  NO ~~~~~~~~~~~~~~~~~~~~Current SYSAUX usage~~~~~~~~~~~~~~~~~~~~| Total SYSAUX size:                         615.6 MB|| Total size of SM/AWR                        73.6 MB (  12.0% of SYSAUX )| Total size of SM/OPTSTAT                    49.5 MB (   8.0% of SYSAUX )| Total size of XDB                          128.1 MB (  20.8% of SYSAUX )| Total size of EM                            95.9 MB (  15.6% of SYSAUX )| Total size of SDO                           53.1 MB (   8.6% of SYSAUX )| Total size of AO                            42.0 MB (   6.8% of SYSAUX )| Total size of XSOQHIST                     42.0 MB (   6.8% of SYSAUX )| Total size of SM/ADVISOR                    18.9 MB (   3.1% of SYSAUX )| Total size of ORDIM/ORDDATA                 12.8 MB (   2.1% of SYSAUX )| Total size of LOGMNR                         7.9 MB (   1.3% of SYSAUX )| Total size of SM/OTHER                       7.5 MB (   1.2% of SYSAUX )| Total size of XSAMD                          4.4 MB (   0.7% of SYSAUX )| Total size of EXPRESSION_FILTER              3.6 MB (   0.6% of SYSAUX )| Total size of TEXT                           3.5 MB (   0.6% of SYSAUX )| Total size of SMON_SCN_TIME                  3.3 MB (   0.5% of SYSAUX )| Total size of WM                             3.1 MB (   0.5% of SYSAUX )| Total size of SQL_MANAGEMENT_BASE            1.7 MB (   0.3% of SYSAUX )| Total size of PL/SCOPE                       1.6 MB (   0.3% of SYSAUX )| Total size of LOGSTDBY                       1.4 MB (   0.2% of SYSAUX )| Total size of EM_MONITORING_USER             1.4 MB (   0.2% of SYSAUX )| Total size of STREAMS                        1.0 MB (   0.2% of SYSAUX )| Total size of JOB_SCHEDULER                  0.7 MB (   0.1% of SYSAUX )| Total size of ORDIM                          0.4 MB (   0.1% of SYSAUX )| Total size of AUTO_TASK                      0.3 MB (   0.1% of SYSAUX )| Total size of Others                        58.3 MB (   9.5% of SYSAUX )| ~~~~~~~~~~~~~~~~~~~~AWR Space Estimation~~~~~~~~~~~~~~~~~~~~ | To estimate the size of the Automatic Workload Repository (AWR)| in SYSAUX, we need the following values:||     - Interval Setting(minutes)|     - Retention Setting (days)|     - Number of Instances|     - Average Number of ActiveSessions|     - Number of Datafiles || For 'Interval Setting',|   Press <return> to usethe current value:     60.0 minutes|   otherwise enter analternative|输入 interval 的值:  60 **   Value for 'IntervalSetting': 60 || For 'Retention Setting',|   Press <return> to usethe current value:   8.00 days|   otherwise enter analternative|输入 retention 的值:  7 **   Value for 'RetentionSetting': 7 || For 'Number of Instances',|   Press <return> to usethe current value:   1.00|   otherwise enter analternative|输入 num_instances 的值:  1 **   Value for 'Number of Instances':1 || For 'Average Number of Active Sessions',|   Press <return> to usethe current value:   0.03|   otherwise enter analternative|输入 active_sessions 的值:  10 **   Value for 'Average Number ofActive Sessions': 10 | ***************************************************| Estimated size of AWR:                     230.2 MB||   The AWR estimate was computedusing|   the following values:||            Interval -        60 minutes|           Retention -      7.00 days|       Num Instances -         1|     Active Sessions -     10.00|           Datafiles -         7| *************************************************** ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Optimizer Stat History Space Estimation~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | To estimate the size of the Optimizer Statistics History| we need the following values:||     - Number of Tables in theDatabase|     - Number of Partitions inthe Database|     - Statistics RetentionPeriod (days)|     - DML Activity in theDatabase (level) || For 'Number of Tables',|   Press <return> to usethe current value:    143.0|   otherwise enter analternative <a positive integer>|输入 number_of_tables 的值: **   Value for 'Number ofTables': 143 || For 'Number of Partitions',|   Press <return> to usethe current value:   0.00|   otherwise enter analternative <a positive integer>|输入 number_of_partitions 的值: **   Value for 'Number ofPartitions': 0 || For 'Statistics Retention',|   Press <return> to use thecurrent value:     31.0 days|   otherwise enter analternative <a positive integer>|输入 stats_retention 的值: **   Value for 'StatisticsRetention': 31 || For 'DML Activity',|   Press <return> to usethe current value:        2<medium>|   otherwise enter analternative <1=low, 2=medium, 3=high>|输入 dml_activity 的值: **   Value for 'DML Activity': 2 | ***************************************************| Estimated size of Stats history             42.1 MB||   The space for OptimizerStatistics history was|   estimated using the followingvalues:||                         Tables-     143|                        Indexes-     355|                        Columns-   1,182|                     Partitions-       0|          Indexes on Partitions-       0|          Columns in Partitions-       0|        Stats Retention in Days-      31|          Level of DML Activity-  Medium| *************************************************** ~~~~~~~~~~~~~~~~~~~~~~Estimated SYSAUX usage~~~~~~~~~~~~~~~~~~~~~~ | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Estimated size of AWR:                     230.2 MB||   The AWR estimate was computedusing|   the following values:||            Interval -        60 minutes|           Retention -      7.00 days|       Num Instances -         1|     Active Sessions -     10.00|           Datafiles -         7| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Estimated size of Stats history             42.1 MB||   The space for OptimizerStatistics history was|   estimated using the followingvalues:||                         Tables-     143|                        Indexes-     355|                        Columns-   1,182|                     Partitions-       0|          Indexes on Partitions -       0|          Columns in Partitions-       0|        Stats Retention in Days-      31|          Level of DML Activity-  Medium| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|   For all the other components,the estimate|   is equal to the current spaceusage of|   the component.| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~||| ***************************************************| Summary of SYSAUX Space Estimation| *************************************************** | Est size of XDB                            128.1 MB| Est size of EM                              95.9 MB| Est size of SDO                             53.1 MB| Est size of AO                              42.0 MB| Est size of XSOQHIST                        42.0 MB| Est size of SM/ADVISOR                      18.9 MB| Est size of ORDIM/ORDDATA                   12.8 MB| Est size of LOGMNR                           7.9 MB| Est size of SM/OTHER                         7.5 MB| Est size of XSAMD                            4.4 MB| Est size of EXPRESSION_FILTER                3.6 MB| Est size of TEXT                             3.5 MB| Est size of SMON_SCN_TIME                    3.3 MB| Est size of WM                               3.1 MB| Est size of SQL_MANAGEMENT_BASE              1.7 MB| Est size of PL/SCOPE                         1.6 MB| Est size of LOGSTDBY                         1.4 MB| Est size of EM_MONITORING_USER               1.4 MB| Est size of STREAMS                          1.0 MB| Est size of JOB_SCHEDULER                    0.7 MB| Est size of ORDIM                            0.4 MB| Est size of AUTO_TASK                        0.3 MB| Est size of Others                          58.3 MB | Est size of SM/AWR                         230.2 MB| Est size of SM/OPTSTAT                      42.1 MB|| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Total Estimated SYSAUX size:               764.8 MB| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| *************************************************** End of Report

5. 快照集baseline：将快照纳入到快照集，则该快照就不会被MMON进程自动删除，快照集是我们保留重要时间段的快照数据的一种方法，通过制定两个快照的snap_id，从而将两个快照id之间的所有快照数据定义成一个快照集。

快照集通常用于保存重要的快照数据，用于性能比较的，或者作为基线的快照，因为快照集中的快照数据可以一直保存，直到快照集被删除。所创建的快照集可以通过dba_hist_baseline试图中显示出来，通过执行命令dbms_workload_repository.create_baseline存储过程来创建快照集：如下

系统中201和202是两个连续的快照集

SQL> begin

  2 dbms_workload_repository.create_baseline(  3 start_snap_id=>201,  4 end_snap_id=>202,  5 baseline_name=>'my1');  6  end;  7  /

查询表