Oracle PL/SQL Tutorial/SQL PLUS Session Environment/AUTOTRACE
Содержание
Autotrace an self join
<source lang="sql">
SQL> SQL> create table t as select * from all_objects where rownum < 50; Table created. SQL> SQL> SQL> alter session set sort_area_size = 1024000; Session altered. SQL> SQL> set autotrace on SQL> SQL> select *
2 from t myTable1, t myTable2 3 where myTable1.object_id = myTable2.object_id 4 /
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S OWNER
--------- --------- ------------------- ------- - - - ------------------------------
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
------------------------------ ---------- -------------- ------------------- --------- ---------
TIMESTAMP STATUS T G S
------- - - -
SYS ICOL$ 20 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$ 20 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1 44 44 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1 44 44 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CON$ 28 28 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CON$ 28 28 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$ 15 15 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$ 15 15 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ# 29 29 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ# 29 29 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ# 3 3 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ# 3 3 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_ROLE_DATA$ 25 25 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_ROLE_DATA$ 25 25 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1 39 39 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1 39 39 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 51 51 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 51 51 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1 26 26 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1 26 26 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FILE$ 17 17 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FILE$ 17 17 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$ 13 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$ 13 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK# 9 9 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK# 9 9 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE1 41 41 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE1 41 41 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON1 48 48 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON1 48 48 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 38 38 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 38 38 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS# 7 7 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS# 7 7 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4 53 53 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4 53 53 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$ 19 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$ 19 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS SEG$ 14 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS SEG$ 14 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_TS# 6 6 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_TS# 6 6 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE2 42 42 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE2 42 42 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS COL$ 21 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS COL$ 21 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS1 43 43 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS1 43 43 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO2 35 35 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO2 35 35 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CLU$ 5 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CLU$ 5 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_DATA$ 23 23 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_DATA$ 23 23 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_DATA$ 24 24 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_DATA$ 24 24 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ1 36 36 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ1 36 36 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL2 46 46 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL2 46 46 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ2 37 37 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ2 37 37 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CCOL1 54 54 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CCOL1 54 54 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TS$ 16 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TS$ 16 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_FILE#_BLOCK# 8 8 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_FILE#_BLOCK# 8 8 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_USER# 10 10 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_USER# 10 10 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO1 34 34 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO1 34 34 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS BOOTSTRAP$ 56 56 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS BOOTSTRAP$ 56 56 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FET$ 12 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FET$ 12 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TAB1 33 33 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TAB1 33 33 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CCOL$ 32 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CCOL$ 32 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS USER$ 22 10 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS USER$ 22 10 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON2 49 49 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON2 49 49 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COBJ# 30 30 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COBJ# 30 30 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS OBJ$ 18 18 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS OBJ$ 18 18 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL3 47 47 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL3 47 47 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_OBJ# 2 2 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_OBJ# 2 2 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TAB$ 4 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TAB$ 4 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CDEF$ 31 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CDEF$ 31 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF1 50 50 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF1 50 50 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
49 rows selected.
Execution Plan
Plan hash value: 2135975663
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 49 | 12544 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN | | 49 | 12544 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| T | 49 | 6272 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T | 49 | 6272 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - access("MYTABLE1"."OBJECT_ID"="MYTABLE2"."OBJECT_ID")
Note
- dynamic sampling used for this statement
Statistics
272 recursive calls
0 db block gets
41 consistent gets
1 physical reads
0 redo size
4375 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
49 rows processed
SQL> alter session set sort_area_size = 10240000; Session altered. SQL> SQL> select *
2 from t myTable1, t myTable2 3 where myTable1.object_id = myTable2.object_id 4 and rownum < 50 5 /
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S OWNER
--------- --------- ------------------- ------- - - - ------------------------------
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
------------------------------ ---------- -------------- ------------------- --------- ---------
TIMESTAMP STATUS T G S
------- - - -
SYS ICOL$ 20 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS ICOL$ 20 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1 44 44 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_USER1 44 44 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CON$ 28 28 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CON$ 28 28 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$ 15 15 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UNDO$ 15 15 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ# 29 29 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_COBJ# 29 29 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ# 3 3 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ# 3 3 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_ROLE_DATA$ 25 25 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_ROLE_DATA$ 25 25 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1 39 39 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_IND1 39 39 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 51 51 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF2 51 51 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1 26 26 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_ROLE_DATA$_1 26 26 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FILE$ 17 17 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FILE$ 17 17 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$ 13 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS UET$ 13 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK# 9 9 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE#_BLOCK# 9 9 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE1 41 41 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE1 41 41 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON1 48 48 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON1 48 48 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 38 38 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ3 38 38 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS# 7 7 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS# 7 7 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4 53 53 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF4 53 53 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$ 19 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS IND$ 19 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS SEG$ 14 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS SEG$ 14 8 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_TS# 6 6 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_TS# 6 6 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE2 42 42 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_FILE2 42 42 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS COL$ 21 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS COL$ 21 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS1 43 43 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TS1 43 43 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO2 35 35 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO2 35 35 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CLU$ 5 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CLU$ 5 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_DATA$ 23 23 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS PROXY_DATA$ 23 23 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_DATA$ 24 24 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_PROXY_DATA$ 24 24 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ1 36 36 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ1 36 36 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL2 46 46 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL2 46 46 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ2 37 37 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_OBJ2 37 37 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CCOL1 54 54 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CCOL1 54 54 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TS$ 16 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TS$ 16 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_FILE#_BLOCK# 8 8 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_FILE#_BLOCK# 8 8 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_USER# 10 10 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_USER# 10 10 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO1 34 34 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_UNDO1 34 34 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS BOOTSTRAP$ 56 56 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS BOOTSTRAP$ 56 56 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FET$ 12 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS FET$ 12 6 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TAB1 33 33 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_TAB1 33 33 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CCOL$ 32 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CCOL$ 32 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS USER$ 22 10 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS USER$ 22 10 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON2 49 49 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CON2 49 49 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COBJ# 30 30 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COBJ# 30 30 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS OBJ$ 18 18 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS OBJ$ 18 18 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL3 47 47 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_COL3 47 47 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_OBJ# 2 2 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS C_OBJ# 2 2 CLUSTER 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TAB$ 4 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS TAB$ 4 2 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CDEF$ 31 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS CDEF$ 31 29 TABLE 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF1 50 50 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N SYS I_CDEF1 50 50 INDEX 07-FEB-06 07-FEB-06 2006-02-07:22:10:08 VALID N N N
49 rows selected.
Execution Plan
Plan hash value: 1415706426
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 49 | 12544 | 5 (20)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | HASH JOIN | | 49 | 12544 | 5 (20)| 00:00:01 | | 3 | TABLE ACCESS FULL| T | 49 | 6272 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL| T | 49 | 6272 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter(ROWNUM<50)
2 - access("MYTABLE1"."OBJECT_ID"="MYTABLE2"."OBJECT_ID")
Note
- dynamic sampling used for this statement
Statistics
15 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
4375 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
49 rows processed
SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL> SQL></source>
autotrace count(*)
<source lang="sql">
SQL> SQL> create table t as select * from all_objects; Table created. SQL> SQL> set autotrace on SQL> select count(*) from t;
COUNT(*)
12586
Execution Plan
Plan hash value: 2966233522
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T |
Note
- rule based optimizer used (consider using cbo)
Statistics
1 recursive calls
0 db block gets
163 consistent gets
159 physical reads
0 redo size
413 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> SQL> set autotrace off SQL> SQL> delete from t where owner <> "SCOTT"; 12586 rows deleted. SQL> SQL> set autotrace on SQL> select count(*) from t;
COUNT(*)
0
Execution Plan
Plan hash value: 2966233522
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T |
Note
- rule based optimizer used (consider using cbo)
Statistics
0 recursive calls
0 db block gets
163 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> SQL> set autotrace off SQL> alter table t move; Table altered. SQL> set autotrace on SQL> select count(*) from t;
COUNT(*)
0
Execution Plan
Plan hash value: 2966233522
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T |
Note
- rule based optimizer used (consider using cbo)
Statistics
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.</source>
autotrace merge command
<source lang="sql">
SQL> create table myTable as select * from dba_objects; Table created. SQL> delete from myTable where rownum <= 100; 100 rows deleted. SQL> create table myTable2 as select * from dba_objects; Table created. SQL> set autotrace on SQL> merge into myTable b
2 using myTable2 o 3 on (b.owner = o.owner and b.object_name = o.object_name 4 and 5 b.subobject_name = o.subobject_name 6 and 7 b.object_id = o.object_id) 8 when matched then update set b.created = o.created 9 when not matched then insert 10 values ( o.OWNER ,o.OBJECT_NAME ,o.SUBOBJECT_NAME ,o.OBJECT_ID ,o.DATA_OBJECT_ID 11 ,o.OBJECT_TYPE ,o.CREATED ,o.LAST_DDL_TIME,o.TIMESTAMP ,o.STATUS,o.TEMPORARY,o.GENERATED 12 ,o.SECONDARY ) 13 /
13219 rows merged.
Execution Plan
Plan hash value: 449939568
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | MERGE STATEMENT | | 13723 | 3832K| | 99 | | 1 | MERGE | MYTABLE | | | | | | 2 | VIEW | | | | | | |* 3 | HASH JOIN OUTER | | 13723 | 2680K| 1504K| 99 | | 4 | TABLE ACCESS FULL| MYTABLE2 | 13723 | 1340K| | 26 | | 5 | TABLE ACCESS FULL| MYTABLE | 13723 | 1340K| | 26 |
Predicate Information (identified by operation id):
3 - access("B"."OBJECT_ID"(+)="O"."OBJECT_ID" AND
"B"."SUBOBJECT_NAME"(+)="O"."SUBOBJECT_NAME" AND
"B"."OBJECT_NAME"(+)="O"."OBJECT_NAME" AND "B"."OWNER"(+)="O"."OWN
ER")
Note
- cpu costing is off (consider enabling it)
Statistics
253 recursive calls
14050 db block gets
388 consistent gets
329 physical reads
4644528 redo size
929 bytes sent via SQL*Net to client
1374 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13219 rows processed
SQL> SQL> set autotrace off SQL> rollback; Rollback complete. SQL> set autotrace on SQL> SQL> merge into myTable b
2 using (select * from myTable2) o 3 on (b.owner = o.owner and b.object_name = o.object_name 4 and 5 b.subobject_name = o.subobject_name 6 and 7 b.object_id = o.object_id) 8 when matched then update set b.created = o.created 9 when not matched then insert 10 values ( o.OWNER ,o.OBJECT_NAME ,o.SUBOBJECT_NAME ,o.OBJECT_ID ,o.DATA_OBJECT_ID 11 ,o.OBJECT_TYPE ,o.CREATED ,o.LAST_DDL_TIME,o.TIMESTAMP ,o.STATUS,o.TEMPORARY,o.GENERATED 12 ,o.SECONDARY ) 13 /
13219 rows merged.
Execution Plan
Plan hash value: 449939568
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | MERGE STATEMENT | | 13723 | 3832K| | 99 | | 1 | MERGE | MYTABLE | | | | | | 2 | VIEW | | | | | | |* 3 | HASH JOIN OUTER | | 13723 | 2680K| 1504K| 99 | | 4 | TABLE ACCESS FULL| MYTABLE2 | 13723 | 1340K| | 26 | | 5 | TABLE ACCESS FULL| MYTABLE | 13723 | 1340K| | 26 |
Predicate Information (identified by operation id):
3 - access("B"."OBJECT_ID"(+)="MYTABLE2"."OBJECT_ID" AND
"B"."SUBOBJECT_NAME"(+)="MYTABLE2"."SUBOBJECT_NAME" AND
"B"."OBJECT_NAME"(+)="MYTABLE2"."OBJECT_NAME" AND
"B"."OWNER"(+)="MYTABLE2"."OWNER")
Note
- cpu costing is off (consider enabling it)
Statistics
4 recursive calls
13763 db block gets
678 consistent gets
0 physical reads
4614184 redo size
929 bytes sent via SQL*Net to client
1390 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13219 rows processed
SQL> SQL> set autotrace off SQL> rollback; Rollback complete. SQL> SQL> drop table myTable; Table dropped. SQL> drop table myTable2; Table dropped. SQL> SQL> SQL></source>
autotrace table with/without an index
<source lang="sql">
SQL> create table website
2 ( hostname varchar2(10), 3 upTime date, 4 load number, 5 other_stats char(65), 6 constraint website_pk primary key(hostname,upTime) 7 ) 8 /
Table created. SQL> SQL> SQL> create table indexedwebsite
2 ( hostname varchar2(10), 3 upTime date, 4 load number, 5 other_stats char(65), 6 constraint indexedwebsite_pk primary key(hostname,upTime) 7 ) 8 organization index 9 /
Table created. SQL> SQL> SQL> declare
2 l_load number;
3 begin
4 for l_HOURS in 1 .. 100
5 loop
6 for l_HOSTS in 1 .. 100
7 loop
8 l_load := dbms_random.random;
9 insert into website(hostname,upTime,load,other_stats)values("hostnm" || l_hosts, sysdate-(100-l_hours)/24,l_load, "x" );
10 insert into indexedwebsite(hostname,upTime,load,other_stats)values("hostnm" || l_hosts, sysdate-(100-l_hours)/24,l_load, "x" );
11 end loop;
12 commit;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> analyze table website compute statistics; Table analyzed. SQL> SQL> SQL> analyze table indexedwebsite compute statistics; Table analyzed. SQL> SQL> SQL> set autotrace on SQL> select avg(load)
2 from website 3 where hostname = "hostnm50" 4 and upTime >= sysdate-100/24 5 / AVG(LOAD)
45908976.3 1 row selected.
Execution Plan
Plan hash value: 757115644
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 29 | 21 | | 1 | SORT AGGREGATE | | 1 | 29 | | |* 2 | TABLE ACCESS FULL| WEBSITE | 100 | 2900 | 21 |
Predicate Information (identified by operation id):
2 - filter("HOSTNAME"="hostnm50" AND
"UPTIME">=SYSDATE@!-4.16666666666666666666666666666666666667)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
136 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select avg(load)
2 from indexedwebsite 3 where hostname = "hostnm50" 4 and upTime >= sysdate-100/24 5 / AVG(LOAD)
45908976.3 1 row selected.
Execution Plan
Plan hash value: 2378983545
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 29 | 4 | | 1 | SORT AGGREGATE | | 1 | 29 | | |* 2 | INDEX RANGE SCAN| INDEXEDWEBSITE_PK | 100 | 2900 | 4 |
Predicate Information (identified by operation id):
2 - access("HOSTNAME"="hostnm50" AND
"UPTIME">=SYSDATE@!-4.16666666666666666666666666666666666667)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
416 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table website; Table dropped. SQL> drop table indexedwebsite; Table dropped.</source>
Controlling the Report
You can control the report by setting the AUTOTRACE system variable.
- SET AUTOTRACE OFF: No AUTOTRACE report is generated(This is the default).
- SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
- SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
- SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
- SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user"s query output, if any.
Quote from www.Oracle.ru
29. 22. AUTOTRACE 29. 22. 1. Controlling the Report 29. 22. 2. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/setautotracetraceonlyexplain.htm">set autotrace traceonly explain</a> 29. 22. 3. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/SETAUTOTRACEOFF.htm">SET AUTOTRACE OFF</a> 29. 22. 4. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Autotraceanselfjoin.htm">Autotrace an self join</a> 29. 22. 5. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/autotracecount.htm">autotrace count(*)</a> 29. 22. 6. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/autotracemergecommand.htm">autotrace merge command</a> 29. 22. 7. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/autotracetablewithwithoutanindex.htm">autotrace table with/without an index</a>
SET AUTOTRACE OFF
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> set autotrace traceonly explain SQL> SQL> select * from employee; Execution Plan
Plan hash value: 2119105728
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 520 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEE | 8 | 520 | 2 (0)| 00:00:01 |
Note
- dynamic sampling used for this statement
SQL> SQL> analyze table employee compute statistics; Table analyzed. SQL> SQL> select * from employee; Execution Plan
Plan hash value: 2119105728
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 376 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEE | 8 | 376 | 2 (0)| 00:00:01 |
SQL> SQL> SET AUTOTRACE OFF SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
set autotrace traceonly explain
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> set autotrace traceonly explain SQL> SQL> select * from employee; Execution Plan
Plan hash value: 2119105728
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 520 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEE | 8 | 520 | 2 (0)| 00:00:01 |
Note
- dynamic sampling used for this statement
SQL> SQL> analyze table employee compute statistics; Table analyzed. SQL> SQL> select * from employee; Execution Plan
Plan hash value: 2119105728
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 8 | 376 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEE | 8 | 376 | 2 (0)| 00:00:01 |
SQL> SQL> SET AUTOTRACE OFF SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
