Influence de la taille du package sur les performances ?

Version imprimable

09/04/2010, 11h44
zorglob

Influence de la taille du package sur les performances ?

Bonjour,

Je rencontre depuis quelques jours des problèmes de performances aléatoire sur un projet.

Pour présenter de quoi il en retourne, je vous explique brievement ce qui se passe :

J'ai une base Oracle9i qu'on va appeler B1 et une base distante Oracle10g qu'on va appeler B2. J'ai créé un DBLink entre les 2 bases. La connection entre les 2 "reseaux" se fait via un VPN.

J'ai un package P1 dans la base B1 qui appel un Package P2 dans la base B2 via mon DBLink. (execute immediate 'P2.Mafonction@MONDB_LINK')

Dans 90% du temps, tout se passe tres bien et je constate des temps de réponse acceptable.

Dans 10% des cas, j'ai, au moment d'appeler la premiere fonction distante de P2 un retour de la fonction hyper tardif (de l'ordre de 40 secondes, voir parfois beaucoup beaucoup plus)

J'ai fais des tests dans tout les sens pour essayer de comprendre pourquoi parfois c'est lent (probleme de VPN, problème de locks, activité important de la base B1 ou B2 ...) mais rien n'explique mes problemes de lenteur qui arrivent de facon aleatoire.

J'ai constaté que le Package P2 est "gros" et j'ai decidé de le coupé en 2 pour voir si ca ne viendrait pas de la et bizarrement ca a l'air d'être mieux.

Donc j'aimerais savoir si il y a des limites de taille d'un package sur oracle ? Si oui lesquels ? Et si du coup mon probleme vient apriori de la et pas d'autre chose.

Si vous avez des idées elles sont les bienvenues.

D'avance merci.
09/04/2010, 11h54
sgora

Le test le plus pertinent aurait été de copier le package en local et de voir si la différence (hors gain réseau) était significative.
Le problème vient plus sûrement d'une ou plusieurs de tes requêtes de ton package qui peuvent être différemment interprétées par les optimiseurs oracle.
Les requêtes faisant intervenir des sites distants par db_link peuvent se révéler également instables, en fonction notamment du site qui va parser et exécuter la ou les requêtes considérées (le DRIVING SITE selon la terminologie Oracle).

Bref, c'est un vaste problème...
09/04/2010, 14h44
mnitu

Vous ne savez pas ce qui se passe. Vous avez faite une hypothèse et maintenant vous cherchez quelque un pour vous réconforter.
Alternativement, vous pouvez tracer la session SQL en mode étendue et analyser la trace d’une de vos exécutions de la procédure qui s’avère à être lente, pour comprendre où le temps passe.
09/04/2010, 15h34
zorglob
Je ne suis sans doute pas assez rentré dans le détail, ce qui explique sans doute vos réponses. Pour etre plus précis je vais vous detaillé un plus ce qui se passe :

Debut de B1.P1.FonctionPrincipale

- Je fais un test de pour savoir si la connection entre les bases est opérationnel (VPN monté ou autre) via un SELECT sur une table de B2

-Si le test est ok alors :
1. J'appel B2.P2.Fonction1 et je récupère le résultat
2. En fonction du résultat de Fonction1 j'appel ensuite B2.P2.Fonction2 qui renvoie également un résultat
3. En fonction du résultat de Fonction2 j'appel une fonction B2.P2.Fonction3
  ect ...
Fin de B1.P1.FonctionPrincipale

J'ai bien sure collé des logs partout et bien entendu avant et apres chaque appel de fonction pour voir ou se situe les lenteurs.
A chaque fois qu'on a constaté des lenteurs, c'etait à l'appel de la premiere fonction B2.P2.Fonction1 ( le log apres l'appel de la fonction datait de 30s, 40s voir plusieurs minutes apres le Log précedent)

J'aavais d'abord pensé à un probleme réseau, mais puisque je fais un test avant l'appel de la fonction et que ce test repond rapidement, j'ai ecarté ce probleme.

