Базы данных Oracle - статьи
Представление статистической информации
Итак, что же можно определить по этой информации? В трассировочных файлах версий до 9.2 типичная итоговая информация в строке STAT будет иметь вид (Release 8.1.7.2):
STATEMENT TEXT
select count(*) from dba_users
STATEMENT EXECUTION PLAN
Rows Row Source Operation ------- ----------------------------------------------- 1 SORT AGGREGATE 12,047 MERGE JOIN 12,048 SORT JOIN 12,047 NESTED LOOPS 12,048 NESTED LOOPS 12,048 NESTED LOOPS 12,048 MERGE JOIN 10 SORT JOIN 9 NESTED LOOPS 10 TABLE ACCESS FULL USER_ASTATUS_MAP 9 TABLE ACCESS BY INDEX ROWID PROFILE$ 162 INDEX RANGE SCAN (object id 43224) 12,056 SORT JOIN 12,047 TABLE ACCESS FULL USER$ 24,094 TABLE ACCESS CLUSTER TS$ 24,094 INDEX UNIQUE SCAN (object id 43126) 24,094 TABLE ACCESS CLUSTER TS$ 24,094 INDEX UNIQUE SCAN (object id 43126) 12,047 TABLE ACCESS BY INDEX ROWID PROFILE$ 216,846 INDEX RANGE SCAN (object id 43224) 12,047 SORT JOIN 7 TABLE ACCESS FULL PROFNAME$
Сейчас, в версии 9.2, информация имеет следующий вид (Release 9.2.0.2):
STATEMENT TEXT
select count(*) from dba_users
STATEMENT EXECUTION PLAN
Rows Row Source Operation ------- ------------------------------------------------- 1 SORT AGGREGATE (cr=167 r=4 w=0 time=31888 us) 36 MERGE JOIN (cr=167 r=4 w=0 time=31863 us) 36 SORT JOIN (cr=164 r=3 w=0 time=30441 us) 36 TABLE ACCESS BY INDEX ROWID PROFILE$
(cr=164 r=3 w=0 time=29994 us) 649 NESTED LOOPS (cr=163 r=3 w=0 time=25712 us) 36 NESTED LOOPS (cr=161 r=3 w=0 time=22979 us) 36 NESTED LOOPS (cr=87 r=3 w=0 time=21223 us) 36 MERGE JOIN (cr=13 r=3 w=0 time=19489 us) 9 SORT JOIN (cr=6 r=3 w=0 time=18519 us) 9 TABLE ACCESS BY INDEX ROWID PROFILE$
(cr=6 r=3 w=0 time=18313 us) 163 NESTED LOOPS (cr=5 r=2 w=0 time=11319 us) 9 TABLE ACCESS FULL USER_ASTATUS_MAP
(cr=3 r=1 w=0 time=1278 us) 153 INDEX RANGE SCAN I_PROFILE
(cr=2 r=1 w=0 time=9769 us) (object id 139) 36 SORT JOIN (cr=7 r=0 w=0 time=837 us) 36 TABLE ACCESS FULL USER$ (cr=7
r=0 w=0 time=420 us) 36 TABLE ACCESS CLUSTER TS$ (cr=74
r=0 w=0 time=1535 us) 36 INDEX UNIQUE SCAN I_TS# (cr=2
r=0 w=0 time=206 us) (object id 7) 36 TABLE ACCESS CLUSTER TS$ (cr=74
r=0 w=0 time=1563 us) 36 INDEX UNIQUE SCAN I_TS# (cr=2
r=0 w=0 time=145 us) (object id 7) 612 INDEX RANGE SCAN I_PROFILE (cr=2
r=0 w=0 time=1219 us) (object id 139) 36 SORT JOIN (cr=3 r=1 w=0 time=1283 us) 1 TABLE ACCESS FULL PROFNAME$ (cr=3
r=1 w=0 time=936 us)
