Sous-select dans la clause FROM

Version imprimable

12/11/2007, 15h10
DIJON

Sous-select dans la clause FROM

ORACLE 7.3
est-ce que quelqu'un sait s'il y a un paramètre spécifique à renseigner dans le fichier de configuration ORACLE pour pouvoir utiliser plusieurs sous-select dans la clause FROM ?

j'ai une requête avec 3 sous-select qui passe très bien sur un serveur de test (également en 7.3) et qui ne rend jamais la main sur notre serveur de prod (la session disparait et l'appli est sans réponse)
avec 2 sous-select cela ne passe pas non plus, avec 1 seul c'est ok

merci de votre aide
12/11/2007, 15h36
orafrance

c'est un problème de perf pas de syntaxe. Vérifie que la volumétrie est la même et que les indexes sont bien créés à l'identique.
12/11/2007, 18h02
DIJON

Citation:

Envoyé par orafrance

c'est un problème de perf pas de syntaxe. Vérifie que la volumétrie est la même et que les indexes sont bien créés à l'identique.

la volumétrie est la même, le USER de la base réelle a été dupliqué sur la base de test,
la requête retourne 400 lignes en 15 secondes sur la BDD de test,

je pense qu'il s'agit d'un problème avec l'optimizer qui rend son tablier devant une requête trop complexe : 3 sous-select + 5 tables dans la clause FROM
après vérification des paramètres :
optimizer_search_limit = 5 sur la BDD réelle n'est pas défini sur la BDD de test
timed_statistics = true sur BDD réelle et = False sur BDD de test
log_archive_start = true sur BDD réelle et = False sur BDD de test

cela peut-il avoir une incidence ?

je pense qu'une solution serait de zapper l'optimizer, mais j'ai du mal avec la doc en anglais, où puis-je trouver de la doc en français sur les HINT ?
12/11/2007, 18h16
orafrance

timed_statistics peut-être optimizer_search_limit probablement pas puisque 5 est la valeur par défaut... éventuellement essaye 10 ;)

log_archive_start n'a rien à voir avec les perfs :?
12/11/2007, 20h19
dyvim

Citation:

Envoyé par DIJON

je pense qu'une solution serait de zapper l'optimizer, mais j'ai du mal avec la doc en anglais, où puis-je trouver de la doc en français sur les HINT ?

Si tu veux juste te passer de l'optimiseur, tu peux utiliser /*+ RULE */.
Quand à la doc Oracle officielle elle est malheureusement uniquement disponible en anglais à ma connaissance.
(attention les hints ne sont pas recommandés... peut être qu'il te faut juste mettre à jour les stats)
Quoi que je suis en train de me demander si l'optimiseur ne fonctionne pas forcément en mode RULE (c'est à dire utilisation des index en priorité) en version 7.3
12/11/2007, 20h20
mnitu
D'accord timed_statistics ajout un surcharge mais mais j'ai de doute que c'est lui la cause.

Il n'est pas clair si sur le serveur de production la requête s'exécute ou pas, si non quelle est l'erreur ?

Donc je pense que pour le serveur de test l'optimiseur des requêtes travail en mode règle et probablement en mode coût sur la base de production. Il est simple de vérifier par un hint SQL sur la base de production:
Code:

1 2 select /*+ RULE */ col1, col2, ... from ...
12/11/2007, 21h49
orafrance

Je ne pensais pas que le CBO était dispo en 7.3... faudrait en effet vérifier l'optimizer mode :koi:
13/11/2007, 10h10
DIJON
Citation:
Envoyé par mnitu

Il n'est pas clair si sur le serveur de production la requête s'exécute ou pas, si non quelle est l'erreur ?

Donc je pense que pour le serveur de test l'optimiseur des requêtes travail en mode règle et probablement en mode coût sur la base de production. Il est simple de vérifier par un hint SQL sur la base de production:

Code:

1 2 select /*+ RULE */ col1, col2, ... from ...
il n'y a aucun message d'erreur, la session est virée sans autre explication,

sinon les EXPLAIN PLAN sont les mêmes (sans hint) sur les 2 BDD,

le hint /*+ RULE */ n'est pas reconnu sur la BDD d'epxloitation : aucun changement dans l'EXPLAIN PLAN alors qu'il est amélioré sur la BDD de test

je pense qu'il s'agit bien d'un problème avec l'optimizer et les stats
13/11/2007, 10h15
orafrance

si le plan d'exécution est correct alors j'vois pas pourquoi il y aurait un problème de stats :koi:
13/11/2007, 16h57
mnitu
J'ai eu un peu du mal pour trouver une doc Oracle 7 :)
Voila copie/coller du Oracle 7 Tunnig Release 7.3.3
Code:

1 2 3 4 5 SELECT --+ RULE empno, ename, sal, job FROM emp WHERE empno = 7566;
Essayez avec cette syntaxe.

Citation:

...
il n'y a aucun message d'erreur, la session est virée sans autre explication
...

