I make apps for other people

Posts from June, 2012

Query timing with SQLPlus

Posted by Chris Jones
On June 15th, 2012 at 10:59

Permalink | Trackback | Links In |

Comments Off on Query timing with SQLPlus
Posted in General

SQL Developer is a good tool for creating queries, but you should be using SQLPlus when trying to time queries. If you don’t already have SQLPlus installed you can get it from Oracle:


You want Oracle Database 11g Release 2 Client ( for Linux x86-64. Unizip the client in your home directory.

Make sure you have the following environment variables defined:

# these point to wherever you have Oracle installed

If you plan to run PL/SQL DDL/DML scripts, you’ll also need a SQLPATH environment variable which points to a directory where you keep the scripts.


If you don’t already have your database defined in your /etc/tnsnames.ora file (assuming you’re using that path and not something under /home/oracle), you’ll need to add the following TNS configuration:

dbalias =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
      (SERVICE_NAME = dbname)

To run SQLPlus against your database as a specific user, use the following command line:

sqlplus username@dbalias

Enter the password when prompted and you’ll now be at the SQL> prompt.

To get script timings, enter the PL/SQL command: