Discussion :

[punkoff]

Bonjour,

La version de ma base est :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


Alors mon schéma où j’exécute ma requête est assez petit (10Mo).

Pour l'instant je vous épargne le statement sql qui ne rimerait à rien sans avoir les données.

Mon problème est le suivant :
J'ai créé une requête (un cube en l’occurrence) qui marche assez bien dans 95% des cas.
Temps de réponse inférieur à 500ms.

La requête s’exécute sur une application via le driver jdbc fournit par oracle.

Mais de temps en temps, avec les même variables bindées ma requête s'emballe et utilise jusqu'à 5Go d'espace temporaire (dans le tablespace TEMP), et du coup les temps de réponse deviennent catastrophique. (sans compter l'espace temporaire utilisée qui est une aberration)

J'aimerai découvrir pourquoi une telle chose survient et surtout par quel moyen puis-je analyser ça.

Tout type de piste me serait utile.

Merci !

Question subsidiaire, y a-t-il un moyen de visualiser des traces sql via l'interface de Enterprise management ? Ou suis-je obligé d'utiliser tkproof ?

[Waldar]

Qu'est-ce que vous appelez un cube au juste, un vrai cube OLAP créé avec Analytic Workspace Manager ou bien un modèle en étoile / flocon basé sur un modèle R-OLAP ?

Effectivement pour 10 Mo de données, 5 Go d'espace temporaire paraît surréaliste.

La piste des traces me paraît la meilleure solution.
Je ne sais pas si OEM permet la lecture de ces dernières, par contre vous n'êtes pas cantonné à tkprof, il y a aussi TVD$XTAT qui produit un fichier html plus "XXIème siècle", même si in fine vous y retrouverez les mêmes informations qu'avec tkprof à peu de chose près.

[punkoff]

Bonjour,

Non ca n'est pas un vrai cube OLAP.

C'est une base "standard" relationnelle où on utilise une fonction OLAP (CUBE BY en l'occurance) pour réaliser des statistiques.


C'est un modèle en ..étoile si je ne m'abuse :
J'ai une table centrale composée de 4 foreign key + une colonne numérique sur laquelle je fais le cube by.

Cette table fait 2 Mo et à 24k d'enregistrement...
J'ai indexé toutes les fk de ma base et le plan que me propose Oracle ne me semble pas "mauvais" (bien que mon interprétation puisse être mauvaise.. concernant Oracle! )

Est-ce que cela répond à votre question ?

Je vais essayer de chopper ces traces et reviendrai avec.

Merci

[Waldar]

Ok, pour les modèles en étoile, les recommandations Oracle sont, dans les grandes lignes :

1. Utiliser des index bitmap sur toutes les FK de la table de faits
2. Vérifier que le paramètre STAR_TRANSFORMATION_ENABLED est TRUE

D'ailleurs en fait ce sont les seules.
La documentation Oracle décrit bien la problématique (tout le chapitre sur le datawarehousing est plutôt bon) :
http://download.oracle.com/docs/cd/B...s.htm#i1006335

Bon, il y a un cas de figure qui pourrait s'appliquer à votre table :

The star transformation may not be chosen by the optimizer for the following cases :
[...]
Tables that are too small for the transformation to be worthwhile

Ah oui, en 11.1.0.7, on a rencontré beaucoup d'ORA-00600 sur un datawarehouse, et un des conseils du support Oracle a été de remettre le paramètre STAR_TRANSFORMATION_ENABLED à false...
Et ça a résolu notre problème (sic). J'espère que ça fonctionne mieux en 11gR2 !

[punkoff]

Merci Waldar, ce liens est intéressant mais il faut que je prenne le temps de le tester



Sinon mon plan d’exécution n'est peut-être pas si bon que ça, car j'ai des MERGE JOIN CARTESIAN qui trainent ... je vais miner dans ce sens là.

[punkoff]

Bon, j'avance un peu mais ce que je découvre me laisse perplexe.

J'ai fait 2 traces (ci-jointe) une avec la requête qui part en live et la 2eme où c'est ok.

Le plan d’exécution est différent.

Ce qui change entre les deux c'est une variable.

Donc pour expliquer un peu plus en profondeur ce qui est réalisé.

J'ai une table de vente (T_SALE_SAL) où sont stockés des chiffres d'affaire selon certain critère.

Ces critères sont en vrac : un trimestre, une activité, un account, un type de vente.

C'est sur cette table là que le cube se fait (entre autres).

En amont je dois sélectionner une liste d'account selon les droits de l'utilisateur afin de diminuer le nombre de lignes sélectionnables pour le cube.

Dans la requête c'est la partie With .... jusqu'a V_TOT qui représente une liste d'account.

Je joins ensuite cette vue V_TOT avec la table T_SALE_SAL afin de diminuer la sélectivité.

Dans le cas joint, seul l'id de l'utilisateur a changé. Mais en sortie de V_TOT j'ai la même liste d'account (pour les 2 users bien qu'ils aient des droits un peu différents).

Et ceci fait changer le plan d’exécution !
Aurais-je oublié de faire quelque chose au niveau des stats .. index .. autres ?


Je n'ai pas encore tester votre solution Waldar, afin d'activer le star join, mais j'aimerais comprendre pourquoi l'optimiseur part en live à cet endroit