回收站引发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_name,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 |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 140 | 15495 | | 5104K(100)| 17:00:53 |
| 1 | UNION-ALL | | | | | | |
| 2 | VIEW | | 138 | 15456 | | 2552K(100)| 08:30:27 |
| 3 | SORT ORDER BY | | 138 | 13248 | | 2552K(100)| 08:30:27 |
|* 4 | HASH JOIN | | 138 | 13248 | | 2552K(100)| 08:30:27 |
| 5 | NESTED LOOPS | | 2 | 132 | | 7 (15)| 00:00:01 |
| 6 | VIEW | | 2 | 86 | | 5 (20)| 00:00:01 |
| 7 | HASH GROUP BY | | 2 | 60 | | 5 (20)| 00:00:01 |
| 8 | VIEW | DBA_DATA_FILES | 2 | 60 | | 4 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | | |
| 10 | NESTED LOOPS | | 1 | 236 | | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 215 | | 1 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 202 | | 1 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 20 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 279 | | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 258 | | 1 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 245 | | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 234 | | 0 (0)| 00:00:01 |
|* 23 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|* 24 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | | 0 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|* 27 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 (0)
- 更多Oracle疑问解答：
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有，生产Oracle Rman 备份脚本的工具啊！
- 初学orcle，希望有大大帮忙解说一下详细步骤，从登录oracle到创建表的过程
- oracle语句问题：一张user表，三个字段，id,name,time，插入记录比如：张三2007，李四2008，张三2011
- 如何写一个ORACLE触发器同步两个表中的数据？
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G，在RMAN中如何按指定的时间恢复数据文件啊？
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊？