Mischiefblog
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:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

You want Oracle Database 11g Release 2 Client (11.2.0.1.0) 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
ORACLE_HOME=~/oracle/product/11.1.0/client_1
LD_LIBRARY_PATH=~/oracle/product/11.1.0/client_1
PATH=$PATH:~/oracle/product/11.1.0/client_1

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.

SQLPATH=~/sqlscripts

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 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (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:

SET TIMING ON