I am looking to be able to explain the metrics we are seeing in the Tablespaces view of the Database Live View. The group I am working with is doing a PoC with the Database Agent and have some concerns around the values pulled back for TEMP tablespaces as they do not believe them to be accurate. All other tablespaces they have checked appear to have correct data populated in the Live View, however, they believe that we are assuming high-watermark for TEMP files and saying 100% used.
The group ran the below query against the monitored database as a way to show that the TEMP spaces were not taking up that much space.
select sum(blocks*8192) from v$sort_usage where tablespace='TEMP'; NOTE: 8192 is block size, that will be different for each db. SUM(BLOCKS*8192) ------------- 68157440
Can anyone explain how we are pulling these metrics, i.e. which tables and/or queries are we using?
Answer by Grzegorz W. ·
I investigated this issue and found a possible cause of different results. Your query is using v$sort_usage table, which is (from my understanding) presenting TEMP usage at given moment, like "10 queries are executed right now and they require 123400 blocks from TEMP tablespace, 987.2 KB total".
Dynatrace query is using v$temp_space_header and dba_temp_files tables to extract temp tablespaces information. I believe it's presenting all created and "allocated" temp files. They were used in the past (because database had lot of work to do at that time) and now might not be needed, but DB is not removing them. If you execute simple query:
select * from dba_temp_files;
You'll see similar data to those presented in Dynatrace.
Also, please check the documentation for three tables mentioned above:
In short words, your query is presenting actual TEMP tablespace usage and Dynatrace query is presenting how much disk space is consumed by TEMP tablespaces.
If you have further questions about that, feel free to post them here.
JDBC Sensor Pack Modification 1 Answer