query dba_free_space(tablespace usage) slow after upgrade 12c R2
前不久有个下线EXADATA并同时从11g R2 升级12C R2的案例，反应升级12c后明显感觉原来查询表空间使用率的脚本时间比升级前长了很多, 要花好几分钟， 这种情况时通常是因为recyclebin$回收站中的对象太多，清理回收站解决， 但是这次的回收站并无多少对象(<100)， 这是一个50 TB左右的数据库，有350个左右的数据文件。
今天有时间分析一下，下一步当然是要看SQL的执行计划，这里使用sql monitor
SQL> select dbms_sqltune.report_sql_monitor(sql_id=>'&sql_id',report_level=>'ALL',type=>'text') from dual;
Enter value for sql_id: 19bgcf8grxdxm
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'19BGCF8GRXDXM',REPORT_LEVEL=>'ALL',TYPE=>'TEXT')
--------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select t.tablespace_name, t.mb "TotalMB", t.mb - nvl(f.mb,0) "UsedMB", nvl(f.mb,0) "FreeMB" ,lpad(ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*100)||'%', 6) "% Used", t.ext "Ext", '|'||rpad(lpad('#',ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*20),'#'),20,' ')||'|' "Used" from ( select tablespace_name, trunc(sum(bytes)/1048576) MB from dba_free_space group by tablespace_name union all select tablespace_name, trunc(sum(bytes_free)/1048576) MB from v$temp_space_header group by tablespace_name ) f, (
select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext from dba_data_files group by tablespace_name union all select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext from dba_temp_files group by tablespace_name ) t where t.tablespace_name = f.tablespace_name (+) order by t.tablespace_name
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SYS (614:29445)
SQL ID : 19bgcf8grxdxm
SQL Execution ID : 16777220
Execution Started : 02/25/2019 16:20:58
First Refresh Time : 02/25/2019 16:21:02
Last Refresh Time : 02/25/2019 16:22:24
Duration : 87s
Module/Action : sqlplus@kdrpt01 (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@kdrpt01 (TNS V1-V3)
Global Stats
===================================================================
| Elapsed | Cpu | IO | Cluster | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===================================================================
| 96 | 28 | 60 | 9.15 | 1M | 168K | 1GB |
===================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
========================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Cluster | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
========================================================================================================================================
| PX Coordinator | QC | | 96 | 28 | 60 | 9.15 | 1M | 168K | 1GB | gc cr disk read (12) |
| | | | | | | | | | | control file sequential read (2) |
| | | | | | | | | | | db file sequential read (54) |
========================================================================================================================================
Instance Drill-Down
=================================================================================================================================
| Instance | Process Names | Elapsed | Cpu | IO | Cluster | Buffer | Read | Read | Wait Events |
| | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | |
=================================================================================================================================
| 1 | QC | 96 | 28 | 60 | 9.15 | 1M | 168K | 1GB | gc cr disk read (12) |
| | | | | | | | | | control file sequential read (3) |
| | | | | | | | | | db file sequential read (54) |
=================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=259291012)
============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | |
| 1 | SORT ORDER BY | | 8 | 122 | | | 1 | | | | . | | |
| 2 | HASH JOIN OUTER | | 8 | 121 | 1 | +4 | 1 | 0 | | | 1MB | | |
| 3 | VIEW | | 8 | 22 | 1 | +4 | 1 | 26 | | | . | | |
| 4 | UNION-ALL | | | | 1 | +4 | 1 | 26 | | | . | | |
| 5 | HASH GROUP BY | | 6 | 17 | 1 | +4 | 1 | 25 | | | . | | |
| 6 | VIEW | DBA_DATA_FILES | 6 | 16 | 1 | +4 | 1 | 1342 | | | . | | |
| 7 | UNION-ALL | | | | 1 | +4 | 1 | 1342 | | | . | | |
| 8 | NESTED LOOPS | | 1 | 6 | | | 1 | | | | . | | |
| 9 | NESTED LOOPS | | 1 | 5 | | | 1 | | | | . | | |
| 10 | NESTED LOOPS | | 1 | 5 | 1 | +4 | 1 | 0 | | | . | | |
| 11 | FIXED TABLE FULL | X$KCCFN | 5 | | 1 | +4 | 1 | 1342 | 23 | 4MB | . | | |
| 12 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 1 | 1 | +4 | 1342 | 0 | | | . | | |
| 13 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 1 | +4 | 1342 | 1342 | | | . | | |
| 14 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | | | | | | | | . | | |
| 15 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | | | | | | | . | | |
| 16 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | . | | |
| 17 | NESTED LOOPS | | 5 | 10 | 1 | +4 | 1 | 1342 | | | . | | |
| 18 | NESTED LOOPS | | 5 | 5 | 1 | +4 | 1 | 1342 | | | . | | |
| 19 | NESTED LOOPS | | 5 | 5 | 1 | +4 | 1 | 1342 | | | . | | |
| 20 | NESTED LOOPS | | 5 | | 1 | +4 | 1 | 1342 | | | . | | |
| 21 | FIXED TABLE FULL | X$KCCFN | 5 | | 1 | +4 | 1 | 1342 | 23 | 4MB | . | | |
| 22 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | | 1 | +4 | 1342 | 1342 | | | . | | |
| 23 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 1 | 1 | +4 | 1342 | 1342 | | | . | | |
| 24 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 1 | +4 | 1342 | 1342 | | | . | | |
| 25 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | | 4 | +1 | 1342 | 1342 | 5407 | 84MB | . | 2.25 | control file sequential read (2) |
| 26 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 1 | +4 | 1342 | 1342 | | | . | | |
| 27 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1 | +4 | 1342 | 1342 | | | . | | |
| 28 | HASH GROUP BY | | 2 | 5 | 1 | +4 | 1 | 1 | | | . | | |
| 29 | VIEW | DBA_TEMP_FILES | 2 | 4 | 1 | +4 | 1 | 8 | | | . | | |
| 30 | SORT UNIQUE | | 2 | 4 | 1 | +4 | 1 | 8 | | | . | | |
| 31 | UNION-ALL | | | | 1 | +4 | 1 | 8 | | | . | | |
| 32 | NESTED LOOPS | | 1 | 1 | 1 | +4 | 1 | 8 | | | . | | |
| 33 | NESTED LOOPS | | 1 | | 1 | +4 | 1 | 8 | | | . | | |
| 34 | NESTED LOOPS | | 1 | | 1 | +4 | 1 | 8 | | | . | | |
| 35 | NESTED LOOPS | | 1 | | 1 | +4 | 1 | 8 | | | . | | |
| 36 | FIXED TABLE FULL | X$KCCTF | 1 | | 1 | +4 | 1 | 8 | 4 | 65536 | . | | |
| 37 | FIXED TABLE FIXED INDEX | X$KCCFN (ind:1) | 1 | | 1 | +4 | 8 | 8 | 32 | 512KB | . | | |
| 38 | FIXED TABLE FIXED INDEX | X$KCVFHTMP (ind:1) | 1 | | 1 | +4 | 8 | 8 | 40 | 576KB | . | | |
| 39 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:2) | 1 | | 1 | +4 | 8 | 8 | | | . | | |
| 40 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 1 | +4 | 8 | 8 | | | . | | |
| 41 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1 | +4 | 8 | 8 | | | . | | |
| 42 | NESTED LOOPS | | 1 | 1 | 1 | +4 | 1 | 0 | | | . | | |
| 43 | HASH JOIN | | 1 | | 1 | +4 | 1 | 16 | | | . | | |
| 44 | PX COORDINATOR | | | | 1 | +4 | 1 | 16 | | | . | | |
| 45 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | | | | | . | | |
| 46 | VIEW | GV$TEMPFILE_INFO_INSTANCE | | | | | | | | | . | | |
| 47 | NESTED LOOPS | | 1 | | | | | | | | . | | |
| 48 | NESTED LOOPS | | 1 | | | | | | | | . | | |
| 49 | MERGE JOIN CARTESIAN | | 1 | | | | | | | | . | | |
| 50 | FIXED TABLE FULL | X$KCCTF | 1 | | | | | | | | . | | |
| 51 | BUFFER SORT | | 1 | | | | | | | | . | | |
| 52 | FIXED TABLE FULL | X$KCVFHTMP | 1 | | | | | | | | . | | |
| 53 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:2) | 1 | | | | | | | | . | | |
| 54 | FIXED TABLE FIXED INDEX | X$KCCFN (ind:1) | 1 | | 1 | +3 | | | | | . | 2.25 | control file sequential read (2) |
| 55 | PX COORDINATOR | | | | 1 | +4 | 1 | 2 | | | . | | |
| 56 | PX SEND QC (RANDOM) | :TQ20000 | 1 | | | | | | | | . | | |
| 57 | VIEW | GV$INSTANCE | | | | | | | | | . | | |
| 58 | MERGE JOIN CARTESIAN | | 1 | | | | | | | | . | | |
| 59 | MERGE JOIN CARTESIAN | | 1 | | | | | | | | . | | |
| 60 | MERGE JOIN CARTESIAN | | 1 | | | | | | | | . | | |
| 61 | FIXED TABLE FULL | X$KSUXSINST | 1 | | | | | | | | . | | |
| 62 | BUFFER SORT | | 1 | | | | | | | | . | | |
| 63 | FIXED TABLE FULL | X$QUIESCE | 1 | | | | | | | | . | | |
| 64 | BUFFER SORT | | 1 | | | | | | | | . | | |
| 65 | FIXED TABLE FULL | X$KJIDT | 1 | | | | | | | | . | | |
| 66 | BUFFER SORT | | 1 | | | | | | | | . | | |
| 67 | FIXED TABLE FULL | X$KVIT | 1 | | | | | | | | . | | |
| 68 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 1 | +4 | 16 | 0 | | | . | | |
| 69 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1 | +4 | 16 | 16 | | | . | | |
| 70 | VIEW | | 26 | 99 | | | 1 | | | | . | | |
| 71 | UNION-ALL | | | | | | 1 | | | | . | | |
| 72 | HASH GROUP BY | | 25 | 89 | 71 | +4 | 1 | 0 | | | 1MB | | |
| 73 | VIEW | DBA_FREE_SPACE | 123 | 88 | 71 | +4 | 1 | 99234 | | | . | | |
| 74 | UNION-ALL | | | | 71 | +4 | 1 | 99234 | | | . | | |
| 75 | NESTED LOOPS | | 1 | 4 | | | 1 | | | | . | | |
| 76 | NESTED LOOPS | | 1 | 4 | | | 1 | | | | . | | |
| 77 | TABLE ACCESS FULL | FET$ | 1 | 4 | | | 1 | | | | . | | |
| 78 | TABLE ACCESS CLUSTER | TS$ | 1 | | | | | | | | . | | |
| 79 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | . | | |
| 80 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | | | | | | . | | |
| 81 | NESTED LOOPS | | 31 | 9 | 3 | +4 | 1 | 96484 | | | . | | |
| 82 | NESTED LOOPS | | 31 | 9 | 3 | +4 | 1 | 96484 | | | . | | |
| 83 | TABLE ACCESS FULL | TS$ | 25 | 9 | 3 | +4 | 1 | 25 | | | . | | |
| 84 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 1 | | 3 | +4 | 25 | 96484 | | | . | 1.12 | Cpu (1) |
| 85 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 3 | +4 | 96484 | 96484 | | | . | | |
| 86 | NESTED LOOPS | | 89 | 60 | 67 | +8 | 1 | 2750 | | | . | | |
| -> 87 | HASH JOIN | | 639 | 60 | 85 | +6 | 1 | 2750 | | | 1MB | 1.12 | Cpu (1) |
| 88 | NESTED LOOPS | | 56 | 39 | 1 | +6 | 1 | 57 | | | . | | |
| 89 | NESTED LOOPS | | 275 | 39 | 1 | +6 | 1 | 57 | | | . | | |
| 90 | TABLE ACCESS FULL | TS$ | 25 | 9 | 1 | +6 | 1 | 25 | | | . | | |
| 91 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 11 | 1 | 1 | +6 | 25 | 57 | | | . | | |
| 92 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 2 | 2 | 1 | +6 | 57 | 57 | | | . | | |
| -> 93 | FIXED TABLE FULL | X$KTFBUE | 100K | 20 | 86 | +5 | 1 | 8M | 163K | 1GB | . | 91.01 | gc cr disk read (12) |
| | | | | | | | | | | | | | Cpu (15) |
| | | | | | | | | | | | | | db file sequential read (54) |
| -> 94 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 83 | +8 | 2750 | 2750 | | | . | | |
| 95 | NESTED LOOPS | | 1 | 12 | | | | | | | . | | |
| 96 | NESTED LOOPS | | 11 | 12 | | | | | | | . | | |
| 97 | NESTED LOOPS | | 1 | 10 | | | | | | | . | | |
| 98 | NESTED LOOPS | | 1 | 10 | | | | | | | . | | |
| 99 | TABLE ACCESS FULL | TS$ | 1 | 9 | | | | | | | . | | |
| 100 | TABLE ACCESS CLUSTER | UET$ | 1 | 1 | | | | | | | . | | |
| 101 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | 1 | | | | | | | . | | |
| 102 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | | | | | | . | | |
| 103 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 11 | 1 | | | | | | | . | | |
| 104 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 2 | | | | | | | . | | |
| 105 | NESTED LOOPS | | 1 | 3 | | | | | | | . | | |
| 106 | NESTED LOOPS | | 1 | 2 | | | | | | | . | | |
| 107 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$ | 1 | 2 | | | | | | | . | | |
| 108 | TABLE ACCESS CLUSTER | TS$ | 1 | | | | | | | | . | | |
| 109 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | . | | |
| 110 | INDEX RANGE SCAN | I_FILE2 | 1 | 1 | | | | | | | . | | |
| 111 | HASH GROUP BY | | 1 | 10 | | | | | | | . | | |
| 112 | NESTED LOOPS | | 1 | 9 | | | | | | | . | | |
| 113 | TABLE ACCESS FULL | TS$ | 1 | 9 | | | | | | | . | | |
| 114 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:2) | 1 | | | | | | | | . | | |
============================================================================================================================================================================================================
Note:
从执行计划看显示是#93 占用了大部分的时间， 使用的是全表扫X$KTFBUE ， 估算是是100K,实际当时已经8M, 相差了80倍， 对于x$ktfbue表当没有统计信息时，默认的统计信息应该是100,000 rows.
View: X$KTFBUE
Desc.: [K]ernel [T]ablespace [F]ile [B]itmapped [U]sed [E]xtents
查看该FIXED TABLE是否有统计信息
SQL> col owner for a30 SQL> col table_name for a30 SQL> select owner,table_name,object_type,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_statistics where table_name='X$KTFBUE'; OWNER TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS LAST_ANAL ------------------------------ ------------------------------ ------------ ---------- ---------- --------- SYS X$KTFBUE FIXED TABLE
Note:
可以看到X$ktfbue无统计信息，下面尝试使用GATHER_FIXED_OBJECTS_STATS。
SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:01:53.92
SQL> select owner,table_name,object_type,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_statistics where table_name='X$KTFBUE';
OWNER TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS LAST_ANAL
------------------------------ ------------------------------ ------------ ---------- ---------- ---------
SYS X$KTFBUE FIXED TABLE
SQL> SELECT count(*),count(last_analyzed),sum(decode(last_analyzed,null,1,0)) FROM DBA_TAB_STATISTICS where OBJECT_TYPE='FIXED TABLE';
COUNT(*) COUNT(LAST_ANALYZED) SUM(DECODE(LAST_ANALYZED,NULL,1,0))
---------- -------------------- -----------------------------------
1335 1180 155
Note:
其实可以看到使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS收集成功后，仍旧有很多FIXED TABLE无统计信息，包含本次出错的X$KTFBUE, 是否非常有趣？其实在MOS note ID 1355608.1 中有记录, 这些FIXED TABLE没有收集统计的原因是因为开发人员在oracle的代码级标注，忽略这些table的统计信息收集，因为他们认为对于一些FIXED TABLE不收集统计信息会更好。
这时如果想收集fixed table统计信息的方法是
SQL> EXEC DBMS_STATS.gather_table_stats('SYS','X$KTFBUE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.70
SQL> select owner,table_name,object_type,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_statistics where table_name='X$KTFBUE';
OWNER TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS LAST_ANAL
------------------------------ ------------------------------ ------------ ---------- ---------- ---------
SYS X$KTFBUE FIXED TABLE 3839 17-MAR-19
SQL> @df
TABLESPACE_NAME TotalMB UsedMB FreeMB % Used Ext Used
------------------------------ ---------- ---------- ---------- ------ --- ----------------------
DBFSTS 30720 1 30719 1% NO |# |
...
26 rows selected.
Elapsed: 00:00:04.13
-- execute plan --
| 86 | NESTED LOOPS | | 8 | 576 | 39 (0)| 00:00:01 | | | |
| 87 | NESTED LOOPS | | 56 | 3640 | 39 (0)| 00:00:01 | | | |
| 88 | NESTED LOOPS | | 56 | 2464 | 39 (0)| 00:00:01 | | | |
|* 89 | TABLE ACCESS FULL | TS$ | 25 | 775 | 9 (0)| 00:00:01 | | | |
| 90 | TABLE ACCESS BY INDEX ROWID BATCHED| RECYCLEBIN$ | 2 | 26 | 2 (0)| 00:00:01 | | | |
|* 91 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 11 | | 1 (0)| 00:00:01 | | | |
|* 92 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 21 | 0 (0)| 00:00:01 | | | |
|* 93 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 | | | |
Note:
在收集X$KTFBUE的统计信息以后，， 执行计划有原来的FIXED FULL TABLE变成了FIXED TABLE FIXED INDEX，现在4秒钟就可以返回数据。问题得到解决，其实在11G r2时当查询dba_extents也会基于这个TABLE同样有可能面对这个问题，使用DBMS_STATS.gather_table_stats(‘SYS’,’X$KTFBUE’)收集这类被忽略的FIXED TABLE.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。