手动purge优化器的统计信息与AWR快照，减少对sysaux表空间的占用

上一篇 / 下一篇  2017-11-29 17:03:51 / 个人分类：手动purge优化器的统计信息和快照

查看( 1749 ) / 评论( 0 ) / 评分( 0 / 0 )

问题：oracle新建快照失败提示：
ORA-13509: 更新 AWR 表时出错
ORA-01683: 索引 ORA-01683: 索引 SYS.WRH$_LATCH_MISSES_SUMMARY_PK 分区 WRH$_LATCH__1403051315_0 无法通过 8192 (在表空间 SYSAUX 中) 扩展
. 分区  无法通过  (在表空间  中) 扩展

分析：
1、在数据库alter日志中发现下述错误信息
ORA-1683: unable to extend index SYS.WRH$_LATCH_MISSES_SUMMARY_PK partition WRH$_LATCH__1403051315_0 by 128 in tablespace SYSAUX
ORA-1683: unable to extend index SYS.WRH$_LATCH_MISSES_SUMMARY_PK partition WRH$_LATCH__1403051315_0 by 8192 in tablespace SYSAUX
2、查询表空间SYSAUX空间利用率：
SELECT Upper(F.TABLESPACE_NAME)         "表空间名",
            D.TOT_GROOTTE_MB                 "表空间大小(M)",
            D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
            To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
            || '%'                           "使用比",
            F.TOTAL_BYTES                    "空闲空间(M)",
            F.MAX_BYTES                      "最大块(M)"
     FROM   (SELECT TABLESPACE_NAME,
                    Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
                    Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
             FROM   SYS.DBA_FREE_SPACE
             GROUP  BY TABLESPACE_NAME) F,
            (SELECT DD.TABLESPACE_NAME,
                    Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
             FROM   SYS.DBA_DATA_FILES DD
             GROUP  BY DD.TABLESPACE_NAME) D
     WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
     and D.tablespace_name = 'SYSAUX';
     
2、查看SYSAUX表空间中资源占用
select *
        from (select segment_name,
                     segment_type,
                     bytes / 1024 / 1024
                from dba_segments
               where tablespace_name = 'SYSAUX'
               order by bytes desc);
注意：若使用12c数据库，需切换至CDB模式下进行查看。
解决方法：增大SYSAUX数据文件或者删减SYSAUX数据。
为表空间SYSAUX增加数据文件：alter tablespace sysaux add datafile '+DATA2/ORCL/1FD9D28891D83E62E0530517020A8AD1/DATAFILE/sysaux2' size 1024M;

select dbms_stats.get_stats_history_retention from dual; -- 查看统计信息的保留期
exec dbms_stats.alter_stats_history_retention(10);
exec dbms_stats.purge_stats(sysdate-20);

删减SYSAUX数据具体操作步骤可参考下述内容。


手动purge优化器的统计信息与AWR快照，减少对sysaux表空间的占用

2016-04-12 15:44 by abce,263阅读,0评论,收藏,编辑

1.运行以下脚本，计算当前优化器统计信息和AWR快照表占用sysaux的空间

?
1
2
SQL> conn /assysdba
SQL> @?/rdbms/admin/awrinfo.sql

2.检查优化器统计信息直方图表中的信息的有效天数

?
1
SQL> selectsystimestamp -min(savtime)fromsys.wri$_optstat_histgrm_history;

3.purge统计信息(修改有效天数)

?
1
SQL> execdbms_stats.purge_stats(sysdate - <noofdays>);

4.在split表WRH$_ACTIVE_SESSION_HISTORY之前，检查其相关信息

?
1
2
3
4
5
6
7
8
9
10
SQL>setlines 150
SQL> col SEGMENT_NAMEfora30
SQL> col PARTITION_NAMEfora50
 
SQL>SELECTowner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GBFROMdba_segmentsWHEREsegment_name='WRH$_ACTIVE_SESSION_HISTORY';
 
OWNER                          SEGMENT_NAME                   PARTITION_NAME                                     SEGMENT_TYPE          SIZE_GB
------------------------------ ------------------------------ -------------------------------------------------- ------------------ ----------
SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT_HISTO_MXDB_MXSN                        TABLEPARTITION    .000061035
SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT__1402125233_0                          TABLEPARTITION    .642578125

5.split awr分区，以便于有更多机会purge小的分区
这个命令会对所有的awr分区对象进行split，并初始化一个split分区

?
1
SQL>altersessionset"_swrf_test_action"= 72;

6.split之后，再次检查分区

?
1
2
3
4
5
6
7
8
9
10
11
SQL>setlines 150
SQL> col SEGMENT_NAMEfora30
SQL> col PARTITION_NAMEfora50
 
SQL>SELECTowner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GBFROMdba_segmentsWHEREsegment_name='WRH$_ACTIVE_SESSION_HISTORY';
 
OWNER                          SEGMENT_NAME                   PARTITION_NAME                                     SEGMENT_TYPE          SIZE_GB
------------------------------ ------------------------------ -------------------------------------------------- ------------------ ----------
SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT_HISTO_MXDB_MXSN                        TABLEPARTITION    .000061035
SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT__1402125233_0                          TABLEPARTITION    .642578125
SYS                            WRH$_ACTIVE_SESSION_HISTORY           WRH$_EVENT__1402125233_9290                       TABLEPARTITION    .000061035

7.查看每个分区表中的快照id(最小、最大快照id)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
    setserveroutputon
    declare
    CURSORcur_partIS
    SELECTpartition_namefromdba_tab_partitions
    WHEREtable_name ='WRH$_ACTIVE_SESSION_HISTORY';
 
    query1 varchar2(200);
    query2 varchar2(200);
 
    TYPE partrecISRECORD (snapid number, dbid number);
    TYPE partlistISTABLEOFpartrec;
 
    Outlist partlist;
    begin
    dbms_output.put_line('PARTITION NAME SNAP_ID DBID');
    dbms_output.put_line('--------------------------- ------- ----------');
 
    forpartincur_part loop
    query1 :='select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')
    group by dbid';
    executeimmediate query1 bulk collectintoOutList;
 
    if OutList.count> 0then
    foriinOutList.first..OutList.lastloop
    dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);
    endloop;
    endif;
 
    query2 :='select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||')
    group by dbid';
    executeimmediate query2 bulk collectintoOutList;
 
    if OutList.count> 0then
    foriinOutList.first..OutList.lastloop
    dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);
    dbms_output.put_line('---');
    endloop;
    endif;
 
    endloop;
    end;
    /
结果：
PARTITIONNAMESNAP_ID DBID
--------------------------- ------- ----------
WRH$_ACTIVE_1402125233_0Min9042 1402125233
WRH$_ACTIVE_1402125233_0Max9287 1402125233

8.根据上面的查询结果删除快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id IN NUMBER,high_snap_id IN NUMBER, dbid IN NUMBER DEFAULT NULL);
即：

?
1
SQL>execDBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(9042,9287,1402125233);

9.再次查看

?
1
2
SQL> conn /assysdba
SQL> @?/rdbms/admin/awrinfo.sql

收藏 举报

TAG:

 

评分：0

我来说两句

日历

« 2024-04-26  
日 一 二 三 四 五 六
 123456
78910111213
14151617181920
21222324252627
282930    

数据统计

  • 访问量: 74955
  • 日志数: 55
  • 建立时间: 2016-04-19
  • 更新时间: 2020-09-23

RSS订阅

Open Toolbar