We have identified an issue where Oracle tablespace information collection through Agent2 is taking more than 60 seconds to respond, causing timeout issues and preventing proper data collection.

After reviewing the query used by Agent2, we found that the query appears to be heavily impacted by CDB_FREE_SPACE, which seems to be the primary contributor to the slow response time.

Through execution plan analysis and additional testing, we tested an alternative query and achieved significantly faster response times.

We would appreciate it if a similar approach could be considered and applied in a future version to improve Oracle tablespace monitoring performance.

The original query and the proposed query are attached below for reference.

Thank you.

ORIGINAL:

SELECT JSON_ARRAYAGG(
               JSON_OBJECT(CON_NAME VALUE
                           JSON_ARRAYAGG(
                                   JSON_OBJECT(TABLESPACE_NAME VALUE
                                               JSON_OBJECT(
                                                       'contents' VALUE CONTENTS,
                                                       'file_bytes' VALUE FILE_BYTES,
                                                       'max_bytes' VALUE MAX_BYTES,
                                                       'free_bytes' VALUE FREE_BYTES,
                                                       'used_bytes' VALUE USED_BYTES,
                                                       'used_pct_max' VALUE USED_PCT_MAX,
                                                       'used_file_pct' VALUE USED_FILE_PCT,
                                                       'used_from_max_pct' VALUE USED_FROM_MAX_PCT,
                                                       'status' VALUE STATUS
                                                   )
                                       )
                               )
                   ) RETURNING CLOB
           )
