深度解析dba_segments和sys.seg$中的細節差異(下)

jeanron100發表於2015-05-06
繼續昨天的內容 http://blog.itpub.net/23718752/viewspace-1624762/
我們已經根據dba_segments和sys.seg$的不同發現最後的差距有2T左右,已經定位到了dba_segments的一些細節資訊,可以發現其實還是一個層級的呼叫關係。

我們把SYS_DBA_SEGS是一個處於中間層的角色,它的定義是3個union all,可以從定義中看到,差別主要還是segment_type的不同,我們採用逐個擊破的方法,一個一個來看。
--&gt第一個子查詢
 select NVL(u.name, 'SYS'), sum(s.blocks)
                               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#
                                 and u.name='PRDAPPO'
group by u.name                               
NVL(U.NAME,'SYS')              SUM(S.BLOCKS)
------------------------------ -------------
PRDAPPO                            323983920
SQL> select 32398390*8192/1024/1024 size_MB from dual;
   SIZE_MB
----------
253112.422
--&gt第二個子查詢。
select NVL(u.name, 'SYS'),sum( s.blocks)
                               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#
                                 and u.name='PRDAPPO'
group by u.name  

no rows selected
--&gt第三個子查詢
select NVL(u.name, 'SYS'), sum( s.blocks)
                               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#
                                 and u.name='PRDAPPO'
group by u.name         
no rows selected

所以看來主要的資料還是在第一個子查詢,但是如果細想,有點奇怪啊,基表中查到的資料是2.6T左右。那剩下的2T還沒有找到原因,到底差在哪了。
我們這個時候可以往回看,sys.seg$裡的資訊得到的是2.6T,dba_segments裡面得到的資訊是5T左右。那麼唯一的差別就在於sys_dba_segs了,是不是這個中間表做了什麼操作呢。
我們擷取相關的欄位檢視一下。
select sum(decode(bitand(segment_flags, 131072), 131072, blocks,
                                          (decode(bitand(segment_flags,1),1,
                                           dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
                                           header_block, segment_type_id, buffer_pool_id, segment_flags,
                                           segment_objd, blocks), blocks))))
                                           from sys_dba_segs where owner='PRDAPPO'  ;
SUM(DECODE(BITAND(SEGMENT_FLAGS,131072),131072,BLOCKS,(DECODE(BITAND(SEGMENT_FLA
--------------------------------------------------------------------------------
                                                                       607401104

這下數字就對上了,可以看到在統計過程中,做了大量的判斷,可以從下面改動的語句中做一些基本的分析。
SQL> select 
    sum(decode(bitand(segment_flags, 131072), 131072,blocks)) col1,
    sum(decode(bitand(segment_flags,1),1,dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
                                           header_block, segment_type_id, buffer_pool_id, segment_flags,
                                           segment_objd, blocks)))  col2
from sys_dba_segs where owner='PRDAPPO' group by segment_flags ; 
  12860336   12860336
              4145504
 209686704  210292912
            385152992
可以從上面的語句看出,主要的差別資料都在dbms_space_admin.segment_number_blocks呼叫中產生差異。
對此,我們需要檢視一下這個包中對應的程式碼,但是不幸的是這部分程式碼做了遮蔽,我們看看是怎麼描述的。
 function segment_number_blocks(
        header_tablespace_id    in    natural ,
        header_relative_file    in    positive ,
        header_block            in    positive ,
        segment_type            in    positive ,
        buffer_pool_id          in    natural ,
        dictionary_flags        in    natural ,
        data_object_id          in    number,
        dictionary_blocks       in    number
                        ) return pls_integer;
  pragma RESTRICT_REFERENCES(segment_number_blocks,WNDS,WNPS,RNPS);
  --
  -- Returns the number of blocks which belong to the segment. Will return
  -- NULL if segment has disappeared. IS NOT to be used for any other
  -- purposes but by the views which need it and are sure that there info
  -- is correct. Else internal errors will abound
我們繼續來看一下,儘管沒有程式碼可供參考,但是我們還是能夠做些什麼,至少我們可以定位到底是哪些segment在統計時出現了大的資料出入。
我們用下面的語句來看一下。
col segment_name format a30
col partition_name format a20
select t1.segment_name,t1.partition_name,t1.sum_blocks,t2.sum_blocks,(t1.sum_blocks-t2.sum_blocks)*8192/1024/1024 diff_size_MB
from
(select owner,segment_name,partition_name,sum(blocks) sum_blocks from dba_segments where owner='PRDAPPO' group by owner,segment_name,partition_name )t1,
(select NVL(u.name, 'SYS')owner,o.name oname,o.subname,sum(s.blocks) sum_blocks
                               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#
                                 and u.name='PRDAPPO'  
group by    u.name,o.name,o.subname)t2
where t1.owner=t2.owner
and t1.segment_name=t2.oname
and t1.partition_name=t2.subname
and t1.sum_blocks-t2.sum_blocks>0
order by t1.sum_blocks-t2.sum_blocks desc 
可以看到,對於不同的segment_type產生的資料差異。可以看到在分割槽表中還是存在著較大的出入,資料差別 779705M+697946M+445368 大約是1.9T左右,可見問題的定位找到了一些突破口。
SEGMENT_TYPE       SUM_BLOCKS SUM_BLOCKS DIFF_SIZE_MB
------------------ ---------- ---------- ------------
TABLE PARTITION     292044544  192242304       779705
INDEX PARTITION     131229056   41891872    697946.75
LOB PARTITION       110592896   53585792       445368
INDEX                27807392    4629536       181077
TABLE                44770432   31578752       103060
LOBSEGMENT            5386880     220928        40359
LOBINDEX                14336      14336            0

透過上面的語句我們可以繼續分析。為什麼有些分割槽相關的段有較大的資料差異。
同時也在Metalink上查了一下,有一篇文章:Bug 12940620  Cached block/extent counts in SEG$ not updated after ADD extent
這個裡面描述的是一個bug,是關於查詢比較慢的問題,和目前的使用的場景有些類似,可以做進一步的關注。



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-1626415/,如需轉載,請註明出處,否則將追究法律責任。

相關文章