“Money is our madness, our vast collective madness.

And of course, if the multitude is mad
the individual carries his own grain of insanity around with him.

I doubt if any man living hands out a pound note without a pang;
and a real tremor, if he hands out a ten-pound note.

We quail, money makes us quail.
It has got us down, we grovel before it in strange terror.
And no wonder, for money has a fearful cruel power among men.

But it is not money we are so terrified of,
it is the collective money-madness of mankind.
For mankind says with one voice: How much is he worth?
Has he no money? Then let him eat dirt, and go cold.”

—Money-Madness by D. H. Lawrence (1885–1930)

The cost displayed in query plans has misled many. The natural assumption is that execution time will be low if the cost is low and high if the cost is high. However, the cost is only an estimate. Query optimization is an extremely complex problem and the optimizer is more likely to be wrong than correct. The inventor of “tuning by cardinality feedback,” Wolfgang Breitling, said “I became interested in the CBO’s selectivity calculations trying to understand why it comes up with some of the ridiculously low cardinality estimates (like 1 when in reality there are 80,000+) which then lead to disastrous access plans that take hours, provided they finish at all, instead of minutes or seconds.” Since the CBO selects the plan with the lowest estimate it can find, it is easy to argue that the plan that it finally chooses is very likely to have an underestimated cost.

Also, the unit of cost is not well known. Prior to Oracle Database 9i, the query optimizer’s goal was to minimize the number of I/O requests; that is the sum of single block reads and multi-block reads. Therefore, the “cost” reported in query plans prior to Oracle Database 9i was the estimated number of I/O requests. Beginning with Oracle Database 9i, the query optimizer has the more ambitious goal of minimizing the query execution time.  However, the resulting time estimate is divided by the estimate of the time to read one database block from storage (SREADTIM) recorded in the system statistics (SYS.AUX_STATS$). Therefore, cost is time but in units of SREADTIM, not clock seconds.

Finally, we tend to forget that the execution time on an execution plan can vary tremendously. Since query plans are cached and reused, the execution time of any query that uses bind variables also depends on the values of the bind variables used for each execution. A query plan that is efficient for one choice of values may be hopelessly inefficient for other values.

To summarize, the cost displayed in query plans is a misleading and useless piece of information:

  • It is an estimate that is more likely to be wrong than correct
  • It is computed using particular values of bind variables
  • It is measured in units of SREADTIM, not clock seconds

Did I mention that the cost is a misleading and useless piece of information?

So how does one gauge the efficiency of a query plan? One executes the query to completion and then one uses the procedure DBMS_XPLAN.DISPLAY_CURSOR (or your own custom query) to display various execution statistics stored in V$SQL_PLAN_STATISTICS. If using Toad, you can use SGA Trace to view query execution plans. Here is an example of the use of DBMS_XPLAN.DISPLAY_CURSOR..

variable b1 number;

exec :b1 := 0;

alter session set "_rowsource_execution_statistics"=true; -- refer to Note 1 for an explanation

select employee_id, first_name, last_name, hire_date from employees
where employee_id > :b1;

