流言终结者: AWR的保留天数和SYSAUX表空间的使用率有关吗？

Comments

1. admin says

   2011/10/24 at 22:40

   WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged

   Applies to:
   Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.2.0.2 – Release: 10.2 to 11.2
   Information in this document applies to any platform.
   ***Checked for relevance on 16-Sep-2010***
   Symptoms
   AWR tables are not being purged according to settings in sys.wrm$_wr_control. Because of this the tables are accumulating more and more rows and the segments associated with these tables become very large.
   Cause

   Oracle is deciding what rows need to be purged based on the retention policy. There is a special mechanism which is used in the case of the large AWR tables. For the large AWR tables, we store the snapshot data into partitions. Purging data from these tables means that a partition gets dropped. During the nightly purge task, we only drop the partition if all the data in the partition has expired. If the partition contains at least one row which, according to the retention policy shouldn’t be removed, then the partition won’t be dropped and as such the table will contain old data.

   Based on the above clarifications we can say that if anything is going wrong with the partition split operation then we will end up by having the AWR tables not to be purged.

   The root cause for the problems can be diagnosed by triggering the same operations which are executed each time the purge job is fired.
   Solution

   1) Check how many partitions do exist for the offending table :

   select table_name,partition_name
   from dba_tab_partitions
   where table_name = ‘WRH$_ACTIVE_SESSION_HISTORY’;

   2) Try to force the execution of the purge operations :

   alter session set “_swrf_test_action” = 72;

   This step can finish successfully :

   Statement processed

   or it can fail:

   ORA-02097: parameter cannot be modified because specified value is invalid
   ORA-13509: error encountered during updates to a AWR table
   ORA-14327: Some index [sub]partitions could not be rebuilt

   3) If the operation on step 2 is failing the generated (udump) trace file must be analyzed. In the trace file a partition split statement can be found :

   *** KEWROCISTMTEXEC – encountered error: (ORA-14327: Some index [sub]partitions could not be rebuilt
   ORA-20001: Component “WRH$_SQLSTA_2446360981_11691” does not exist on index “WRH$_SQLSTAT_INDEX” in schema “SYS”.
   ORA-06512: at “SYS.DBMS_I_INDEX_UTL”, line 748
   ORA-06512: at “SYS.DBMS_INDEX_UTL”, line 397
   ORA-06512: at “SYS.DBMS_INDEX_UTL”, line 556
   ORA-06512: at line 1
   )
   *** SQLSTR: total-len=228, dump-len=228,
   STR={alter table WRH$_SQLSTAT split partition
   WRH$_SQLSTA_2446360981_11691 at (2446360981,12774) into (partition
   WRH$_SQLSTA_2446
   360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774
   tablespace SYSAUX) update indexes}

   The split statement must be executed manually :

   alter table WRH$_SQLSTAT split partition WRH$_SQLSTA_2446360981_11691 at (2446360981,12774)
   into (partition WRH$_SQLSTA_2446 360981_11691 tablespace SYSAUX, partition WRH$_SQLSTA_2446360981_12774 tablespace SYSAUX)
   update indexes;

   4) Steps 2 and 3 must be executed until setting the event is successful.

   5) Check how many partitions do exist for the offending table after all the above actions :
   set serveroutput on
   declare
   CURSOR cur_part IS
   SELECT partition_name from dba_tab_partitions
   WHERE table_name = ‘WRH$_ACTIVE_SESSION_HISTORY’;

   query1 varchar2(200);
   query2 varchar2(200);

   TYPE partrec IS RECORD (snapid number, dbid number);
   TYPE partlist IS TABLE OF partrec;

   Outlist partlist;
   begin
   dbms_output.put_line(‘PARTITION NAME SNAP_ID DBID’);
   dbms_output.put_line(‘————————— ——- ———-‘);

   for part in cur_part loop
   query1 := ‘select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition (‘||part.partition_name||’) group by dbid’;
   execute immediate query1 bulk collect into OutList;

   if OutList.count > 0 then
   for i in OutList.first..OutList.last loop
   dbms_output.put_line(part.partition_name||’ Min ‘||OutList(i).snapid||’ ‘||OutList(i).dbid);
   end loop;
   end if;

   query2 := ‘select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition (‘||part.partition_name||’) group by dbid’;
   execute immediate query2 bulk collect into OutList;

   if OutList.count > 0 then
   for i in OutList.first..OutList.last loop
   dbms_output.put_line(part.partition_name||’ Max ‘||OutList(i).snapid||’ ‘||OutList(i).dbid);
   dbms_output.put_line(‘—‘);
   end loop;
   end if;

   end loop;
   end;
   /

   We should have more partitions now and it is expected that the old partitions will get automatically removed when the retention period will expire.

   Once you have identified, and split the partitions, you may want to drop a partition range, by dropping a snapshot range:

   DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
   low_snap_id IN NUMBER,
   high_snap_id IN NUMBER
   dbid IN NUMBER DEFAULT NULL);

   回复
