现象描述：
在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 |00:00:14.54 | 220K| 35475 |
| 32 | NESTED LOOPS | | 1 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 33 | NESTED LOOPS | | 1 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 34 | TABLE ACCESS FULL | FET$ | 1 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
|* 35 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
|* 36 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
|* 37 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
| 38 | NESTED LOOPS | | 1 | 92 | 7 (0)| 00:00:01 | 30528 |00:00:00.15 | 1435 | 0 |
| 39 | NESTED LOOPS | | 1 | 92 | 7 (0)| 00:00:01 | 30528 |00:00:00.12 | 1431 | 0 |
|* 40 | TABLE ACCESS FULL | TS$ | 1 | 17 | 7 (0)| 00:00:01 | 17 |00:00:00.01 | 22 | 0 |
|* 41 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 17 | 6 | 0 (0)| | 30528 |00:00:00.11 | 1409 | 0 |
|* 42 | INDEX UNIQUE SCAN | I_FILE2 | 30528 | 1 | 0 (0)| | 30528 |00:00:00.02 | 4 | 0 |
|* 43 | HASH JOIN | | 1 | 371K| 122 (36)| 00:00:02 | 181 |00:00:14.38 | 219K| 35475 |
| 44 | INDEX FULL SCAN | I_FILE2 | 1 | 99 | 1 (0)| 00:00:01 | 99 |00:00:00.01 | 1 | 0 |
|* 45 | HASH JOIN | | 1 | 609K| 117 (34)| 00:00:02 | 181 |00:00:14.37 | 219K| 35475 |
| 46 | NESTED LOOPS | | 1 | 1427 | 78 (0)| 00:00:01 | 1473 |00:00:00.01 | 917 | 0 |
| 47 | NESTED LOOPS | | 1 | 8381 | 78 (0)| 00:00:01 | 1473 |00:00:00.01 | 898 | 0 |
|* 48 | TABLE ACCESS FULL | TS$ | 1 | 17 | 7 (0)| 00:00:01 | 17 |00:00:00.01 | 22 | 0 |
|* 49 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 17 | 493 | 3 (0)| 00:00:01 | 1473 |00:00:00.01 | 876 | 0 |
| 50 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1473 | 86 | 10 (0)| 00:00:01 | 1473 |00:00:00.01 | 19 | 0 |
| 51 | FIXED TABLE FULL | X$KTFBUE | 1 | 100K| 35 (100)| 00:00:01 | 405K|00:00:14.19 | 218K| 35475 | -----执行计划的瓶颈
| 52 | NESTED LOOPS | | 1 | 1 | 19 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 53 | NESTED LOOPS | | 1 | 493 | 19 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 54 | NESTED LOOPS | | 1 | 1 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 55 | NESTED LOOPS | | 1 | 1 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
|* 56 | TABLE ACCESS FULL | TS$ | 1 | 1 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 57 | TABLE ACCESS CLUSTER | UET$ | 0 | 1 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 58 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 0 | 1 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 59 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
|* 60 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 0 | 493 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 61 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 0 | 1 | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 62 | TABLE ACCESS BY INDEX ROWID | TS$ | 17 | 1 | 1 (0)| 00:00:01 | 17 |00:00:00.01 | 22 | 0 |
|* 63 | INDEX UNIQUE SCAN | I_TS1 | 17 | 1 | 0 (0)| | 17 |00:00:00.01 | 5 | 0 |
|* 64 | FIXED TABLE FIXED INDEX | X$KCFISTSA (ind:1) | 17 | 6 | 0 (0)| | 17 |00:00:00.02 | 68 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$DC954E9B
4 - SEL$8 / B@SEL$2
5 - SEL$8
6 - SET$2 / DBA_DATA_FILES@SEL$8
7 - SET$2
8 - SEL$96467BBE
11 - SEL$96467BBE / X$KCCFN@SEL$11
12 - SEL$96467BBE / F@SEL$9
13 - SEL$96467BBE / F@SEL$9
14 - SEL$96467BBE / FE@SEL$9
15 - SEL$96467BBE / TS@SEL$9
16 - SEL$96467BBE / TS@SEL$9
17 - SEL$03235BA3
21 - SEL$03235BA3 / X$KCCFN@SEL$14
22 - SEL$03235BA3 / HC@SEL$12
23 - SEL$03235BA3 / F@SEL$12
24 - SEL$03235BA3 / F@SEL$12
25 - SEL$03235BA3 / FE@SEL$12
26 - SEL$03235BA3 / TS@SEL$12
27 - SEL$03235BA3 / TS@SEL$12
28 - SEL$3 / A@SEL$2
29 - SEL$3
30 - SET$1 / DBA_FREE_SPACE@SEL$3
31 - SET$1
32 - SEL$4
34 - SEL$4 / F@SEL$4
35 - SEL$4 / TS@SEL$4
36 - SEL$4 / TS@SEL$4
37 - SEL$4 / FI@SEL$4
38 - SEL$5
40 - SEL$5 / TS@SEL$5
41 - SEL$5 / F@SEL$5
42 - SEL$5 / FI@SEL$5
43 - SEL$6 ---注意这些SEL$6
44 - SEL$6 / FI@SEL$6
48 - SEL$6 / TS@SEL$6
49 - SEL$6 / RB@SEL$6
50 - SEL$6 / RB@SEL$6
51 - SEL$6 / U@SEL$6 ---注意这些SEL$6
52 - SEL$7
56 - SEL$7 / TS@SEL$7
57 - SEL$7 / U@SEL$7
58 - SEL$7 / U@SEL$7
59 - SEL$7 / FI@SEL$7
60 - SEL$7 / RB@SEL$7
61 - SEL$7 / RB@SEL$7
62 - SEL$DC954E9B / TS@SEL$15
63 - SEL$DC954E9B / TS@SEL$15
64 - SEL$DC954E9B / TSATTR@SEL$15
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$96467BBE")
MERGE(@"SEL$285A8194")
OUTLINE_LEAF(@"SEL$03235BA3")
MERGE(@"SEL$1CF66C63")
OUTLINE_LEAF(@"SET$2")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$DC954E9B")
MERGE(@"SEL$686F3001")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$285A8194")
MERGE(@"SEL$11")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$1CF66C63")
MERGE(@"SEL$14")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$686F3001")
MERGE(@"SEL$15")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$15")
NO_ACCESS(@"SEL$DC954E9B" "B"@"SEL$2")
NO_ACCESS(@"SEL$DC954E9B" "A"@"SEL$2")
INDEX_RS_ASC(@"SEL$DC954E9B" "TS"@"SEL$15" ("TS$"."NAME"))
FULL(@"SEL$DC954E9B" "TSATTR"@"SEL$15")
LEADING(@"SEL$DC954E9B" "B"@"SEL$2" "A"@"SEL$2" "TS"@"SEL$15" "TSATTR"@"SEL$15")
USE_HASH(@"SEL$DC954E9B" "A"@"SEL$2")
USE_NL(@"SEL$DC954E9B" "TS"@"SEL$15")
USE_NL(@"SEL$DC954E9B" "TSATTR"@"SEL$15")
NO_ACCESS(@"SEL$3" "DBA_FREE_SPACE"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$3")
NO_ACCESS(@"SEL$8" "DBA_DATA_FILES"@"SEL$8")
USE_HASH_AGGREGATION(@"SEL$8")
FULL(@"SEL$03235BA3" "X$KCCFN"@"SEL$14")
FULL(@"SEL$03235BA3" "HC"@"SEL$12")
INDEX_RS_ASC(@"SEL$03235BA3" "F"@"SEL$12" ("FILE$"."FILE#"))
FULL(@"SEL$03235BA3" "FE"@"SEL$12")
INDEX(@"SEL$03235BA3" "TS"@"SEL$12" "I_TS#")
LEADING(@"SEL$03235BA3" "X$KCCFN"@"SEL$14" "HC"@"SEL$12" "F"@"SEL$12" "FE"@"SEL$12" "TS"@"SEL$12")
USE_NL(@"SEL$03235BA3" "HC"@"SEL$12")
USE_NL(@"SEL$03235BA3" "F"@"SEL$12")
USE_NL(@"SEL$03235BA3" "FE"@"SEL$12")
USE_NL(@"SEL$03235BA3" "TS"@"SEL$12")
FULL(@"SEL$96467BBE" "X$KCCFN"@"SEL$11")
INDEX_RS_ASC(@"SEL$96467BBE" "F"@"SEL$9" ("FILE$"."FILE#"))
FULL(@"SEL$96467BBE" "FE"@"SEL$9")
INDEX(@"SEL$96467BBE" "TS"@"SEL$9" "I_TS#")
LEADING(@"SEL$96467BBE" "X$KCCFN"@"SEL$11" "F"@"SEL$9" "FE"@"SEL$9" "TS"@"SEL$9")
USE_NL(@"SEL$96467BBE" "F"@"SEL$9")
USE_NL(@"SEL$96467BBE" "FE"@"SEL$9")
USE_NL(@"SEL$96467BBE" "TS"@"SEL$9")
FULL(@"SEL$7" "TS"@"SEL$7")
INDEX_RS_ASC(@"SEL$7" "U"@"SEL$7" "I_FILE#_BLOCK#")
INDEX(@"SEL$7" "FI"@"SEL$7" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$7" "RB"@"SEL$7" ("RECYCLEBIN$"."TS#"))
LEADING(@"SEL$7" "TS"@"SEL$7" "U"@"SEL$7" "FI"@"SEL$7" "RB"@"SEL$7")
USE_NL(@"SEL$7" "U"@"SEL$7")
USE_NL(@"SEL$7" "FI"@"SEL$7")
USE_NL(@"SEL$7" "RB"@"SEL$7")
NLJ_BATCHING(@"SEL$7" "RB"@"SEL$7")
FULL(@"SEL$6" "TS"@"SEL$6")
INDEX(@"SEL$6" "RB"@"SEL$6" ("RECYCLEBIN$"."TS#"))
FULL(@"SEL$6" "U"@"SEL$6")
INDEX(@"SEL$6" "FI"@"SEL$6" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$6" "TS"@"SEL$6" "RB"@"SEL$6" "U"@"SEL$6" "FI"@"SEL$6") ---注意这些SEL$6
USE_NL(@"SEL$6" "RB"@"SEL$6")
NLJ_BATCHING(@"SEL$6" "RB"@"SEL$6")
USE_HASH(@"SEL$6" "U"@"SEL$6") ---注意这些SEL$6， "U"@"SEL$6"对应X$KTFBUE
USE_HASH(@"SEL$6" "FI"@"SEL$6")
SWAP_JOIN_INPUTS(@"SEL$6" "FI"@"SEL$6")
FULL(@"SEL$5" "TS"@"SEL$5")
FULL(@"SEL$5" "F"@"SEL$5")
INDEX(@"SEL$5" "FI"@"SEL$5" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$5" "TS"@"SEL$5" "F"@"SEL$5" "FI"@"SEL$5")
USE_NL(@"SEL$5" "F"@"SEL$5")
USE_NL(@"SEL$5" "FI"@"SEL$5")
FULL(@"SEL$4" "F"@"SEL$4")
INDEX(@"SEL$4" "TS"@"SEL$4" "I_TS#")
INDEX(@"SEL$4" "FI"@"SEL$4" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$4" "F"@"SEL$4" "TS"@"SEL$4" "FI"@"SEL$4")
USE_NL(@"SEL$4" "TS"@"SEL$4")
USE_NL(@"SEL$4" "FI"@"SEL$4")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")
11 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
12 - filter("F"."SPARE1" IS NULL)
13 - access("FNFNO"="F"."FILE#")
14 - filter("FE"."FENUM"="F"."FILE#")
16 - access("F"."TS#"="TS"."TS#")
21 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
22 - filter("FNFNO"="HC"."KTFBHCAFNO")
23 - filter("F"."SPARE1" IS NOT NULL)
24 - access("FNFNO"="F"."FILE#")
25 - filter("FE"."FENUM"="F"."FILE#")
27 - access("HC"."KTFBHCTSN"="TS"."TS#")
35 - filter("TS"."BITMAPPED"=0)
36 - access("TS"."TS#"="F"."TS#")
37 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
41 - filter("TS"."TS#"="F"."KTFBFETSN")
42 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
43 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
45 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
48 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
49 - access("TS"."TS#"="RB"."TS#")
56 - filter("TS"."BITMAPPED"=0)
58 - access("TS"."TS#"="U"."TS#")
59 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
60 - access("U"."TS#"="RB"."TS#")
61 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))
62 - filter(("TS"."ONLINE$"<>3 AND BITAND("FLAGS",2048)<>2048))
63 - access("A"."TABLESPACE_NAME"="TS"."NAME")
64 - filter("TS"."TS#"="TSATTR"."TSID")
执行计划分析：
通过执行计划可以看出，时间及buffer瓶颈主要在于51步对基表X$KTFBUE的全表扫描（可以通过必应查询"dba_free_space查询慢"），而该基表是dba_free_space查询定义中的基表之一，所以此时不仅是该SQL慢，几乎所有常用的涉及dba_free_space的SQL都会慢，如select count(*) from dba_free_space;，而51步之所以采用全表扫描，是因为优化器认为46步TS$与RECYCLEBIN$两表关联后的e-rows有1427行，与基表X$KTFBUE（大表）做关联查询走hash join会更好（45步hash join），但事实却未必如此。
由于基表X$KTFBUE（大表）似乎可以走基表对应的索引（通过其他数据库该SQL的执行计划发现），所以可以尝试利用hint迫使TS$与RECYCLEBIN$两表关联后与基表X$KTFBUE（大表）通过nested loops做关联。
具体的hint调节方法为：
1、从执行计划的"Query Block Name / Object Alias (identified by operation id):"部分查看45步到51步对应SEL$6，并注意到基表X$KTFBUE（大表）对应U@SEL$6
2、从执行计划的“Outline Data”寻找SEL$6对应的hint，我们重点要调节U@SEL$6对应的join方法及关联顺序，所以查到如下hint：LEADING(@“SEL$6” “TS”@“SEL$6” “RB”@“SEL$6” “U”@“SEL$6” “FI”@“SEL$6”) USE_HASH(@“SEL$6” “U”@“SEL$6”)
3、将USE_HASH(@“SEL$6” “U”@“SEL$6”)改为USE_NL(@“SEL$6” “U”@“SEL$6”)，LEADING(@“SEL$6” “TS”@“SEL$6” “RB”@“SEL$6” “U”@“SEL$6” “FI”@“SEL$6”)关联顺序不变。
4、当然不同SQL需要慢慢尝试调整，其他SQL可能是U@SEL$4，此处只是尝试出关键hint后分析为何如此。也曾尝试过hint里直接写基表的名字，如USE_NL(X$KTFBUE）等，但可能由于子查询等的原因，该hint无法生效。
hint调整结果：
SELECT/*+ LEADING(@"SEL$6" "TS"@"SEL$6" "RB"@"SEL$6" "U"@"SEL$6" "FI"@"SEL$6") USE_NL(@"SEL$6" "U"@"SEL$6") */
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
调整后执行计划：
可以看出执行时间由14.67秒降到了0.41秒，buffers由221K降到了7366，而且该方法无需调整数据库，只需稍微调整一下SQL内容即可。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g4sbw6w7ukydk, child number 1
-------------------------------------
SELECT/*+ LEADING(@"SEL$6" "TS"@"SEL$6" "RB"@"SEL$6" "U"@"SEL$6"
"FI"@"SEL$6") USE_NL(@"SEL$6" "U"@"SEL$6") */ 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: 1948522273
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 15988 (100)| | 17 |00:00:00.41 | 7352 | 14 |
| 1 | NESTED LOOPS | | 1 | 11 | 15988 (100)| 00:03:12 | 17 |00:00:00.41 | 7352 | 14 |
| 2 | NESTED LOOPS | | 1 | 2 | 15988 (100)| 00:03:12 | 17 |00:00:00.38 | 7284 | 14 |
|* 3 | HASH JOIN | | 1 | 2 | 15986 (100)| 00:03:12 | 17 |00:00:00.38 | 7262 | 14 |
| 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.09 | 408 | 0 |
| 7 | UNION-ALL | | 1 | | | | 99 |00:00:00.09 | 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.09 | 305 | 0 |
| 18 | NESTED LOOPS | | 1 | 1 | 1 (0)| 00:00:01 | 99 |00:00:00.09 | 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 | 15980 (100)| 00:03:12 | 17 |00:00:00.29 | 6854 | 14 |
| 29 | HASH GROUP BY | | 1 | 17 | 15980 (100)| 00:03:12 | 17 |00:00:00.29 | 6854 | 14 |
| 30 | VIEW | DBA_FREE_SPACE | 1 | 371K| 15958 (100)| 00:03:12 | 30722 |00:00:00.28 | 6854 | 14 |
| 31 | UNION-ALL | | 1 | | | | 30722 |00:00:00.27 | 6854 | 14 |
| 32 | NESTED LOOPS | | 1 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 33 | NESTED LOOPS | | 1 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 34 | TABLE ACCESS FULL | FET$ | 1 | 1 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
|* 35 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
|* 36 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
|* 37 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
| 38 | NESTED LOOPS | | 1 | 92 | 7 (0)| 00:00:01 | 30541 |00:00:00.15 | 1435 | 0 |
| 39 | NESTED LOOPS | | 1 | 92 | 7 (0)| 00:00:01 | 30541 |00:00:00.12 | 1431 | 0 |
|* 40 | TABLE ACCESS FULL | TS$ | 1 | 17 | 7 (0)| 00:00:01 | 17 |00:00:00.01 | 22 | 0 |
|* 41 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 17 | 6 | 0 (0)| | 30541 |00:00:00.12 | 1409 | 0 |
|* 42 | INDEX UNIQUE SCAN | I_FILE2 | 30541 | 1 | 0 (0)| | 30541 |00:00:00.02 | 4 | 0 |
|* 43 | HASH JOIN | | 1 | 371K| 15928 (100)| 00:03:12 | 181 |00:00:00.11 | 5375 | 14 |
| 44 | INDEX FULL SCAN | I_FILE2 | 1 | 99 | 1 (0)| 00:00:01 | 99 |00:00:00.01 | 1 | 0 |
| 45 | NESTED LOOPS | | 1 | 609K| 15923 (100)| 00:03:12 | 181 |00:00:00.10 | 5374 | 14 |
| 46 | NESTED LOOPS | | 1 | 1427 | 78 (0)| 00:00:01 | 1473 |00:00:00.01 | 917 | 0 |
|* 47 | TABLE ACCESS FULL | TS$ | 1 | 17 | 7 (0)| 00:00:01 | 17 |00:00:00.01 | 22 | 0 |
| 48 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 17 | 86 | 10 (0)| 00:00:01 | 1473 |00:00:00.01 | 895 | 0 |
|* 49 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 17 | 493 | 3 (0)| 00:00:01 | 1473 |00:00:00.01 | 876 | 0 |
|* 50 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1473 | 427 | 11 (100)| 00:00:01 | 181 |00:00:00.10 | 4457 | 14 |
| 51 | NESTED LOOPS | | 1 | 1 | 19 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 52 | NESTED LOOPS | | 1 | 493 | 19 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 53 | NESTED LOOPS | | 1 | 1 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 54 | NESTED LOOPS | | 1 | 1 | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
|* 55 | TABLE ACCESS FULL | TS$ | 1 | 1 | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 22 | 0 |
| 56 | TABLE ACCESS CLUSTER | UET$ | 0 | 1 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 57 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 0 | 1 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 0 (0)| | 0 |00:00:00.01 | 0 | 0 |
|* 59 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 0 | 493 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 60 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 0 | 1 | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 61 | TABLE ACCESS BY INDEX ROWID | TS$ | 17 | 1 | 1 (0)| 00:00:01 | 17 |00:00:00.01 | 22 | 0 |
|* 62 | INDEX UNIQUE SCAN | I_TS1 | 17 | 1 | 0 (0)| | 17 |00:00:00.01 | 5 | 0 |
|* 63 | FIXED TABLE FIXED INDEX | X$KCFISTSA (ind:1) | 17 | 6 | 0 (0)| | 17 |00:00:00.02 | 68 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$DC954E9B
4 - SEL$8 / B@SEL$2
5 - SEL$8
6 - SET$2 / DBA_DATA_FILES@SEL$8
7 - SET$2
8 - SEL$96467BBE
11 - SEL$96467BBE / X$KCCFN@SEL$11
12 - SEL$96467BBE / F@SEL$9
13 - SEL$96467BBE / F@SEL$9
14 - SEL$96467BBE / FE@SEL$9
15 - SEL$96467BBE / TS@SEL$9
16 - SEL$96467BBE / TS@SEL$9
17 - SEL$03235BA3
21 - SEL$03235BA3 / X$KCCFN@SEL$14
22 - SEL$03235BA3 / HC@SEL$12
23 - SEL$03235BA3 / F@SEL$12
24 - SEL$03235BA3 / F@SEL$12
25 - SEL$03235BA3 / FE@SEL$12
26 - SEL$03235BA3 / TS@SEL$12
27 - SEL$03235BA3 / TS@SEL$12
28 - SEL$3 / A@SEL$2
29 - SEL$3
30 - SET$1 / DBA_FREE_SPACE@SEL$3
31 - SET$1
32 - SEL$4
34 - SEL$4 / F@SEL$4
35 - SEL$4 / TS@SEL$4
36 - SEL$4 / TS@SEL$4
37 - SEL$4 / FI@SEL$4
38 - SEL$5
40 - SEL$5 / TS@SEL$5
41 - SEL$5 / F@SEL$5
42 - SEL$5 / FI@SEL$5
43 - SEL$6
44 - SEL$6 / FI@SEL$6
47 - SEL$6 / TS@SEL$6
48 - SEL$6 / RB@SEL$6
49 - SEL$6 / RB@SEL$6
50 - SEL$6 / U@SEL$6
51 - SEL$7
55 - SEL$7 / TS@SEL$7
56 - SEL$7 / U@SEL$7
57 - SEL$7 / U@SEL$7
58 - SEL$7 / FI@SEL$7
59 - SEL$7 / RB@SEL$7
60 - SEL$7 / RB@SEL$7
61 - SEL$DC954E9B / TS@SEL$15
62 - SEL$DC954E9B / TS@SEL$15
63 - SEL$DC954E9B / TSATTR@SEL$15
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$96467BBE")
MERGE(@"SEL$285A8194")
OUTLINE_LEAF(@"SEL$03235BA3")
MERGE(@"SEL$1CF66C63")
OUTLINE_LEAF(@"SET$2")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$DC954E9B")
MERGE(@"SEL$686F3001")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$285A8194")
MERGE(@"SEL$11")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$1CF66C63")
MERGE(@"SEL$14")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$686F3001")
MERGE(@"SEL$15")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$15")
NO_ACCESS(@"SEL$DC954E9B" "B"@"SEL$2")
NO_ACCESS(@"SEL$DC954E9B" "A"@"SEL$2")
INDEX_RS_ASC(@"SEL$DC954E9B" "TS"@"SEL$15" ("TS$"."NAME"))
FULL(@"SEL$DC954E9B" "TSATTR"@"SEL$15")
LEADING(@"SEL$DC954E9B" "B"@"SEL$2" "A"@"SEL$2" "TS"@"SEL$15" "TSATTR"@"SEL$15")
USE_HASH(@"SEL$DC954E9B" "A"@"SEL$2")
USE_NL(@"SEL$DC954E9B" "TS"@"SEL$15")
USE_NL(@"SEL$DC954E9B" "TSATTR"@"SEL$15")
NO_ACCESS(@"SEL$3" "DBA_FREE_SPACE"@"SEL$3")
USE_HASH_AGGREGATION(@"SEL$3")
NO_ACCESS(@"SEL$8" "DBA_DATA_FILES"@"SEL$8")
USE_HASH_AGGREGATION(@"SEL$8")
FULL(@"SEL$03235BA3" "X$KCCFN"@"SEL$14")
FULL(@"SEL$03235BA3" "HC"@"SEL$12")
INDEX_RS_ASC(@"SEL$03235BA3" "F"@"SEL$12" ("FILE$"."FILE#"))
FULL(@"SEL$03235BA3" "FE"@"SEL$12")
INDEX(@"SEL$03235BA3" "TS"@"SEL$12" "I_TS#")
LEADING(@"SEL$03235BA3" "X$KCCFN"@"SEL$14" "HC"@"SEL$12" "F"@"SEL$12" "FE"@"SEL$12" "TS"@"SEL$12")
USE_NL(@"SEL$03235BA3" "HC"@"SEL$12")
USE_NL(@"SEL$03235BA3" "F"@"SEL$12")
USE_NL(@"SEL$03235BA3" "FE"@"SEL$12")
USE_NL(@"SEL$03235BA3" "TS"@"SEL$12")
FULL(@"SEL$96467BBE" "X$KCCFN"@"SEL$11")
INDEX_RS_ASC(@"SEL$96467BBE" "F"@"SEL$9" ("FILE$"."FILE#"))
FULL(@"SEL$96467BBE" "FE"@"SEL$9")
INDEX(@"SEL$96467BBE" "TS"@"SEL$9" "I_TS#")
LEADING(@"SEL$96467BBE" "X$KCCFN"@"SEL$11" "F"@"SEL$9" "FE"@"SEL$9" "TS"@"SEL$9")
USE_NL(@"SEL$96467BBE" "F"@"SEL$9")
USE_NL(@"SEL$96467BBE" "FE"@"SEL$9")
USE_NL(@"SEL$96467BBE" "TS"@"SEL$9")
FULL(@"SEL$7" "TS"@"SEL$7")
INDEX_RS_ASC(@"SEL$7" "U"@"SEL$7" "I_FILE#_BLOCK#")
INDEX(@"SEL$7" "FI"@"SEL$7" ("FILE$"."TS#" "FILE$"."RELFILE#"))
INDEX(@"SEL$7" "RB"@"SEL$7" ("RECYCLEBIN$"."TS#"))
LEADING(@"SEL$7" "TS"@"SEL$7" "U"@"SEL$7" "FI"@"SEL$7" "RB"@"SEL$7")
USE_NL(@"SEL$7" "U"@"SEL$7")
USE_NL(@"SEL$7" "FI"@"SEL$7")
USE_NL(@"SEL$7" "RB"@"SEL$7")
NLJ_BATCHING(@"SEL$7" "RB"@"SEL$7")
FULL(@"SEL$6" "TS"@"SEL$6")
INDEX_RS_ASC(@"SEL$6" "RB"@"SEL$6" ("RECYCLEBIN$"."TS#"))
FULL(@"SEL$6" "U"@"SEL$6")
INDEX(@"SEL$6" "FI"@"SEL$6" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$6" "TS"@"SEL$6" "RB"@"SEL$6" "U"@"SEL$6" "FI"@"SEL$6")
USE_NL(@"SEL$6" "RB"@"SEL$6")
USE_NL(@"SEL$6" "U"@"SEL$6")
USE_HASH(@"SEL$6" "FI"@"SEL$6")
SWAP_JOIN_INPUTS(@"SEL$6" "FI"@"SEL$6")
FULL(@"SEL$5" "TS"@"SEL$5")
FULL(@"SEL$5" "F"@"SEL$5")
INDEX(@"SEL$5" "FI"@"SEL$5" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$5" "TS"@"SEL$5" "F"@"SEL$5" "FI"@"SEL$5")
USE_NL(@"SEL$5" "F"@"SEL$5")
USE_NL(@"SEL$5" "FI"@"SEL$5")
FULL(@"SEL$4" "F"@"SEL$4")
INDEX(@"SEL$4" "TS"@"SEL$4" "I_TS#")
INDEX(@"SEL$4" "FI"@"SEL$4" ("FILE$"."TS#" "FILE$"."RELFILE#"))
LEADING(@"SEL$4" "F"@"SEL$4" "TS"@"SEL$4" "FI"@"SEL$4")
USE_NL(@"SEL$4" "TS"@"SEL$4")
USE_NL(@"SEL$4" "FI"@"SEL$4")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")
11 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
12 - filter("F"."SPARE1" IS NULL)
13 - access("FNFNO"="F"."FILE#")
14 - filter("FE"."FENUM"="F"."FILE#")
16 - access("F"."TS#"="TS"."TS#")
21 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
22 - filter("FNFNO"="HC"."KTFBHCAFNO")
23 - filter("F"."SPARE1" IS NOT NULL)
24 - access("FNFNO"="F"."FILE#")
25 - filter("FE"."FENUM"="F"."FILE#")
27 - access("HC"."KTFBHCTSN"="TS"."TS#")
35 - filter("TS"."BITMAPPED"=0)
36 - access("TS"."TS#"="F"."TS#")
37 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
41 - filter("TS"."TS#"="F"."KTFBFETSN")
42 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
43 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
47 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$")))
49 - access("TS"."TS#"="RB"."TS#")
50 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#"))
55 - filter("TS"."BITMAPPED"=0)
57 - access("TS"."TS#"="U"."TS#")
58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
59 - access("U"."TS#"="RB"."TS#")
60 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))
61 - filter(("TS"."ONLINE$"<>3 AND BITAND("FLAGS",2048)<>2048))
62 - access("A"."TABLESPACE_NAME"="TS"."NAME")
63 - filter("TS"."TS#"="TSATTR"."TSID")
Note
-----
- cardinality feedback used for this statement
7604
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
