8i查询DBA_FREE_SPACE视图极慢的问题

还是那套古老的8.1.7.4，在该系统上检查表空间使用情况的SQL运行缓慢，其SQL如下:

<a href="http://blog.51cto.com/maclean/1277532#">?</a>

<code>SELECT</code> <code>D.TABLESPACE_NAME,</code>

<code>       </code><code>SPACE</code> <code>"SUM_SPACE(M)"</code><code>,</code>

<code>       </code><code>SPACE</code> <code>- NVL(FREE_SPACE, 0) </code><code>"USED_SPACE(M)"</code><code>,</code>

<code>       </code><code>ROUND((1 - NVL(FREE_SPACE, 0) / </code><code>SPACE</code><code>) * 100, 2) </code><code>"USED_RATE(%)"</code><code>,</code>

<code>       </code><code>FREE_SPACE </code><code>"FREE_SPACE(M)"</code>

<code>  </code><code>FROM</code> <code>(</code><code>SELECT</code> <code>TABLESPACE_NAME, ROUND(</code><code>SUM</code><code>(BYTES) / (1024 * 1024), 2) </code><code>SPACE</code>

<code>          </code><code>FROM</code> <code>DBA_DATA_FILES</code>

<code>         </code><code>GROUP</code> <code>BY</code> <code>TABLESPACE_NAME) D,</code>

<code>       </code><code>(</code><code>SELECT</code> <code>TABLESPACE_NAME,</code>

<code>               </code><code>ROUND(</code><code>SUM</code><code>(BYTES) / (1024 * 1024), 2) FREE_SPACE</code>

<code>          </code><code>FROM</code> <code>DBA_FREE_SPACE</code>

<code>         </code><code>GROUP</code> <code>BY</code> <code>TABLESPACE_NAME) F</code>

<code> </code><code>where</code> <code>d.tablespace_name = f.tablespace_name(+)</code>

<code> </code><code>order</code> <code>by</code> <code>"USED_RATE(%)"</code> <code>desc</code><code>;</code>

<code>/*很面熟的DBA常用脚本吧？*/</code>

经确认其中对DBA_FREE_SPACE视图的查询耗费了大量时间，8i中该视图的默认定义是:

<code>select</code> <code>ts.</code><code>name</code><code>,</code>

<code>       </code><code>fi.file#,</code>

<code>       </code><code>f.block#,</code>

<code>       </code><code>f.length * ts.blocksize,</code>

<code>       </code><code>f.length,</code>

<code>       </code><code>f.file#</code>

<code>  </code><code>from</code> <code>sys.ts$ ts, sys.fet$ f, sys.file$ fi</code>

<code> </code><code>where</code> <code>ts.ts# = f.ts#</code>

<code>   </code><code>and</code> <code>f.ts# = fi.ts#</code>

<code>   </code><code>and</code> <code>f.file# = fi.relfile#</code>

<code>   </code><code>and</code> <code>ts.bitmapped = 0</code>

<code>/*以上查询DMT表空间上的</code><code>FREE</code> <code>EXTENT*/</code>

<code>union</code> <code>all</code>

<code>/*以下查询LMT表空间上的</code><code>FREE</code> <code>EXTENT*/</code>

<code>select</code> <code>/*+ ordered use_nl(f) use_nl(fi) */</code>

<code> </code><code>ts.</code><code>name</code><code>,</code>

<code> </code><code>fi.file#,</code>

<code> </code><code>f.ktfbfebno,</code>

<code> </code><code>f.ktfbfeblks * ts.blocksize,</code>

<code> </code><code>f.ktfbfeblks,</code>

<code> </code><code>f.ktfbfefno</code>

<code>  </code><code>from</code> <code>sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi</code>

<code> </code><code>where</code> <code>ts.ts# = f.ktfbfetsn</code>

<code>   </code><code>and</code> <code>f.ktfbfetsn = fi.ts#</code>

<code>   </code><code>and</code> <code>f.ktfbfefno = fi.relfile#</code>

<code>   </code><code>and</code> <code>ts.bitmapped &lt;&gt; 0</code>

<code>   </code><code>and</code> <code>ts.online$ </code><code>in</code> <code>(1, 4)</code>

<code>   </code><code>and</code> <code>ts.contents$ = 0</code>

<code>/*也许你感到奇怪，实际上8i中就有了本地管理模式的表空间了，只是很少有人用。(</code><code>"In Oracle 8i the EXTENT MANAGEMENT clause was introduced into the CREATE TABLESPACE statement allowing extent management to be LOCAL or DICTIONARY. Locally Manages Tablespaces (LMT) have a bitmap of the blocks, or groups of blocks, they contain allowing them to track extent allocation without reference to the data dictionary."</code><code>)*/</code>

<code>/*因字典管理模式下FET$基表往往较大，导致</code><code>UNION</code> <code>ALL</code><code>以上部分在连接操作时会产生大量的逻辑读，最终导致了对DBA_FREE_SPACE视图的查询十分缓慢。*/</code>

Oracle 提供了官方的视图并不意味着我们非它不可用，可以通过修改DBA_FREE_SPACE的定义，或另建一个具有相同功能但查询SQL构造不同的视图来加快查询速度:

<code>explain plan </code><code>for</code>

<code>select</code> <code>/*+use_hash (tsfi, fet2 ) */</code>

<code> </code><code>tsfi.tablespace_name,</code>

<code> </code><code>tsfi.file_id,</code>

<code> </code><code>fet2.block_id,</code>

