Oracle - Performance - Timing a Statement

Always, start with a fresh database session to remove any caching from previous runs.

connect user/password@database;

set timing on;

select 
	PKG_ACCT_DECRYPTION_FUNCTIONS.f_secure_decrypt(PKG_ACCT_ENCRYPTION_FUNCTIONS.f_secure_encrypt(to_char(sysdate,'mm/dd/yyyy hh24:mi:ss')))
from dual;

Also see Explain Plan and SysTimeStamp with Milliseconds.

Oracle Explain Plan

set serveroutput on;
clear;

explain plan for
   select
   d.id,
   --d.xml,
   extractValue(d.xml,'/Account/StarId')"StarID",
   extractValue(d.xml,'/Account/Name') "DistributorName",
   NVL(extractValue(d.xml,'/Account/FranchiseCode'),'< Null >') "FranchiseCode"
   from
   genesis_dist_to_ent d
   where
   -- rownum <= 5
   extractValue(d.xml,'/Account/StarId') = '16534' ;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Results:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | B
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               | 29002 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  2 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  3 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  4 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  5 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  6 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  7 |  FILTER                      |                               |       |
|   8 |   INDEX FAST FULL SCAN       | PK_GENESIS_DIST_TO_ENT        | 29002 |
|*  9 |   TABLE ACCESS BY INDEX ROWID| GENESIS_XMLINDEX_PATHTABLE    |   371 |
|* 10 |    INDEX RANGE SCAN          | SYS3119198_GENESIS__PATHID_IX |     4 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."PATHID"=HEXTORAW('4B1D')  AND "SYS_P0"."RID"=:B1)
   3 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
   4 - access("SYS_P2"."PATHID"=HEXTORAW('7F13')  AND "SYS_P2"."RID"=:B1)
   5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P4"."LOCATOR")=1)
   6 - access("SYS_P4"."PATHID"=HEXTORAW('35BE')  AND "SYS_P4"."RID"=:B1)
   7 - filter( (SELECT "SYS_P6"."VALUE" FROM "PROD"."GENESIS_XMLINDEX_PATHTABLE"
              WHERE "SYS_P6"."RID"=:B1 AND "SYS_P6"."PATHID"=HEXTORAW('4B1D')  A
              SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)='16534')
   9 - filter(SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)
  10 - access("SYS_P6"."PATHID"=HEXTORAW('4B1D')  AND "SYS_P6"."RID"=:B1)
Note
-----
   - 'PLAN_TABLE' is old version
 
35 rows selected

PL/SQL : Example using SysTimeStamp (milliseconds for SysDate)

Oracle's SYSDATE only goes to the second. If you want more precision, then you need to use the new SYSTIMESTAMP (available starting in Oracle 9). Source :

declare

ltimestamp_start       timestamp;
ltimestamp_stop        timestamp;
linterval_diff         interval day to second;

ldt_temp               date;

begin

ltimestamp_start := systimestamp;
dbms_output.put_line(ltimestamp_start);

  -- Routine to performance test... 
  for i in 1 .. 10000 loop
    select sysdate into ldt_temp from dual;
  end loop;

ltimestamp_stop := systimestamp;
dbms_output.put_line(ltimestamp_stop);

linterval_diff := ltimestamp_stop - ltimestamp_start;

dbms_output.put_line(CHR(10)||LPAD('=',22,'=')||CHR(10));
dbms_output.put_line('  Runtime Difference'||CHR(10)||LPAD('=',22,'='));
dbms_output.put_line(
     '     Days : '||EXTRACT(DAY FROM linterval_diff)||CHR(10)||
     '    Hours : '||EXTRACT(HOUR FROM linterval_diff)||CHR(10)||
     '  Minutes : '||EXTRACT(MINUTE FROM linterval_diff)||CHR(10)||
     '  Seconds : '||EXTRACT(SECOND FROM linterval_diff) );
end;

Results :

27-NOV-06 01.26.50.118424 PM
27-NOV-06 01.26.51.125141 PM

======================
Runtime Difference
======================
Days : 0
Hours : 0
Minutes : 0
Seconds : 1.006717