[11/27] 10053 trace

orcl@HR> alter session set tracefile_identifier='ocmkorea';

Session altered.

Elapsed: 00:00:00.00

Elapsed: 00:00:00.05

orcl@HR> alter session set events '10053 trace name context forever';

Session altered.

Elapsed: 00:00:00.06

orcl@HR> select e.last_name, d.department_name

  2  from employees e, departments d

  3  where e.department_id = d.department_id;

[oracle@lnx02 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@lnx02 trace]$ ls *ocmkorea*

orcl_ora_15820_ocmkorea.trc  orcl_ora_8439_ocmkorea.trc

orcl_ora_15820_ocmkorea.trm  orcl_ora_8439_ocmkorea.trm

[oracle@lnx02 trace]$ ls -al *ocmkorea*

-rw-r----- 1 oracle oinstall  1022 Nov 13 15:11 orcl_ora_15820_ocmkorea.t        rc

-rw-r----- 1 oracle oinstall    60 Nov 13 15:11 orcl_ora_15820_ocmkorea.t        rm

-rw-r----- 1 oracle oinstall 77213 Nov 27 10:31 orcl_ora_8439_ocmkorea.tr        c

-rw-r----- 1 oracle oinstall 25069 Nov 27 10:31 orcl_ora_8439_ocmkorea.tr        m

[oracle@lnx02 trace]$ vi orcl_ora_8439_ocmkorea.trc

******** Index join OK ********

1091

1092 ******** End index join costing ********

1093   Best:: AccessPath: IndexJoin

1094          Cost: 2.50  Degree: 1  Resp: 2.50  Card: 107.00  Bytes: 0

1095

1096 Access path analysis for DEPARTMENTS

1097 ***************************************

1098 SINGLE TABLE ACCESS PATH

1099   Single Table Cardinality Estimation for DEPARTMENTS[D]

1100   Table: DEPARTMENTS  Alias: D

1101     Card: Original: 27.000000  Rounded: 27  Computed: 27.00  Non Adjusted: 2     7.00

1102   Access Path: TableScan

1103     Cost:  3.00  Resp: 3.00  Degree: 0

1104       Cost_io: 3.00  Cost_cpu: 40197

1105       Resp_io: 3.00  Resp_cpu: 40197

1106   Best:: AccessPath: TableScan

1107          Cost: 3.00  Degree: 1  Resp: 3.00  Card: 27.00  Bytes: 0

1108

1109 ***************************************

1300   Best join order: 1

1301   Cost: 5.5043  Degree: 1  Card: 106.0000  Bytes: 2862

1302   Resc: 5.5043  Resc_io: 4.0000  Resc_cpu: 48669328

1303   Resp: 5.5043  Resp_io: 4.0000  Resc_cpu: 48669328