EM 13c: SQL On Tablespace Performs Very Poorly on INSERT INTO MGMT_DB_SIZE_GTT (Doc ID 2724898.1)

Last updated on DECEMBER 04, 2023

Applies to:

Symptoms

 SQL on Tablespace performs very poorly

IO Intensive:
+++++++++
INSERT INTO mgmt_db_size_gtt
SELECT  free.tablespace_name,
       SUM(free.sz) AS sz
FROM
       (
               SELECT  tablespace_name,
                       NVL(SUM(bytes)/1048576, 0) sz
               FROM    sys.dba_free_space
               GROUP BY tablespace_name
               UNION ALL
               SELECT  tablespace_name,
                       NVL(SUM(bytes)/1048576, 0) sz
               FROM    dba_undo_extents
               WHERE   status='EXPIRED'
               GROUP BY tablespace_name
       )
       free
GROUP BY free.tablespace_name

CPU Intensive:
+++++++++


SELECT m.tablespace_name, ROUND(m.used_percent, 2),
      ROUND((m.tablespace_size - m.used_space)*t.block_size/1024/1024, 3) mb_free
FROM dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p
WHERE p.name='statistics_level' and p.value!='BASIC'
AND t.contents NOT IN ('TEMPORARY', 'UNDO')
AND t.tablespace_name = m.tablespace_name

Changes

Cause

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