oracle11g有DBMS_MONITOR跟踪TRUNCATE操作

1,小表1W,中表100W与大表1000W的区别
          1，分别建立小中大表
             CREATE TABLE T_SMALL(A NUMBER);
             CREATE TABLE T_MIDDLE(A NUMBER);
             CREATE TABLE T_BIG(A NUMBER);
          2，此期仅对比测试小中大表TRUNCATE内部有何不同，故只能TRUNCATE
             跟踪，不用跟踪INSERT操作
          3，小表
               --1,插入
                   DECLARE
                    TYPE TYP_TAB_V_UPPER IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
                    V_UPPER TYP_TAB_V_UPPER;
                    V_END PLS_INTEGER:=&END;
                   BEGIN
                    FOR I IN 1..V_END LOOP
                      V_UPPER(I):=I;
                    END LOOP;
                  
                   
                    FORALL I IN 1..V_END
                      INSERT INTO T_SMALL VALUES(V_UPPER(I));
                      COMMIT;
                   END;
               --2,10046跟踪
                 ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12';
                
               --3,TRUNCATE表操作
                 TRUNCATE TABLE T_SMALL; 
              
               --4,停止10046跟踪
                 ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
                
               --5,提取TRC文件，并复制到另一个文件中
                  位置：D:\oracle\diag\rdbms\orcl\orcl\trace
                  新位置：D:\10046
                  删除原位置TRC文件，为了重新生成新的TRC文件
                  问题：在11G对应的USER_DUMP_DEST未发现产生的TRC文件，怪了
                   解决：
                       --以SYSDBA开启SCOTT会话监控
                       SQL> exec dbms_monitor.session_trace_enable(132,21);
                      
                       SQL> exec dbms_monitor.session_trace_disable(132);
                       --小结：
                          USER_DUMP_DEST生成的追踪文件orcl_ora_7112，7112对应V$PROCESS的SPID
               --6,格式化TRC文件（复制）
            
             中表插入
               --1,插入
                   DECLARE
                    TYPE TYP_TAB_V_UPPER IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
                    V_UPPER TYP_TAB_V_UPPER;
                    V_END PLS_INTEGER:=&END;
                   BEGIN
                    FOR I IN 1..V_END LOOP
                      V_UPPER(I):=I;
                    END LOOP;
                  
                   
                    FORALL I IN 1..V_END
                      INSERT INTO T_MIDDLE VALUES(V_UPPER(I));
                      COMMIT;
                   END;
               --2,追踪SCOTT会话
                 exec dbms_monitor.session_trace_ENABLE(132);
                
               --3,TRUNCATE表操作
                 TRUNCATE TABLE T_MIDDLE; 
              
               --4,关闭追踪SCOTT会话
                 exec dbms_monitor.session_trace_disable(132);
                
               --5,提取TRC文件，并复制到另一个文件中
                  位置：D:\oracle\diag\rdbms\orcl\orcl\trace
                  新位置：D:\10046
                  删除原位置TRC文件，为了重新生成新的TRC文件
                  问题：在11G对应的USER_DUMP_DEST未发现产生的TRC文件，怪了
                   解决：
                       --以SYSDBA开启SCOTT会话监控
                       SQL> exec dbms_monitor.session_trace_enable(132,21);
                      
                       SQL> exec dbms_monitor.session_trace_disable(132);
                       --小结：
                          USER_DUMP_DEST生成的追踪文件orcl_ora_7112，7112对应V$PROCESS的SPID
               --6,格式化TRC文件（复制）
             大表插入  
               --1,插入
                   DECLARE
                    TYPE TYP_TAB_V_UPPER IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
                    V_UPPER TYP_TAB_V_UPPER;
                    V_END PLS_INTEGER:=&END;
                   BEGIN
                    FOR I IN 1..V_END LOOP
                      V_UPPER(I):=I;
                    END LOOP;
                  
                   
                    FORALL I IN 1..V_END
                      INSERT INTO T_BIG VALUES(V_UPPER(I));
                      COMMIT;
                   END;
               --2,10046跟踪
                 exec dbms_monitor.session_trace_enable(132,21);
                
               --3,TRUNCATE表操作
                 TRUNCATE TABLE T_BIG; 
              
               --4,停止10046跟踪
                 exec dbms_monitor.session_trace_disable(132);
                
               --5,提取TRC文件，并复制到另一个文件中
                  位置：D:\oracle\diag\rdbms\orcl\orcl\trace
                  新位置：D:\10046
                  删除原位置TRC文件，为了重新生成新的TRC文件
                  问题：在11G对应的USER_DUMP_DEST未发现产生的TRC文件，怪了
                   解决：
                       --以SYSDBA开启SCOTT会话监控
                       SQL> exec dbms_monitor.session_trace_enable(132,21);
                      
                       SQL> exec dbms_monitor.session_trace_disable(132);
                       --小结：
                          USER_DUMP_DEST生成的追踪文件orcl_ora_7112，7112对应V$PROCESS的SPID
               --6,格式化TRC文件（复制）
              
              
               小结：1，对于TRUNCATE而言，大中小表的内部操作（ORACLE内部）差不多，故每个TRC文件
                        TKPROF之后差不多大小
              
      2,10046诊断TRC如何分析
          1，10046 TRC文件示例，重复部分略去
             TKPROF: Release 11.2.0.1.0 - Development on 星期五 9月 14 13:45:52 2012

               Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
              
               Trace file: d:\10046\orcl_ora_t_middle.trc
               Sort options: default
              
               ********************************************************************************
               count    = number of times OCI procedure was executed
               cpu      = cpu time in seconds executing
               elapsed  = elapsed time in seconds executing
               disk     = number of physical reads of buffers from disk
               query    = number of buffers gotten for consistent read
               current  = number of buffers gotten in current mode (usually for update)
               rows     = number of rows processed by the fetch or execute call
               ********************************************************************************
              
               SQL ID: c2wk6dy1akjbs
               Plan Hash: 1537128132
               select count(FA#)
               from
                SYS_FBA_TRACKEDTABLES where OBJ# = 73333 and DROPSCN = 0
              
              
               call     count       cpu    elapsed       disk      query    current        rows
               ------- ------  -------- ---------- ---------- ---------- ----------  ----------
               Parse        2      0.00       0.00          0          0          0           0
               Execute      2      0.00       0.00          0          0          0           0
               Fetch        2      0.00       0.00          0          2          0           2
               ------- ------  -------- ---------- ---------- ---------- ----------  ----------
               total        6      0.00       0.00          0          2          0           2
              
               Misses in library cache during parse: 1
               Optimizer mode: CHOOSE
               Parsing user id: SYS   (recursive depth: 1)
              
               Rows     Row Source Operation
               -------  ---------------------------------------------------
                     1  SORT AGGREGATE (cr=1 pr=0 pw=0 time=0 us)
                     0   TABLE ACCESS BY INDEX ROWID SYS_FBA_TRACKEDTABLES (cr=1 pr=0 pw=0 time=0 us cost=1 size=6 card=1)
                     0    INDEX UNIQUE SCAN SYS_C001432 (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 1214)
              
               ********************************************************************************
              
               SQL ID: 65r8rs8x7bnhf
               Plan Hash: 0
               LOCK TABLE "T_MIDDLE" IN EXCLUSIVE MODE  NOWAIT
              
              
               call     count       cpu    elapsed       disk      query    current        rows
               ------- ------  -------- ---------- ---------- ---------- ----------  ----------
               Parse        1      0.01       0.02          0          0          0           0
               Execute      1      0.00       0.00          0          0          0           0
               Fetch        0      0.00       0.00          0          0          0           0
               ------- ------  -------- ---------- ---------- ---------- ----------  ----------
               total        2      0.01       0.02          0          0          0           0
              
               Misses in library cache during parse: 1
               Optimizer mode: ALL_ROWS
               Parsing user id: 84     (recursive depth: 1)
               ********************************************************************************
              
               SQL ID: 4nackmz96wbrb
               Plan Hash: 3777519066
               TRUNCATE TABLE T_MIDDLE
              
              
               call     count       cpu    elapsed       disk      query    current        rows
               ------- ------  -------- ---------- ---------- ---------- ----------  ----------
               Parse        1      0.01       0.02          0          2          0           0
               Execute      1      0.00       0.01          5         96        181           0
               Fetch        0      0.00       0.00          0          0          0           0
               ------- ------  -------- ---------- ---------- ---------- ----------  ----------
               total        2      0.01       0.04          5         98        181           0
              
               Misses in library cache during parse: 1
               Optimizer mode: ALL_ROWS
               Parsing user id: 84 
              
               Elapsed times include waiting on following events:
                 Event waited on                             Times   Max. Wait  Total Waited
                 ----------------------------------------   Waited  ----------  ------------
                 reliable message                                3        0.00          0.00
                 enq: RO - fast object reuse                     2        0.01          0.01
                 db file sequential read                         5        0.00          0.00
                 local write wait                                3        0.00          0.00
                 log file sync                                   1        0.02          0.02
                 SQL*Net message to client                       1        0.00          0.00
                 SQL*Net message from client                     1        0.00          0.00
               ********************************************************************************
          2,10046文件的结构；
              
               1，每个SQL的语句，这个SQL包括用户执行的SQL以及递归调用ORACLE的SQL
                  是否包括递归调用ORACLE的SQL可由TKPROF选项控制
               2，每个SQL的解析，执行，提取三个阶段各项指标的性能数据，比如：
                  COUNT,CPU,ELAPSED等  
               3，每个SQL的执行计划
               4，在运行这些SQL时数据库的等待事件   
            小结：1，TRUNCATE操作在底层会调用ORACLE内部的一些字典表如：OBJ#,SEG#,MLOG$
                     以及一些底层包如：DBMS_STANDARD和SED打头的包
                  2,如何你要深入研究这些底层操作之间内部的流程，就要详细看这个TRC文件，
                    即：每个内部表的含义，
                        TRC文件中执行内部SQL的顺序
                        内部SQL之间如何发生关系
                        这是另一个话题了

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

转载于:http://blog.itpub.net/9240380/viewspace-743454/