当前位置：首页 -> AI编程基础 -> 数据库编程

回收站引发ORACLE查询表空间使用缓慢问题(一)

2014-11-24 16:10:22 · 作者: · 浏览: 1
标签: 回收站 引发 ORACLE 查询表 空间 使用 缓慢 问题
回收站引发ORACLE查询表空间使用缓慢问题
一个哥们问我 ，他们查询 表空间使用率 跑了一个多小时，这个太坑爹了，让我 帮忙优化一下。
SQL语句如下 
[html] 
select * from   
    (  
      select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",  
             to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,  
             datafiles,total_gb  "TOTAL_SIZE_GB",  
            (total_gb-free_gb) "USED_SIZE_GB",  
             free_gb "FREE_SIZE_GB",  
             round((100-free_gb/total_gb*100),2) "USED_PCT",  
             round(free_gb/total_gb*100,2) "FREE_PCT"   
      from  dba_tablespaces ts,  
            (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb   
             from dba_free_space group by tablespace_name) fr1,  
            (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles    
             from dba_data_files group by tablespace_name) df1   
      where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"  
    )   
union all  
select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,  
       total_gb "TOTAL_SIZE_GB",  
       round(total_gb-free_gb,2) "USED_SIZE_GB",  
       free_gb "FREE_SIZE_GB",  
       round((100-free_gb/total_gb*100),2) "USED_PCT",  
       round(free_gb/total_gb*100,2) "FREE_PCT"   
from  (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,  
      (select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2  
union all  
select null,null,null,null,null,null,null,null,null from dual;

这个SQL挺简单的， 就是访问几个数据字典而已，执行计划如下 
[html] 
SQL> set autotrace traceonly;  
SQL>       select * from   
  2            (  
  3              select ts.tablespace_na






me,ts.contents "TABLESPACE_TYPE",  
  4                     to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,  
  5                     datafiles,total_gb  "TOTAL_SIZE_GB",  
  6                    (total_gb-free_gb) "USED_SIZE_GB",  
  7                     free_gb "FREE_SIZE_GB",  
  8                     round((100-free_gb/total_gb*100),2) "USED_PCT",  
  9                     round(free_gb/total_gb*100,2) "FREE_PCT"   
            from  dba_tablespaces ts,  
 10   11                    (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb   
 12                     from dba_free_space group by tablespace_name) fr1,  
 13                    (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles    
 14                     from dba_data_files group by tablespace_name) df1   
 15              where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"  
 16            )   
 17        union all  
 18        select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,  
 19               total_gb "TOTAL_SIZE_GB",  
 20               round(total_gb-free_gb,2) "USED_SIZE_GB",  
 21               free_gb "FREE_SIZE_GB",  
 22               round((100-free_gb/total_gb*100),2) "USED_PCT",  
 23               round(free_gb/total_gb*100,2) "FREE_PCT"   
 24        from  (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,  
 25              (select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2 
 26        union all  
 27        select null,null,null,null,null,null,null,null,null from dual;  
  
  
  
  
31 rows selected.  
  
Elapsed: 00:50:32.18  
  
Execution Plan  
----------------------------------------------------------  
Plan hash value: 3463738489  
  
--------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                               | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
-------------------------------------