1

I work on Oracle database 19c. I have several big Oracle tables and want to extract size of each one for each day of one month. I wrote this query:

select date,sum_size,sum(sum_size) over (order by date) from (select date,(sum(vsize(id))+sum(vsize(time))+sum(vsize(name)))/1024/1024/1024 sum_size from the_table group by date)

But, I have two issues: First, if the column was null, the above query did not return any result and I have to check the column which was not null. Second, I have several tables with a lot of columns, I'd rather if there is any solution to do this dynamically and run that on all tables.

Would you please guide me if there is better way to do that?

Any help is really appreciated.

M_Gh
  • 1,046
  • 4
  • 17
  • 43
  • Does this answer your question? [How do I calculate tables size in Oracle](https://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle) – OldProgrammer May 27 '23 at 12:02
  • @OldProgrammer, I have already read that question, I want to monitor size of the group of the tables in duration of one month and it does not help. – M_Gh May 27 '23 at 13:12
  • @M_Gh - which column in your example query was null? And I'm assuming only some rows in the column were null, not all? – Pancho May 27 '23 at 14:08
  • @Pancho, for example, *name* column in one table does not have value. – M_Gh May 27 '23 at 15:08
  • 1
    `vsize` is not very useful for this task. Because it doesn't count any storage overhead (like row headers, pctfree that cannot be used for new rows), it shows nulls to be of zero size while they may dramatically affect space consumption if they are in the middle of row (see https://dbfiddle.uk/qk91Ejum). It doesn't consider a space consumed by deletes and updates or by direct path inserts that were rolled back. It would be more accurate to use rowcount per day multiplied by manually calculated space for each row (by using block count). – astentx May 27 '23 at 18:24
  • The AWR view [DBA_HIST_SEG_STAT](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SEG_STAT.html#GUID-B0B7903A-727A-4F2A-A5DC-88937670DCFD) *may* be able to help here. Unfortunately, the view only contains data "based on a set of criteria", which seems to mean it only records information about tables that actively used. And AWR by default only stores 8 days of data, although there are ways to increase that the default storage to a month. (But be careful not to waste too much space on AWR itself.) – Jon Heller Jun 06 '23 at 04:56

1 Answers1

1

There's really no need to gather row sizes like this yourself using VSIZE. Oracle has already done this for you with its own statistics gathering. Simply gather stats (dbms_stats.gather_table_stats) and examine dba_tables.avg_row_len. For total table size, the formatted blocks under the high water mark (HWM) are recorded in dba_tables.blocks (have to multiply by the block size), and the total allocated space can always be obtained without any stats gathering at all from dba_segments.bytes.

I wrote a monitoring app that does what you're describing: gathers object space utilization every day for trending/projection purposes. You really don't need anything other than dba_segments for this purpose (though you will have to join to various other views to get fully identifying information, like dba_indexes, dba_lobs, dba_tables, dba_nested_tables, and the dba_*_partitions and dba_*_subpartitions varieties of the first three of these). This way, you can gather sizing on as many objects as you'd like as often as you'd like (I do 100% of the objects in every database, every day, which is a lot) because there's no need to do expensive querying or statistics gathering.

If you have a user-defined date column in a special table or special set of tables in which you are absolutely certain that they represent when the rows were first inserted, are never out of sequence, never incorrect, are never updated, and no deletes ever happen, you could get a historical picture after the fact from these special tables only. But it's easier to simply take the percentage of rows for a date over the total table row count and multiply that by segment size. Rows aren't going to vary in size from day to day statistically enough to matter in an average.

SELECT datecol,
       COUNT(*)/COUNT(*) OVER (PARTITION BY 1)*MAX(s.bytes) estimated_bytes
  FROM mytable t, 
       dba_segments s
 WHERE s.segment_name = 'MYTABLE'
GROUP BY datecol

But any kind of serious trending should instead take a snapshot on a daily basis and store the results rather than relying on specific column values. Think about how compression impacts things. A table can have a mixture of compressed and uncompressed rows, even a mixture of compression types. Yesterday's rows could occupy 2x-10x more space than a similar day last month with the same number of rows and VSIZE numbers (which represents the uncompressed byte length). If you're interested in Oracle space usage, you really can't compute it on a row-by-row basis. It has to be done at the segment level by daily snapshotting, not by any after-to-fact query method.

Paul W
  • 5,507
  • 2
  • 2
  • 13
  • It appears to me the OP is wanting to retrospectively look at size of table content grouped daily over a time period and by my understanding your answer provides more "the size of the segment at moment of execution" only. Am I correct in this understanding or are you able to say for example "last Wednesday table segment A was X Gb in size"? Thanks – Pancho May 27 '23 at 13:05
  • @Pancho, you're right. – M_Gh May 27 '23 at 13:13
  • You can't possibly get historical sizing like that unless you have a predefined strict correlation with a user date column in the table maintained by application code and can rely on that. For 95% of the tables out there in Oracle-land, that won't be the case. All I have to do is update that date column and the results are totally wrong. However, if you are certain you can rely on it, you are still better off simply calculating number of rows per date as a percentage of the table and multiplying that by the segment size. – Paul W May 27 '23 at 16:26
  • 1
    Edited my answer. Also another method is to range partition your table by the "date" column and then you can simply use `dba_segments.bytes` on each partition to get your by-date number. Computing space utilization per Oracle's datatype storage model is a DBA-type task that rarely relies on specific columns are user data. This is one way to align your business need with the actual structure of segments so you can get meaningful sizing data that DBAs actually can use. It really isn't useful to programmers. Think about what happens when a DBA compresses a table! VSIZE will be totally wrong. – Paul W May 27 '23 at 16:37