Avez-vous essaié d'exécuter la requête sous sqlplus ? D'habitude il y a un Ora 03113 "end of file communication chanel" quand ça va mal.

Citation:

...
je pense qu'il s'agit bien d'un problème avec l'optimizer et les stats
...

Comme vous voyez nous pensons la même chose :)
13/11/2007, 18h15
DIJON

je n'ai toujours pas trouvé la raison,
voici ce que j'ai fait :
je regénéré les stats : sans résultat
j'ai viré les stats : sans résultat
j'ai executé la requête après l'instruction ALTER SESSION SET OPTIMIZER_GOAL = RULE : sans résultat
j'ai modifié ma requête : utilisation de vues à la place des sous-requêtes, suppression de jointures externes : sans résultat
je laisse tomber pour ce soir ...

PS : avec SQLPLUS même problème, aucun message et la syntaxe --+ RULE n'est pas supportée
13/11/2007, 20h18
mnitu
Faitez une trace sql
Vérifiez que le paramètre timed_statistics est true. Mettez-la à true sur les deux bases.
Vérifiez le paramètre user_dump_dest

Commencez avec le serveur de test.
Code:

1 2 3 4 5 6 sqlplus utilisateur/mot_de_passe SQL> alter session set sql_trace = true; -- l'utilisateur doit avoir les droits pour faire alter session SQL> select ... from ... SQL> exit
Cherchez dans le répertoire user_dump_dest le fichier le plus récent.
Code:

1 2 tkprof <fichier_in.trc> <fichier_out.txt> sort=execpu, fchcpu
le fichier fichier_out contient les résultats de la trace sql
Même chose sur le serveur de prod. Une fois la requête lancé le fichier de trace est crée sur le serveur.
Code:

1 2 tail -f nom_fic_in.trc doit montrer sa progression.
Quel est le résultat de ces manips ?

PS. Cherchez sur ce site les détails sur les trace, comment analyser les perfs. etc.
13/11/2007, 21h11
orafrance

la volumétrie est-elle la même ? Il me semblait bien que les stats n'étaient pas en cause ;)
14/11/2007, 17h28
DIJON

je viens vous donner quelques nouvelles, je n'avance pas très vite car il n'est pas possible d'arrêter la base de production en journée, donc il n'est pas possible de tester certaines choses,
j'ai tout de même avancé grâce à vos conseils (MERCI !!!),
je n'ai pas pu comparer les traces entre TEST et PROD (impossible de récupérer les traces sur TEST)
et l'utilitaire tkprof plante lamentablement ...
mais bon,
j'ai bidouillé ma requête afin de la simplifier et j'ai réussi à obtenir une réponse sur PROD avec seulement certains critères de sélection (ok sur l'année 2006 et pas pour l'année 2007 pour être précise),
en comparant les traces de la requête avec les critères de sélection sur 2006, puis sur 2007, une instruction SORT AGGREGATE apparaît dans la TRACE de la requête qui retourne une réponse (2006) et pas dans l'autre
... peut être un problème pour trier les données (il y a des group by) ...

voilà ce que j'obtiens en vérifiant le paramètrage des options :
SORT_AERA_SIZE : 1638400 sur TEST et 65536 sur PROD
SORT_AREA_RETAINED_SIZE : 16384 sur TEST et 65536 sur PROD

peut être une piste, nous allons essayer de modifier ce paramètrage sur PROD cette nuit ...

sinon parmis les paramètres qui sont très différents, il y a :
gc_db_locks : 25600 sur TEST et 100 sur PROD
open_cursors : 100 sur TEST et 50 sur PROD
15/11/2007, 13h20
DIJON

la modification des paramètres n'a eu aucune incidence,
en désespoir de cause, j'ai modifié ma requête afin de supprimer les 3 jointures externes (en abusant des UNION) et la requête fonctionne ...
c'est une solution d'urgence mais j'essaie quand même de trouver l'origine du problème
15/11/2007, 13h29
mnitu

Donc en fait vous avez de plans d'exécution qui ne sont pas identiques entre les deux bases (mais ce n’est pas pour faire cette remarque intelligente que j'interviens :mouarf:)
Il me semble que vous utilisé le CBO et je me rappelle qu’en Oracle7 mais aussi en 8, collecter des statistiques sur le dictionnaire d’Oracle (user sys) avait des résultats désagréables.
Peut être c’est une piste.
16/11/2007, 18h24
DIJON

si, si, les plans d'exécution sont identiques sur les 2 bases (TEST et PROD, toutes les deux en 7.3),
et j'ai viré les statistiques,
d'autres part nous avons fais le test sur une base 10G et la requête retourne le résultat très rapidement sur les mêmes données (import de l'USER),
je n'ai pas eu le temps de travailler sur le sujet aujourd'hui mais il semble que ce soit les jointures externes qui soient en cause et cette base/ce serveur en particulier
à suivre ...

Fuseau horaire GMT +2. Il est actuellement 22h53.