----------- -------------------- ------------------------- ---------
        100 Steven               King                      17-JUN-87
        101 Neena                Kochhar                   21-SEP-89
        102 Lex                  De Haan                   13-JAN-93
        103 Alexander            Hunold                    03-JAN-90
        104 Bruce                Ernst                     21-MAY-91
        105 David                Austin                    25-JUN-97
        106 Valli                Pataballa                 05-FEB-98
        107 Diana                Lorentz                   07-FEB-99
        108 Nancy                Greenberg                 17-AUG-94
        109 Daniel               Faviet                    16-AUG-94
        110 John                 Chen                      28-SEP-97
        111 Ismael               Sciarra                   30-SEP-97
        112 Jose Manuel          Urman                     07-MAR-98
        113 Luis                 Popp                      07-DEC-99
        114 Den                  Raphaely                  07-DEC-94
        115 Alexander            Khoo                      18-MAY-95
        116 Shelli               Baida                     24-DEC-97
        117 Sigal                Tobias                    24-JUL-97
        118 Guy                  Himuro                    15-NOV-98
        119 Karen                Colmenares                10-AUG-99
        120 Matthew              Weiss                     18-JUL-96
        121 Adam                 Fripp                     10-APR-97
        122 Payam                Kaufling                  01-MAY-95
        123 Shanta               Vollman                   10-OCT-97
        124 Kevin                Mourgos                   16-NOV-99
        125 Julia                Nayer                     16-JUL-97
        126 Irene                Mikkilineni               28-SEP-98
        127 James                Landry                    14-JAN-99
        128 Steven               Markle                    08-MAR-00
        129 Laura                Bissot                    20-AUG-97
        130 Mozhe                Atkinson                  30-OCT-97
        131 James                Marlow                    16-FEB-97
        132 TJ                   Olson                     10-APR-99
        133 Jason                Mallin                    14-JUN-96
        134 Michael              Rogers                    26-AUG-98
        135 Ki                   Gee                       12-DEC-99
        136 Hazel                Philtanker                06-FEB-00
        137 Renske               Ladwig                    14-JUL-95
        138 Stephen              Stiles                    26-OCT-97
        139 John                 Seo                       12-FEB-98
        140 Joshua               Patel                     06-APR-98
        141 Trenna               Rajs                      17-OCT-95
        142 Curtis               Davies                    29-JAN-97
        143 Randall              Matos                     15-MAR-98
        144 Peter                Vargas                    09-JUL-98
        145 John                 Russell                   01-OCT-96
        146 Karen                Partners                  05-JAN-97
        147 Alberto              Errazuriz                 10-MAR-97
        148 Gerald               Cambrault                 15-OCT-99
        149 Eleni                Zlotkey                   29-JAN-00
        150 Peter                Tucker                    30-JAN-97
        151 David                Bernstein                 24-MAR-97
        152 Peter                Hall                      20-AUG-97
        153 Christopher          Olsen                     30-MAR-98
        154 Nanette              Cambrault                 09-DEC-98
        155 Oliver               Tuvault                   23-NOV-99
        156 Janette              King                      30-JAN-96
        157 Patrick              Sully                     04-MAR-96
        158 Allan                McEwen                    01-AUG-96
        159 Lindsey              Smith                     10-MAR-97
        160 Louise               Doran                     15-DEC-97
        161 Sarath               Sewall                    03-NOV-98
        162 Clara                Vishney                   11-NOV-97
        163 Danielle             Greene                    19-MAR-99
        164 Mattea               Marvins                   24-JAN-00
        165 David                Lee                       23-FEB-00
        166 Sundar               Ande                      24-MAR-00
        167 Amit                 Banda                     21-APR-00
        168 Lisa                 Ozer                      11-MAR-97
        169 Harrison             Bloom                     23-MAR-98
        170 Tayler               Fox                       24-JAN-98
        171 William              Smith                     23-FEB-99
        172 Elizabeth            Bates                     24-MAR-99
        173 Sundita              Kumar                     21-APR-00
        174 Ellen                Abel                      11-MAY-96
        175 Alyssa               Hutton                    19-MAR-97
        176 Jonathon             Taylor                    24-MAR-98
        177 Jack                 Livingston                23-APR-98
        178 Kimberely            Grant                     24-MAY-99
        179 Charles              Johnson                   04-JAN-00
        180 Winston              Taylor                    24-JAN-98
        181 Jean                 Fleaur                    23-FEB-98
        182 Martha               Sullivan                  21-JUN-99
        183 Girard               Geoni                     03-FEB-00
        184 Nandita              Sarchand                  27-JAN-96
        185 Alexis               Bull                      20-FEB-97
        186 Julia                Dellinger                 24-JUN-98
        187 Anthony              Cabrio                    07-FEB-99
        188 Kelly                Chung                     14-JUN-97
        189 Jennifer             Dilly                     13-AUG-97
        190 Timothy              Gates                     11-JUL-98
        191 Randall              Perkins                   19-DEC-99
        192 Sarah                Bell                      04-FEB-96
        193 Britney              Everett                   03-MAR-97
        194 Samuel               McCain                    01-JUL-98
        195 Vance                Jones                     17-MAR-99
        196 Alana                Walsh                     24-APR-98
        197 Kevin                Feeney                    23-MAY-98
        198 Donald               OConnell                  21-JUN-99
        199 Douglas              Grant                     13-JAN-00
        200 Jennifer             Whalen                    17-SEP-87
        201 Michael              Hartstein                 17-FEB-96
        202 Pat                  Fay                       17-AUG-97
        203 Susan                Mavris                    07-JUN-94
        204 Hermann              Baer                      07-JUN-94
        205 Shelley              Higgins                   07-JUN-94
        206 William              Gietz                     07-JUN-94

