My Oracle Support Banner

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

Last updated on AUGUST 17, 2020

Applies to:

Enterprise Manager for Oracle Database - Version 13.3.0.0.0 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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