Просмотр исходного текста страницы Oracle PL/SQL/SQL Plus/autotrace

==Autotrace a large table==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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(*)==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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)==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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==



   
  <!-- start source code -->
   
    <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"==



   
  <!-- start source code -->
   
    <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>