Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Timing

set timing on

SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      l_start number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for
 10          "select object_name
 11             from all_objects
 12            where object_id = " || i;
 13          fetch l_rc into l_dummy;
 14          close l_rc;
 15      end loop;
 16      dbms_output.put_line
 17      ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
 18        " seconds..." );
 19  end;
 20  /
47.99 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:48.17
SQL>

Time the PL/SQL block

SQL>
SQL> set echo on
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> set timing on
SQL> declare
  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      myStart number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for "select object_name from all_objects where object_id = " || i;
 10          fetch l_rc into l_dummy;
 11          close l_rc;
 12      end loop;
 13      dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||" seconds..." );
 14  end;
 15  /
20.19 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.27
SQL>
SQL> declare
  2      type rc is ref cursor;
  3      l_rc rc;
  4      l_dummy all_objects.object_name%type;
  5      myStart number default dbms_utility.get_time;
  6  begin
  7      for i in 1 .. 1000
  8      loop
  9          open l_rc for "select object_name from all_objects where object_id = :x"
 10          using i;
 11          fetch l_rc into l_dummy;
 12          close l_rc;
 13      end loop;
 14      dbms_output.put_line( round( (dbms_utility.get_time-myStart)/100, 2 ) ||" seconds..." );
 15  end;
 16  /
.19 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
SQL>
SQL> set timing off

TIMING table copying

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> TIMING START entire_script
SQL>
SQL> --Copy the emp table
SQL> TIMING START copy_emps
SQL> CREATE TABLE emp_copy AS
  2     SELECT * FROM emp;
Table created.
SQL> TIMING STOP
timing for: copy_emps
Elapsed: 00:00:00.07
SQL>
SQL> --Copy the project table
SQL> TIMING START copy_project
SQL> CREATE TABLE project_copy AS
  2     SELECT * FROM project;
Table created.
SQL> TIMING STOP
timing for: copy_project
Elapsed: 00:00:00.07
SQL>
SQL> --Copy the server_usage
SQL> TIMING START copy_server_usage
SQL> CREATE TABLE server_usage_copy AS
  2     SELECT * FROM server_usage;
Table created.
SQL> TIMING STOP
timing for: copy_server_usage
Elapsed: 00:00:00.10
SQL>
SQL> TIMING STOP
timing for: entire_script
Elapsed: 00:00:00.41
SQL>
SQL> --Drop old versions of the copies, if any exist
SQL> DROP TABLE emp_copy;
Table dropped.
SQL> DROP TABLE project_copy;
Table dropped.
SQL> DROP TABLE server_usage_copy;
Table dropped.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table project;
Table dropped.
SQL> drop table server_usage;
Table dropped.