回收站引发Oracle查询表空间使用缓慢
回收站引发Oracle查询表空间使用缓慢
一个同事问我 ，他们查询Oracle 表空间使用率 跑了一个多小时，这个太坑爹了，让我 帮忙优化一下。
SQL语句如下
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挺简单的， 就是访问几个数据字典而已，执行计划如下
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)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 23 | | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_TS1 | 1 | | | 0 (0)| 00:00:01 |
| 32 | VIEW | | 3507 | 102K| | 2552K(100)| 08:30:27 |
| 33 | HASH GROUP BY | | 3507 | 102K| | 2552K(100)| 08:30:27 |
| 34 | VIEW | DBA_FREE_SPACE | 3507 | 102K| | 2552K(100)| 08:30:27 |
| 35 | UNION-ALL | | | | | | |
| 36 | NESTED LOOPS | | 1 | 71 | | 4 (0)| 00:00:01 |
| 37 | NESTED LOOPS | | 1 | 46 | | 3 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 40 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 70 | 5390 | | 11 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 70 | 4900 | | 11 (0)| 00:00:01 |
|* 43 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 (0)| 00:00:01 |
|* 44 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 2 | 78 | | 0 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 3435 | 385K| | 2552K(100)| 08:30:26 |
| 47 | NESTED LOOPS | | 96156 | 9M| | 2552K(100)| 08:30:26 |
|* 48 | HASH JOIN | | 354K| 14M| 8632K| 1538 (1)| 00:00:19 |
| 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 368K| 4314K| | 1103 (1)| 00:00:14 |
|* 50 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 (0)| 00:00:01 |
|* 51 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | | 7 (100)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 (0)| 00:00:01 |
| 54 | NESTED LOOPS | | 1 | 96 | | 17 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 84 | | 15 (0)| 00:00:01 |
| 56 | NESTED LOOPS | | 1 | 59 | | 14 (0)| 00:00:01 |
| 57 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 59 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 61368 | | | 2 (0)| 00:00:01 |
| 62 | NESTED LOOPS | | 1 | 39 | | 2552K(100)| 08:30:27 |
| 63 | VIEW | | 1 | 13 | | 2552K(100)| 08:30:27 |
| 64 | SORT AGGREGATE | | 1 | 13 | | | |
| 65 | VIEW | DBA_FREE_SPACE | 3507 | 45591 | | 2552K(100)| 08:30:27 |
| 66 | UNION-ALL | | | | | | |
| 67 | NESTED LOOPS | | 1 | 57 | | 4 (0)| 00:00:01 |
| 68 | NESTED LOOPS | | 1 | 46 | | 3 (0)| 00:00:01 |
| 69 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 71 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 (0)| 00:00:01 |
| 72 | NESTED LOOPS | | 70 | 4410 | | 11 (0)| 00:00:01 |
| 73 | NESTED LOOPS | | 70 | 3920 | | 11 (0)| 00:00:01 |
|* 74 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 (0)| 00:00:01 |
|* 75 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 2 | 78 | | 0 (0)| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
| 77 | NESTED LOOPS | | 3435 | 338K| | 2552K(100)| 08:30:26 |
| 78 | NESTED LOOPS | | 96156 | 8826K| | 2552K(100)| 08:30:26 |
|* 79 | HASH JOIN | | 354K| 9M| 8632K| 1538 (1)| 00:00:19 |
| 80 | TABLE ACCESS FULL | RECYCLEBIN$ | 368K| 4314K| | 1103 (1)| 00:00:14 |
|* 81 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 (0)| 00:00:01 |
|* 82 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | | 7 (100)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 84 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 (0)| 00:00:01 |
| 85 | NESTED LOOPS | | 1 | 82 | | 17 (0)| 00:00:01 |
| 86 | NESTED LOOPS | | 1 | 70 | | 15 (0)| 00:00:01 |
| 87 | NESTED LOOPS | | 1 | 59 | | 14 (0)| 00:00:01 |
| 88 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 (0)| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 90 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
|* 92 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 61368 | | | 2 (0)| 00:00:01 |
| 93 | VIEW | | 1 | 26 | | 4 (0)| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 13 | | | |
| 95 | VIEW | DBA_DATA_FILES | 2 | 26 | | 4 (0)| 00:00:01 |
| 96 | UNION-ALL | | | | | | |
| 97 | NESTED LOOPS | | 1 | 222 | | 2 (0)| 00:00:01 |
| 98 | NESTED LOOPS | | 1 | 215 | | 1 (0)| 00:00:01 |
| 99 | NESTED LOOPS | | 1 | 202 | | 1 (0)| 00:00:01 |
|*100 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|*101 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 20 | | 1 (0)| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|*103 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 104 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 (0)| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
| 106 | NESTED LOOPS | | 1 | 265 | | 2 (0)| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 258 | | 1 (0)| 00:00:01 |
| 108 | NESTED LOOPS | | 1 | 245 | | 1 (0)| 00:00:01 |
| 109 | NESTED LOOPS | | 1 | 234 | | 0 (0)| 00:00:01 |
|*110 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|*111 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | | 0 (0)| 00:00:01 |
|*112 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 11 | | 1 (0)| 00:00:01 |
|*113 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|*114 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 115 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 (0)| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
| 117 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TS"."NAME"="FR1"."TABLESPACE_NAME")
13 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
14 - filter("F"."SPARE1" IS NULL)
15 - access("FNFNO"="F"."FILE#")
16 - filter("FE"."FENUM"="F"."FILE#")
18 - access("F"."TS#"="TS"."TS#")
23 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
24 - filter("FNFNO"="HC"."KTFBHCAFNO")
25 - filter("F"."SPARE1" IS NOT NULL)
26 - access("FNFNO"="F"."FILE#")
27 - filter("FE"."FENUM"="F"."FILE#")
29 - access("HC"."KTFBHCTSN"="TS"."TS#")
30 - filter("TS"."ONLINE$"<>3 AND BITAND("FLAGS",2048)<>2048)
31 - access("TS"."NAME"="DF1"."TABLESPACE_NAME")
39 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40 - filter("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")
43 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
44 - filter("TS"."TS#"="F"."KTFBFETSN")
45 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
48 - access("TS"."TS#"="RB"."TS#")
50 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
51 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
52 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
53 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
59 - filter("TS"."BITMAPPED"=0)
60 - access("TS"."TS#"="U"."TS#")
61 - access("U"."TS#"="RB"."TS#")
70 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
71 - filter("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")
74 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
75 - filter("TS"."TS#"="F"."KTFBFETSN")
76 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
79 - access("TS"."TS#"="RB"."TS#")
81 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
82 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
83 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
84 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
89 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
90 - filter("TS"."BITMAPPED"=0)
91 - access("TS"."TS#"="U"."TS#")
92 - access("U"."TS#"="RB"."TS#")
100 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
101 - filter("F"."SPARE1" IS NULL)
102 - access("FNFNO"="F"."FILE#")
103 - filter("FE"."FENUM"="F"."FILE#")
105 - access("F"."TS#"="TS"."TS#")
110 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
111 - filter("FNFNO"="HC"."KTFBHCAFNO")
112 - filter("F"."SPARE1" IS NOT NULL)
113 - access("FNFNO"="F"."FILE#")
114 - filter("FE"."FENUM"="F"."FILE#")
116 - access("HC"."KTFBHCTSN"="TS"."TS#")
Statistics
----------------------------------------------------------
18098002 recursive calls 1800W 的递归调用
7676 db block gets
3986596 consistent gets 398W 逻辑读
790235 physical reads 79W 物理读
0 redo size
2730 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
31 rows processed
|
评论暂时关闭