J'ai ensuite imaginé que c'etait ma premiere fonction qui posait probleme car les fonctions suivantes s'echainaient TOUJOURS rapidement. J'ai donc modifié ma fonction pour qu'elle ne fasse RIEN si ce n'est un simple RETURN d'une chaine vide.
Les lenteurs etaient toujours la, et toujours sur ce "premier appel" de fonction (les suivantes s'executant toujours normalement)

J'ai ensuite isolé la fonction 1 en la mettant dans un package seul B2.P3 et j'ai modifier mon package B1.P1 afin d'appeler cette fonction seule, puis d'appeler les autres fonctions comme avant à savoir B2.P2.Fonction2, B2.P2.Fonction3 ...
Les lenteurs etaient maintenant au niveau de l'appel de la Function B2.P2.Function2

J'en ai conclu qu'il s'agissait d'un probleme lié a mon package car je n'avais pas franchement d'autres explications (vos suggestions sont les bienvenues).

Puisque certaines fonctions de mon package P2 ne sont jamais appelées par B1.P1, j'ai décidé de découper mon package avec d'un coté les fonctions appelée par B1.P1 et de l'autre les fonctions qui était directement déclenchée par le package B2.P2
et depuis ca à l'air de répondre correctement à chaque fois.

J'en ai donc conclu qu'il pouvait peut etre s'agir d'un probleme lié a la taille de mon package car il est assez gros et parce que n'etant pas un expert oracle (je debute) je n'avais pas d'autres idées.
09/04/2010, 15h42
sgora

Tout dépend de ce que tu fais dans ces fonctions, tu fais référence à des objets de B2, B1, les deux ?

Comme te le conseille mnitu, le mieux est de faire une trace de ta session quand tu lances ce package.

Au fait, gros pour toi, c'est combien de lignes de code ?
09/04/2010, 16h06
zorglob

Chaque Base B1 ou B2 ne font jamais référence à des table de l'une ou l'autre.
J'entend par la que ce sont des bases dont les tables n'ont rien en "commun", mes packages lorsqu'ils travaillent sur les données, le font uniquement sur des tables locales.

Si vous voulez, B1 déclenche l'appel d'une fonction sur B2 qui est censé travailler localement, et me renvoyer quelque chose. Donc mis à part les paramètres des fonctions et le résultat des fonctions, rien n'est censé transiter entre les bases.

Concernant la taille de mon package, le fichier texte fait 275ko pour 6000 lignes de code.

Le problème c'est que je veux bien tracer tout ce que vous voulez mais je ne vois pas quoi tracer puisque le problème intervient de façon aléatoire et que je pourrais tout aussi bien n'appeler que ma première fonction qui je le rappel ne fait rien et ça serait toujours lent "parfois".

:cry:
09/04/2010, 16h17
zorglob

Ne maitrisant pas la facon dont oracle travail, je n'ai pas trop d'idée d'ou le probleme peut venir, j'avais schematisé mon probleme comme ceci :

B1 appel B2 :

00:00:00 <B1> : Allo B2 t'es la ?
00:00:01 <B2> : Yep
00:00:01 <B1> : Ah cool, bon vas y appel moi voir P2.Multiplier (1,1) je sais que tu sais faire toi
00:00:01 <B2> : Quoi ? Oulala !! Ou je l'ai foutu lui deja
00:00:10 <B2> : Ah le Voila son foutu P2, alors quelle fonction deja ?
00:00:15 <B2> : Mult...
00:00:20 <B2> : Multip...
00:00:35 <B2> : Multiplier, voila je l'ai. Ben t'es con c'est facile : 1*1 = 1. Le resultat c'est 1. Tsssssssss !!!
00:00:35 <B1> :Ahhh ben voila c'est 1 merci B2.

:aie:

Tenter de fixer le package sur la base 2 en mémoire

Bonjour,

Si on considère que la taille du package influe et que c'est son premier appel qui est long vous pouvez tenter de le "punaiser" en mémoire.

Je veux dire : Le grimper en mémoire au démarrage de l'instance et demander à Oracle de ne pas le décharger.
Cette approche est utilisée quand des programmes sont déchargés pour les raisons LRU et doivent être re-chargés avant exécution.

Il faut pour cela utiliser le package dbms_shared_pool qu'on invoque au démarrage de l'instance ou moyen d'un trigger on startup du genre :
Code:

1 2 3 4 5 6 7 Create Or Replace Trigger bdd_on_startup After Startup On Database -- 23/07/2003 - Mbo - Demande de chargement au démarrage de l'instance des objets listés Begin DBMS_SHARED_POOL.Keep ('mon_schéma.mon_package'); End;
Evidemment il y a un peu de config :
- Vérifier la présence du package DBMS_SHARED_POOL sur l’instance
- Ce package est crée par le script ORACLE_HOME/rdbms/admin/dbmspool.sql
- Créer un synonyme public sur le package

Si on veut créer le trigger depuis un compte il faut lui attribuer connecté SYS des privilèges :
SELECT on v_$db_object_cache
EXECUTE on dbms_shared_pool (peut-être avec l'option grant. Essayer d'abord sans)
ADMINISTER DATABASE TRIGGER

Ensuite il faut vérifier le bon fonctionnement du trigger :
Lire la table sys.v_$db_object_cache après redémarrage de l’instance de base de données. Vérifier la valeur de PINS pour les objets "punaisés"
Vérifier le fichier d’alerte de l’instance après redémarrage à la recherche d’éventuelles erreurs provoquées par le déclencheur BDD_ON_STARTUP

Je ne sais pas ce que ça vaut en 10g mais en 9i ça marche bien (et le fichier dbmspool.sql est présent en 10g)

Pozzo

12/04/2010, 09h42
mnitu
Citation:

Envoyé par zorglob

Chaque Base B1 ou B2 ne font jamais référence à des table de l'une ou l'autre.
J'entend par la que ce sont des bases dont les tables n'ont rien en "commun", mes packages lorsqu'ils travaillent sur les données, le font uniquement sur des tables locales.

Si vous voulez, B1 déclenche l'appel d'une fonction sur B2 qui est censé travailler localement, et me renvoyer quelque chose. Donc mis à part les paramètres des fonctions et le résultat des fonctions, rien n'est censé transiter entre les bases.

Concernant la taille de mon package, le fichier texte fait 275ko pour 6000 lignes de code.

Le problème c'est que je veux bien tracer tout ce que vous voulez mais je ne vois pas quoi tracer puisque le problème intervient de façon aléatoire et que je pourrais tout aussi bien n'appeler que ma première fonction qui je le rappel ne fait rien et ça serait toujours lent "parfois".

:cry:

Je comprends bien que c’est lent parfois mais, sans comprendre pourquoi, toute solution me ressemble plus à une loterie qu’à l’ingénierie. Donc, ajoutez une trace SQL étendue dans le package de départ, ajoutez une trace étendue dans la partie initialisation du package distant. Faite des tests et regardez que est-ce que ça donne.

Si le problème est dû seulement au chargement du package en mémoire vous pouvez en simuler l’effet en purgeant le shared_pool sur la BD distante, avant d’exécuter la fonction du package.
Code:

1 2 3 alter system flush shared_pool /
Mais, y compris dans ce cas la question est pourquoi le package est déchargé assez vite de la mémoire ?

Voilà comment procéder:

Créez dans la base distante deux procédures pour activer/désactiver la trace SQL étendue

Code:

1
2
3
4
5
6
7
8
9
10
11
 
create or replace procedure active_trace Is
Begin
  execute immediate 'alter session set events ''10046 trace name context forever, level 8''';
End;
/
create or replace procedure desactive_trace Is
Begin
  execute immediate 'alter session set events ''10046 trace name context off''';
End;
/

Activez la trace de deux cotés

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
alter session set events '10046 trace name context forever, level 8'
/
exec active_trace@B2
 
variable b varchar2(10)
exec :b := pk_test.f1@B2
exec :b := pk_test.f2@B2
 
exec desactive_trace@B2
 
alter session set events '10046 trace name context off'
/

Et maintenant vous avez deux fichiers trace (.trc) qui vous diront (presque) toute l'histoire.

16/04/2010, 09h50
zorglob

Merci de ton aide précieuse, je vais tester tout ça et ne manquerais pas de revenir ici pour faire part des résultats.