Oracle PL/SQL Tutorial/SQL PLUS Session Environment/Timing
set timing on
<source lang="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 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></source>
Time the PL/SQL block
<source lang="sql">
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</source>
TIMING table copying
<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> 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.</source>
