Using the Explain only option. 07:54:44 orcl9i> set autotrace on exp 07:55:20 orcl9i> r 1 select a.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_code, c.city, c.state_province 2 from hr.employees a, 3 hr.departments b, 4 hr.locations c 5 where a.department_id=b.department_id 6 and b.location_id=c.location_id 7 and a.employee_id = 174 8* order by a.last_name EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME STREET_ADDRESS ----------- ------------------------- ------------- ------------------------------ ---------------------------------------- POSTAL_CODE CITY STATE_PROVINCE ------------ ------------------------------ ------------------------- 174 Abel 80 Sales Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=82) 1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=82) 2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=34) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=2 C ard=1 Bytes=15) 4 3 INDEX (UNIQUE SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE) (Cos t=1 Card=1) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (Cost=1 Card=1 Bytes=19) 6 5 INDEX (UNIQUE SCAN) OF 'DEPT_ID_PK' (UNIQUE) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LOCATIONS' (Cost=1 Car d=1 Bytes=48) 8 7 INDEX (UNIQUE SCAN) OF 'LOC_ID_PK' (UNIQUE) Using the statistics option 07:55:43 orcl9i> set autotrace on stat 07:55:57 orcl9i> r 1 select a.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_code, c.city, c.state_province 2 from hr.employees a, 3 hr.departments b, 4 hr.locations c 5 where a.department_id=b.department_id 6 and b.location_id=c.location_id 7 and a.employee_id = 174 8* order by a.last_name EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME STREET_ADDRESS ----------- ------------------------- ------------- ------------------------------ ---------------------------------------- POSTAL_CODE CITY STATE_PROVINCE ------------ ------------------------------ ------------------------- 174 Abel 80 Sales Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 876 bytes sent via SQL*Net to client 368 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Using "autotrace on" option to display both statistics and acces path prediction. 07:56:15 orcl9i> set autotrace on 07:56:20 orcl9i> r 1 select a.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_code, c.city, c.state_province 2 from hr.employees a, 3 hr.departments b, 4 hr.locations c 5 where a.department_id=b.department_id 6 and b.location_id=c.location_id 7 and a.employee_id = 174 8* order by a.last_name EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME STREET_ADDRESS ----------- ------------------------- ------------- ------------------------------ ---------------------------------------- POSTAL_CODE CITY STATE_PROVINCE ------------ ------------------------------ ------------------------- 174 Abel 80 Sales Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=82) 1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=82) 2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=34) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=2 C ard=1 Bytes=15) 4 3 INDEX (UNIQUE SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE) (Cos t=1 Card=1) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (Cost=1 Card=1 Bytes=19) 6 5 INDEX (UNIQUE SCAN) OF 'DEPT_ID_PK' (UNIQUE) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LOCATIONS' (Cost=1 Car d=1 Bytes=48) 8 7 INDEX (UNIQUE SCAN) OF 'LOC_ID_PK' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 876 bytes sent via SQL*Net to client 368 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Using the "traceonly" option to suppress the query's output. 07:56:22 orcl9i> set autotrace traceonly 07:56:41 orcl9i> r 1 select a.employee_id, a.last_name, b.department_id, b.department_name,c.street_address, c.postal_code, c.city, c.state_province 2 from hr.employees a, 3 hr.departments b, 4 hr.locations c 5 where a.department_id=b.department_id 6 and b.location_id=c.location_id 7 and a.employee_id = 174 8* order by a.last_name Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=82) 1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=82) 2 1 NESTED LOOPS (Cost=3 Card=1 Bytes=34) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=2 C ard=1 Bytes=15) 4 3 INDEX (UNIQUE SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE) (Cos t=1 Card=1) 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENTS' (Cost=1 Card=1 Bytes=19) 6 5 INDEX (UNIQUE SCAN) OF 'DEPT_ID_PK' (UNIQUE) 7 1 TABLE ACCESS (BY INDEX ROWID) OF 'LOCATIONS' (Cost=1 Car d=1 Bytes=48) 8 7 INDEX (UNIQUE SCAN) OF 'LOC_ID_PK' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 876 bytes sent via SQL*Net to client 368 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 07:56:43 orcl9i> spool off