跟踪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   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-660036/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-660036/