24. März 2011

Table Functions, der Optimizer und Statistiken ...

Table Functions, the Optimizer and Statistics ...
Heute möchte ich mich mal dem Thema Table Functions widmen - ich werde aber nicht schreiben, wie man eine Table Function bauen kann, sondern vielmehr, wie man dem Query Opimizer ein wenig Information über die Table Function geben kann - wichtig wäre vor allem, dass der Optimizer weiss, wieviele Zeilen die Table-Function zurückgeben wird, denn das hat Einfluß auf seine Entscheidungen. So richtet sich die Join-Strategie doch erheblich nach der Anzahl Zeilen in den beteiligten Tabellen. Wir beginnen mit einem Beispiel - eine sehr einfache Table Function ...
Today I'd like to write about Table Functions. But this posting will not be about writing these (there is enough information available) - this posting is about how we can tell the Query Optimizer a bit about our table function. An important information would be how many rows the actual table function call will return - this would impact the optimizer decisions for e.g. the join strategy. But let's take the topic step by step. We'll start with a very simple table function example ...
create type tf_t as object(
  col1       number
);
/

create type tf_ct as table of tf_t
/ 

create or replace function tf (p_cnt in number)
return tf_ct pipelined as
begin
  for i in 1..p_cnt loop
    pipe row (tf_t(i*2));
  end loop;
  return;
end;
/
sho err

SQL> select * from table(tf(10));

      COL1
----------
         2
         4
         6
         8
        10
         :
So weit so gut - nun werfen wir mal einen Blick auf den Ausführungsplan dieser einfachen Abfrage ... und danach erstellen wir den Ausführungsplan nochmal mit dem Argument 100000. Also einen Ausführungsplan für 10 zurückgegebene Zeilen und einen für 100000 zurückgegebene Zeilen ...
So far, so good. Now we'll see what the query optimizer thinks about our function. We'll look into the execution plan for a invokation with "10" as well as for "100000" as parameter. So the table function will return 10 rows in the first and 100000 rows in the second case.
SQL> explain plan for 
  2  select * from table(tf(10))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

SQL> explain plan for 
  2  select * from table(tf(100000))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   |  8168 | 16336 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Egal, was Ihr als Parameter angebt: die Anzahl Zeilen, die der Optimizer als Ergebnismenge der Table Function annimmt, ist immer gleich. Woher soll er es auch wissen? Es gibt eben hier keine Tabelle mit Statistiken. Aber: Die Datenbank erlaubt durchaus, dem Optimizer diese Information zu geben. Das ist auch in der Dokumentation zum Extensible Optimizer Interface beschrieben. Das ist übrigens Teil des Data Cartridge Developers Guide, einem meiner Meinung nach hochinteressanten Handbuch. Wir müssen demnach einen neuen Objekttypen implementieren. Und dieser Objekttyp muss zwei Funktionen enthalten: ODCIGetInterfaces und ODCIStatsTableFunction. Also brauchen wir zuerst den Object Type als solchen ...
The plan always looks the same, regardless of the given table function argument. Anyway: how should the optimizer know? There is not table and so there are no statistics.
But, the Oracle database allows to privide information for the query optimizer. This is documented in the Extensible Optimizer Interface, which is part of the Data Cartridge Developers Guide (IMHO one of the most interesting documentation handbooks). So we have to implement another object type for the "communication" with the optimizer. And this object type must contain two functions: ODCIGetInterfaces and ODCIStatsTableFunction. So we start the the type declaration ...
CREATE TYPE tfstats as object (
  dummy number,                          -- object types need attributes - here is one.
  static FUNCTION ODCIGetInterfaces(
    ifclist  OUT ODCIObjectList
  ) RETURN NUMBER,
  STATIC FUNCTION ODCIStatsTableFunction(
   func      IN  SYS.ODCIFuncInfo, 
   outStats  OUT SYS.ODCITabFuncStats, 
   argDesc   IN  SYS.ODCIArgDescList, 
   argument  in number                   -- list all table function arguments here
  ) RETURN NUMBER
);
/
sho err
Das war einfach - nun kommt die konkrete Implementierung. Für unser Beispiel ist die aber nicht besonders schwierig. Der Parameter der Table Function gibt die Anzahl Zeilen ja an; also geben wir ihn einfach wieder zurück. Genau dieses Detail ist in der Praxis mit Sicherheit am schwierigsten, denn aufwändige Berechnungen und Abfragen sind hier fehl am Platz - man muss mit möglichst wenig Aufwand eine Abschätzung finden ...
... and then we continue with the actual implementation. Our example is very simple. The table function arguments determines exactly the amount of returned rows. So we simply return the argument as the optimizer information in ODCIStatsTableFunction. In practice this will be the most difficult part of the task. Complex and expensive calculations or queries are not appropriate here (note that this will be called when the optimizer generated the execution plan). So you will have to find a good approximation with as less efforts as possible ...
create or replace type body tfstats as
  static FUNCTION ODCIGetInterfaces(
    ifclist  OUT ODCIObjectList
  ) RETURN NUMBER is
  begin
    -- Always return SYS.ODCISTATS2 here
    ifclist := ODCIObjectList(ODCIObject('SYS','ODCISTATS2'));
  return ODCIConst.Success;
  end;

  STATIC FUNCTION ODCIStatsTableFunction(
    func      IN SYS.ODCIFuncInfo, 
    outStats OUT SYS.ODCITabFuncStats, 
    argDesc   IN SYS.ODCIArgDescList, 
    argument  in NUMBER 
  ) RETURN NUMBER is
  begin
    outStats := SYS.ODCITabFuncStats(argument);
    return ODCIConst.Success;
  end;
