Discussion :

[devkais]

Bonjour,
Suite à une migration Oracle 10g vers 11g, un traitement qui dure normalement 9 heures (10g) , dure maintenant 21 heures (11g) !
Afin de comprendre la situation et la raison de cette régression faut-il faire un rapport AWR sur une durée de 21 heures ? Est-ce fiable ? Faut-il se limiter à une heure pour un rapport AWR ? si oui comment ?
Par ailleurs, J’ai essayé de tracer le traitement de 21 heures, mais les fichiers traces ont fait exploser le file système …
En effet l’utilisation du paramètre MAX_DUMP_FILE_SIZE n’est pas suffisante puisqu’il s’agit d’un traitement (boîte noire) multi-connexions/sessions donc génération de plusieurs fichiers traces…
Avez-vous une idée ?
Merci pour votre aide précieuse.

[pachot]

Bonjour,

Le rapport sur 21 heures peut permettre de comparer les stats (timed event, timemodel) avec le rapport sur 9 heures d'avant migration pour se faire une idée.
Les sections 'segment statistics' peuvent permettre une comparaison rapide aussi.

Mais il ne contiendra pas tous les SQL. Donc il ne permettra pas de voir les quelques requêtes qui sont plus longues. Parce que les SQL qui ne sont plus en shared pool à la fin ne seront pas présents.

Il y a plusieurs approches possibles:
- voir avec les pages performances de OEM les requêtes qui semblent longues.
- Tunig Pack as des outils qui peuvent permettre de comparer (si la 10g est toujours dispo)
- regarder des rapports sur des durées plus courtes. En regardant 'Captured SQL account for ...% of Total DB Time (s)' dans la section 'SQL ordered by Elapsed Time' on vérifie que le rapport couvre quelque chose de significatif.
- faire des requêtes sur dbs_hist_sqlstat pour trouver les requêtes les plus consommatrices

D'une manière générale dans ce genre de cas, je ne perd pas trop de temps à chercher à comprendre ce qui est plus long qu'avant. Je préfère me concentrer à tuner le traitement 11g avec comme objectif d'arriver à 9 heures de traitement. La comparaison, c'est juste pour avoir une idée.

Cordialement,
Franck.

[mnitu]

Si possible, essayez de faire tourner le traitement avec l’optimiseur en compatibilité 10g (optimizer_features_enable) comme un fix rapide pour gagner le temps nécessaire à l’optimisation du traitement.

[ojo77]

Pour un meilleurs diagnostique vous pouvez aussi effecteur un SQLHC pour la requête (dès lors que vous avez le SQL_ID de la requête) voire une SQLT (tous deux disponibles sur MyOracleSupport notes 1465741.1 et 1366133.1) ... Mieux qu'AWR (à mon avis) lorsqu'il s'agit de tuning SQL

[Oratorio]

C'est un réél problème qu'on a rencontré et la solution n'est pas unique mais en règle générale

- update stats avant le migration
- ALTER SYSTEM SET cursor_sharing=force (see [ID 1169017.1])

We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g.

A number of customers have seen an increase in the number of child cursors since migrating to Oracle Database 11g Release 2. This can lead to many problems including complete CPU saturation of a machine requiring a database instance bounce or general database performance issues in the form of waits on mutexes and 'library cache lock'.

aussi cela a amélioré certains traitements de quelques heures à 2 minutes
- optimizer_index_cost_adj=10
- db_file_multiblock_read_count=32
- _optimizer_cost_based_transformation=off

[ojo77]

- optimizer_index_cost_adj=10
- db_file_multiblock_read_count=32
- _optimizer_cost_based_transformation=off

Ho non, pitié !!!! Pourquoi pas optimizer_mode=rule tant qu'on y est ?

Les deux paramètres que vous donnez auront meilleurs compte à être calculé par Oracle à partir des statistiques système (dbms_stats.gather_system_stats) et le paramètre caché que vous dennez enlève à oracle la possibilité de réécrire les requêtes.

[Oratorio]

et le paramètre caché que vous dennez enlève à oracle la possibilité de réécrire les requêtes.

