分类目录归档:Oracle Bug

sysaux中HEATMAP 对象较大

在一次数据检查中,发现HEATMAP对象比较大

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0


SQL> col owner for a30
SQL> col  segment_name for a30
SQL> col segment_type for a20
set lines 150
SELECT owner, segment_name, segment_type, sum(bytes/1024/1024/1024)
FROM dba_extents
WHERE tablespace_name='SYSAUX'
and segment_name='HEATMAP'
group by owner, segment_name, segment_type;SQL> SQL>   2    3    4    5  

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE         SUM(BYTES/1024/1024/1024)
------------------------------ ------------------------------ -------------------- -------------------------
SYS                            HEATMAP                        SYSTEM STATISTICS                   1.58789063

检查Heat Map特性为关闭

SQL> show parameter HEAT_MAP;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
heat_map                             string      OFF

查询mos发现相关Bug 20678613 – HEATMAP SIZE IS 500 MB, THOUGH HEAT_MAP IS TURNED OFF.通过设置_drop_stat_segment为1来自动减小HEATMAP(其默认值为0)

---默认值
SQL> col name for a52
SQL> col value for a24
SQL> col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
SQL> SQL>   2    3    4    5    6    7  order by name
/  8  
Enter value for param: _drop_stat_segment
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_drop_stat_segment%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ----------------------------
_drop_stat_segment                                   0                        drop ilm statistics segment

---设置值
ALTER SYSTEM SET "_drop_stat_segment" =1; 

如果需要也可以人工进行删除

exec dbms_space_admin.heat_map_segment_drop;

参见:HEATMAP Segment Size Is Large In SYSAUX Even When Heatmap=Off (Doc ID 2024036.1)
Bug 24704547 : SYS.HEATMAP GROWS IN SIZE IN 12C UNDER SYSAUX TBS

发表在 Oracle Bug | 标签为 , | 评论关闭

select default$ from col$ where rowid=:1 大量解析

在一个12.1.0.2的库的awr中发现大量47r1y8yn34jmj语句的解析
47r1y8yn34jmj


对应的完整sql为:select default$ from col$ where rowid=:1,按道理说正常的库不应该出现大量该类sql的解析,查询mos发现相关Bug 20907061 : HIGH # OF EXECUTIONS FOR RECURSIVE CALL ON COL$
20907061

发表在 ORACLE 12C, Oracle Bug | 标签为 , | 评论关闭

回收站中有大量wri$_rcs表

在对一套Oracle 12.1.0.2的数据库巡检之时发现大量WRI$_RCS_数字_1的表在回收站中,从命名中看该表应该是Oracle某个自动任务处理后,表未被正常处理干净,遗留在回收站中.
wri$_rcs


查询mos确认是Bug 20114306 – Objects left in recyclebin after upgrade to 12.1.0.2 or with fix for bug 16851194 present – superseded (文档 ID 20114306.8)
20114306

可以尝试打上补丁21498770或者23100700然后设置_fix_control

alter system set "_fix_control"='16851194:off' ;

确认该_fix_control是否可以设置,可以查询 v$system_fix_control视图

发表在 ORACLE 12C, Oracle Bug | 标签为 | 评论关闭