tablespace query hangs
Hi Support,
A simple below query to check tablespace usage from application just hangs.
We have CDB with single PDB. We tried running the same sql from database node as sys user,it just hangs.
Database version 19.17 2 node RAC
From CDB,it gives results. But from PDB it just hangs. Back end session shows db file sequential read.
This is happening in UAT. From prod,it is giving results immediately.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8w2c6js39gc35, child number 0
-------------------------------------
SELECT df.tablespace_name , round((df.total_space/(1024*1024*1024)
),2) total_space , round(((df.total_space -
fs.free_space)/(1024*1024*1024)),2) used_space ,
round((fs.free_space/(1024*1024*1024)),2) free_space ,
round(((df.total_space-fs.free_space) / df.total_space)*100, 2)
PCT_USED FROM ( SELECT tablespace_name, SUM(bytes) TOTAL_SPACE
FROM dba_data_files GROUP BY tablespace_name) df , ( SELECT
tablespace_name, SUM(bytes) FREE_SPACE FROM dba_free_space
GROUP BY tablespace_name) fs WHERE df.tablespace_name =
fs.tablespace_name(+) and df.tablespace_name in ('SYSTEM',
'APPS_TS_SEED', 'APPS_TS_TX_DATA', 'APPS_TS_TX_IDX') ORDER BY
fs.tablespace_name
Plan hash value: 1968685924
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes 0