c'est les changements adaptés à notre situation
à vous de voir parmi ces paramètres ce qui vous convient

Le paramètre en question a été modifié car l'optimiseur 11 n'était pas capable d'évaluer l'utilisation de champs (surtout de type date) basés sur des fonctions alors que l'optimiseur 10.2.0.4 était capable de le faire

[mnitu]

c'est les changements adaptés à notre situation

Et c’est bien ça le problème quand vous les présentés en règle générale

...la solution n'est pas unique mais en règle générale

[Oratorio]

les 2 premiers points est une règle générale

- update stats avant le migration
- ALTER SYSTEM SET cursor_sharing=force

[mnitu]

Update de stats avant la jointure ça ne peut pas faire du mal à personne mais le cursor sharing doit avoir sa valeur par défaut EXACT et non pas force!
Après qu'il y a des applications mal écrites c'est un autre plat.

[Oratorio]

Update de stats avant la jointure ça ne peut pas faire du mal à personne mais le cursor sharing doit avoir sa valeur par défaut EXACT et non pas force!
Après qu'il y a des applications mal écrites c'est un autre plat.

regarde le doc d'oracle que j'ai posté
la valeur ne doit pas être EXACT dans 11g
d'ailleurs cette valeur disparaitra en v12

[pachot]

regarde le doc d'oracle que j'ai posté
la valeur ne doit pas être EXACT dans 11g

Non, c'est la valeur par défaut ! Et c'est la valeur conseillée.
C'est SIMILAR qui est dépréciée. Remplacé par Adaptive Cursor Sharing qui marche avec EXACT. FORCE est un workaround pour les appli qui n'utilisent pas de variables.

d'ailleurs cette valeur disparaitra en v12

Non, ce sera toujours la valeur par défaut:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 
SQL> select name,value,isdefault from v$parameter where name='cursor_sharing';
 
NAME                 VALUE                ISDEFAULT
-------------------- -------------------- ---------
cursor_sharing       EXACT                TRUE

Vous avez contournés les problèmes lors de votre migration avec des optimizer_index_cost_adj, des cursor_sharing=force et des désactivations de nouvelles features.

Ok. Mais il ne faut pas conseiller ça !

[Mohamed.Houri]

Adaptive Cursor Sharing qui marche avec EXACT.

Adaptive Cursor Sharing peut(je dis bien peut) bien aussi fonctionner sous cursor_sharing EXACT que cursor_sharing FORCE. Il peut aussi bien ne pas fonctionner pour les deux valeurs.

Pour que l'Adaptive Cursor Sharing fonctionne il faut

1. qu'il soit bien sensitive
2. et bind aware

1) Il ne peut pas être bind aware avant d'être bind sensitive
2) il ne peut pas être bind sensitive si on n'utilise pas les variables de liaison (bind variable)

Donc le cursor sharing peut tout aussi bien etre exact cela ne vas pas pousser l' Adaptive cursor sharing à fonctionner.

[skuatamad]

Code :

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

1
2
3
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit Beta

classe

[Oratorio]

oui c'est SIMILAR qui est Deprecated

The cursor_sharing parameter was introduced as a workaround for legacy applications that could not scale because they had not yet been redesigned to use bind variables. It has been presumed that most applications have been redesigned since then. If you are still using such an application, our recommendation is to set cursor_sharing = FORCE. This setting maximizes cursor sharing while leveraging the Adaptive Cursor Sharing framework to generate multiple execution plans based on different literal value ranges if necessary

c'est le problème qu'on a eu avec des applications financières de gestion de portefeuille comme GP, DECALOG ..

[devkais]

FORCE est un workaround pour les appli qui n'utilisent pas de variables.

bonjour Franck ,
Dans notre appli. chaque requête ou ordre DML est encapsulé dans un
EXECUTE IMMEDIATE.
Exemple simple :

Code :

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

EXECUTE IMMEDIATE 'INSERT INTO...WHERE colonne='||p_valeur ;

