oracle Oracle如何预测表大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5677679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to predict table sizes Oracle?
提问by filippo
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 bytesand not gigas:)
最后，这是一个近似值。我知道我在这里和那里缺少一些字节，包括开销和其他东西。我只是想确保我只缺少字节而不是gigas:)
回答by Vincent Malgrat
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 of avg(vsize(COL1))). However, it doesn't take into account the physical arrangement of rows.
1）你的方法是合理的计算行的平均大小。（但请注意，如果您的列包含空值，则应使用avg(nvl(vsize(col1), 0))代替avg(vsize(COL1))）。但是，它没有考虑行的物理排列。
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 formatfor more information.
首先，它没有考虑标题信息（来自块和行）：您无法将 8k 数据放入 8k 块中。有关更多信息，请参阅有关数据块格式的文档。
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 on pctused).
然后，行并不总是整齐地存储。Oracle 在每个块中留出一些空间，以便行在更新时可以增长（由pctfree参数控制）。此外，当行被删除时，不会立即回收空白空间（如果您没有将 ASSM 与本地管理的表空间一起使用，则块返回到可用块列表所需的可用空间量取决于pctused）。
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.
如果您的表中已经有一些代表性数据，您可以通过将实际使用的空间（all_tables.blocks*block_size在收集统计信息后）与平均行长度进行比较来估计您需要的额外空间量。
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.
顺便说一句，Oracle 可以轻松地为您提供平均行长度的良好估计：收集表和查询的统计信息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.
2）大部分时间（阅读：除非有错误或您陷入索引的非典型使用），索引将与行数成比例增长。
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.
如果您有代表性数据，您可以通过将其实际大小乘以行数的相对增长来很好地估计其未来的大小。
回答by Adam Musch
The last time Oracle published their formulae for estimating the size of schema objectswas 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.
Oracle 上一次发布用于估计模式对象大小的公式是在 Oracle 8.0 中，这意味着链接的文档已经过时了十年。但是，我认为 Oracle 保留段头、块头或行头信息的方式不会发生太大变化。
