Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.


Does this make room for a more reliable metric we can use to measure the relative performance (CPU, I/O, or time) of similar queries which are designed to have the same end goal?

share|improve this question

There are a variety of metrics you can use.

If you actually execute the queries, Oracle provides a ton of different metrics to evaluate relative performance. In SQL*Plus, if you enable autotrace, you'll get a quick listing of a few execution statistics after you run a query. In the vast majority of cases, if you are reducing logical I/O (the "consistent gets" metric in the autotrace output), you're improving response time.

SQL> set autotrace on;
SQL> select ename from emp;



14 rows selected.

Execution Plan
Plan hash value: 3956160932

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |    14 |    84 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |    84 |     3   (0)| 00:00:01 |

        340  recursive calls
          0  db block gets
         71  consistent gets
          8  physical reads
          0  redo size
        715  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         14  rows processed

If you want to go into more detail, however, Oracle provides far more metrics. If you query V$STATNAME, you'll see hundreds of statistics that are available. V$SESSTAT contains the current value of every statistic for your session so you can grab the "before" data, run a query, then grab the "after" data and look at the change in virtually any performance statistic you might be interested in. Tom Kyte put together a nice runstats package that automates this so that you don't have to write the test harness yourself.

share|improve this answer
Logical I/O's is both "db block gets" and "consistent gets". The pithy way to remember to tune them is "High LIO SQL, away"! – Adam Musch Apr 10 '12 at 16:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.