首页 » ORACLE [C]系列, ORACLE 9i-23c » query dba_free_space(tablespace usage) slow after upgrade 12c R2

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.

打赏

,

对不起,这篇文章暂时关闭评论。