Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

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.

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

Your Answer

 
discard

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.