Oracle PL/SQL/SQL Plus/autotrace
Содержание
- 1 Autotrace a large table
- 2 autotrace a nested query
- 3 autotrace ansi full outer join
- 4 Autotrace a query on a huge table
- 5 Autotrace a query with group clause
- 6 autotrace command
- 7 autotrace count(*)
- 8 autotrace ctxsys.context index
- 9 AUTOTRACE exists (subquery)
- 10 Autotrace lower text function
- 11 autotrace merge command
- 12 Autotrace on and off
- 13 Autotrace running total
- 14 AUTOTRACE table joining
- 15 AUTOTRACE table joining and aggregate function
- 16 autotrace table with/without an index
- 17 Execution Plan
- 18 set autotrace on explain for every single statement
- 19 set autotrace traceonly
- 20 set autotrace traceonly explain, and condition
- 21 set autotrace traceonly explain for bitmap index
- 22 set autotrace traceonly statistics
- 23 set autotrace traceonly statistics for "select * from tableName"
Autotrace a large table
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> SQL> set autotrace traceonly SQL> SQL> select owner, count(*)
2 from my_all_objects 3 group by owner;
13 rows selected.
Execution Plan
Plan hash value: 2509106709
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 33589 | 557K| 136 (6)| 00:00:02 | | 1 | HASH GROUP BY | | 33589 | 557K| 136 (6)| 00:00:02 | | 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 33589 | 557K| 130 (2)| 00:00:02 |
Note
- dynamic sampling used for this statement
Statistics
48 recursive calls
0 db block gets
537 consistent gets
466 physical reads
0 redo size
694 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)
13 rows processed
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL> SQL>
</source>
autotrace a nested query
<source lang="sql">
SQL> SQL> create table customer(
2 cust_no integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 );
Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");
1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");
1 row created. SQL> SQL> create table ord(
2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> SQL> set autotrace traceonly SQL> SELECT cust_no, lastname
2 FROM customer c 3 WHERE cust_no NOT IN (SELECT cust_no 4 FROM ord );
7 rows selected.
Execution Plan
Plan hash value: 2957014985
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 25 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| CUSTOMER | 14 | 350 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| ORD | 12 | 156 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "ORD" "ORD" WHERE
LNNVL("CUST_NO"<>:B1)))
3 - filter(LNNVL("CUST_NO"<>:B1))
Note
- dynamic sampling used for this statement
Statistics
58 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
586 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)
7 rows processed
SQL> SQL> set autotrace off SQL> drop table customer; Table dropped. SQL> drop table ord; Table dropped. SQL> SQL> --
</source>
autotrace ansi full outer join
<source lang="sql">
SQL> SQL> SQL> create table myTable as
2 select "myTable" as C1 3 ,OBJECT_NAME 4 ,SUBOBJECT_NAME 5 ,OBJECT_ID 6 ,DATA_OBJECT_ID 7 ,OBJECT_TYPE 8 ,CREATED 9 ,LAST_DDL_TIME 10 ,TIMESTAMP 11 ,STATUS 12 ,TEMPORARY 13 ,GENERATED 14 ,SECONDARY 15 from dba_objects;
Table created. SQL> SQL> create table myTable2 as
2 select "myTable2" as C1 3 ,OBJECT_NAME || "myTable2" as object_name 4 ,SUBOBJECT_NAME 5 ,OBJECT_ID 6 ,DATA_OBJECT_ID 7 ,OBJECT_TYPE 8 ,CREATED 9 ,LAST_DDL_TIME 10 ,TIMESTAMP 11 ,STATUS 12 ,TEMPORARY 13 ,GENERATED 14 ,SECONDARY 15 from dba_objects 16 where rownum <= 10000;
Table created. SQL> SQL> create index myTable_object_id on myTable (object_id); Index created. SQL> SQL> create index myTable2_object_id on myTable2 (object_id); Index created. SQL> SQL> analyze table myTable compute statistics; Table analyzed. SQL> SQL> analyze table myTable2 compute statistics; Table analyzed. SQL> SQL> set autotrace TRACEONLY SQL> set timing on SQL> select *
2 from myTable a, myTable2 b 3 where a.object_id = b.object_id(+) 4 union 5 select * 6 from myTable a, myTable2 b 7 where a.object_id(+) = b.object_id;
13158 rows selected. Elapsed: 00:00:00.62 Execution Plan
Plan hash value: 4186416997
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT | | 23158 | 4296K| | 47039 | | 1 | SORT UNIQUE | | 23158 | 4296K| 10M| 47039 | | 2 | UNION-ALL | | | | | | | 3 | NESTED LOOPS OUTER | | 13158 | 2441K| | 26343 | | 4 | TABLE ACCESS FULL | MYTABLE | 13158 | 1169K| | 27 | | 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 1 | 99 | | 2 | |* 6 | INDEX RANGE SCAN | MYTABLE2_OBJECT_ID | 1 | | | 1 | | 7 | NESTED LOOPS OUTER | | 10000 | 1855K| | 20022 | | 8 | TABLE ACCESS FULL | MYTABLE2 | 10000 | 966K| | 22 | | 9 | TABLE ACCESS BY INDEX ROWID| MYTABLE | 1 | 91 | | 2 | |* 10 | INDEX RANGE SCAN | MYTABLE_OBJECT_ID | 1 | | | 1 |
Predicate Information (identified by operation id):
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
10 - access("A"."OBJECT_ID"(+)="B"."OBJECT_ID")
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
43520 consistent gets
0 physical reads
0 redo size
1301014 bytes sent via SQL*Net to client
10027 bytes received via SQL*Net from client
879 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
13158 rows processed
SQL> SQL> SQL> select *
2 from myTable a full outer join myTable2 b 3 using (object_id);
13158 rows selected. Elapsed: 00:00:00.52 Execution Plan
Plan hash value: 3236823177
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 13658 | 4401K| 26365 | | 1 | VIEW | | 13658 | 4401K| 26365 | | 2 | UNION-ALL | | | | | | 3 | NESTED LOOPS OUTER | | 13158 | 2441K| 26343 | | 4 | TABLE ACCESS FULL | MYTABLE | 13158 | 1169K| 27 | | 5 | TABLE ACCESS BY INDEX ROWID| MYTABLE2 | 1 | 99 | 2 | |* 6 | INDEX RANGE SCAN | MYTABLE2_OBJECT_ID | 1 | | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL | MYTABLE2 | 500 | 49500 | 22 | |* 9 | INDEX RANGE SCAN | MYTABLE_OBJECT_ID | 1 | 13 | 1 |
Predicate Information (identified by operation id):
6 - access("A"."OBJECT_ID"="B"."OBJECT_ID"(+))
7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "MYTABLE" "A" WHERE
"A"."OBJECT_ID"=:B1))
9 - access("A"."OBJECT_ID"=:B1)
Note
- cpu costing is off (consider enabling it)
Statistics
1 recursive calls
0 db block gets
45912 consistent gets
0 physical reads
0 redo size
956084 bytes sent via SQL*Net to client
10027 bytes received via SQL*Net from client
879 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
13158 rows processed
SQL> SQL> set timing off SQL> set autotrace off SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL> drop table myTable2; Table dropped.
</source>
Autotrace a query on a huge table
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> set autotrace traceonly SQL> select owner, count(*) from my_all_objects group by owner; 13 rows selected.
Execution Plan
Plan hash value: 2509106709
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 33589 | 557K| 136 (6)| 00:00:02 | | 1 | HASH GROUP BY | | 33589 | 557K| 136 (6)| 00:00:02 | | 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 33589 | 557K| 130 (2)| 00:00:02 |
Note
- dynamic sampling used for this statement
Statistics
48 recursive calls
0 db block gets
537 consistent gets
466 physical reads
0 redo size
694 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)
13 rows processed
SQL> set autotrace off SQL> SQL> drop table my_all_objects; Table dropped. SQL>
</source>
Autotrace a query with group clause
<source lang="sql">
SQL> SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> insert into my_all_objects
2 ( owner, object_name, object_type, object_id ) 3 values 4 ( "New Owner", "New Name", "New Type", 1111111 );
1 row created. SQL> SQL> SQL> set autotrace traceonly SQL> select owner, count(*)
2 from my_all_objects 3 where owner = "New Owner" 4 group by owner;
Execution Plan
Plan hash value: 3282931909
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 5 | 85 | 131 (2)| 00:00:02 | | 1 | SORT GROUP BY NOSORT| | 5 | 85 | 131 (2)| 00:00:02 | |* 2 | TABLE ACCESS FULL | MY_ALL_OBJECTS | 5 | 85 | 131 (2)| 00:00:02 |
Predicate Information (identified by operation id):
2 - filter("OWNER"="New Owner")
Note
- dynamic sampling used for this statement
Statistics
50 recursive calls
0 db block gets
548 consistent gets
466 physical reads
0 redo size
476 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> set autotrace off SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL>
</source>
autotrace command
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 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 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> set autotrace on; SQL> SQL> select count(*) from employee;
COUNT(*)
8
Execution Plan
Plan hash value: 301197670
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| EMPLOYEE | 8 | 2 (0)| 00:00:01 |
Note
- dynamic sampling used for this statement
Statistics
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
411 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> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> 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 ctxsys.context index
<source lang="sql">
SQL> SQL> SQL> create table t ( x clob ); Table created. SQL> SQL> set autotrace traceonly explain SQL> select * from t; Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| T |
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> create index t_idx on t(x) indextype is ctxsys.context; Index created. SQL> select * from t; Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 82 | 8200 | 1 | | 1 | TABLE ACCESS FULL| T | 82 | 8200 | 1 |
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.
</source>
AUTOTRACE exists (subquery)
<source lang="sql">
SQL>CREATE TABLE project (
2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 );
Table created. SQL> SQL> SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789); 1 row created. SQL> SQL> SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 );
Table created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created. SQL> SQL>CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL>SET ECHO ON SQL>SET AUTOTRACE ON SQL> SQL>SELECT emp_id, ename
2 FROM emp 3 WHERE EXISTS (SELECT * 4 FROM server_usage 5 WHERE server_usage.pro_id = 1001 6 AND server_usage.emp_id = emp.emp_id);
User System Privileges Page 1
--------------------
- Mary
1 row selected.
Execution Plan
Plan hash value: 2816981487
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | |* 1 | FILTER | | | 2 | TABLE ACCESS FULL| EMP | |* 3 | INDEX RANGE SCAN | SERVER_USAGE_PK |
Predicate Information (identified by operation id):
1 - filter( EXISTS (SELECT 0 FROM "SERVER_USAGE" "SERVER_USAGE"
WHERE "SERVER_USAGE"."EMP_ID"=:B1 AND "SERVER_USAGE"."PRO_ID"=1
001))
3 - access("SERVER_USAGE"."PRO_ID"=1001 AND
"SERVER_USAGE"."EMP_ID"=:B1)
Note
- rule based optimizer used (consider using cbo)
Statistics
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
470 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 emp; Table dropped. SQL>drop table project; Table dropped. SQL>drop table server_usage; Table dropped.
</source>
Autotrace lower text function
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> SQL> set autotrace traceonly SQL> select lower(owner) from my_all_objects group by owner; 13 rows selected.
Execution Plan
Plan hash value: 2509106709
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 33589 | 557K| 136 (6)| 00:00:02 | | 1 | HASH GROUP BY | | 33589 | 557K| 136 (6)| 00:00:02 | | 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 33589 | 557K| 130 (2)| 00:00:02 |
Note
- dynamic sampling used for this statement
Statistics
48 recursive calls
0 db block gets
537 consistent gets
466 physical reads
0 redo size
593 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)
13 rows processed
SQL> set autotrace off SQL> SQL> drop table my_all_objects; Table dropped. SQL>
</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 on and off
<source lang="sql">
SQL> SQL> create table my_all_objects
2 nologging 3 as 4 select * from all_objects 5 union all 6 select * from all_objects 7 union all 8 select * from all_objects 9 /
Table created. SQL> SQL> insert into my_all_objects
2 ( owner, object_name, object_type, object_id ) 3 values 4 ( "New Owner", "New Name", "New Type", 1111111 );
1 row created. SQL> SQL> SQL> set autotrace traceonly SQL> select count(*)
2 from my_all_objects 3 where owner = "New Owner";
Execution Plan
Plan hash value: 2942800307
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 17 | 131 (2)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | TABLE ACCESS FULL| MY_ALL_OBJECTS | 5 | 85 | 131 (2)| 00:00:02 |
Predicate Information (identified by operation id):
2 - filter("OWNER"="New Owner")
Note
- dynamic sampling used for this statement
Statistics
50 recursive calls
0 db block gets
548 consistent gets
466 physical reads
0 redo size
411 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> set autotrace off SQL> SQL> drop table my_all_objects; Table dropped. SQL> SQL>
</source>
Autotrace running total
<source lang="sql">
SQL> SQL> set echo on SQL> SQL> create table t
2 as 3 select object_name ename, 4 mod(object_id,50) deptno, 5 object_id sal 6 from all_objects 7 where rownum <= 1000 8 /
Table created. SQL> SQL> create index t_idx on t(deptno,ename); Index created. SQL> SQL> select ename, deptno, sal,
2 (select sum(sal) 3 from t e2 4 where e2.deptno < emp.deptno 5 or (e2.deptno = emp.deptno and e2.ename <= emp.ename )) 6 running_total, 7 (select sum(sal) 8 from t e3 9 where e3.deptno = emp.deptno 10 and e3.ename <= emp.ename) 11 department_total, 12 (select count(ename) 13 from t e3 14 where e3.deptno = emp.deptno 15 and e3.ename <= emp.ename) seq 16 from t emp 17 order by deptno, ename 18 /
1000 rows selected.
Execution Plan
Plan hash value: 92139442
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1000 | 43000 | 4 (25)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 43 | | | |* 2 | TABLE ACCESS FULL | T | 50 | 2150 | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 43 | | | | 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 2 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | T_IDX | 1 | | 2 (0)| 00:00:01 | | 6 | SORT AGGREGATE | | 1 | 30 | | | |* 7 | INDEX RANGE SCAN | T_IDX | 1 | 30 | 2 (0)| 00:00:01 | | 8 | SORT ORDER BY | | 1000 | 43000 | 4 (25)| 00:00:01 | | 9 | TABLE ACCESS FULL | T | 1000 | 43000 | 3 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - filter("E2"."DEPTNO"<:B1 OR "E2"."DEPTNO"=:B2 AND "E2"."ENAME"<=:B3)
5 - access("E3"."DEPTNO"=:B1 AND "E3"."ENAME"<=:B2)
7 - access("E3"."DEPTNO"=:B1 AND "E3"."ENAME"<=:B2)
Note
- dynamic sampling used for this statement
Statistics
39 recursive calls
0 db block gets
15678 consistent gets
4 physical reads
0 redo size
44533 bytes sent via SQL*Net to client
1106 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000 rows processed
SQL> SQL> set autotrace off
</source>
AUTOTRACE table joining
<source lang="sql">
SQL> SQL>CREATE TABLE project (
2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 );
Table created. SQL> SQL> SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789); 1 row created. SQL> SQL> SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 );
Table created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created. SQL> SQL>CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL>SET ECHO ON SQL>SET AUTOTRACE TRACEONLY EXPLAIN SQL> SQL>SELECT ename, SUM(hours_logged)
2 FROM emp, server_usage 3 WHERE emp.emp_id = server_usage.emp_id 4 GROUP BY ename;
Execution Plan
Plan hash value: 2561811960
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | SERVER_USAGE | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | |* 5 | INDEX UNIQUE SCAN | EMP_PK |
Predicate Information (identified by operation id):
5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID")
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> SQL>set autotrace off SQL> SQL>drop table emp; Table dropped. SQL>drop table project; Table dropped. SQL>drop table server_usage; Table dropped.
</source>
AUTOTRACE table joining and aggregate function
<source lang="sql">
SQL>CREATE TABLE project (
2 pro_id NUMBER(4), 3 pro_name VARCHAR2(40), 4 budget NUMBER(9,2), 5 CONSTRAINT project_pk PRIMARY KEY (pro_id) 6 );
Table created. SQL> SQL> SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1001, "A",12345); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1002, "ERP",23456); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1003, "SQL",34567); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1004, "CRM",45678); 1 row created. SQL>INSERT INTO project(pro_id, pro_name, budget)VALUES (1005, "VPN",56789); 1 row created. SQL> SQL> SQL>CREATE TABLE server_usage (
2 pro_id NUMBER(4), 3 emp_id NUMBER, 4 time_log_date DATE, 5 hours_logged NUMBER(8,2), 6 dollars_charged NUMBER(8,2), 7 CONSTRAINT server_usage_pk PRIMARY KEY (pro_id, emp_id, time_log_date) 8 );
Table created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);
1 row created. SQL>INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)
2 VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);
1 row created. SQL> SQL>CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL>INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL>SET ECHO ON SQL>SET AUTOTRACE ON EXPLAIN SQL> SQL>SELECT ename, SUM(hours_logged)
2 FROM emp, server_usage 3 WHERE emp.emp_id = server_usage.emp_id 4 GROUP BY ename;
User System Privileges Page 1
SUM(HOURS_LOGGED)
-----------------
Less 1129 Mary 1123 Mike 1127 Peter 1126 Tom 1124 5 rows selected.
Execution Plan
Plan hash value: 2561811960
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | SORT GROUP BY | | | 2 | NESTED LOOPS | | | 3 | TABLE ACCESS FULL | SERVER_USAGE | | 4 | TABLE ACCESS BY INDEX ROWID| EMP | |* 5 | INDEX UNIQUE SCAN | EMP_PK |
Predicate Information (identified by operation id):
5 - access("EMP"."EMP_ID"="SERVER_USAGE"."EMP_ID")
Note
- rule based optimizer used (consider using cbo)
SQL> SQL>set autotrace off SQL> SQL>drop table emp; Table dropped. SQL>drop table project; Table dropped. SQL>drop table server_usage; Table dropped.
</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>
Execution Plan
<source lang="sql">
SQL> SQL> create table indextest as select * from dba_objects
2 where owner in ("OUTLN","PUBLIC","SCOTT","SYS","SYSTEM");
Table created. SQL> SQL> analyze table indextest compute statistics; Table analyzed. SQL> SQL> set autotrace trace explain SQL> select owner, object_name from indextest
2 where object_name = "DBA_INDEXES";
Execution Plan
Plan hash value: 2792531790
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 21 | 36 (3)| 00:00:01 | |* 1 | TABLE ACCESS FULL| INDEXTEST | 1 | 21 | 36 (3)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("OBJECT_NAME"="DBA_INDEXES")
SQL> SQL> create index indxtest_objname_idx
2 on indextest (object_name);
Index created. SQL> SQL> select owner, object_name from indextest
2 where object_name = "DBA_INDEXES";
Execution Plan
Plan hash value: 1012932391
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| INDEXTEST | 1 | 21 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | INDXTEST_OBJNAME_IDX | 1 | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("OBJECT_NAME"="DBA_INDEXES")
SQL> SQL> SQL> drop table indextest; Table dropped. SQL> SQL>
</source>
set autotrace on explain for every single statement
<source lang="sql">
SQL> set autotrace on explain SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created.
Execution Plan
| Id | Operation | Name |
| 0 | INSERT STATEMENT | |
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> set autotrace on SQL> SQL> select ename from emp where empno < 7500; ENAME
Tom Jack Wil Jane Mary Black Chris Smart Peter Take Ana Jane Fake Mike 14 rows selected.
Execution Plan
Plan hash value: 1482559701
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | |* 2 | INDEX RANGE SCAN | E_PK |
Predicate Information (identified by operation id):
2 - access("EMPNO"<7500)
Note
- rule based optimizer used (consider using cbo)
Statistics
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
572 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)
14 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped.
</source>
set autotrace traceonly
<source lang="sql">
create table t as
select *
from all_objects
where rownum < 10001;
create index t_idx on t(object_id); analyze table t compute statistics; set autotrace traceonly select * from t where object_id = 10; drop table t; --
</source>
set autotrace traceonly explain, and condition
<source lang="sql">
SQL> SQL> SQL> create table t
2 as 3 select mod(ROWNUM,3) a, ROWNUM b, ROWNUM c 4 from all_tables;
Table created. SQL> SQL> set autotrace traceonly explain SQL> select * from t where b = 1 and c = 1; Execution Plan
Plan hash value: 1601196873
| Id | Operation | Name |
| 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| T |
Predicate Information (identified by operation id):
1 - filter("C"=1 AND "B"=1)
Note
- rule based optimizer used (consider using cbo)
SQL> SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped.
</source>
set autotrace traceonly explain for bitmap index
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> create bitmap index emp_bm_idx on emp( d.dname)from emp e, dept d where e.deptno = d.deptno
2 /
SQL> begin
2 dbms_stats.set_table_stats( user, "EMP", numrows => 1000000, numblks => 300000 ); 3 dbms_stats.set_table_stats( user, "DEPT",numrows => 100000, numblks => 30000 ); 4 end; 5 /
PL/SQL procedure successfully completed. SQL> SQL> set autotrace traceonly explain SQL> SQL> select count(*) from emp, dept where emp.deptno = dept.deptno and dept.dname = "SALES"
2 /
Execution Plan
Plan hash value: 1546158010
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 35 | 50088 | | 1 | SORT AGGREGATE | | 1 | 35 | | |* 2 | HASH JOIN | | 10M| 333M| 50088 | |* 3 | TABLE ACCESS FULL| DEPT | 1000 | 22000 | 4554 | | 4 | TABLE ACCESS FULL| EMP | 1000K| 12M| 45533 |
Predicate Information (identified by operation id):
2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
3 - filter("DEPT"."DNAME"="SALES")
Note
- cpu costing is off (consider enabling it)
SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL> SQL>
</source>
set autotrace traceonly statistics
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> set autotrace traceonly statistics SQL> SQL> select * from emp; 14 rows selected.
Statistics
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1433 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)
14 rows processed
SQL> SQL> set autotrace off SQL> SQL> SQL> drop table emp; Table dropped.
</source>
set autotrace traceonly statistics for "select * from tableName"
<source lang="sql">
SQL> SQL> create table t as select * from all_objects where 1=0; Table created. SQL> SQL> select * from t; no rows selected SQL> SQL> set autotrace traceonly statistics SQL> SQL> select * from t; no rows selected
Statistics
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SQL> set autotrace off SQL> SQL> insert into t select * from all_objects; 12599 rows created. SQL> SQL> rollback; Rollback complete. SQL> SQL> select * from t; no rows selected SQL> SQL> set autotrace traceonly statistics SQL> SQL> select * from t; no rows selected
Statistics
0 recursive calls
0 db block gets
166 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SQL> set autotrace off SQL> SQL> drop table t; Table dropped. SQL> SQL>
</source>