107 rows selected.

select * from table(dbms_xplan.display_cursor(format=>'typical iostats last -cost'));

SQL_ID  91wuwaapxuv6a, child number 0
select employee_id, first_name, last_name, hire_date from employees
where employee_id > :b1

Plan hash value: 1445457117

| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| E-Time   | A-Rows |   A-Time   | Buffers |
|   0 | SELECT STATEMENT  |           |      1 |        |       |          |    107 |00:00:00.01 |      14 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |      1 |    107 |  2889 | 00:00:01 |    107 |00:00:00.01 |      14 |

Predicate Information (identified by operation id):

   1 - filter("EMPLOYEE_ID">:B1)

Instead of the Cost column—which has been omitted from the above example by way of the format specifier—focus on the Buffers column. It is the cumulative number of buffers retrieved in consistent mode (LAST_CR_BUFFER_GETS from V$SQL_PLAN_STATISTICS) during the execution of any line andall its children. Therefore, the buffers reported for the first line are the total number of buffers retrieved during the execution of the entire query. The columns prefixed with E- such as E-Rows and E-Time are expected values while those prefixed with A- such as A-Rows and A-Time are actual values. Tuning by cardinality feedback is based on the premise that whenever the query optimizer chooses a bad plan, it can be traced back to errors in the estimation of the number of rows retrieved at various points in the execution plan; that is, one needs to compare E-Rows with A-Rows.

Let me say it again and again. Ignore the cost. Focus on Buffers instead. But don’t forget that it depends on the values of the bind variables for the particular execution that you are tracing. And don’t ever forget that SQL is a non-procedural language so the execution plan that you see today may not be the execution plan that you see tomorrow. In fact, the shared pool may contain multiple execution plans for the same SQL query.

To be continued.


  1. We needed to set the value of the session parameter “_rowsource_execution_statistics” to True in order for Oracle Database to keep track of the buffers retrieve at each step of the query. There are other ways to do so, including the parameter statistics_level, the SQL hint gather_plan_statistics, and the 10046 trace event.
  2. To use DBMS_XPLAN.DISPLAY_CURSOR, you need select privileges on V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL. This is unfortunate because you will either need to use an administrator account such as SYSTEM or be granted these privileges by a DBA administrator. If using Toad, you can use SGA Trace to view query execution plans but you still need DBA privileges.

About the Author

Iggy Fernandez

My name is Ignatius. Everyone calls me Iggy. My name means “fiery” (the Latin word for fire is ignis and the Sanskrit word for fire is agni). I grew up in the same town as rock star Freddie Mercury; that’s your clue. I now live in Northern California, in the San Francisco Bay Area. I am the editor of the NoCOUG Journal and the author of Beginning Oracle Database 11g Administration (Apress, 2009). I have a lot of opinions but am willing to change them when confronted with fresh facts. As the American philosopher Ralph Waldo Emerson said in 1841 in his essay on self-reliance: “A foolish consistency is the hobgoblin of little minds, adored by little statesmen and philosophers and divines. … speak what you think to-day in words as hard as cannon-balls, and tomorrow speak what tomorrow thinks in hard words again, though it contradict every thing you said to-day.” Feel free to correct me when I say something wrong.

Start the discussion at forums.toadworld.com