Hash table size für Lookup-Ergebnisspeicherung

Jonathan Lewis hat dieser Tage in seinem Blog einen Fall untersucht, in dem ein Funktionsaufruf einen Tabellenzugriff enthält, der im Execution Plan nicht erscheint (was auch bei FILTER-Operationen vorkommen kann). Wenn man die rowsource-Statistiken und die projection-Informationen betrachtet, wird deutlich, dass der Funktionsaufruf in einem SORT UNIQUE step untergebracht ist. Festhalten kann man auf jeden Fall, dass der Plan die Operation nicht besonders deutlich abbildet.

Ausgehend vom gegebenen Beispiel habe ich ein paar Experimente durchgeführt, die ein Ergebnis lieferten, das ich so nicht erwartet hatte:

drop table t1;
drop table t2;

-- nur 250 unterschiedliche IDs, statt 2500 im Original
create table t1 tablespace test_ts
as
select
    mod(rownum, 250)          id,
    lpad(rownum,200)    padding
from    all_objects
where   rownum <= 2500
;

create table t2 tablespace test_ts
as
select  * from t1
;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

-- Funktion als DETERMINISTIC definiert
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
;

-- letzte Ergebnisspalten aus Gründen der Übersichtlichkeit abgeschnitten
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   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 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.16 |   54670 |
|   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]

Die Änderung im Test liegt nur in der Reduzierung der ID-Werte und der Definition der Funktion als DETERMINISTIC. Meine Annahme war, dass der Funktionsaufruf auf diese Weise nur einmal für jeden distinkten Wert ausgeführt werden müsste, woraus sich 250 FTS auf die Tabelle T1 ergeben sollten. Daraus ergab sich die Erwartung, dass die Buffers-Angabe in step 4 bei 19250 (= 250 * 77) liegen würde. Tatsächlich lautete das Ergebnis aber 54670 (= 710 * 77). Woher kommt die Abweichung? Die Antwort lieferten mir die Kommentare von Sayan Malakshinov und Kapitel 9 (Query Transformation) in Cost Based Oracle: die intern zur Speicherung der Zwischenergebnisse des Lookups verwendete HASH TABLE ist nicht groß genug, um hash collision zu vermeiden. Um die Größe dieser Struktur zu verändern, kann man den Parameter _query_execution_cache_max_size anpassen (default: 65536; wie immer gilt, dass die Änderung von underscore-Parametern auf eigene Gefahr erfolgt):

alter session set "_query_execution_cache_max_size"=262144;

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   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 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.08 |   29743 |
|   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 |
--------------------------------------------------------------------------------------
|   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 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |    250 |00:00:00.06 |   19348 |
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      77 |
--------------------------------------------------------------------------------------

Mit den 2M für _query_execution_cache_max_size bin ich dann mit 19348 Buffers schon recht nah am erwarteten Ergebnis von 19250 - und das genügt mir in diesem Fall.