“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;
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- -------------------- ------------------------- ---------
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.
Notes:
- 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.
- 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.
Start the discussion at forums.toadworld.com