<code> </code><code>tsfi.blocksize * fet2.blocks,</code>

<code> </code><code>fet2.blocks,</code>

<code> </code><code>tsfi.relfile#</code>

<code>  </code><code>from</code> <code>(</code><code>select</code> <code>/*+ use_hash ( ts, fi ) */</code>

<code>         </code><code>ts.</code><code>name</code>      <code>tablespace_name,</code>

<code>         </code><code>fi.file#     file_id,</code>

<code>         </code><code>ts.BLOCKSIZE,</code>

<code>         </code><code>fi.relfile#,</code>

<code>         </code><code>ts.ts#</code>

<code>          </code><code>from</code> <code>sys.ts$ ts, sys.file$ fi</code>

<code>         </code><code>where</code> <code>ts.ts# = fi.ts#</code>

<code>           </code><code>and</code> <code>ts.online$ </code><code>in</code> <code>(1, 4)) tsfi,</code>

<code>       </code><code>(</code><code>select</code> <code>f.block# block_id, f.length blocks, f.file# file_id, f.ts#</code>

<code>          </code><code>from</code> <code>sys.fet$ f</code>

<code>        </code><code>union</code> <code>all</code>

<code>        </code><code>select</code> <code>f.ktfbfebno  block_id,</code>

<code>               </code><code>f.ktfbfeblks blocks,</code>

<code>               </code><code>f.ktfbfefno,</code>

<code>               </code><code>ktfbfetsn</code>

<code>          </code><code>from</code> <code>sys.x$ktfbfe f) fet2</code>

<code> </code><code>where</code> <code>fet2.file_id = tsfi.relfile#</code>

<code>   </code><code>and</code> <code>fet2.ts# = tsfi.ts# /*此查询需SYSDBA权限*/ ;</code>

<code>Explained</code>

<code>select</code> <code>* </code><code>from</code>  <code>table</code><code>(dbms_xplan.display);</code>

<code>PLAN_TABLE_OUTPUT</code>

<code>---------------------------------------------------------------------------------</code>

<code>Plan hash value: 717737944</code>

<code>| Id  | Operation            | </code><code>Name</code>     <code>| </code><code>Rows</code>  <code>| Bytes | Cost (%CPU)| </code><code>Time</code>     <code>|</code>

<code>|   0 | </code><code>SELECT</code> <code>STATEMENT     |          |    20 |  1560 |     9  (12)| 00:00:01 |</code>

<code>|*  1 |  HASH </code><code>JOIN</code>           <code>|          |    20 |  1560 |     9  (12)| 00:00:01 |</code>

<code>|*  2 |   HASH </code><code>JOIN</code>          <code>|          |     4 |   104 |     6  (17)| 00:00:01 |</code>

<code>|   3 |    </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| FILE$    |     4 |    36 |     2   (0)| 00:00:01 |</code>

<code>|*  4 |    </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code> <code>| TS$      |     5 |    85 |     3   (0)| 00:00:01 |</code>

<code>|   5 |   </code><code>VIEW</code>               <code>|          |   101 |  5252 |     3   (0)| 00:00:01 |</code>

<code>|   6 |    </code><code>UNION</code><code>-</code><code>ALL</code>         <code>|          |       |       |            |          |</code>

<code>|   7 |     </code><code>TABLE</code> <code>ACCESS </code><code>FULL</code><code>| FET$     |     1 |    52 |     3   (0)| 00:00:01 |</code>

<code>|   8 |     FIXED </code><code>TABLE</code> <code>FULL</code> <code>| X$KTFBFE |   100 |  5200 |     0   (0)| 00:00:01 |</code>

<code>Predicate Information (identified </code><code>by</code> <code>operation id):</code>

<code>---------------------------------------------------</code>

<code>   </code><code>1 - access(</code><code>"FET2"</code><code>.</code><code>"FILE_ID"</code><code>=</code><code>"FI"</code><code>.</code><code>"RELFILE#"</code> <code>AND</code>

<code>              </code><code>"FET2"</code><code>.</code><code>"TS#"</code><code>=</code><code>"TS"</code><code>.</code><code>"TS#"</code><code>)</code>

<code>   </code><code>2 - access(</code><code>"TS"</code><code>.</code><code>"TS#"</code><code>=</code><code>"FI"</code><code>.</code><code>"TS#"</code><code>)</code>

<code>   </code><code>4 - filter(</code><code>"TS"</code><code>.</code><code>"ONLINE$"</code><code>=1 </code><code>OR</code> <code>"TS"</code><code>.</code><code>"ONLINE$"</code><code>=4)</code>

<code>/*改写后可以大幅减少逻辑读从而提高性能*/</code>

<code>/*可以建立DBA_FREE_SPACE功能相同的替代品，并代入到表空间使用率的脚本中*/</code>

<code>CREATE</code> <code>OR</code> <code>REPLACE</code> <code>VIEW</code> <code>DBA_FREE_SPACE_NEW (</code>

<code>  </code><code>TABLESPACE_NAME,</code>

<code>  </code><code>FILE_ID,</code>

<code>  </code><code>BLOCK_ID,</code>

<code>  </code><code>BYTES,</code>

<code>  </code><code>BLOCKS,</code>

<code>  </code><code>RELATIVE_FNO</code>

<code>) </code><code>AS</code>

<code>   </code><code>and</code> <code>fet2.ts# = tsfi.ts#  /*建此视图需SYSDBA权限*/ ;</code>

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277532