深度解析dba_segments和sys.seg$中的細節差異(下)
繼續昨天的內容 http://blog.itpub.net/23718752/viewspace-1624762/
我們已經根據dba_segments和sys.seg$的不同發現最後的差距有2T左右，已經定位到了dba_segments的一些細節資訊，可以發現其實還是一個層級的呼叫關係。
我們把SYS_DBA_SEGS是一個處於中間層的角色，它的定義是3個union all，可以從定義中看到，差別主要還是segment_type的不同，我們採用逐個擊破的方法，一個一個來看。
-->第一個子查詢
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
-->第二個子查詢。
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
-->第三個子查詢
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
------------------ ---------- ---------- ------------
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，是關於查詢比較慢的問題,和目前的使用的場景有些類似，可以做進一步的關注。
我們已經根據dba_segments和sys.seg$的不同發現最後的差距有2T左右，已經定位到了dba_segments的一些細節資訊，可以發現其實還是一個層級的呼叫關係。
我們把SYS_DBA_SEGS是一個處於中間層的角色，它的定義是3個union all，可以從定義中看到，差別主要還是segment_type的不同，我們採用逐個擊破的方法，一個一個來看。
-->第一個子查詢
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
-->第二個子查詢。
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
-->第三個子查詢
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對此，我們需要檢視一下這個包中對應的程式碼，但是不幸的是這部分程式碼做了遮蔽，我們看看是怎麼描述的。
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左右，可見問題的定位找到了一些突破口。
------------------ ---------- ---------- ------------
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 深度解析dba_segments和sys.seg$中的細節差異(上)
- 深度學習 SSD的理解和細節分析深度學習
- Java 異常處理中的種種細節！Java
- 簡單對比MySQL和Oracle中的一個sql解析細節MySqlOracle
- linux 下的差異和增量備份(轉)Linux
- jquery版本中的差異jQuery
- 細述企業級儲存NAS和SAN差異
- UDP和TCP的差異UDPTCP
- vue和react的差異VueReact
- ERP差異來源和差異處理
- 深度比較常見庫中序列化和反序列化效能的效能差異
- MySQL中myisam和innodb有什麼差異？MySql
- 深度解密HTTP通訊細節解密HTTP
- Linux系統中Ubuntu和Redhat的差異有哪些?LinuxUbuntuRedhat
- resin與jetty解析el表示式差異Jetty
- oracle中update的細節Oracle
- 一文讀懂深度學習與機器學習的差異深度學習機器學習
- Oracle 8i 與 oracle 9 下 RMAN 中 CROSSCHECK 使用的差異OracleROS
- Gradle中的差異化構建Gradle
- 並行和非並行在不通場景中的效能差異並行
- 解析SwiftUI佈局細節（一）SwiftUI
- 《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(五)筆記SQLOracle函式
- 《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(三)筆記SQLOracle函式
- 《卸甲筆記》-PostgreSQL和Oracle的SQL差異分析之五：函式的差異(一)筆記SQLOracle函式
- 深度解析VC中的訊息傳遞機制（下）
- Bootstrap和Tailwind CSS之間的差異？bootAICSS
- Java和C++的基本差異(轉)JavaC++
- dba_tables 和 dba_segments 表中 blocks 的區別BloC
- TrustStrategy——Java中httpclient.jar和httpcore.jar版本差異帶來的異常RustJavaHTTPclientJAR
- 關於在SQL語句中ON和WHERE中條件使用的差異SQL
- java中for迴圈和ArrayList的詳細解析案例Java
- 兩表中某列的資料差異
- Index Full Scan和Index Fast Full Scan行為差異分析（下）IndexAST
- delphi中回撥函式差異函式
- 工作流和BPM之間的差異
- 談談 mysql和oracle的使用感受 -- 差異MySqlOracle
- 聊聊t-io和netty的差異Netty
- mac和windows執行maven命令的差異MacWindowsMaven