oracle效率问题

[杨无邪]

SELECT C.TABLESPACE_NAME,C.MAX_EXTENTS,C.CONTENTS,G.SEGMENT_SPACE_MANAGEMENT,(C.MAX_EXTENTS - E.CURRENT_EXTENTS) FREE_EXTENTS,SPACE,
ROUND((NVL(FREE_SPACE, 0) / SPACE) * 100, 2) FREE_RATE,E.CURRENT_EXTENTS EXTENTS_COUNT,C.NEXT_EXTENT,MAX_SPACE,MAX_AUTOEXTENSIBLE
FROM (SELECT TABLESPACE_NAME, MAX_EXTENTS, CONTENTS, NEXT_EXTENT FROM DBA_TABLESPACES) C,
(SELECT TABLESPACE_NAME, SUM(EXTENTS) CURRENT_EXTENTS FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) E,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS,ROUND(sum(MAXBYTES) / 1024 / 1024, 2) MAX_SPACE,
MAX(AUTOEXTENSIBLE) MAX_AUTOEXTENSIBLE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,SUM(BLOCKS) BLOCKS,ROUND(sum(MAXBYTES) / 1024 / 1024, 2) MAX_SPACE,
MAX(AUTOEXTENSIBLE) MAX_AUTOEXTENSIBLE FROM dba_temp_files GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
UNION
SELECT D.TABLESPACE_NAME,ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0)) / 1024 / 1024,2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER F, DBA_TEMP_FILES D, V$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME AND F.FILE_ID(+) = D.FILE_ID AND P.FILE_ID(+) = D.FILE_ID) F,
(SELECT NAME,DECODE(BITAND(TS.FLAGS, 32), 32, 'AUTO', 'MANUAL') SEGMENT_SPACE_MANAGEMENT FROM SYS.TS$ TS) G
WHERE C.TABLESPACE_NAME = E.TABLESPACE_NAME(+)
AND C.TABLESPACE_NAME = D.TABLESPACE_NAME(+)
AND C.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND C.TABLESPACE_NAME = G.NAME(+)
Order by C.TABLESPACE_NAME;
这条sql语句执行起来非常慢，不知道怎么优化？

[小海葵1]

我的天

[杨无邪]

[linwaterbin]

执行计划贴上来，才有办法帮你分析

[杨无邪]

[杨无邪]

[杨无邪]

貌似是数据太多的问题，oracle里面回收站的内容没有清楚掉

[izj]

⊙﹏⊙b汗。。。。。

[土匪八号]

我去 ，没见过这场的代码 你在过程中试一下 见几个临时表

[倾听之路]