2. admin says

   2011/10/24 at 22:41

   Suggestions if your SYSAUX Tablespace grows rapidly or too large

   Applies to:
   Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.2.0.2 – Release: 11.1 to 11.2
   This problem can occur on any platform.
   Symptoms
   Observed exceptional SYSAUX tablespace growth and associated overhead purging AWR objects

   We anticipate RAC and Exadata will force proportionately higher SYSAUX tablespace growth to maintain performance and auditing statistics. The following example provides some suggestions on how to approach purging SYSAUX OPSTAT based data which can be one of the largest SYSAUX tablespace storage consumers.
   Cause
   Legacy data from previously collected SQLSETs resulted in partitions for OP_STAT and WRH/WRI based objects

   – These are AWR/performance/tuning set based views

   It is known that old WRH a/o OPSTAT data can be stranded under various circumstances.
   One reason that is not well known is that SQLSETs and BASELINEs require data that is collected and shared with AWR snapshots. Manual or automatic purging of legacy snapshots will not automatically free any snapshots that also share data with the SQLSETs and BASELINEs. This data would only be freed if the SQLSETS and BASELINES were dropped first.

   One problem with dropping snapshots: There is no warming about how many snapshots have not been dropped if purging by a range. You will only receive the feedback that the “operation completed”

   Solution

   SYSAUX Space Usage:
   ================

   Make sure the SYSAUX tablespace is set to AUTOEXTEND off.

   Collect awrinfo report and verify which object is consuming the most space in the SYSAUX tablespace.

   In the report you may tend to see the following tables are consuming the most space

   1. WRH$_ACTIVE_SESSION_HISTORY
   2. WRI$_OPTSTAT_HISTGRM_HISTORY
   3. WRI$_OPTSTAT_HISTHEAD_HISTORY

   Example from the AWRINFO.SQL script

   *************************************
   (2) Size estimates for AWR snapshots
   *************************************
   |
   | Estimates based on 60 mins snapshot INTERVAL:

   | AWR size/day 745.8 MB (31,823 K/snap * 24 snaps/day)
   | AWR size/wk 5,220.9 MB (size_per_day * 7) per instance
   | AWR size/wk 10,441.8 MB (size_per_day * 7) per database
   |
   | Estimates based on 24 snaps in past 24 hours:

   | AWR size/day 745.8 MB (31,823 K/snap and 24 snaps in past 24 hours)
   | AWR size/wk 5,220.9 MB (size_per_day * 7) per instance
   | AWR size/wk 10,441.8 MB (size_per_day * 7) per database
   |

   i.e.per the above information 10GB is the expected weekly size

   However the stats show 37gb even after several purge attempts
   ########################################################
   (I) AWR Snapshots Information
   ########################################################
   *****************************************************
   (1a) SYSAUX usage – Schema breakdown (dba_segments)
   *****************************************************
   |
   | Total SYSAUX size 37,933.8 MB ( 97% of 39,292.9 MB MAX with AUTOEXTEND OFF )
   |
   | Schema SYS occupies 37,178.3 MB ( 98.0% ) <<< 37gb | Schema SYSTEM occupies 731.6 MB ( 1.9% ) | Schema ABC occupies 18.7 MB ( 0.0% ) | Schema WMSYS occupies 3.1 MB ( 0.0% ) | Schema DBSNMP occupies 2.2 MB ( 0.0% ) | ******************************************************** (1b) SYSAUX occupants space usage (v$sysaux_occupants) ******************************************************** | | Occupant Name Schema Name Space Usage | -------------------------------------------------------- | SM/OPTSTAT SYS 30,778.6 MB << OPTSTAT 30gb | SM/AWR SYS 5,283.1 MB << AWR | SM/ADVISOR SYS 940.4 MB << Advisors can 'trap' snapshot data* | LOGMNR SYSTEM 722.3 MB | SM/OTHER SYS 32.1 MB largest consumers ----------------------- ASH 737.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_1198427434_5673 - 98% TABLE PARTITION ASH 144.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_1198427434_5993 - 97% TABLE PARTITION ASH 96.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_1198427434_5673 - 84% INDEX PARTITION ASH 24.0 WRH$_ACTIVE_SESSION_HISTORY_PK.WRH$_ACTIVE_1198427434_5993 - 78% INDEX PARTITION > />
   SQLPLANS need to be reviewed as they can “trap” Snapshot information*

   ++++++++++++++++++++++++++
   SQLPLAN 656.0 MB WRH$_SQL_PLAN – 93% TABLE
   SQLPLAN 136.0 MB WRH$_SQL_PLAN_PK – 77% INDEX

   * Snapshots cannot be dropped IF the data is required for SQLPLANs or BASELINEs even though the Snapshot purge appears to be successful

   – i.e. you receive no feedback that the Snapshots cannot be dropped due to SQLPLAN / Baseline required data shared by snapshots

   Below we see where the information is not being released

   **********************************
   (4) Space usage by non-AWR components (> 500K)
   **********************************
   COMPONENT MB SEGMENT_NAME SEGMENT_TYPE
   ——— ——— —————————————–
   NON_AWR 11,376.3 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX
   NON_AWR 8,098.9 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE
   NON_AWR 5,813.3 SYS.I_WRI$_OPTSTAT_H_ST INDEX
   NON_AWR 1,988.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE
   NON_AWR 1,475.3 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX
   NON_AWR 908.6 SYS.I_WRI$_OPTSTAT_HH_ST INDEX
   NON_AWR 636.0 SYS.SYS_LOB0000006213C00038$$ LOBSEGMENT
   NON_AWR 380.0 SYS.WRI$_ADV_OBJECTS TABLE
   NON_AWR 256.0 SYS.I_WRI$_OPTSTAT_SYNOPSIS INDEX
   NON_AWR 224.0 SYS.WRI$_OPTSTAT_TAB_HISTORY TABLE
   NON_AWR 216.6 SYS.I_WRI$_OPTSTAT_TAB_ST INDEX
   NON_AWR 208.0 SYS.WRI$_ADV_OBJECTS_IDX_01 INDEX
   NON_AWR 201.5 SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST INDEX
   NON_AWR 200.0 SYS.WRI$_OPTSTAT_SYNOPSIS$ TABLE
   NON_AWR 160.0 SYS.SYS_LOB0000006207C00004$$ LOBSEGMENT
   NON_AWR 96.0 SYS.WRI$_ADV_OBJECTS_PK INDEX

   ACTION PLAN

   Follow steps from the document

   Note: 387914.1 WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged

   1. Check how many partitions do exist for the offending table

   select table_name,partition_name from dba_tab_partitions
   where table_name = ‘WRH$_ACTIVE_SESSION_HISTORY’;

   2. Try to force the execution of the purge operations :

   alter session set “_swrf_test_action” = 72;

   3. Drop snapshots manually with small range.

   DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
   low_snap_id IN NUMBER,
   high_snap_id IN NUMBER
   dbid IN NUMBER DEFAULT NULL);
   – or –
   exec dbms_stats.purge_stats(sysdate-&days);

   4. If the drop snapshot did not finish or consume for UNDO space. Terminate the session and wait for a day or two the default auto purge job should automatically drop the old snapshots.

   Purge:
   WRI$_OPTSTAT_HISTGRM_HISTORY and WRI$_OPTSTAT_HISTHEAD_HISTORY

   1. Backup both the above tables using conventional Export
   do not use expdp as this does not work for some sys related objects.

   2. Truncate the table and verify is there any UNUSED indexes if so rebuild them.
   – Consider setting the indexes to UNUSABLE and then rebuild

   回复
3. zhouyf says

   2011/10/25 at 11:47

   相当强悍
   备注同样无比强悍。完美了~~:)

   回复