【 使用环境 】测试环境
【 OB or 其他组件 】oceanbase 4.4.1.0
【 使用版本 】
【问题描述】这些概念有啥区别，分别是如何计算的
个人感觉：数据大小好像是表大小+索引大小，那磁盘使用大小分别包含了哪几块，宏块使用率的分子和分母分别是什么呢？
个人感觉：数据大小好像是表大小+索引大小，那磁盘使用大小分别包含了哪几块，宏块使用率的分子和分母分别是什么呢？
2 个赞
select
b.table_name,
(select num_rows from dba_tables c where b.table_name=c.table_name) as “行数”,
round(sum(a.REQUIRED_SIZE)/ 1024 / 1024,2) as “磁盘使用大小(MiB)”,
round(sum(a.DATA_SIZE)/ 1024 / 1024,2) as “数据大小(MiB)”,
round(sum(a.DATA_SIZE)/sum(a.REQUIRED_SIZE)100,2)||’%’ as “宏块利用率”,
count() as “分区数”
from
dba_ob_tablet_replicas a,
dba_ob_table_locations b
where
a.tablet_id = b.tablet_id
and b.DATABASE_NAME=’’
group by
b.table_name
看下这个SQL
2 个赞
磁盘使用大小统计的宏块占用的大小(每个宏块2M），数据大小是数据真实占用的大小，宏块的利用率就是 数据真实占用大小跟宏块占用大小的比值
2 个赞
WITH CTE AS (
SELECT
DATABASE_NAME,
TABLE_NAME,
TABLE_ID,
TABLE_TYPE,
DATA_TABLE_ID,
DATA_SIZE,
REQUIRED_SIZE
from
oceanbase.DBA_OB_TABLE_LOCATIONS A,
oceanbase.DBA_OB_TABLET_REPLICAS B
where
A.TABLET_ID = B.TABLET_ID
AND A.SVR_IP = B.SVR_IP
AND DATABASE_NAME = ‘test’ – 修改对应的DATABASE_NAME
)
SELECT
DATABASE_NAME,TABLE_NAME,NUM_ROWS,
CASE
WHEN REQUIRED_SIZE>POWER(1024, 4) THEN ROUND(REQUIRED_SIZE/1024/1024/1024/1024,2)||‘TiB’
WHEN REQUIRED_SIZE>POWER(1024, 3) THEN ROUND(REQUIRED_SIZE/1024/1024/1024,2)||‘GiB’
WHEN REQUIRED_SIZE>POWER(1024, 2) THEN ROUND(REQUIRED_SIZE/1024/1024,2)||‘MiB’
WHEN REQUIRED_SIZE>POWER(1024, 1) THEN ROUND(REQUIRED_SIZE/1024,1)||‘KiB’
ELSE ‘0’ END AS REQUIRED_SIZE,
CASE
WHEN DATA_SIZE>POWER(1024, 4) THEN ROUND(DATA_SIZE/1024/1024/1024/1024,2)||‘TiB’
WHEN DATA_SIZE>POWER(1024, 3) THEN ROUND(DATA_SIZE/1024/1024/1024,2)||‘GiB’
WHEN DATA_SIZE>POWER(1024, 2) THEN ROUND(DATA_SIZE/1024/1024,2)||‘MiB’
WHEN DATA_SIZE>POWER(1024, 1) THEN ROUND(DATA_SIZE/1024,1)||‘KiB’
ELSE ‘0’ END AS DATA_SIZE,
CASE WHEN REQUIRED_SIZE=0 then 0 else round(DATA_SIZE/REQUIRED_SIZE,2)100 END||’%’ AS “宏块使用率”,
TAB_PAR_CNT
FROM
(
SELECT
T.DATABASE_NAME,
T.TABLE_NAME,
NVL((T.DATA_SIZE + T1.DATA_SIZE), 0) AS DATA_SIZE,
NVL((T.REQUIRED_SIZE + T1.REQUIRED_SIZE), 0) AS REQUIRED_SIZE,
NVL(TAB.TABLE_ROWS, 0) AS NUM_ROWS,
PAR_CNT+NVL(IDX_CN,0) AS TAB_PAR_CNT
FROM
(
SELECT
DATABASE_NAME,
TABLE_NAME,
TABLE_ID,
SUM(DATA_SIZE) AS DATA_SIZE,
SUM(REQUIRED_SIZE) AS REQUIRED_SIZE,
COUNT() PAR_CNT
FROM
CTE
WHERE
TABLE_TYPE = ‘USER TABLE’
GROUP BY
DATABASE_NAME,
TABLE_NAME,
TABLE_ID
) T
LEFT JOIN (
SELECT
DATA_TABLE_ID,
SUM(DATA_SIZE) AS DATA_SIZE,
SUM(REQUIRED_SIZE) AS REQUIRED_SIZE,
COUNT(*) AS IDX_CN
FROM
CTE
WHERE
TABLE_TYPE <> ‘USER TABLE’
GROUP BY
DATA_TABLE_ID
) T1 ON T.TABLE_ID = T1.DATA_TABLE_ID
LEFT JOIN information_schema.tables TAB ON (
TAB.TABLE_NAME = T.TABLE_NAME
AND TAB.table_schema=T.DATABASE_NAME
)
ORDER BY REQUIRED_SIZE DESC
) V
上面都是一些系统表，你对比这个没啥意义
数据大小是真实数据的占用空间情况