querying system tables is consuming time more than usual
I have oracle rac 19c with 2 nodes on linux 7, the below query to check tablespace size is running on the system every almost couple of hours, usually it takes 2 sec maximum to complete but now its almost taking 8 sec to finish
set pagesize 100;
select tab.Name as TABLESPACE_NAME, trunc((tab.usd)) as
USED, tab.sz,((tab.usd*100)/tab.sz) as pecentage_used from (SELECT d.tablespace_name NAME,
NVL (a.BYTES / 1024 / 1024, 0) sz, ( (NVL (a.BYTES / 1024 /1024, 0))
- (NVL (NVL (f.BYTES,0), 0) / 1024 /1024)
) usd
from SYS.dba_tablespaces d,
(SELECT tablespace_name, autoextensible, SUM (BYTES) BYTES
from dba_data_files
GROUP BY tablespace_name, autoextensible) a,