现象描述:
在Oracle 11g中执行如下SQL进行表空间使用情况的日常基线采集时发现,部分数据库会采集不到数据,经过程序方面的排查发现,由于该SQL的执行时间过长(15秒左右),超出了程序设定的响应阈值,故而导致数据采集不到的现象。
SELECT t.tablespace_name AS "tablespace",
substr(t.contents, 1, 4) AS "type",
trunc ((t.tbs_size - nvl (t.free_space, 0)) / 1024 / 1024) AS "used_mb",
trunc (t.tbs_size / 1024 / 1024) AS "total_mb",
trunc (nvl (t.free_space, 0) / 1024 / 1024) AS "free_mb",
trunc ((t.tbs_size - nvl (t.free_space, 0)) * 100 / t.tbs_size) AS "used_perc_%"
FROM
(SELECT a.tablespace_name,
b.tbs_size,
a.free_space,
tbs.contents
FROM
(SELECT tablespace_name,
sum(bytes) free_space
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
sum(bytes) tbs_size
FROM dba_data_files
GROUP BY tablespace_name) b,
dba_tablespaces tbs
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = tbs.tablespace_name) t
查看执行计划:
通过对上述SQL的分解,可以发现执行缓慢主要是由于FROM dba_free_space部分,而从查阅到的资料来看,有的建议清理回收站的,有的建议通过加入hint重建dba_free_space视图的别名视图,但感觉需要做出的改动都较大。我们直接来看一下该SQL的执行计划:
SQL> alter session set statistics_level=ALL;
Session altered.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID akbmdv5gfs26k, child number 1
-------------------------------------
SELECT t.tablespace_name AS "tablespace", substr(t.contents, 1,
4) AS "type", trunc ((t.tbs_size - nvl (t.free_space, 0)) / 1024
/ 1024) AS "used_mb", trunc (t.tbs_size / 1024 / 1024) AS
"total_mb", trunc (nvl (t.free_space, 0) / 1024 / 1024) AS
"free_mb", trunc ((t.tbs_size - nvl (t.free_space, 0)) * 100 /
t.tbs_size) AS "used_perc_%" FROM (SELECT a.tablespace_name,
b.tbs_size, a.free_space, tbs.contents FROM
(SELECT tablespace_name, sum(bytes) free_space FROM
dba_free_space GROUP BY tablespace_name) a, (SELECT
tablespace_name, sum(bytes) tbs_size FROM
dba_data_files GROUP BY tablespace_name) b,
dba_tablespaces tbs WHERE a.tablespace_name =
b.tablespace_name AND a.tablespace_name = tbs.tablespace_name) t
Plan hash value: 2040129742
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 182 (100)| | 17 |00:00:14.67 | 221K| 35475 |
| 1 | NESTED LOOPS | | 1 | 11 | 182 (37)| 00:00:03 | 17 |00:00:14.67 | 221K| 35475 |
| 2 | NESTED LOOPS | | 1 | 2 | 182 (37)| 00:00:03 | 17 |00:00:14.65 | 221K| 35475 |
|* 3 | HASH JOIN | | 1 | 2 | 180 (38)| 00:00:03 | 17 |00:00:14.65 | 221K| 35475 |
| 4 | VIEW | | 1 | 2 | 5 (20)| 00:00:01 | 17 |00:00:00.09 | 408 | 0 |
| 5 | HASH GROUP BY | | 1 | 2 | 5 (20)| 00:00:01 | 17 |00:00:00.09 | 408 | 0 |
| 6 | VIEW | DBA_DATA_FILES | 1 | 2 | 4 (0)| 00:00:01 | 99 |00:00:00.08 | 408 | 0 |
| 7 | UNION-ALL | | 1 | | | | 99 |00:00:00.08 | 408 | 0 |
| 8 | NESTED LOOPS | | 1 | 1 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 103 | 0 |
| 9 | NESTED LOOPS | | 1 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 103 | 0 |
| 10 | NESTED LOOPS | | 1 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 103 | 0 |
|* 11 | FIXED TABLE FULL | X$KCCFN | 1 | 1 | 0 (0)| | 99 |00:00:00.01 | 0 | 0 |
|* 12 | TABLE ACCESS BY INDEX ROWID| FILE$ | 99 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 103 | 0 |
|* 13 | INDEX UNIQUE SCAN | I_FILE1 | 99 | 1 | 0 (0)| | 99 |00:00:00.01 | 4 | 0 |
|* 14 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
| 15 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 16 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
| 17 | NESTED LOOPS | | 1 | 1 | 2 (0)| 00:00:01 | 99 |00:00:00.08 | 305 | 0 |
| 18 | NESTED LOOPS | | 1 | 1 | 1 (0)| 00:00:01 | 99 |00:00:00.08 | 202 | 0 |
| 19 | NESTED LOOPS | | 1 | 1 | 1 (0)| 00:00:01 | 99 |00:00:00.01 | 202 | 0 |
| 20 | NESTED LOOPS | | 1 | 1 | 0 (0)| | 99 |00:00:00.01 | 99 | 0 |
|* 21 | FIXED TABLE FULL | X$KCCFN | 1 | 1 | 0 (0)| | 99 |00:00:00.01 | 0 | 0 |
|* 22 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 99 | 1 | 0 (0)| | 99 |00:00:00.01 | 99 | 0 |
|* 23 | TABLE ACCESS BY INDEX ROWID| FILE$ | 99 | 1 | 1 (0)| 00:00:01 | 99 |00:00:00.01 | 103 | 0 |
|* 24 | INDEX UNIQUE SCAN | I_FILE1 | 99 | 1 | 0 (0)| | 99 |00:00:00.01 | 4 | 0 |
|* 25 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 99 | 1 | 0 (0)| | 99 |00:00:00.08 | 0 | 0 |
| 26 | TABLE ACCESS CLUSTER | TS$ | 99 | 1 | 1 (0)| 00:00:01 | 99 |00:00:00.01 | 103 | 0 |
|* 27 | INDEX UNIQUE SCAN | I_TS# | 99 | 1 | 0 (0)| | 99 |00:00:00.01 | 4 | 0 |
| 28 | VIEW | | 1 | 17 | 175 (38)| 00:00:03 | 17 |00:00:14.56 | 220K| 35475 |
| 29 | HASH GROUP BY | | 1 | 17 | 175 (38)| 00:00:03 | 17 |00:00:14.56 | 220K| 35475 |
| 30 | VIEW | DBA_FREE_SPACE | 1 | 371K| 152 (29)| 00:00:02 | 30709 |00:00:14.55 | 220K| 35475 |
| 31 | UNION-ALL | | 1 | | | | 30709