Sorting

Here’s a little quirk of execution plans that came up recently on the Oak Table network. If you call a function in a query, and do some sorting with the results, where does the work of calling the function get reported in the execution plan if you trace the query or look at the in-memory rowsource execution stats. Let’s take a look at a simple example:

create table t1
as
select
	rownum 			id,
	lpad(rownum,200)	padding
from	all_objects
where	rownum <= 2500
;

create table t2
as
select	* from t1
;

-- collect stats

create or replace function f (i_target in number)
return number
as
	m_target	number;
begin
	select max(id) into m_target from t1 where id <= i_target;
	return m_target;
end;
/

So I’ve got two tables with exactly the same data and a function that will do a full tablescan of t1 (which is going to be 75 blocks) and return the original input (assuming the input was between 1 and 2,500). Here’s the query I want to run (and it will return no rows), followed by the base execution plan.

select	/*+ gather_plan_statistics */
	id
from 	t1
minus
select
	f(id)
from	t2
;

select * from table(dbms_xplan.display_cursor(null,null,'basic +rows'));

--------------------------------------------
| Id  | Operation           | Name | Rows  |
--------------------------------------------
|   0 | SELECT STATEMENT    |      |       |
|   1 |  MINUS              |      |       |
|   2 |   SORT UNIQUE       |      |  2500 |
|   3 |    TABLE ACCESS FULL| T1   |  2500 |
|   4 |   SORT UNIQUE       |      |  2500 |
|   5 |    TABLE ACCESS FULL| T2   |  2500 |
--------------------------------------------

Because of the call to f() in the select against t2, I’m going to call the function 2,500 times and incur a load of buffer reads (2,500 * 75) doing so. Where will those buffer gets and the attendant CPU appear in the plan ? This example is by no means an exhaustive analysis of all the possible options when you include functions in your select list, but in this particular case the function isn’t called until we run the SORT UNIQUE operation at line 4:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last projection'));

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.54 |     187K|       |       |          |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.54 |     187K|       |       |          |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |   2500 |00:00:00.01 |      75 | 70656 | 70656 |63488  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      75 |       |       |          |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |   2500 |00:00:00.54 |     187K| 70656 | 70656 |63488  (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      75 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - STRDEF[22]
   2 - (#keys=1) "ID"[NUMBER,22]
   3 - "ID"[NUMBER,22]
   4 - (#keys=1) "F"("ID")[22]
   5 - "ID"[NUMBER,22]

The 187K buffer gets for the function calls (and any other resources) appear at line 4 of the plan, when you might have expected them to appear in line5 as part of the tablescan. This observation can be confirmed by checking the column projection information – the output from line 5 is the “ID”, the output from line 4 includes “F”(“ID”). I don’t often look at the projection information, but it’s nice to know that sometimes it can give you some ideas of what’s going on when the row source execution stats don’t seem to be what you were expecting.

Footnote: The same sort of effect appears with scalar subqueries in the select lists, although in any  recent versions of Oracle the plan for the scalar subquery appears in the main plan (although sometimes in a counter-intuitive position) and will give you a much better idea of where and why the work is being done.