为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。 【DM版本】:dm8 【操作系统】：麒麟 【CPU】:鲲鹏 【问题描述】*：表空间使用率查询很慢，数据量100G左右，几十秒才出结果
试试这个查看，看看是否也会很慢
SELECT TS.NAME AS TABLESPACE_NAME ,ROUND(TS.TOTAL_SIZE * PAGE / 1024.0 / 1024.0,2) AS TOTAL_MB ,ROUND(TS.USED_SIZE * PAGE / 1024.0 / 1024.0,2) AS USED_MB ,ROUND((TS.TOTAL_SIZE - TS.USED_SIZE) * PAGE / 1024.0 / 1024.0,2) AS FREE_MB ,DF.PATH AS DATAFILE_PATH ,ROUND(DF.TOTAL_SIZE * PAGE / 1024.0 / 1024.0,2) AS DATAFILE_TOTAL_MB ,ROUND((DF.TOTAL_SIZE - DF.FREE_SIZE) * PAGE / 1024.0 / 1024.0,2) AS DATAFILE_USED_MB ,ROUND(DF.FREE_SIZE * PAGE / 1024.0 / 1024.0,2) AS DATAFILE_FREE_MB --,ROUND(LEAST(DF.TOTAL_SIZE,GREATEST((DF.FREE_PAGE_NO - 1),4096)) * PAGE / 1024.0 / 1024.0,2)AS DATAFILE_MINSIZE FROM V$TABLESPACE TS ,V$DATAFILE DF WHERE DF.GROUP_ID = TS.ID ORDER BY TS.ID,DF.ID
给SYS用户收集下统计信息看看
CALL SP_SET_PARA_double_VALUE(1, 'FILE_SCAN_PERCENT', 5.0); 调低这个抽样比例，应该有优化。
试试这个查看，看看是否也会很慢
SELECT TS.NAME AS TABLESPACE_NAME ,ROUND(TS.TOTAL_SIZE * PAGE / 1024.0 / 1024.0,2) AS TOTAL_MB ,ROUND(TS.USED_SIZE * PAGE / 1024.0 / 1024.0,2) AS USED_MB ,ROUND((TS.TOTAL_SIZE - TS.USED_SIZE) * PAGE / 1024.0 / 1024.0,2) AS FREE_MB ,DF.PATH AS DATAFILE_PATH ,ROUND(DF.TOTAL_SIZE * PAGE / 1024.0 / 1024.0,2) AS DATAFILE_TOTAL_MB ,ROUND((DF.TOTAL_SIZE - DF.FREE_SIZE) * PAGE / 1024.0 / 1024.0,2) AS DATAFILE_USED_MB ,ROUND(DF.FREE_SIZE * PAGE / 1024.0 / 1024.0,2) AS DATAFILE_FREE_MB --,ROUND(LEAST(DF.TOTAL_SIZE,GREATEST((DF.FREE_PAGE_NO - 1),4096)) * PAGE / 1024.0 / 1024.0,2)AS DATAFILE_MINSIZE FROM V$TABLESPACE TS ,V$DATAFILE DF WHERE DF.GROUP_ID = TS.ID ORDER BY TS.ID,DF.ID