Vous ête d'accord avec moi que cette façon d'exécuter les ordres DML n'utilise pas des variables bind(p_valeur ?).
Est-il intéressant dans ce cas de positionner le paramètre cursor_sharing à FORCE pour forcer le partage du curseur et par conséquent diminuer le parsing et donc gagner en performances ?
Comment peut-on bénéficier du CS avec ce mode de fonctionnement (EXECUTE IMMEDIATE).
A+

[Mohamed.Houri]

Lorsque vous utilisez du SQL statique c'est-à-dire du PL/SQL (procédure, fonction) où il n’y a aucune utilisation du SQL dynamique, vous bénéficierez d’une utilisation automatique des variables de liaison (bind variables). Le PL/SQL est ainsi fait. Il existe un système ‘’d’auto binding’’ interne qui vous évite ce problème de non utilisation des variables de liaison.

Par contre, lorsque vous utilisez du SQL dynamique vous devez redoubler de vigilance pour au moins deux raisons principales:

1.Vous devez vous-même veiller à l’utilisation des variables de liaison ; ce qui veut dire que vous perdez cet avantage que vous auriez eu si vous aviez opté pour le PL/SQL statique

2.Vous devez faire attention aux attaques ‘’SQL injection’’ d’autant plus que dans votre cas vous utilisez des concaténations, du SQL dynamique, et vous laissez le soin au cursor sharing de transformer vos variables passées en dur en variables de liaison : tous les ingrédients pour permettre une bonne attaque de votre système.

Revenons maintenant à la question que vous avez posée à Franck, je me permets de vous répondre bien que la question ne me soit pas directement adressée. Lorsque vous utilisez ce genre de SQL dynamique vous pouvez utiliser la clause USING (ce site contient déjà plusieurs discussions sur ce sujet) qui vous permet de transmettre des variables de liaison au lieu et place de paramètres en dur.

Je suppose que vos appels aux instructions ‘EXECUTE IMMEDIATE’ sont inclus dans des procédures stockées. Si c’est le cas alors sachez que même si vous forcez le cursor sharing à sa valeur FORCE, vous ne résoudrez pas votre problème de Parsing. Si les programmes externes qui appellent vos procédures utilisent ce genre d’appel ‘BEGIN procedure() END ;’ alors vous serez toujours dans la même situation avec cursor sharing FORCE ou EXACT. Lisez ce résumé et vous comprendrez peut-être pourquoi

http://hourim.wordpress.com/?s=shared

Enfin, sachez aussi que l'utilisation du cursor sharing FORCE génère d’autres effet collatéraux qu’un œil non averti peut ne pas percevoir. J’en veux pour exemple le cas suivant:
imaginez que dans votre application vous avez un index du type fonction (function based index) qui ressemble à ceci

Code :

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

 create index ind_fbi_usr on t1 (substr(col1,1,5))

Cet index cessera d’être choisi par le CBO si vous positionnez le cursor sharing à FORCE. Je vous laisse le soin de découvrir pourquoi.

Je reviens enfin à votre première question qui a motivé votre intervention : il s’agit d’un traitement qui dure normalement 9 heures (en 10g), dure maintenant 21 heures (11g) !

Il est notoirement connu que lors d’une migration, ce sont en grande partie, des changements de plan d’exécution qui altèrent la bonne performance observée en 10g. Ces changements peuvent être dus à plusieurs raisons (statistiques, changement le l’optimisateur, paramètres de l’optimisateur, etc..). Comme nous n’allons pas tirer dans le noir en espérant toucher la cible, il serait intéressant de faire une trace (10046 level 12) d’une heure pendant que le traitement tourne en 11g et analysez cette trace. Vous y allez certainement découvrir la raison quelle qu’elle soit. Un rapport AWR de 21 heures est presque inutile car des détails importants seront noyés dans les moyennes faites par AWR. En effet, tout ce qui est dans v$active_session_history n’est pas transféré dans les tables AWR. Ce sont plutôt quelques snapshots (1/10) qui vont être transférés dans les tables AWR. C’est pour cette raison que plus la durée du rapport AWR est longue plus des détails importants disparaissent dans les moyennes.