end;
/
sho err 
Nun ist der Objekttyp implementiert - fehlt noch die Verbindung zur konkreten Table Function. Und das geht so.
Now, as the implementation is complete, we need to associate this with the table function. The following call will "tell" the optimizer where to look for statistics information regarding a particular table function.
SQL> ASSOCIATE STATISTICS WITH FUNCTIONS tf USING tfstats;
Dann schauen wir uns die obigen Ausführungspläne nochmals an ...
That's it - let's look into the execution plan ...
SQL> explain plan for 
  2  select * from table(tf(10))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |    10 |    20 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   |    10 |    20 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

SQL> explain plan for 
  2  select * from table(tf(100000))
  3  /

SQL> select * from table(dbms_xplan.display())
  2  /

--------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows   |  Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      | 100000 | 200000 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| TF   | 100000 | 200000 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Folgt nun noch ein Test, was der Optimizer daraus macht. Ich habe für mein Beispiel den Session-Parameter OPTIMIZER_INDEX_COST_ADJ auf "1" gestellt, damit Indexzugriffe "billig" sind und der Optimizer schneller auf Nested Loops umschaltet ... Und den Ausführungsplan berechne ich nun mal für folgende Query:
Now I'd like to see what the optimizer does with that information. For the following example I set OPTIMIZER_INDEX_COST_ADJ to "1" - that makes index access "cheap" and the optimizer will tend to the "nested loops" join stategy faster. And now I'll generate the execution plan for the following query ...
explain plan for 
select s.prod_id
from sh.sales s, table(tf({argument})) t
where t.col1 = s.prod_id
/
Ohne dieses Verfahren sieht der Ausführungsplan stets gleich aus. Der Optimizer geht von den oben schon dargestellten ca. 8000 Zeilen aus und entscheidet sich wie folgt.
Without our approach the optimizer always thinks that the table function will return about 8000 rows. So the execution plan always looks the same:
-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |   104M|   596M|   620  (91)| 00:00:08 |
|*  1 |  HASH JOIN                         |                |   104M|   596M|   620  (91)| 00:00:08 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| TF             |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   PARTITION RANGE ALL              |                |   918K|  3589K|    29   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS     |                |   918K|  3589K|    29   (0)| 00:00:01 |
|   5 |     BITMAP INDEX FAST FULL SCAN    | SALES_PROD_BIX |       |       |            |          |
-----------------------------------------------------------------------------------------------------
Mit dem Verfahren (und dem Parameter OPTIMIZER_INDEX_COST_ADJ = 1 sieht der Plan mit dem Parameter 100000 in der Table Function so aus ...
With the additional information the optimizer has the choice - and it uses it. When the table function returns many rows (here: 100000) the join strategy is HASH JOIN ...
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |  1276M|  7302M|       |  7677  (90)| 00:01:33 |
|*  1 |  HASH JOIN                         |                |  1276M|  7302M|  1368K|  7677  (90)| 00:01:33 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| TF             |   100K|   195K|       |    29   (0)| 00:00:01 |
|   3 |   PARTITION RANGE ALL              |                |   918K|  3589K|       |     1   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS     |                |   918K|  3589K|       |     1   (0)| 00:00:01 |
|   5 |     BITMAP INDEX FULL SCAN         | SALES_PROD_BIX |       |       |       |            |          |
-------------------------------------------------------------------------------------------------------------
... und mit dem Parameter 20 so.
... and for less rows (here: 20) the optimizer decides to do NESTED LOOPS.
-----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |   255K|  1495K|    35   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |                |   255K|  1495K|    35   (0)| 00:00:01 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| TF             |    20 |    40 |    29   (0)| 00:00:01 |
|   3 |   PARTITION RANGE ALL              |                | 12762 | 51048 |    35   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS     |                | 12762 | 51048 |    35   (0)| 00:00:01 |
|*  5 |     BITMAP INDEX SINGLE VALUE      | SALES_PROD_BIX |       |       |            |          |
-------------------------------------------------------------------------------------------------------------
Das finde ich eine ganz interessante Möglichkeit - Wenn Table Functions stark schwankende Ergebnismengen liefern und sich diese einfach abschätzen lassen, dann ist das eine recht interessante Herangehensweise ...
Probably it is not applicably for each and every table function; but if a table function returns different amounts of rows and the amount of rows can be estimated easily ... this might be an interesting approach.

Kommentare:

Andriy Terletskyy hat gesagt…

Mit external Table hat der Optimizer auch das Problem mit fehlender Statistik.
Kann man da auch so was Ähnliches machen?

Carsten Czarski hat gesagt…

Hallo Andriy,

auf diesem Wege geht das nicht; aber es gibt eine Möglichkeit, Statistiken für externe Tabellen zu sammeln.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/stats.htm#i41788

Und dort heißt es weiter unten ...

For external tables, statistics are not collected during GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS, and automatic optimizer statistics collection processing. However, you can collect statistics on an individual external table using GATHER_TABLE_STATS. Sampling on external tables is not supported so the ESTIMATE_PERCENT option should be explicitly set to NULL. Because data manipulation is not allowed against external tables, it is sufficient to analyze external tables when the corresponding file changes.

Beste Grüße

-Carsten

Beliebte Postings