Oracle 如何预测表大小？
我正在尝试对我拥有的一些表进行增长预测，为此我必须对行大小、每天生成的行数以及数学进行一些计算。
我将表中每行的平均大小计算为每个字段的平均大小之和。基本上来说：
SELECT 'COL1' , avg(vsize(COL1)) FROM TABLE union
SELECT 'COL2' , avg(vsize(COL2)) FROM TABLE
总结一下，乘以一天的条目数，然后据此进行预测。
事实证明，对于我查看过的其中一张表，结果大小比我想象的要小得多，这让我想知道我的方法是否正确。
另外，我的预测没有考虑索引大小——当然我应该考虑。
我的问题是：
我使用的这个方法可靠吗？
有关如何对索引进行预测的提示？
我已经完成了谷歌搜索，但我找到的方法都是关于段和扩展或基于整个表的计算。我将需要使用表的实际行进行预测（我必须分析表中的数据才能计算出每天有多少条记录）。
最后，这是一个近似值。我知道我到处都缺少一些字节，包括开销和其他东西。我只是想确保我只缺少字节而不是千兆:)
I'm trying to do a growth prediction on some tables I have and for that I've got to do some calculations on my row sizes, how many rows I generate by day and well.. the maths.
I'm calculating the average size of each row in my table as the sum of the average size of each field. So basicaly:
SELECT 'COL1' , avg(vsize(COL1)) FROM TABLE union
SELECT 'COL2' , avg(vsize(COL2)) FROM TABLE
Sum that up, multiply by the number of entries of a day and work the predictions from there.
Turns out that for one of the tables I've looked the resulting size is a lot smaller than I thought it would be and got me wondering if my method was right.
Also, I did not consider indexes sizes for my predictions - and of course I should.
My questions are:
Is this method I'm using reliable?
Tips on how could I work the predictions for the Indexes?
I've done my googling, but the methods I find are all about the segments and extends or else calculations based in the whole table. I will need the step with the actual row of my table to do the predictions (I have to analyse the data in the table in order to figure how many records a day).
And finally, this is an approximation. I know I'm missing some bytes here and there with overheads and stuff. I just want to make sure I'm only missing bytes and not gigas :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
1）你的方法可以很好地计算行的平均大小。 （但请注意，如果您的列包含 null，则应使用
avg(nvl(vsize(col1), 0))而不是avg(vsize(COL1))） 。但是，它没有考虑行的物理排列。首先，它没有考虑标头信息（来自块和行）：您无法将 8k 数据放入 8k 块中。请参阅有关数据块格式的文档了解更多信息。
然后，行并不总是整齐地存储。 Oracle 在每个块中留出一些空间，以便行在更新时可以增长（由
pctfree参数控制）。此外，当删除行时，不会立即回收空白空间（如果您没有将 ASSM 与本地管理的表空间一起使用，则块返回到可用块列表所需的可用空间量取决于 pctused ）。如果表中已经有一些代表性数据，则可以通过将实际使用的空间（收集统计数据后的
all_tables.blocks*block_size）与平均行进行比较来估计所需的额外空间量长度。顺便说一句，Oracle 可以轻松地为您提供平均行长度的良好估计：收集表的统计信息并查询
all_tables.avg_row_len。2）大多数时候（阅读：除非有错误或者您陷入索引的非典型使用），索引将与行数成比例增长。
如果您有代表性数据，则可以通过将其实际大小乘以行数的相对增长来很好地估计其未来的大小。
1) Your method is sound to calculate the average size of a row. (Though be aware that if your column contains null, you should use
avg(nvl(vsize(col1), 0))instead ofavg(vsize(COL1))). However, it doesn't take into account the physical arrangement of rows.First of all, it doesn't take into account the header info (from both blocks and rows): you can't fit 8k data into 8k blocks. See the documentation on data block format for more information.
Then, rows are not always stored neatly packed. Oracle lets some space in each blocks so that the rows can grow when they are updated (governed by the
pctfreeparameter). Also when the rows are deleted the empty space is not reclaimed right away (if you're not using ASSM with locally managed tablespaces, the amount of free space required for a block to return to the list of available blocks depends onpctused).If you already have some representative data in your table, you can estimate the amount of extra space you will need by comparing the space physically used (
all_tables.blocks*block_sizeafter having gathered statistics) to the average row length.By the way Oracle can easily give you a good estimate of the average row length: gather statistics on the table and query
all_tables.avg_row_len.2) Most of the time (read: unless there is a bug or you fall into an atypical use of the index), the index will grow proportionaly to the number of rows.
If you have representative data, you can have a good estimation of its future size by multiplying its actual size by the relative growth of the number of rows.
Oracle 上次发布其估算公式的时间模式对象的大小是在Oracle 8.0中的，这意味着链接的文档已经过时了十年。不过，我预计 Oracle 保留段头、块头或行头信息的方式不会有太大变化。
The last time Oracle published their formulae for estimating the size of schema objects was in Oracle 8.0, which means the linked document is ten years out of date. However, I don't expect very much has changed in how Oracle reserves segment header, block header, or row header information.