Oracle Scratchpad

January 25, 2013

Sorting

Filed under: Execution plans,Oracle,Troubleshooting — Jonathan Lewis @ 5:45 pm BST Jan 25,2013

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.

10 Comments »

  1. Jonathan,

    perhaps it’s not too closely related to the topic of the article – but playing a little bit with your example I got a surprising “Buffers” value. My modifications of your example were the use of MOD to limit the number of different IDs and a DETERMINISTIC keyword added to the function definition:

    create table t1
    as
    select
        mod(rownum, 250)          id,
        lpad(rownum,200)    padding
    from    all_objects
    where   rownum <= 2500
    ;
     
    create table t2
    as
    select  * from t1
    ;
     
    exec dbms_stats.gather_table_stats(user, 't1')
    exec dbms_stats.gather_table_stats(user, 't2')
     
    create or replace function f (i_target in number)
    return number deterministic
    as
        m_target    number;
    begin
        select max(id) into m_target from t1 where id <= i_target;
        return m_target;
    end;
    /
    
    select  /*+ gather_plan_statistics */
        id
    from    t1
    minus
    select
        f(id)
    from    t2
    ;
     
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.16 |   54747 |       |       |          |
    |   1 |  MINUS              |      |      1 |        |      0 |00:00:00.16 |   54747 |       |       |          |
    |   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 | 13312 | 13312 |12288  (0)|
    |   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |       |       |          |
    |   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.16 |   54670 | 13312 | 13312 |12288  (0)|
    |   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------
    
    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]
    

    I expected the Buffers for step 4 to be 77 * 250 = 19250: with one FTS for each distinct value in t2. But I got 54670 (= 77 * 710) (and I got the same results with clustered data when using TRUNC instead of MOD to create the test tables). Can you explain the number of buffers?

    Martin

    Comment by Martin Preiss — January 26, 2013 @ 5:41 pm BST Jan 26,2013 | Reply

    • Martin, i recently investigate deterministic functions mechanizm. It is because of hash collision of parameters hash. When function executes with different input parameter and their hashes is equal, will be cashed only first result. I’ll write about it fully later.

      Comment by Sayan Malakshinov — January 26, 2013 @ 5:52 pm BST Jan 26,2013 | Reply

      • Sayan,

        thank you for your explaining comment. In Cost Based Oracle (chapter 9) I saw that in 10g the size of the hash table was controlled by _query_execution_cache_max_size – and obviously that is still true in 11.2.0.1. With values above the default (65536) the number of Buffers drops to the value I expected:

        alter session set "_query_execution_cache_max_size"=262144;
        
        -----------------------------------------------------------------------------------------------------------------
        | Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
        -----------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.09 |   29820 |       |       |          |
        |   1 |  MINUS              |      |      1 |        |      0 |00:00:00.09 |   29820 |       |       |          |
        |   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 | 13312 | 13312 |12288  (0)|
        |   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |       |       |          |
        |   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.08 |   29743 | 13312 | 13312 |12288  (0)|
        |   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |       |       |          |
        -----------------------------------------------------------------------------------------------------------------
        
        alter session set "_query_execution_cache_max_size"=2097152;
        
        -----------------------------------------------------------------------------------------------------------------
        | Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
        -----------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.06 |   19425 |       |       |          |
        |   1 |  MINUS              |      |      1 |        |      0 |00:00:00.06 |   19425 |       |       |          |
        |   2 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.01 |      77 | 13312 | 13312 |12288  (0)|
        |   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |       |       |          |
        |   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.06 |   19348 | 13312 | 13312 |12288  (0)|
        |   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |       |       |          |
        -----------------------------------------------------------------------------------------------------------------
        

        With the difference of 98 blocks (19348 – 19250) I can live …

        Martin

        Comment by Martin Preiss — January 26, 2013 @ 8:16 pm BST Jan 26,2013 | Reply

    • Also hash functions for deterministic functions and scalar subquery caching mechanizm are the same.

      Comment by Sayan Malakshinov — January 26, 2013 @ 5:53 pm BST Jan 26,2013 | Reply

  2. [...] example I gave last week showing how a SORT operation in an execution plan might include the work of resolving function [...]

    Pingback by Losing it « Oracle Scratchpad — January 28, 2013 @ 6:11 pm BST Jan 28,2013 | Reply

  3. Jonathan,

    thank you, sir! Your opinion is very important for me!
    I have a couple more finds about deterministic functions and ssc and I will post them asap.

    Comment by Sayan Malakshinov — February 13, 2013 @ 6:28 pm BST Feb 13,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,015 other followers