Check SQL runtimes directly on database server

Sometimes slow queries are identified based on delayed queries flag in the db.log file, however, it might result from JVM issues on Product Master server. In order to exclude this, such slow queries can be run directly on dbserver to exclude network or JVM issues. Of course you should use only select statements for such runtime measurement tests.

Symptoms

  • Oracle:
    • You can get query runtime using sqlplus. Create a file containing the SQL along with some settings, for example, sql1.sql. The file has to end with .sql otherwise sqlplus will not recognize the input.
      set timing on;
      set linesize 1000;
      set pagesize 1000;

      <SQL statement>;

      quit;
    • run the query:
      sqlplus <user>/<passwd> @sql1.sql
    • the query result will be printed on screen with the runtime shown like:
      Elapsed: 00:00:02.71
    • If you want to avoid screen display, you can use additional settings to redirect the output to a file:
      set timing on;
      set linesize 1000;
      set pagesize 1000;
      set term off;
      spool tmp.out;

      <SQL statement>;

      spool off;
      quit;
  • DB2®:
    • Use the db2batch utility to collect query execution time.
      • Save the SQL into file. Aggregate the SQL on one line and terminate with a semicolon.
      • Invoke db2batch:
        db2batch -d <dbname> -a <userid>/<passwd> -f <sqlfile> -i complete -o f -1 p 1 o 5

        whereby the options have following meaning
        -i complete:  The time to prepare, execute, and fetch are expressed separately
        -o  specifies a set of options like:
        f  number of rows to fetch  (-1 all,  n = n rows)
        p  level of performance information to be returned (2 = Return elapsed time and CPU time)
        o  query optimization level  (default 5)