[devkais]

Bonjour Mohamed et merci pour vos conseils.

1.Vous devez vous-même veiller à l’utilisation des variables de liaison ; ce qui veut dire que vous perdez cet avantage que vous auriez eu si vous aviez opté pour le PL/SQL statique

En utilisant la clause USING et laissant le cursor_sharing=EXACT est-ce que la variable bind sera utilisée dans mon cas :

Code sql :

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

execute immediate 'insert into TOTO ...select * from ...where col1= :1 using p_valeur';

au lieu de

Code sql :

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

execute immediate 'insert into TOTO ...select * from ...where col1='||p_valeur;

Je suppose que vos appels aux instructions ‘EXECUTE IMMEDIATE’ sont inclus dans des procédures stockées. Si c’est le cas alors sachez que même si vous forcez le cursor sharing à sa valeur FORCE, vous ne résoudrez pas votre problème de Parsing. Si les programmes externes qui appellent vos procédures utilisent ce genre d’appel ‘BEGIN procedure() END ;’ alors vous serez toujours dans la même situation avec cursor sharing FORCE ou EXACT.

Effectivement cela se passe comme vous l'avez décrit.
Dois-je utiliser l'appel avec CALL <package>.<procedure> depuis l'interface.
Si oui ce genre d'appel nécessite-il un changement du mécanisme d'appel existant depuis l'interface ? existe-il des restrictions ?

Enfin, sachez aussi que l'utilisation du cursor sharing FORCE génère d’autres effet collatéraux qu’un œil non averti peut ne pas percevoir.

Je n'ai pas l'intention de changer la valeur par défaut de ce paramètre(EXACT) qui en plus demeure la valeur par défaut en 12c (voir le commentaire de Franck).
----------------
Une autre question SVP :
Dans cette application j'ai une petite requête simple du type

Code sql :

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

select statut from table_surveillance where...

Cette requête est planifiée pour s'exécuter toutes les 4 secondes pour surveiller l'arrivée de flux et déclencher un traitement.
Elle apparaît toujours dans mon rapport AWR : Dois-je me méfier de ce genre de requête très rapide mais consommatrice au final et étudier par exemple la possibilité de réduire la fréquence de leur exécution afin de soulager les ressources machine ou c'est minime comme gain ?
merci.

[ojo77]

Une autre question SVP :
Dans cette application j'ai une petite requête simple du type

Code sql :

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

select statut from table_surveillance where...

Cette requête est planifiée pour s'exécuter toutes les 4 secondes pour surveiller l'arrivée de flux et déclencher un traitement.
Elle apparaît toujours dans mon rapport AWR : Dois-je me méfier de ce genre de requête très rapide mais consommatrice au final et étudier par exemple la possibilité de réduire la fréquence de leur exécution afin de soulager les ressources machine ou c'est minime comme gain ?
merci.

Si vous avez un besoin fonctionnel de cette requête vous ne pouvez pas vous en passer. Par contre il faudra vous assurer qu'elle ne dérive pas dans le temps (que sa consommation IO/CPU et que son temps d'exécution restent stables) et qu'elle est optimale (que le nombre de consistent gets qu'elle effectue ne peut pas être diminué)

[pachot]

Elle apparaît toujours dans mon rapport AWR : Dois-je me méfier de ce genre de requête très rapide mais consommatrice au final et étudier par exemple la possibilité de réduire la fréquence de leur exécution afin de soulager les ressources machine ou c'est minime comme gain ?
merci.

Oui, tout à fait. Il y a 2 moyens d'améliorer les performances d'une opération:
- la rendre plus rapide
- l'exécuter moins souvent.

Donc l'idée serait de réduire la fréquence si possible, et de la rendre la plus optimale: l'idéal serait qu'elle ne lise qu'un seul block.

Cette requête est planifiée pour s'exécuter toutes les 4 secondes pour surveiller l'arrivée de flux et déclencher un traitement.

Il y a d'autres solutions pour ce type de besoin. Comment arrive ce flux ? Ça fait penser à Advanced Queueing.