Oracle DB extension autoextendable tablespaces size metric

[rastislav_danis]

Hello

customer has autoextendable tablespaces used in their Oracle DBs and Used % metric (Metric key​ com.dynatrace.extension.sql-oracle.tablespaces.usage) doesn't really show it's real used space. They use custom sql query to achieve % used based on free space available for autoextension. So if Dynatrace extention shows 92% usage, script shows 25% (considering total extendable space for datafile). Would it be possible to include this kind of metric into Oracle DB Extension?

Part of query is:

AUTOEXTEND AS (SELECT tablespace_name, round (SUM (size_to_grow),2) total_growth_tbs FROM (SELECT tablespace_name, round (SUM (maxbytes)/1024/1024,2) size_to_grow FROM dba_data_files WHERE autoextensible = 'YES' GROUP BY tablespace_name UNION SELECT tablespace_name, round (SUM (BYTES)/1024/1024,2) size_to_grow FROM dba_data_files WHERE autoextensible = 'NO' GROUP BY tablespace_name) GROUP BY tablespace_name)

 As you can see they use SUM (maxbytes), where AUTOEXTENSIBLE = "YES" and (SUM (BYTES), where AUTOEXTENSIBLE = "NO".

[TomásSeroteRoos · Accepted solution]

The extension currently uses CDB_TABLESPACE_USAGE_METRICS which according to Oracle's documentation already takes into account auto-extensible datafiles:

---

The maximum size of the tablespace, expressed as number of data blocks

• If the tablespace contains any datafiles with autoextend enabled, then this column displays the maximum size to which the tablespace can grow. Underlying storage free space, such as Oracle ASM or file system storage, is also taken into account when computing this value.

  For example:

  • If a tablespace has a current size of 5 GB, the combined maximum size of its datafiles is 32 GB, and its underlying storage has 20 GB of free space, then this column will have a value of approximately 25 GB.

  • If a tablespace has a current size of 10 GB, the combined maximum size its datafiles is 20 GB, and its underlying storage has 25 GB of free space, then this column will have a value of approximately 20 GB.

• If the tablespace contains only datafiles with autoextend disabled, then this column displays the combined size of all datafiles in the tablespace.

---

In the past we had a similar query to the one you show, but the trouble is we had to keep adjusting it since there were always edge cases to consider. The thought was that by using Oracle's official view for tablespace usage we would get the most accurate metrics. And indeed most customers report that these metrics provide the accurate value.

If in your case you see a discrepancy in what you expect vs. what is actually reported in this view, maybe it makes sense to, in a first instance, reach out to Oracle? As I hope you can understand, we are reticent to make these kind of potentially breaking changes while we know that for most customers the data is reported fine.

[rastislav_danis]

Thanks, now i see that this should show correct values. I will check why extension's query for TBSP space shows low values for Total space metric (com.dynatrace.extension.sql-oracle.tablespaces.totalSpace) at customer's db and post update asap (will check what extension's query in tablespaces featureset returns).

[rastislav_danis]

Now i have customer's dba info and it seems that there was confusion with actual Oracle TBSP USAGE metrics. They historicaly used some inhouse solution, probably from past times, when maybe DBA_TABLESPACE_USAGE_METRICS view didn't exist (introduced from 11.2 as i digged in history) 😉

[TomásSeroteRoos]

I see! Then I take it everything is fine using this view?

[rastislav_danis]

As they were used to their own monitoring style for a long ages it will take some time to deal with requests for some "extra" metrics. But yes, they understood the message. Thanks for explanation.