实例讲解sql_trace和access path的用法

    PARSING IN CURSOR #1 len=55 dep=0 uid=0 oct=42 lid=0 tim=3176627539 hv=3381932903 ad='668aba24'

    alter session set events '10046 trace name context off'

    END OF STMT

    PARSE #1：c=0，e=107，p=0，cr=0，cu=0，mis=0，r=0，dep=0，og=4，tim=3176627528

    BINDS #1：

    EXEC #1：c=0，e=889，p=0，cr=0，cu=0，mis=0，r=0，dep=0，og=4，tim=3176633060

    SQL> create unique index IDX_T_ID on T （object_id）；

    Index created.

    SQL> alter session set events '10046 trace name context forever，level 12'；

    Session altered.

    SQL> select count（object_id） from t where object_id>0；

    COUNT（OBJECT_ID）

  ----------------

    30536

    SQL> alter session set events '10046 trace name context off'；

    Session altered.

    此种情况说明使用了不合理的索引，此时索引使用的是index range扫描方式，从另一个方面也阐述了"db file sequential read"的产生原理。

    *** 2006-04-21 14：01：55.000

    =====================

    PARSING IN CURSOR #1 len=68 dep=0 uid=0 oct=42 lid=0 tim=3222433563 hv=570864185 ad='668b0b0c'

    alter session set events '10046 trace name context forever，level 12'

    END OF STMT

    EXEC #1：c=0，e=85，p=0，cr=0，cu=0，mis=0，r=0，dep=0，og=4，tim=3222433552

    WAIT #1： nam='SQL*Net message to client' ela= 5 p1=1111838976 p2=1 p3=0

    *** 2006-04-21 14：02：09.000

    WAIT #1： nam='SQL*Net message from client' ela= 14067572 p1=1111838976 p2=1 p3=0

    =====================

    PARSING IN CURSOR #1 len=48 dep=0 uid=0 oct=3 lid=0 tim=3236513737 hv=1107120101 ad='677ffa8c'

    select count（object_id） from t where object_id>0

    END OF STMT

    PARSE #1：c=0，e=1716，p=0，cr=0，cu=0，mis=1，r=0，dep=0，og=4，tim=3236513728

    BINDS #1：

    EXEC #1：c=0，e=944，p=0，cr=0，cu=0，mis=0，r=0，dep=0，og=4，tim=3236519497

    WAIT #1： nam='SQL*Net message to client' ela= 7 p1=1111838976 p2=1 p3=0

    WAIT #1： nam='db file sequential read' ela= 12439 p1=1 p2=50923 p3=1

    WAIT #1： nam='db file sequential read' ela= 203 p1=1 p2=50924 p3=1

    WAIT #1： nam='db file sequential read' ela= 158 p1=1 p2=50925 p3=1

    WAIT #1： nam='db file sequential read' ela= 157 p1=1 p2=50926 p3=1

    WAIT #1： nam='db file sequential read' ela= 151 p1=1 p2=50927 p3=1

    WAIT #1： nam='db file sequential read' ela= 149 p1=1 p2=50928 p3=1

    WAIT #1： nam='db file sequential read' ela= 156 p1=1 p2=50929 p3=1

    WAIT #1： nam='db file sequential read' ela= 150 p1=1 p2=50930 p3=1

    WAIT #1： nam='db file sequential read' ela= 148 p1=1 p2=50931 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=50932 p3=1

    WAIT #1： nam='db file sequential read' ela= 167 p1=1 p2=50933 p3=1

    WAIT #1： nam='db file sequential read' ela= 156 p1=1 p2=50934 p3=1

    WAIT #1： nam='db file sequential read' ela= 147 p1=1 p2=50935 p3=1

    WAIT #1： nam='db file sequential read' ela= 149 p1=1 p2=50936 p3=1

    WAIT #1： nam='db file sequential read' ela= 1330 p1=1 p2=51233 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51234 p3=1

    WAIT #1： nam='db file sequential read' ela= 149 p1=1 p2=51235 p3=1

    WAIT #1： nam='db file sequential read' ela= 142 p1=1 p2=51236 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51237 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51238 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51239 p3=1

    WAIT #1： nam='db file sequential read' ela= 146 p1=1 p2=51240 p3=1

    WAIT #1： nam='db file sequential read' ela= 203 p1=1 p2=51241 p3=1

    WAIT #1： nam='db file sequential read' ela= 158 p1=1 p2=51242 p3=1

    WAIT #1： nam='db file sequential read' ela= 158 p1=1 p2=51243 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51244 p3=1

    WAIT #1： nam='db file sequential read' ela= 158 p1=1 p2=51245 p3=1

    WAIT #1： nam='db file sequential read' ela= 154 p1=1 p2=51246 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51247 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51248 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51249 p3=1

    WAIT #1： nam='db file sequential read' ela= 172 p1=1 p2=51250 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51251 p3=1

    WAIT #1： nam='db file sequential read' ela= 213 p1=1 p2=51252 p3=1

    WAIT #1： nam='db file sequential read' ela= 166 p1=1 p2=51253 p3=1

    WAIT #1： nam='db file sequential read' ela= 156 p1=1 p2=51254 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51255 p3=1

    WAIT #1： nam='db file sequential read' ela= 154 p1=1 p2=51256 p3=1

    WAIT #1： nam='db file sequential read' ela= 156 p1=1 p2=51257 p3=1

    WAIT #1： nam='db file sequential read' ela= 154 p1=1 p2=51258 p3=1

    WAIT #1： nam='db file sequential read' ela= 171 p1=1 p2=51259 p3=1

    WAIT #1： nam='db file sequential read' ela= 156 p1=1 p2=51260 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51261 p3=1

    WAIT #1： nam='db file sequential read' ela= 151 p1=1 p2=51262 p3=1

    WAIT #1： nam='db file sequential read' ela= 149 p1=1 p2=51263 p3=1

    WAIT #1： nam='db file sequential read' ela= 147 p1=1 p2=51264 p3=1

    WAIT #1： nam='db file sequential read' ela= 150 p1=1 p2=51265 p3=1

    WAIT #1： nam='db file sequential read' ela= 151 p1=1 p2=51266 p3=1

    WAIT #1： nam='db file sequential read' ela= 140 p1=1 p2=51267 p3=1

    WAIT #1： nam='db file sequential read' ela= 151 p1=1 p2=51268 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51269 p3=1

    WAIT #1： nam='db file sequential read' ela= 146 p1=1 p2=51270 p3=1

    WAIT #1： nam='db file sequential read' ela= 148 p1=1 p2=51271 p3=1

    WAIT #1： nam='db file sequential read' ela= 199 p1=1 p2=51272 p3=1

    WAIT #1： nam='db file sequential read' ela= 158 p1=1 p2=51273 p3=1

    WAIT #1： nam='db file sequential read' ela= 154 p1=1 p2=51274 p3=1

    WAIT #1： nam='db file sequential read' ela= 154 p1=1 p2=51275 p3=1

    WAIT #1： nam='db file sequential read' ela= 153 p1=1 p2=51276 p3=1

    WAIT #1： nam='db file sequential read' ela= 151 p1=1 p2=51277 p3=1

    WAIT #1： nam='db file sequential read' ela= 152 p1=1 p2=51278 p3=1

    WAIT #1： nam='db file sequential read' ela= 151 p1=1 p2=51279 p3=1

    WAIT #1： nam='db file sequential read' ela= 150 p1=1 p2=51280 p3=1

    WAIT #1： nam='db file sequential read' ela= 148 p1=1 p2=51281 p3=1

    FETCH #1：c=15625，e=102159，p=63，cr=64，cu=0，mis=0，r=1，dep=0，og=4，tim=3236628878

    WAIT #1： nam='SQL*Net message from client' ela= 382 p1=1111838976 p2=1 p3=0

    FETCH #1：c=0，e=2，p=0，cr=0，cu=0，mis=0，r=0，dep=0，og=0，tim=3236631569

    WAIT #1： nam='SQL*Net message to client' ela= 4 p1=1111838976 p2=1 p3=0

    WAIT #1： nam='SQL*Net message from client' ela= 3303736 p1=1111838976 p2=1 p3=0

    STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE '

    STAT #1 id=2 cnt=30536 pid=1 pos=1 obj=31689 op='INDEX RANGE SCAN IDX_T_ID '

    =====================

上一页  [1] [2] [3] [4] [5] [6] 下一页