熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

在Oracle10g Release 2中的Autotrace

2013-11-13 15:43:42  來源: Oracle 

  今天在測試過程中偶然發現在Oracleg Release Autotrace的功能已經被極大加強和改變
  
  這是一個很有意思的小的變化關於autotrace的使用請參考:啟用AutoTrace
  
  接下來讓我們先來看一下什麼地方發生了改變: SQL> set linesize
  
  SQL> set autotrace on
  SQL> select count(*) from v$session
    /
  
   COUNT(*)
  
      
  
  
  Execution Plan
  
  Plan hash value:
  
  
  | Id | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time   |
  
  |  | SELECT STATEMENT     |         |   |   |     ()| :: |
  |  | SORT AGGREGATE      |         |   |   |      |     |
  |  |  NESTED LOOPS      |         |   |   |     ()| :: |
  |*  |  FIXED TABLE FULL    | X$KSUSE     |   |   |     ()| :: |
  |*  |  FIXED TABLE FIXED INDEX| X$KSLED (ind:) |   |   |     ()| :: |
  
  
  Predicate Information (identified by operation id):
  
  
    filter(SINST_ID=USERENV(INSTANCE) AND BITAND(SKSSPAFLG)<> AND
         BITAND(SKSUSEFLG)<>)
    filter(SKSUSEOPC=EINDX)
  
  
  Statistics
  
   recursive calls
   db block gets
   consistent gets
   physical reads
   redo size
   bytes sent via SQL*Net to client
   bytes received via SQL*Net from client
   SQL*Net roundtrips to/from client
   sorts (memory)
   sorts (disk)
   rows processed
  
  注意此時autotrace的輸出被良好格式化並給出關於執行計劃部分的簡要注釋
  其實這裡並沒有帶來新的東西從Oraclei開始Oracle提供了一個新的工具dbms_xplan用以格式化和查看SQL的執行計劃其原理是通過對plan_table的查詢和格式化提供更友好的用戶輸出
  
  dbms_xplan的調用的語法類似:
  select * from table(dbms_xplandisplay(format=>BASIC))
  使用 TABLE() 操作符或者 CAST 操作
  
  具體用法可以參考Oracle官方文檔
  
  實際上從Oraclei開始我們就經常使用如下方式調用dbms_xplan:
  Connected to:
  Oraclei Enterprise Edition Release Production
  With the Partitioning option
  JServer Release Production
  
  SQL> explain plan for
    select count(*) from dual;
  
  Explained
  
  SQL> @?/rdbms/admin/utlxplp;
  
  PLAN_TABLE_OUTPUT
  
  
  
  | Id | Operation      | Name    | Rows | Bytes | Cost |
  
  |  | SELECT STATEMENT   |       |    |    |    |
  |  | SORT AGGREGATE   |       |    |    |    |
  |  |  TABLE ACCESS FULL | DUAL    |    |    |    |
  
  
  Note: rule based optimization
  
   rows selected
  
  utlxplpsql腳本中正是調用了dbms_xplan:
  
  SQL> get ?/rdbms/admin/utlxplp;
   Rem
   Rem $Header: utlxplpsql jan:: bdagevil Exp $
   Rem
   Rem utlxplpsql
   Rem
   Rem Copyright (c) Oracle Corporation All rights reserved
   Rem
   Rem  NAME
   Rem   utlxplpsql UTiLity eXPLain Parallel plans
   Rem
   Rem  DESCRIPTION
   Rem   script utility to display the explain plan of the last explain plan
   Rem     command Display also Parallel Query information if the plan happens to
   Rem   run parallel
   Rem
   Rem  NOTES
   Rem   Assume that the table PLAN_TABLE has been created The script
   Rem   utlxplansql should be used to create that table
   Rem
   Rem   With SQL*plus it is recomended to set linesize and pagesize before
   Rem   running this script For example:
   Rem      set linesize
   Rem      set pagesize
   Rem
   Rem  MODIFIED  (MM/DD/YY)
   Rem  bdagevil  // rewrite with new dbms_xplan package
   Rem  bdagevil  // include CPU cost
   Rem  bdagevil  // increase Name column
   Rem  jihuang   // change order by to order siblings by
   Rem  jihuang   // include plan info for recursive SQL in LE row source
   Rem  bdagevil  // make deterministic with orderby
   Rem  bdagevil  // Explain plan script for parallel plans
   Rem  bdagevil  // Created
   Rem
   set markup html preformat on
   Rem
   Rem Use the display table function from the dbms_xplan package to display the last
   Rem explain plan Use default mode which will display only relevant information
   Rem
  * select * from table(dbms_xplandisplay());
  
  SQL>
  
  而在OraclegROracle幫我們簡化了這個過程一個autotrace就完成了所有的輸出這也是易用性上的一個進步吧在使用Oracle的過程中我們經常能夠感受到Oracle針對用戶需求或易用性的改進這也許是很多人喜愛Oracle的一個原因吧
  
  如果足夠信息我們還會注意到在Oracleg中PLAN_TABLE不再需要創建Oracle缺省增加了一個字典表PLAN_TABLE$然後基於PLAN_TABLE$創建公用同義詞供用戶使用
  
  關於dbms_xplan工具的使用還可以參考Itpub上的討論:使用dbms_xplan工具查看執行計劃
From:http://tw.wingwit.com/Article/program/Oracle/201311/17205.html
  • 上一篇文章:

  • 下一篇文章:
  • Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.