Hi colleges,
We execute the same query of datafiles in two same databases and in the second not ends... any idea why?
------------------------------------------------------
oda001 query tablespaces ends in 3 secs
-----------------------------------------------------
[oracle@bcn1-oda-001 ~]$ cat /etc/*rel*
Oracle Linux Server release 6.7
sqlplus / as sysdba
SQL> set lines 500
SQL> select version from v$instance;
VERSION
-----------------
12.1.0.2.0
SQL> set autotrace traceonly explain
SQL> SELECT df.tablespace_name , df.file_name , round(df.bytes/1024/1024/1024,2) GB, round(SUM(fs.bytes)/1024/1024/1024,2) GB_FREE
FROM dba_data_files df,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.file_id = fs.file_id
GROUP BY df.tablespace_name, df.file_name, df.bytes/1024/1024/1024
ORDER BY df.tablespace_name, df.file_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 108490650
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 3256 | 42 (5)| 00:00:01 |
| 1 | SORT GROUP BY | | 22 | 3256 | 42 (5)| 00:00:01 |
|* 2 | HASH JOIN | | 32 | 4736 | 41 (3)| 00:00:01 |
| 3 | VIEW | DBA_DATA_FILES | 6 | 720 | 12 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS | | 1 | 146 | 4 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 128 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 123 | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | FILE$ | 1 | 20 | 3 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KCCFN | 5 | 515 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 5 | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS CLUSTER | TS$ | 1 | 18 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 5 | 750 | 8 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 5 | 725 | 8 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 5 | 670 | 5 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 5 | 580 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KCCFN | 5 | 515 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 13 | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS CLUSTER | TS$ | 1 | 18 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS FULL | FILE$ | 328 | 3608 | 3 (0)| 00:00:01 |
|* 22 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 5 | 0 (0)| 00:00:01 |
| 23 | VIEW | DBA_FREE_SPACE | 42356 | 1158K| 29 (4)| 00:00:01 |
| 24 | UNION-ALL | | | | | |
| 25 | NESTED LOOPS | | 1 | 72 | 9 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 72 | 9 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 61 | 9 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | FET$ | 1 | 39 | 9 (0)| 00:00:01 |
|* 29 | TABLE ACCESS CLUSTER | TS$ | 1 | 22 | 0 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 0 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 0 (0)| 00:00:01 |
|* 33 | HASH JOIN | | 42353 | 2026K| 12 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | FILE$ | 328 | 3608 | 3 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 42353 | 1571K| 9 (0)| 00:00:01 |
|* 36 | TABLE ACCESS FULL | TS$ | 23 | 644 | 9 (0)| 00:00:01 |
|* 37 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 1811 | 18110 | 0 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | 1 | 143 | 3 (0)| 00:00:01 |
| 39 | NESTED LOOPS | | 1 | 143 | 3 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | 1 | 132 | 2 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 1 | 67 | 2 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 39 | 2 (0)| 00:00:01 |
|* 43 | TABLE ACCESS CLUSTER | TS$ | 1 | 28 | 0 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 45 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | 0 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 0 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 1 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 1 | 124 | 4 (0)| 00:00:01 |
| 49 | NESTED LOOPS | | 1 | 124 | 4 (0)| 00:00:01 |
| 50 | NESTED LOOPS | | 1 | 113 | 4 (0)| 00:00:01 |
| 51 | NESTED LOOPS | | 1 | 91 | 4 (0)| 00:00:01 |
| 52 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 39 | 2 (0)| 00:00:01 |
| 53 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 2 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 |
|* 55 | TABLE ACCESS CLUSTER | TS$ | 1 | 22 | 0 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 0 (0)| 00:00:01 |
| 58 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DF"."TABLESPACE_NAME"="FS"."TABLESPACE_NAME" AND
"DF"."FILE_ID"="FS"."FILE_ID")
7 - access("FNFNO"="F"."FILE#")
8 - filter("F"."SPARE1" IS NULL)
9 - filter("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS NOT NULL AND
BITAND("FNFLG",4)<>4)
10 - filter("X$KCCFE"."FENUM"="F"."FILE#" AND
("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL))
12 - access("F"."TS#"="TS"."TS#")
14 - access("FNFNO"="F"."FILE#")
17 - filter("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS NOT NULL AND
BITAND("FNFLG",4)<>4)
18 - filter("FNFNO"="HC"."KTFBHCAFNO")
20 - access("HC"."KTFBHCTSN"="TS"."TS#")
21 - filter("F"."SPARE1" IS NOT NULL)
22 - filter("X$KCCFE"."FENUM"="F"."FILE#" AND
("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL))
29 - filter("TS"."BITMAPPED"=0)
30 - access("TS"."TS#"="F"."TS#")
31 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
33 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
36 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND
"TS"."BITMAPPED"<>0)
37 - filter("TS"."TS#"="F"."KTFBFETSN")
43 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND
"TS"."BITMAPPED"<>0)
44 - access("TS"."TS#"="RB"."TS#")
45 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
46 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
54 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
"U"."SEGBLOCK#"="RB"."BLOCK#")
55 - filter("TS"."BITMAPPED"=0)
56 - access("TS"."TS#"="U"."TS#")
57 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 3 Sql Plan Directives used for this statement
----------------------------------------
oda005 query not ends
----------------------------------------
[oracle@bcn1-oda-005 ~]$ cat /etc/*rel*
Oracle Linux Server release 6.8
sqlplus / as sysdba
SQL> set lines 500
SQL> select version from v$instance;
VERSION
-----------------
12.1.0.2.0
SQL> set autotrace traceonly explain
SQL> SELECT df.tablespace_name , df.file_name , round(df.bytes/1024/1024/1024,2) GB, round(SUM(fs.bytes)/1024/1024/1024,2) GB_FREE
FROM dba_data_files df,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.file_id = fs.file_id
GROUP BY df.tablespace_name, df.file_name, df.bytes/1024/1024/1024
ORDER BY df.tablespace_name, df.file_name; 2 3 4 5 6
Execution Plan
----------------------------------------------------------
Plan hash value: 1570315029
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 3168 | 100 (13)| 00:00:01 |
| 1 | SORT GROUP BY | | 22 | 3168 | 100 (13)| 00:00:01 |
|* 2 | HASH JOIN | | 58 | 8352 | 99 (13)| 00:00:01 |
| 3 | VIEW | DBA_DATA_FILES | 6 | 690 | 10 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS | | 1 | 141 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 122 | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 117 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | FILE$ | 1 | 20 | 2 (0)| 00:00:01 |
|* 9 | FIXED TABLE FULL | X$KCCFN | 5 | 485 | 0 (0)| 00:00:01 |
|* 10 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 5 | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 5 | 725 | 7 (0)| 00:00:01 |
|* 14 | HASH JOIN | | 5 | 700 | 7 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 5 | 645 | 5 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 5 | 550 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KCCFN | 5 | 485 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 13 | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS CLUSTER | TS$ | 1 | 19 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 21 | TABLE ACCESS FULL | FILE$ | 130 | 1430 | 2 (0)| 00:00:01 |
|* 22 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 5 | 0 (0)| 00:00:01 |
| 23 | VIEW | DBA_FREE_SPACE | 19510 | 552K| 89 (14)| 00:00:01 |
| 24 | UNION-ALL | | | | | |
| 25 | NESTED LOOPS | | 1 | 73 | 7 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 73 | 7 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 1 | 62 | 7 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | FET$ | 1 | 39 | 7 (0)| 00:00:01 |
|* 29 | TABLE ACCESS CLUSTER | TS$ | 1 | 23 | 0 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 0 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 11 | 0 (0)| 00:00:01 |
|* 33 | HASH JOIN | | 19477 | 970K| 9 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | FILE$ | 126 | 1386 | 2 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 19477 | 760K| 7 (0)| 00:00:01 |
|* 36 | TABLE ACCESS FULL | TS$ | 15 | 435 | 7 (0)| 00:00:01 |
|* 37 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 1292 | 14212 | 0 (0)| 00:00:01 |
|* 38 | HASH JOIN | | 31 | 3596 | 60 (20)| 00:00:01 |
| 39 | TABLE ACCESS FULL | FILE$ | 126 | 1386 | 2 (0)| 00:00:01 |
|* 40 | HASH JOIN | | 488 | 51240 | 58 (21)| 00:00:01 |
| 41 | NESTED LOOPS | | 488 | 19520 | 46 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 810 | 19520 | 46 (0)| 00:00:01 |
|* 43 | TABLE ACCESS FULL | TS$ | 15 | 435 | 7 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 54 | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 32 | 352 | 4 (0)| 00:00:01 |
| 46 | FIXED TABLE FULL | X$KTFBUE | 100K| 6347K| 12 (100)| 00:00:01 |
| 47 | NESTED LOOPS | | 1 | 97 | 13 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 54 | 97 | 13 (0)| 00:00:01 |
| 49 | NESTED LOOPS | | 1 | 86 | 9 (0)| 00:00:01 |
| 50 | NESTED LOOPS | | 1 | 75 | 9 (0)| 00:00:01 |
|* 51 | TABLE ACCESS FULL | TS$ | 1 | 23 | 7 (0)| 00:00:01 |
| 52 | TABLE ACCESS CLUSTER | UET$ | 1 | 52 | 2 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 0 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 0 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 54 | | 1 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 11 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DF"."TABLESPACE_NAME"="FS"."TABLESPACE_NAME" AND "DF"."FILE_ID"="FS"."FILE_ID")
7 - access("FNFNO"="F"."FILE#")
8 - filter("F"."SPARE1" IS NULL)
9 - filter("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS NOT NULL AND
BITAND("FNFLG",4)<>4)
10 - filter("X$KCCFE"."FENUM"="F"."FILE#" AND
("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL))
12 - access("F"."TS#"="TS"."TS#")
14 - access("FNFNO"="F"."FILE#")
17 - filter("FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND "FNNAM" IS NOT NULL AND
BITAND("FNFLG",4)<>4)
18 - filter("FNFNO"="HC"."KTFBHCAFNO")
20 - access("HC"."KTFBHCTSN"="TS"."TS#")
21 - filter("F"."SPARE1" IS NOT NULL)
22 - filter("X$KCCFE"."FENUM"="F"."FILE#" AND
("CON_ID"=TO_NUMBER(SYS_CONTEXT('USERENV','CON_ID')) OR "CON_ID" IS NULL))
29 - filter("TS"."BITMAPPED"=0)
30 - access("TS"."TS#"="F"."TS#")
31 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
33 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
36 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
"TS"."ONLINE$"=4))
37 - filter("TS"."TS#"="F"."KTFBFETSN")
38 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
40 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
43 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
"TS"."ONLINE$"=4))
44 - access("TS"."TS#"="RB"."TS#")
51 - filter("TS"."BITMAPPED"=0)
53 - access("TS"."TS#"="U"."TS#")
55 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
56 - access("U"."TS#"="RB"."TS#")
57 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 5 Sql Plan Directives used for this statement