FROM (SELECT NVL(df.CON_NAME, 'NOCDB')           AS CON_NAME,
             df.TABLESPACE_NAME                  AS TABLESPACE_NAME,
             df.CONTENTS                         AS CONTENTS,
             NVL(SUM(df.BYTES), 0)               AS FILE_BYTES,
             NVL(SUM(df.MAX_BYTES), 0)           AS MAX_BYTES,
             NVL(SUM(f.FREE), 0)                 AS FREE_BYTES,
             NVL(SUM(df.BYTES) - SUM(f.FREE), 0) AS USED_BYTES,
             ROUND(CASE SUM(df.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)
                       END, 2)                   AS USED_PCT_MAX,
             ROUND(CASE SUM(df.BYTES)
                       WHEN 0 THEN 0
                       ELSE (NVL(SUM(df.BYTES) - SUM(f.FREE), 0)) / SUM(df.BYTES) * 100
                       END, 2)                   AS USED_FILE_PCT,
             ROUND(CASE SUM(df.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE NVL(SUM(df.BYTES) - SUM(f.FREE), 0) / SUM(df.MAX_BYTES) * 100
                       END, 2)                   AS USED_FROM_MAX_PCT,
             CASE df.STATUS
                 WHEN 'ONLINE' THEN 1
                 WHEN 'OFFLINE' THEN 2
                 WHEN 'READ ONLY' THEN 3
                 ELSE 0
                 END                             AS STATUS
      FROM (SELECT ct.CON$NAME                              AS CON_NAME,
                   cdf.FILE_ID,
                   ct.CONTENTS,
                   ct.STATUS,
                   cdf.FILE_NAME,
                   cdf.TABLESPACE_NAME,
                   TRUNC(cdf.BYTES)                         AS BYTES,
                   TRUNC(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES
            FROM CDB_DATA_FILES cdf,
                 CDB_TABLESPACES ct
            WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME
              AND cdf.CON_ID = ct.CON_ID) df,
           (SELECT TRUNC(SUM(BYTES)) AS FREE,
                   FILE_ID
            FROM CDB_FREE_SPACE
            GROUP BY FILE_ID) f
      WHERE df.FILE_ID = f.FILE_ID
      GROUP BY df.CON_NAME,
               df.TABLESPACE_NAME,
               df.CONTENTS,
               df.STATUS
      UNION ALL
      SELECT NVL(Y.CON_NAME, 'NOCDB')                 AS CON_NAME,
             Y.NAME                                   AS TABLESPACE_NAME,
             Y.CONTENTS                               AS CONTENTS,
             NVL(SUM(Y.BYTES), 0)                     AS FILE_BYTES,
             NVL(SUM(Y.MAX_BYTES), 0)                 AS MAX_BYTES,
             NVL(MAX(NVL(Y.FREE_BYTES, 0)), 0)        AS FREE_BYTES,
             NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0) AS USED_BYTES,
             ROUND(CASE SUM(Y.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE (SUM(Y.BYTES) / SUM(Y.MAX_BYTES) * 100)
                       END, 2)                        AS USED_PCT_MAX,
             ROUND(CASE SUM(Y.BYTES)
                       WHEN 0 THEN 0
                       ELSE (NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0)) / SUM(Y.BYTES) * 100
                       END, 2)                        AS USED_FILE_PCT,
             ROUND(CASE SUM(Y.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0) / SUM(Y.MAX_BYTES) * 100
                       END, 2)                        AS USED_FROM_MAX_PCT,
             CASE Y.STATUS
                 WHEN 'ONLINE' THEN 1
                 WHEN 'OFFLINE' THEN 2
                 WHEN 'READ ONLY' THEN 3
                 ELSE 0
                 END                                  AS STATUS
      FROM (SELECT ct.CON$NAME                  AS CON_NAME,
                   ctf.TABLESPACE_NAME          AS NAME,
                   ct.CONTENTS,
                   ctf.STATUS                   AS STATUS,
                   ctf.BYTES                    AS BYTES,
                   (SELECT ((f.TOTAL_BLOCKS - s.TOT_USED_BLOCKS) * vp.VALUE)
                    FROM (SELECT CON_ID,
                                 TABLESPACE_NAME,
                                 SUM(USED_BLOCKS) TOT_USED_BLOCKS
                          FROM V$SORT_SEGMENT
                          WHERE TABLESPACE_NAME != 'DUMMY'
                          GROUP BY CON_ID,
                                   TABLESPACE_NAME) s,
                         (SELECT CON_ID,
                                 TABLESPACE_NAME,
                                 SUM(BLOCKS) TOTAL_BLOCKS
                          FROM CDB_TEMP_FILES
                          WHERE TABLESPACE_NAME != 'DUMMY'
                          GROUP BY CON_ID,
                                   TABLESPACE_NAME) f,
                         (SELECT VALUE
                          FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') vp
                    WHERE f.TABLESPACE_NAME = s.TABLESPACE_NAME
                      AND f.TABLESPACE_NAME = ctf.TABLESPACE_NAME
                      AND f.CON_ID = s.CON_ID
                      AND f.CON_ID = ct.CON_ID) AS FREE_BYTES,
                   CASE
                       WHEN ctf.MAXBYTES = 0 THEN ctf.BYTES
                       ELSE ctf.MAXBYTES
                       END                      AS MAX_BYTES
            FROM CDB_TEMP_FILES ctf,
                 CDB_TABLESPACES ct
            WHERE ctf.TABLESPACE_NAME = ct.TABLESPACE_NAME
              AND ctf.CON_ID = ct.CON_ID) Y
      GROUP BY Y.CON_NAME,
               Y.NAME,
               Y.CONTENTS,
               Y.STATUS)
GROUP BY CON_NAME

PROPOSE:

SELECT JSON_ARRAYAGG(
               JSON_OBJECT(CON_NAME VALUE
                           JSON_ARRAYAGG(
                                   JSON_OBJECT(TABLESPACE_NAME VALUE
                                               JSON_OBJECT(
                                                       'contents' VALUE CONTENTS,
                                                       'file_bytes' VALUE FILE_BYTES,
                                                       'max_bytes' VALUE MAX_BYTES,
                                                       'free_bytes' VALUE FREE_BYTES,
                                                       'used_bytes' VALUE USED_BYTES,
                                                       'used_pct_max' VALUE USED_PCT_MAX,
                                                       'used_file_pct' VALUE USED_FILE_PCT,
                                                       'used_from_max_pct' VALUE USED_FROM_MAX_PCT,
                                                       'status' VALUE STATUS
                                                   )
                                       )
                               )
                   ) RETURNING CLOB
           )
FROM (SELECT NVL(df.CON_NAME, 'NOCDB')           AS CON_NAME,
             df.TABLESPACE_NAME                  AS TABLESPACE_NAME,
             df.CONTENTS                         AS CONTENTS,
             NVL(SUM(df.BYTES), 0)               AS FILE_BYTES,
             NVL(SUM(df.MAX_BYTES), 0)           AS MAX_BYTES,
             NVL(SUM(df.BYTES) - SUM(f.USED_BYTES), 0) AS FREE_BYTES,
             NVL(SUM(f.USED_BYTES), 0) AS USED_BYTES,
             ROUND(CASE SUM(df.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)
                       END, 2)                   AS USED_PCT_MAX,
             ROUND(CASE SUM(df.BYTES)
                       WHEN 0 THEN 0
                       ELSE (NVL(SUM(df.BYTES) - SUM(f.USED_BYTES), 0)) / SUM(df.BYTES) * 100
                       END, 2)                   AS USED_FILE_PCT,
             ROUND(CASE SUM(df.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE NVL(SUM(f.USED_BYTES), 0) / SUM(df.MAX_BYTES) * 100
                       END, 2)                   AS USED_FROM_MAX_PCT,
             CASE df.STATUS
                 WHEN 'ONLINE' THEN 1
                 WHEN 'OFFLINE' THEN 2
                 WHEN 'READ ONLY' THEN 3
                 ELSE 0
                 END                             AS STATUS
      FROM (SELECT ct.CON$NAME                              AS CON_NAME,
                   ct.CONTENTS,
                   ct.STATUS,
                   ct.TABLESPACE_NAME,
                   SUM(cdf.BYTES)                         AS BYTES,
                   SUM(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES
            FROM CDB_DATA_FILES cdf,
                 CDB_TABLESPACES ct
            WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME
              AND cdf.CON_ID = ct.CON_ID
            GROUP BY ct.CON$NAME,
               ct.TABLESPACE_NAME,
               ct.CONTENTS,
               ct.STATUS) df,
           (SELECT CON_ID,TABLESPACE_NAME,
                   TRUNC(SUM(BYTES)) AS USED_BYTES
            FROM CDB_SEGMENTS
            GROUP BY CON_ID,TABLESPACE_NAME) f
      WHERE df.TABLESPACE_NAME = f.TABLESPACE_NAME
      GROUP BY df.CON_NAME,
               df.TABLESPACE_NAME,
               df.CONTENTS,
               df.STATUS
      UNION ALL
      SELECT NVL(Y.CON_NAME, 'NOCDB')                 AS CON_NAME,
             Y.NAME                                   AS TABLESPACE_NAME,
             Y.CONTENTS                               AS CONTENTS,
             NVL(SUM(Y.BYTES), 0)                     AS FILE_BYTES,
             NVL(SUM(Y.MAX_BYTES), 0)                 AS MAX_BYTES,
             NVL(MAX(NVL(Y.FREE_BYTES, 0)), 0)        AS FREE_BYTES,
             NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0) AS USED_BYTES,
             ROUND(CASE SUM(Y.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE (SUM(Y.BYTES) / SUM(Y.MAX_BYTES) * 100)
                       END, 2)                        AS USED_PCT_MAX,
             ROUND(CASE SUM(Y.BYTES)
                       WHEN 0 THEN 0
                       ELSE (NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0)) / SUM(Y.BYTES) * 100
                       END, 2)                        AS USED_FILE_PCT,
             ROUND(CASE SUM(Y.MAX_BYTES)
                       WHEN 0 THEN 0
                       ELSE NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0) / SUM(Y.MAX_BYTES) * 100
                       END, 2)                        AS USED_FROM_MAX_PCT,
             CASE Y.STATUS
                 WHEN 'ONLINE' THEN 1
                 WHEN 'OFFLINE' THEN 2
                 WHEN 'READ ONLY' THEN 3
                 ELSE 0
                 END                                  AS STATUS
      FROM (SELECT ct.CON$NAME                  AS CON_NAME,
                   ctf.TABLESPACE_NAME          AS NAME,
                   ct.CONTENTS,
                   ctf.STATUS                   AS STATUS,
                   ctf.BYTES                    AS BYTES,
                   (SELECT ((f.TOTAL_BLOCKS - s.TOT_USED_BLOCKS) * vp.VALUE)
                    FROM (SELECT CON_ID,
                                 TABLESPACE_NAME,
                                 SUM(USED_BLOCKS) TOT_USED_BLOCKS
                          FROM V$SORT_SEGMENT
                          WHERE TABLESPACE_NAME != 'DUMMY'
                          GROUP BY CON_ID,
                                   TABLESPACE_NAME) s,
                         (SELECT CON_ID,
                                 TABLESPACE_NAME,
                                 SUM(BLOCKS) TOTAL_BLOCKS
                          FROM CDB_TEMP_FILES
                          WHERE TABLESPACE_NAME != 'DUMMY'
                          GROUP BY CON_ID,
                                   TABLESPACE_NAME) f,
                         (SELECT VALUE
                          FROM V$PARAMETER
                          WHERE NAME = 'db_block_size') vp
                    WHERE f.TABLESPACE_NAME = s.TABLESPACE_NAME
                      AND f.TABLESPACE_NAME = ctf.TABLESPACE_NAME
                      AND f.CON_ID = s.CON_ID
                      AND f.CON_ID = ct.CON_ID) AS FREE_BYTES,
                   CASE
                       WHEN ctf.MAXBYTES = 0 THEN ctf.BYTES
                       ELSE ctf.MAXBYTES
                       END                      AS MAX_BYTES
            FROM CDB_TEMP_FILES ctf,
                 CDB_TABLESPACES ct
            WHERE ctf.TABLESPACE_NAME = ct.TABLESPACE_NAME
              AND ctf.CON_ID = ct.CON_ID) Y
      GROUP BY Y.CON_NAME,
               Y.NAME,
               Y.CONTENTS,
               Y.STATUS)
GROUP BY CON_NAME