跟蹤SQL - SQL Trace 及 10046 事件

           與SQL_Trace相比，10046事件跟蹤SQL提供了更豐富的資訊，最重要的就是包含等待事件資訊。它同樣可以跟蹤自身會話資訊和其他會話的資訊。跟蹤自身會話語法：alter session set events '10046 trace name context forever,level 8';執行需要進行測試的SQL語句，然後關閉跟蹤。alter session set events '10046 trace name context off'或者使用dbms_support包進行跟蹤。預設情況下，dbms_support包沒有安裝。以sys身份執行$ORACLE_HOME/rdbms/admin/dbmssupp.sql，並給予使用者執行許可權grant execute on dbms_support to gaoxuan語法：exec sys.dbms_support.start_trace;執行需要進行測試的SQL語句，然後關閉跟蹤。exec sys.dbms_support.stop_trace;同樣使用tkprof生成報告，內容類似：call      count        cpu     elapsed        disk       query     current         rows
------- ------   -------- ---------- ---------- ---------- ----------   ----------
Parse         1       0.00        0.00           0           0           0            0
Execute       1       0.00        0.00           0           0           0            0
Fetch         9     746.59      739.39       13400    46876317           0          863
------- ------   -------- ---------- ---------- ---------- ----------   ----------
total        11     746.59      739.40       13400    46876317           0          863Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 55  Rows      Row Source Operation
-------   ---------------------------------------------------
     863   SORT GROUP BY
331603    HASH JOIN 
331603     NESTED LOOPS 
331603      NESTED LOOPS 
331603       NESTED LOOPS 
34717720        NESTED LOOPS 
    1411         NESTED LOOPS 
    1411          NESTED LOOPS 
    1412           NESTED LOOPS 
    1436            NESTED LOOPS 
     392             INLIST ITERATOR 
     392              TABLE ACCESS BY INDEX ROWID BS_FAVOUR_GATHER_T
     392               INDEX RANGE SCAN BS_FAVOUR_GATHER_UI (object id 19877)
    1436             TABLE ACCESS BY INDEX ROWID BS_FAVOUR_MENU_T
   35320              INDEX RANGE SCAN BS_FAVOUR_MENU_INDEX (object id 19883)
    1412            TABLE ACCESS BY INDEX ROWID BF_FAVOUR_USE_T
    1412             INDEX RANGE SCAN BF_FAVOUR_USE_INDEX (object id 12916)
    1411           TABLE ACCESS BY INDEX ROWID BF_MONTH_FEE_RULE_T
    1411            INDEX UNIQUE SCAN BF_MONTH_FEE_RULE_I (object id 15050)
    1411          TABLE ACCESS BY INDEX ROWID BS_SERVICE_KIND_T
    1411           INDEX UNIQUE SCAN BS_SERVICE_KIND_UI (object id 19961)
34717720         PARTITION RANGE ITERATOR PARTITION: KEY KEY
34717720          TABLE ACCESS BY LOCAL INDEX ROWID BF_MONTH_USER_INFO_T PARTITION: KEY KEY
70383040           INDEX RANGE SCAN BF_MONTH_USER_INFO_U_I PARTITION: KEY KEY (object id 105461)
331603        INDEX UNIQUE SCAN BS_FAVOUR_MENU_INDEX (object id 19883)
331603       TABLE ACCESS BY INDEX ROWID BBF_COLUMN_RELATION_T
331603        INDEX UNIQUE SCAN BBF_COLUMN_RELATION_I (object id 6330)
331603      TABLE ACCESS BY INDEX ROWID BF_FAVOUR_PRESENT_T
331603       INDEX UNIQUE SCAN BF_FAVOUR_PRESENT_UI (object id 12906)
      12     TABLE ACCESS FULL BS_CITY_ID_T
Elapsed times include waiting on following events:
   Event waited on                              Times    Max. Wait   Total Waited
   ----------------------------------------    Waited   ----------   ------------
   SQL*Net message to client                       10         0.00           0.00
   SQL*Net message from client                     10         2.77          23.01
   direct path write                              893         0.08           0.17
   latch free                                       4         0.01           0.01
   db file scattered read                           1         0.01           0.01
   direct path read                               893         0.14           2.58
   SQL*Net more data to client                     25         0.00           0.00