tbspAllocation_cdb Metric Takes Longer To Execute Against 18c Database Target (Doc ID 2678912.1)

Last updated on NOVEMBER 30, 2022

Applies to:

Symptoms

This issue was discovered in RAC Container Database target version 18c

The tablespace allocation metric takes longer to execute when database target is version 18c and causes the metric collection execution to timeout, as seen below:

$ $AGENT_HOME/bin/emctl control agent runCollection INT18T:rac_database tbspAllocation_cdb
Oracle Enterprise Manager Cloud Control 13c Release 3
Copyright (c) 1996, 2018 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection error: tbspAllocation_cdb:Collection item execution timed out in 300 seconds

$

 

The query that gets executed by tbspAllocation_cdb metric is:

SELECT /*+ first_rows */ pdb.name,
       ts.tablespace_name,
       NVL(t.bytes/1024/1024,0) allocated_space,
       NVL(DECODE(un.bytes,null,DECODE(ts.contents,'TEMPORARY',
DECODE(ts.extent_management,'LOCAL',u.bytes,t.bytes - NVL(u.bytes, 0)),
t.bytes - NVL(u.bytes, 0)), un.bytes)/1024/1024,0) used_space
  FROM
       cdb_tablespaces ts, v$containers pdb,
      (select con_id, tablespace_name, sum(bytes) bytes
       from cdb_free_space group by con_id,tablespace_name
     UNION ALL
        SELECT con_id, tablespace_name, NVL(SUM(bytes_used), 0)
          FROM gv$temp_extent_pool
        GROUP BY con_id, tablespace_name) u,
       (SELECT con_id, tablespace_name,
               SUM(NVL(bytes, 0)) bytes
          FROM cdb_data_files
      GROUP BY con_id, tablespace_name
     UNION ALL
        SELECT con_id, tablespace_name,
               SUM(NVL(bytes, 0)) bytes
          FROM cdb_temp_files
      GROUP BY con_id, tablespace_name) t,
       (SELECT ts.con_id, ts.tablespace_name,
NVL(um.used_space*ts.block_size, 0) bytes
          FROM cdb_tablespaces ts, cdb_tablespace_usage_metrics um
         WHERE ts.tablespace_name = um.tablespace_name(+)
           AND ts.con_id = um.con_id(+)
           AND ts.contents='UNDO') un
 WHERE ts.tablespace_name = t.tablespace_name(+)
   AND ts.tablespace_name = u.tablespace_name(+)
   AND ts.tablespace_name = un.tablespace_name(+)
   AND ts.con_id = pdb.con_id
   AND ts.con_id = u.con_id(+)
   AND ts.con_id = t.con_id(+)
   AND ts.con_id = un.con_id(+)
ORDER BY 1,2

Changes

 N/A

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.