Un des éléments fondamentaux utilisés par Oracle pour calculer le coût d’une requête est la cardinalité des différentes opérations des plans d’exécution examinés. Cette cardinalité est l’estimation du nombre de lignes retournées pour les étapes des plans. Pour calculer une cardinalité, Oracle s’appuie sur les statistiques collectées mais également sur des modèles de calcul.

Autrement dit, si les statistiques ou les modèles de calcul sont en contradiction avec le nombre de lignes réellement retournées par une opération, il est possible (probable?) que le plan préféré par Oracle soit sous-optimal. C’est sur ce principe que vous vous appuyez pour mettre en oeuvre la méthode de « cardinality feedback » qui consiste à comparer la cardinalité aux nombres de lignes effectivement retournées pour détecter facilement ce type de situation. Vous pouvez réaliser cette analyse « à l’oeil » avec des outils comme trcanlzr, le hint gather_plan_statistics ou SQL Real Time Monitoring. Oracle 11.2 utilise lui-même cette approche « automatiquement » depuis qu’un algorithme a été embarqués dans le moteur en 11.2 (cf cet article précédent qui l’illustre).

Vous connaissez tout ça et vous savez, par exemple,
que le modèle de calcul de la cardinalité de requêtes sur DUAL peut-être largement faux comme l’illustre le plan ci-dessous pour une requête qui ramène en réalité 1000 lignes :

explain plan for 
   select rownum 
     from dual 
  connect by level <=1000;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1731520519

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT                        |      |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(LEVEL<=1000)

Vous connaissez également tout un tas de méthodes non-supportées comme les hints « cardinality » et « opt_estimate ». Vous connaissez aussi les solutions supportées comme le calcul de statistiques multi-colonnes, le SQL Profile ou le dynamic sampling pour influer sur le calcul de la cardinalité.

Aujourd’hui, nous allons regarder un mécanisme que vous ne connaissez peut-être pas encore pour faire la même chose : le développement de statistiques étendues en PL/SQL.

Mais commençons par un problème assez répandu : Le fait qu’Oracle estime qu’une fonction pipeline renvoie 8168 lignes dans le cas de taille de blocs de 8K et jusqu’en 11.2! Je sens que j’en ai perdu un ou 2, non ? Pour vous en convaincre, testez ! Vous trouverez ci-dessous un exemple de fonction pipeline qui renvoie N lignes ayant pour valeurs 1 à N; N est le paramètre passé à cette fonction. Le code est disponible ci-dessous ; faites le test par vous-même et vous constaterez que la cardinalité de la fonction est 8168:

create or replace package x_pkg is
 type x_result is record (lno number);
 type x_resultset is table of x_result;
 function list(N number) return x_resultset pipelined;
end x_pkg;
/

create or replace package body x_pkg is
  function list(N number) return x_resultset pipelined
  is
    mline x_result;
  begin
    for i in 1..N loop
       mline.lno:=i;
       pipe row(mline);
    end loop;
  end;
end;
/

select * from table(x_pkg.list(3));

  LNO
-----
    1
    2
    3

explain plan for select * from table(x_pkg.list(3));

set tab off
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3635949001

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

Vous rentrerez probablement assez facilement, par vous-même, dans la mise en oeuvre du développement d’un type objet en PL/SQL que vous associerez à un objet pour renvoyer les statistiques. L’ensemble de la documentation est disponible en particulier dans les sections ci-dessous de la documentation :

Oracle® Database Data Cartridge Developer’s Guide11g Release 2 (11.2)
21 Extensible Optimizer Interface
15 Power Demand Cartridge Example

Pour cet exemple, vous créerez un type qui implémente un attribut quelconque, une fonction qui renvoie la version des statistiques (ici SYS.ODCISTAT2) et une fonction qui calcule la cardinalité à partir du paramètre de la fonction pipeline. Son implémentation ne nécessite que quelques lignes :

CREATE OR REPLACE TYPE x_pkg_stats_type AS OBJECT (
   n NUMBER,
   STATIC FUNCTION ODCIGetInterfaces (
                       ifclist OUT SYS.ODCIObjectList
                       ) RETURN NUMBER,
   STATIC FUNCTION ODCIStatsTableFunction (
                      p_function IN  SYS.ODCIFuncInfo,
                      p_stats    OUT SYS.ODCITabFuncStats,
                      p_args     IN  SYS.ODCIArgDescList,
                      n          IN  NUMBER
                      ) RETURN NUMBER
   );
/

CREATE OR REPLACE TYPE BODY x_pkg_stats_type AS
   STATIC FUNCTION ODCIGetInterfaces(
                     ifclist OUT sys.ODCIObjectList) RETURN NUMBER 
   IS
   BEGIN
      ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCISTATS2'));
      RETURN ODCIConst.Success;
   END ODCIGetInterfaces;

   STATIC FUNCTION ODCIStatsTableFunction (
                     p_function IN  SYS.ODCIFuncInfo,
                     p_stats    OUT SYS.ODCITabFuncStats,
                     p_args     IN  SYS.ODCIArgDescList,
                     n          IN  NUMBER
                     ) RETURN NUMBER IS
   BEGIN
      p_stats := SYS.ODCITabFuncStats(n);
      RETURN ODCIConst.success;
   END ODCIStatsTableFunction;

END;
/

Une fois le type objet créé, il suffit de l’associer au package qui contient la fonction pipeline à l’aide de la commande « associate statistics » ci-dessous :

associate statistics with packages x_pkg using x_pkg_stats_type;

col object format a10
col "TYPE" format a12
col objtype format a25
select object_owner||'.'||object_name object, 
       object_type "TYPE",
       statstyp
e_schema||'.'||statstype_name objtype
  from user_associations;

OBJECT     TYPE         OBJTYPE
---------- ------------ -------------------------
DEMO.X_PKG PACKAGE      DEMO.X_PKG_STATS_TYPE

Et voilà… Testez par vous-même ! L’association du type et du package permet de modifier le modèle de calcul des statistiques :

explain plan for select * from table(x_pkg.list(3));

set tab off
select * from table(dbms_xplan.display(format=>'basic +rows'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------
Plan hash value: 3635949001

----------------------------------------------------------
| Id  | Operation                         | Name | Rows  |
----------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |     3 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| LIST |     3 |
----------------------------------------------------------

Vous pourrez avec cette méthode également influer sur le coût d’une opération ou sur la sélectivité d’une clause WHERE. Pensez-y. Et pour finir, supprimez association, type et package :

disassociate statistics from packages X_PKG;

col object format a10
col "TYPE" format a12
col objtype format a25
select object_owner||'.'||object_name object, 
       object_type "TYPE",
       statstype_schema||'.'||statstype_name objtype
  from user_associations;

no rows selected

drop type body x_pkg_stats_type;
drop type x_pkg_stats_type;
drop package body x_pkg;
drop package x_pkg;