从DBA_SEGMENTS说起

白鳝的洞穴 2020-07-06

3881

前几天一个客户的数据库访问dba_segments出现大量的IO开销，导致了一套财务系统的性能问题。当时客户很疑惑，访问一张系统视图，怎么会出现这么多的IO开销，引起系统的问题呢？

实际上，如果DBA的基本功扎实的话，这类问题不太难回答。自从表空间的管理从字典管理升级为本地管理后，针对空间相关的字典表的锁冲突大大降低了，超大数据库（当时的概念是超过1TB，不过这个超大的概念并无定论，和你的数据库运行环境与业务有关）的性能得到了改善。万事有利必有弊，这方面性能提升的代价是如果我们要查询和段空间、数据文件空间相关的信息变得麻烦起来，以前只要访问几张字典表的操作变成了对数据文件中段的扫描。自从使用本地空间管理后，大家感触最深的是查询表空间的空闲空间变得慢了起来，在一个有几百甚至几千个数据文件的系统中查询一下DBA_FREE_SPACE可能需要花上几分钟甚至几十分钟。这是因为访问DBA_FREE_SPACE去统计空闲空间等于把所有的数据文件扫描一遍，找到可用的空闲空间。前些年老白几乎每年都能遇到查询数据库空闲空间导致的系统故障，因为很多做数据库监控工具的厂商没有意识到这个问题，每隔几分钟，甚至不到1分钟就会自动去统计空闲空间，在某些比较大的数据库或者IO十分敏感的环境中，有可能这种查询有时候需要耗时几十分钟甚至几个消失，一个查询还没有结束，另外一个查询就发起了，在业务高峰期，这种操作十分有害。

对DBA_FREE_SPACE的认知大多数DBA是比较清楚的，对DBA_EXTENTS大家可能也比较清晰。UET$表已经废弃多时了，现在所有的和扩展有关的访问实际上也是在扫描数据文件。不过可能是老白离开一线的时间太长了，这个问题刚刚到我手里的时候，还没反应过来，DBA_SEGMENTS不就是SEG$的一个试图视角吗？怎么会产生大量的IO呢？于是我让客户做了个10046的TRACE，居然发现大量的db sequential read出现在一些用户的文件上，当时我也没有回过神来。经过两次10046的确认，我觉得是不是DBA_SEGMENTS也有一些字段需要扫描数据文件，于是看了看DBA_SEGMENTS视图。

从上面的字段看，如果需要扫描数据文件，那么也就是这几个画了红线的字段了。于是我检查了一下DBA_SEGMENS的定义：

这个视图并不和我的想象一样是直接访问seg$的，而是从一个系统视图sys_dba_segs中去查找数据的。其中和空间相关的几个字段是通过dbms_space_admin来访问的。sys_dba_segs视图的结构也十分复杂：

CREATE OR REPLACE FORCE VIEW "SYS"."SYS_DBA_SEGS" ("OWNER", "SEGMENT_NAME", "P

ARTITION_NAME", "SEGMENT_TYPE", "SEGMENT_TYPE_ID", "SEGMENT_SUBTYPE", "TABLESPAC

E_ID", "TABLESPACE_NAME", "BLOCKSIZE", "HEADER_FILE", "HEADER_BLOCK", "BYTES", "

BLOCKS", "EXTENTS", "INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX_EXTENTS

", "MAX_SIZE", "RETENTION", "MINRETENTION", "PCT_INCREASE", "FREELISTS", "FREELI

ST_GROUPS", "RELATIVE_FNO", "BUFFER_POOL_ID", "FLASH_CACHE", "CELL_FLASH_CACHE",

"SEGMENT_FLAGS", "SEGMENT_OBJD") AS

select NVL(u.name, 'SYS'), o.name, o.subname,

so.object_type, s.type#,

decode(bitand(s.spare1, 2097408), 2097152, 'SECUREFILE', 256, 'ASSM', 'MS

SM'),

ts.ts#, ts.name, ts.blocksize,

f.file#, s.block#,

NVL(s.blocks, 0) * ts.blocksize, NVL(s.blocks, 0), s.extents,

s.iniexts * ts.blocksize,

s.extsize * ts.blocksize,

s.minexts, s.maxexts,

decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL),

