Oracle PL/SQL Tutorial/SQL PLUS Session Environment/AUTOTRACE — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
Autotrace an self join
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>
autotrace count(*)
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.
autotrace merge command
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>
autotrace table with/without an index
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.
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
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.
set autotrace traceonly explain
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.