Oracle PL/SQL/SQL Plus/Timing

Loop timing

   
SQL>
SQL>
SQL>  set timing on
SQL>
SQL>  declare
  2      some_string varchar2(255);
  3    begin
  4      for idx in 1 .. 100000 loop
  5        some_string := rpad("*",254,"*");
  6      end loop;
  7    end;
  8    /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
SQL>
SQL> set timing off
SQL>
SQL>

Performace difference between sql and pl/sql

    
SQL>
SQL>
SQL> create table myTable as select * from all_objects;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats( user, "myTable" );
PL/SQL procedure successfully completed.
SQL>
SQL> create table myTable2 as select * from myTable;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats( user, "myTable2" );
PL/SQL procedure successfully completed.
SQL>
SQL> set timing on
SQL>
SQL> update myTable set object_name = lower(object_name);
12599 rows updated.
Elapsed: 00:00:00.15
SQL> begin
  2     for x in ( select rowid rid, object_name, rownum r from myTable2 )
  3     loop
  4          update myTable2 set object_name = lower(x.object_name)
  5           where rowid = x.rid;
  6          if ( mod(x.r,100) = 0 ) then
  7             commit;
  8          end if;
  9     end loop;
 10     commit;
 11  end;
 12  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.59
SQL>
SQL> set timing off
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTable2;
Table dropped.

Statistics query Timing on index

   
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> set autotrace traceonly
SQL> set timing on
SQL> select ename, deptno, sal,
  2    sum(sal) over
  3      (order by deptno, ename) running_total,
  4    sum(sal) over
  5      (partition by deptno
  6       order by ename) department_total,
  7    row_number() over
  8      (partition by deptno
  9       order by ename) seq
 10  from t emp
 11  order by deptno, ename
 12  /
1000 rows selected.
Elapsed: 00:00:00.19
Execution Plan
----------------------------------------------------------
Plan hash value: 2335850315
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 43000 |     3   (0)| 00:00:01 |
|   1 |  WINDOW SORT       |      |  1000 | 43000 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |  1000 | 43000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         15  consistent gets
          0  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 timing off
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>

Time and autotrace a big table

   
SQL>
SQL>
SQL> set echo on
SQL>
SQL> set termout off
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> analyze table my_all_objects compute statistics;
Table analyzed.
SQL>
SQL> set autotrace on
SQL> set timing on
SQL> select owner, count(*) from my_all_objects group by owner;
OWNER                          COUNT(*)
------------------------------ --------
MDSYS                           1374.00
TSMSYS                             6.00
FLOWS_020100                    3255.00
PUBLIC                          8298.00
OUTLN                             21.00
sqle                           603.00
CTXSYS                          1014.00
HR                               102.00
FLOWS_FILES                       33.00
SYSTEM                          1266.00
DBSNMP                           138.00
OWNER                          COUNT(*)
------------------------------ --------
XDB                             1002.00
SYS                            ########
13 rows selected.
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 2509106709
-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |    13 |    65 |   136   (6)| 00:00:02 |
|   1 |  HASH GROUP BY     |                |    13 |    65 |   136   (6)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| MY_ALL_OBJECTS | 36945 |   180K|   130   (2)| 00:00:02 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        470  consistent gets
          0  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 timing off
SQL> set autotrace off
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>

time a query with where clause

   
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 timing on
SQL> select owner, count(*)
  2    from my_all_objects
  3   where owner = "New Owner"
  4   group by owner;
OWNER                          COUNT(*)
------------------------------ --------
New Owner                          1.00
Elapsed: 00:00:00.10
SQL> set timing off
SQL>
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>

Time command

  
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>
SQL>
SQL>  set autotrace on;
SQL>  set timing on;
SQL>  select count(*) from employee;
  COUNT(*)
----------
         8
Elapsed: 00:00:00.07
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.
Elapsed: 00:00:00.14
SQL>
SQL>
SQL>
SQL>

Time lower text function

   
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> spool off
not spooling currently
SQL> set termout on
SQL>
SQL>
SQL> set timing on
SQL> select lower(owner) from my_all_objects group by owner;
LOWER(OWNER)
------------------------------
mdsys
tsmsys
flows_020100
public
outln
sqle
ctxsys
hr
flows_files
system
dbsnmp
LOWER(OWNER)
------------------------------
xdb
sys
13 rows selected.
Elapsed: 00:00:00.25
SQL> set timing off
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>
SQL>

Time query for a huge table

   
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 timing on
SQL>
SQL> select owner, count(*)
  2  from my_all_objects
  3  group by owner;
OWNER                          COUNT(*)
------------------------------ --------
MDSYS                           1374.00
TSMSYS                             6.00
FLOWS_020100                    3255.00
PUBLIC                          8298.00
OUTLN                             21.00
sqle                           603.00
CTXSYS                          1014.00
HR                               102.00
FLOWS_FILES                       33.00
SYSTEM                          1266.00
DBSNMP                           138.00
OWNER                          COUNT(*)
------------------------------ --------
XDB                             1002.00
SYS                            ########
13 rows selected.
Elapsed: 00:00:00.26
SQL>
SQL> set timing off
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>

Timing passing table collection parameter to a procedure

    
SQL> create or replace type num_list is table of number;
  2  /
Type created.
SQL> create or replace procedure DO_WORK(x num_list) is
  2   begin
  3       for i in 1 .. x.count loop
  4           null;
  5       end loop;
  6   end;
  7  /
Procedure created.
SQL> set timing on
SQL> declare
  2   v varchar2(32767) := "num_list(";
  3   begin
  4       for i in 1 .. 5000 loop
  5           v := v || case when i = 1 then to_char(i) else ","||i end;
  6       end loop;
  7       v := v || ")";
  8       execute immediate "begin do_work("||v||"); end;";
  9   end;
 10  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.97
SQL> set timing off
SQL>

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.

timing unconditional loop

   
SQL>
SQL>  set timing on
SQL>
SQL>  declare
  2      l_idx         pls_integer := 0;
  3      l_some_string varchar2(255);
  4    begin
  5      loop
  6        l_idx := l_idx + 1;
  7        exit when l_idx = 100000;
  8        l_some_string := rpad("*",254,"*");
  9      end loop;
 10    end;
 11    /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
SQL>
SQL>  set timing off
SQL>
SQL>
SQL>