Database Memory & 4030/4031 (MOSC)

MOSC Banner

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,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center