Discussion :

[Wurlitzer]

Bonjour,

J'ai une requête qui utilise du PL/SQL du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT TH.LIBELLE ,
       QUES.LIBELLE ,
       PACKAGEPLSQL.function (BTH.IDTH)
          ColPLSQL,
       TH.IDTH
  FROM  BTH, TH, QUES
 WHERE     TH.IDTHREF = BTH.IDTH
       AND TH.NIQUEST = QUES.NIQUEST
       AND (    
            BTH.DATEVALEUR >= 201307011503
            AND BTH.DATEVALEUR <= 201307051503
                   )

qui s'execute correctement (d'un point vu temps d'execution).

Je voudrais maintenant ajouter un filtre sur le resultat de mon package PL/SQL

J'ai essayé comme ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT TH.LIBELLE ,
       QUES.LIBELLE ,
       PACKAGEPLSQL.function (BTH.IDTH)
          ColPLSQL,
       TH.IDTH
  FROM  BTH, TH, QUES
 WHERE     TH.IDTHREF = BTH.IDTH
       AND TH.NIQUEST = QUES.NIQUEST
       AND (    
            BTH.DATEVALEUR >= 201307011503
            AND BTH.DATEVALEUR <= 201307051503
                   )
AND PACKAGEPLSQL.function (BTH.IDTH) = 0

et

comme ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT * FROM (
SELECT TH.LIBELLE ,
       QUES.LIBELLE ,
       PACKAGEPLSQL.function (BTH.IDTH)
          ColPLSQL,
       TH.IDTH
  FROM  BTH, TH, QUES
 WHERE     TH.IDTHREF = BTH.IDTH
       AND TH.NIQUEST = QUES.NIQUEST
       AND (    
            BTH.DATEVALEUR >= 201307011503
            AND BTH.DATEVALEUR <= 201307051503
                   )
) WHERE ColPLSQL = 1

Et dans les deux cas j'ai des temps de réponse catastrophique.

J'ai essayé dans le deuxième cas d'ajouter des HINT du genre NO_QUERY_TRANSFORMATION et ca n'arrange rien.

Je commence a etre a court d'idée. Vous comprenez ce qui ne vas pas ? Ce que je pourrais tenter. Je trouve absurde de faire ce test en dans l'application plutot que dans la requete et pourtant c'est ce qui semble le plus efficae.

Merci,

FJJ

[ojo77]

A priori il y a un changement de plan entre vos requêtes, le CBO doit se planter dans l'estimation de sélectivité de la fonction.

Il peut être intéressant d'ajouter un index et de voir ce que ça donne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create index BTH (PACKAGEPLSQL.FUNCTION (IDTH)) ;

[pacmann]

Salut,

Tu peux indiquer le plan d'exécution avec et sans la condition supplémentaire ?

Peut être que ce truc là marche, mais le hint est non documenté (et en plus c'est sale !) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
WITH t AS (
SELECT /*+materialize*/TH.LIBELLE ,
       QUES.LIBELLE ,
       PACKAGEPLSQL.FUNCTION (BTH.IDTH)
          ColPLSQL,
       TH.IDTH
  FROM  BTH, TH, QUES
 WHERE     TH.IDTHREF = BTH.IDTH
       AND TH.NIQUEST = QUES.NIQUEST
       AND (    
            BTH.DATEVALEUR >= 201307011503
            AND BTH.DATEVALEUR <= 201307051503
                   )
)
SELECT *
FROM t 
WHERE PACKAGEPLSQL.FUNCTION (IDTH) = 0

[skuatamad]

Pour qu'un index de fonction soit envisageable il faut que la fonction soit déterministe, donc ça n'est peut être pas le cas.
Par contre il est intéressant d'encapsuler les appels PL/SQL dans une sous-requête scalaire afin de profiter du cache de curseur.
Le gain dépendra évidemment du nombre de BTH.IDTH répétés dans la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT TH.LIBELLE ,
       QUES.LIBELLE ,
       (select PACKAGEPLSQL.FUNCTION (BTH.IDTH) from dual) as ColPLSQL,
       TH.IDTH
  FROM BTH, TH, QUES
 WHERE TH.IDTHREF = BTH.IDTH
   AND TH.NIQUEST = QUES.NIQUEST
   AND BTH.DATEVALEUR >= 201307011503
   AND BTH.DATEVALEUR <= 201307051503
   and (select PACKAGEPLSQL.FUNCTION (BTH.IDTH) from dual) = 0

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH t AS (
SELECT TH.LIBELLE ,
       QUES.LIBELLE ,
       (select PACKAGEPLSQL.FUNCTION (BTH.IDTH) from dual) as ColPLSQL,
       TH.IDTH
  FROM BTH, TH, QUES
 WHERE TH.IDTHREF = BTH.IDTH
   AND TH.NIQUEST = QUES.NIQUEST
   AND BTH.DATEVALEUR >= 201307011503
   AND BTH.DATEVALEUR <= 201307051503
)
SELECT *
  FROM t 
 WHERE ColPLSQL = 0

[Wurlitzer]

Merci !!! Cela fait longtemps que j'avais pas eu l'occasion de venir sur ce forum et cela fait plaisir de voir qu'il est toujours aussi réactif et compétent.

Et donc voici ce que je peux répondre à vos demandes éclaircissement.

Le plan d'execution avec ou sans la clause

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND PACKAGEPLSQL.FUNCTION (BTH.IDTH) = 0

est le même. Donc pas d'espoir de ce coté là.

La fonction PACKAGEPLSQL.FUNCTION à deux défauts : elle n'est pas déterministe et elle est "couteuse".

Le pb etait donc que sans clause

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND PACKAGEPLSQL.FUNCTION (BTH.IDTH) = 0

cette fonction n'etait executé que sur le resultat du select (une quarantaine de ligne) alors qu'avec cette clause elle était exécuté sur le nombre de ligne de BTH correspondant au critère de date (plusieurs milliers).

Sans que j'ai tout compris la bonne syntaxe est donc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH t AS (
SELECT TH.LIBELLE ,
       QUES.LIBELLE ,
       (SELECT PACKAGEPLSQL.FUNCTION (BTH.IDTH) FROM dual) AS ColPLSQL,
       TH.IDTH
  FROM BTH, TH, QUES
 WHERE TH.IDTHREF = BTH.IDTH
   AND TH.NIQUEST = QUES.NIQUEST
   AND BTH.DATEVALEUR >= 201307011503
   AND BTH.DATEVALEUR <= 201307051503
)
SELECT *
  FROM t 
 WHERE ColPLSQL = 0

ou j'applique ma condition ColPLSQL = 0 sur le resultat (ma quarantaine de ligne). L'autre syntaxe proposé faisait que l'on exécutait deux la fonction une fois dans la clause WHERE et une fois en tant que colonne.

Si on utilise le WITH sans (SELECT PACKAGEPLSQL.FUNCTION (BTH.IDTH) FROM dual) AS ColPLSQL. Oracle essaye de faire l'intelligent et pousse le la condition dans la vue et encore une fois se retrouve a exécuter des milliers de fois ma fonction.

Merci à tous