to_char(decode(bitand(s.spare1, 2097152), 2097152,

decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX',

4, 'DEFAULT', 'INVALID'), NULL)),

decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL),

decode(bitand(ts.flags, 3), 1, to_number(NULL),

s.extpct),

decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(s.lists, 0, 1, s.lists)),

decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(s.groups, 0, 1, s.groups)),

s.file#, bitand(s.cachehint, 3), bitand(s.cachehint, 12)/4,

bitand(s.cachehint, 48)/16, NVL(s.spare1,0),

decode(bitand(s.spare1, 1), 1, s.hwmincr, o.dataobj#)

from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,

sys.file$ f

where s.file# = so.header_file

and s.block# = so.header_block

and s.ts# = so.ts_number

and s.ts# = ts.ts#

and o.obj# = so.object_id

and o.owner# = u.user# (+)

and s.type# = so.segment_type_id

and o.type# = so.object_type_id

and s.ts# = f.ts#

and s.file# = f.relfile#

union all

select NVL(u.name, 'SYS'), un.name, NULL,

decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'), s.type#,

NULL, ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,

NVL(s.blocks, 0) * ts.blocksize, NVL(s.blocks, 0), s.extents,

s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,

s.maxexts,

decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL),

NULL, NULL, s.extpct,

decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(s.lists, 0, 1, s.lists)),

decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(s.groups, 0, 1, s.groups)),

s.file#, bitand(s.cachehint, 3), bitand(s.cachehint, 12)/4,

bitand(s.cachehint, 48)/16, NVL(s.spare1,0), un.us#

from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f

where s.file# = un.file#

and s.block# = un.block#

and s.ts# = un.ts#

and s.ts# = ts.ts#

and s.user# = u.user# (+)

and s.type# in (1, 10)

and un.status$ != 1

and un.ts# = f.ts#

and un.file# = f.relfile#

union all

select NVL(u.name, 'SYS'), to_char(f.file#) || '.' || to_char(s.block#), NULL,

decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY',

4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#,

NULL, ts.ts#, ts.name, ts.blocksize,

f.file#, s.block#,

NVL(s.blocks, 0) * ts.blocksize, NVL(s.blocks, 0), s.extents,

s.iniexts * ts.blocksize,

s.extsize * ts.blocksize,

s.minexts, s.maxexts,

decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL),

NULL, NULL, decode(bitand(ts.flags, 3), 1, to_number(NULL),

s.extpct),

decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(s.lists, 0, 1, s.lists)),

decode(bitand(ts.flags, 32), 32, to_number(NULL),

decode(s.groups, 0, 1, s.groups)),

s.file#, bitand(s.cachehint, 3), bitand(s.cachehint, 12)/4,

bitand(s.cachehint, 48)/16, NVL(s.spare1,0), s.hwmincr

from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f

where s.ts# = ts.ts#

and s.user# = u.user# (+)

and s.type# not in (1, 5, 6, 8, 10)

and s.ts# = f.ts#

and s.file# = f.relfile#

通过这个方式我们搞清楚DBA_SEGMETNS是怎么来的，再来看用户的这个问题就比较简单了。实际上客户访问dba_segments大多数情况下是要查一查某个用户下有多少个SEGMENTS，甚至有些查询去访问dba_tables也是可以解决的。由于开发人员喜欢写SELECT * FROM ....，或者通过ORM随意的去访问一张表。所以导致了这个问题。弄清楚原因后，要优化就比较方便了。开发商修改一下访问DBA_SEGMENTS的逻辑，只在必要时才去查询这几个需要通过dbms_space_admin查找的字段，这个问题就迎刃而解了。

通过这个十分简单的案例，我们要了解一个事情，就是有一些系统视图，一定要了解清楚其根源才能更好的去使用它。就像前阵子老白说过的关于共享池的X$视图的访问导致共享池故障的问题一样，如果不理解就盲目的使用，是存在很大的风险的。

技术

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

从DBA_SEGMENTS说起

评论