patternMinor

Measuring the relative performance of queries given the assertion that "cost" isn't reliable

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

theassertionisnreliablethatperformancemeasuringqueriescostgiven

Problem

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.

Source

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?

Solution

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;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

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

Statistics
----------------------------------------------------------
        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.

Code Snippets

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

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

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


Statistics
----------------------------------------------------------
        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

Context

StackExchange Database Administrators Q#16077, answer score: 2

Revisions (0)

No revisions yet.