Using systimestamp to do benchmark : SYSTIMESTAMP « Date Timezone « Oracle PL / SQL






Using systimestamp to do benchmark

   

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>--Obtain the starting time
SQL>COLUMN start_time NEW_VALUE start
SQL>SELECT systimestamp(9) start_time FROM dual;

User System Privileges                                              Page    1

START_TIME
---------------------------------------------------------------------------
26-OCT-09 11.02.43.953000000 AM -08:00

1 row selected.

SQL>
SQL>--Do some work that you want to measure
SQL>SELECT COUNT(*) FROM emp;

User System Privileges                                              Page    1

  COUNT(*)
----------
        10

1 row selected.

SQL>SELECT COUNT(*) FROM project;

User System Privileges                                              Page    1

  COUNT(*)
----------
         5

1 row selected.

SQL>SELECT COUNT(*) FROM server_usage;

User System Privileges                                              Page    1

  COUNT(*)
----------
         7

1 row selected.

SQL>
SQL>--Grab the ending time
SQL>COLUMN end_time NEW_VALUE end
SQL>SELECT systimestamp(9) end_time FROM dual;

User System Privileges                                              Page    1

END_TIME
---------------------------------------------------------------------------
26-OCT-09 11.02.44.313000000 AM -08:00

1 row selected.

SQL>
SQL>--Compute the elapsed time
SQL>COLUMN elapsed_time NEW_VALUE elapsed
SQL>SELECT TO_TIMESTAMP_TZ('&end')
  2         - TO_TIMESTAMP_TZ('&start') elapsed_time
  3  FROM dual;

User System Privileges                                              Page    1

ELAPSED_TIME
---------------------------------------------------------------------------
+000000000 00:00:00.360000000

1 row selected.

SQL>
SQL>--Display the elapsed time
SQL>PROMPT The elapsed time for the SELECTs was: &elapsed
The elapsed time for the SELECTs was: +000000000 00:00:00.360000000
SQL>
SQL>
SQL>drop table emp;

Table dropped.

SQL>drop table project;

Table dropped.

SQL>drop table server_usage;

Table dropped.

   
    
    
  








Related examples in the same category

1.Get the SYSTIMESTAMP
2.Display the sessions current time/timezone AND the Operating systems time/timezone in PL/SQL code block
3.Use systimestamp in insert statement