Hi There,
I have a case with an 11.2.0.4 database where the query that display tablespace information from OEM12cR4 is extremely slow and is causing a timout.
This is the query:
WITH df AS
(SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext
FROM dba_data_files GROUP BY tablespace_name), tf AS (
SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext
FROM dba_temp_files GROUP BY tablespace_name), sp AS
(SELECT tablespace_name, used_percent
FROM dba_tablespace_usage_metrics)
SELECT d.tablespace_name, TO_CHAR(s.used_percent, '99999990.0'), NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), a.autoext, NVL(a.bytes / 1024 / 1024, 0), NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024, NVL(f.bytes, 0) / 1024 / 1024, d.status, a.cnt, d.contents, d.extent_management, d.segment_space_management
FROM dba_tablespaces d, df a, sp s,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name ) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND NOT d.contents = 'UNDO' AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) AND d.tablespace_name LIKE '%' UNION ALL
SELECT d.tablespace_name, TO_CHAR(s.used_percent, '99999990.0'), NVL((t.ub*d.block_size) / a.bytes * 100, 0), a.autoext, NVL(a.bytes / 1024 / 1024, 0), NVL(t.ub*d.block_size, 0)/1024/1024, (NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), d.status, a.cnt, d.contents, d.extent_management, d.segment_space_management
FROM dba_tablespaces d, tf a, sp s,
(SELECT ss.tablespace_name , sum(ss.used_blocks) ub
FROM gv$sort_segment ss GROUP BY ss.tablespace_name ) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' and d.tablespace_name LIKE '%' UNION ALL
SELECT d.tablespace_name, TO_CHAR(s.used_percent, '99999990.0'), NVL(u.bytes / a.bytes * 100, 0), a.autoext, NVL(a.bytes / 1024 / 1024, 0), NVL(u.bytes, 0) / 1024 / 1024, NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, d.status, a.cnt, d.contents, d.extent_management, d.segment_space_management
FROM dba_tablespaces d, df a, sp s,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_undo_extents
where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name ) u
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' AND d.tablespace_name LIKE '%' ORDER BY 1
After checking tablespace segments, one of them got more that 240'000 segments!
Half of them where considered as slow after runing this query:
select tablespace_name
, count(*) as seg_cnt
, sum(DECODE(bitand(segment_flags,131072),0,1)) as slow_segs
from sys.sys_dba_segs
where bitand(segment_flags,1)=1
and segment_type not in ('ROLLBACK', 'DEFERRED ROLLBACK', 'TYPE2 UNDO')
group by tablespace_name
having sum(DECODE(bitand(segment_flags,131072),0,1)) > 0;
Thanks to: Oracle slow SQL query against dba_segments solved | DaDBm
After correcting the sizing information, I still have the same problem.
My initial thought is that I have to many segments for that particular tablespace' I should switch to a good uniform size.
In addition, as the database is managed from a cloud control, every 30 minutes, they query runs and might impact the system to query tablespace free space...
Any idea?
Cheers
jko