Discussion :

[fredodeveloppeur]

Bonjour à tous,

La situation est la suivante, nous avons déplacé les index d'un tablespace sur un autre (à cause d'un problème de stockage).

Depuis, nous rencontrons des lenteurs sur les requetes sql lancées de "sqldeveloppeur" ou d'une connexion "ado.net".

Cela peut-il être lié ?

Merci pour votre aide.
Fred.

[Waldar]

Peut-être, si le tablespace original était sur une grappe RAID 0 de SSD et que le nouveau est sur un HD magnétique tout seul.

Sinon, il faut prendre des mesures. Isoler une requête où l'impact est important, regarder le plan d'exécution réel et tracer la requête.
Si vous pouvez effectuer ces mesures avec l'index sur ces deux tablespaces c'est bien entendu l'idéal.

[fredodeveloppeur]

Bonjour et merci pour cette première réponse.

Nous ne pouvons plus utiliser l'ancien tablespace car corrompu à cause du matériel.

Nous avons donc regardé le plan d’exécution entre la machine de prod ou le problème existe et une machine de test.

TEST :
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7259 | 4373K| 5356 (1)| 00:01:05 |
|* 1 | TABLE ACCESS FULL| XXXXXXXXXXXX | 7259 | 4373K| 5356 (1)| 00:01:05 |
----------------------------------------------------------------------------------

Prod : (lenteurs)
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 140K| 83M| 8067 (1)| 00:01:37 |
|* 1 | TABLE ACCESS FULL| XXXXXXXXXXXX | 140K| 83M| 8067 (1)| 00:01:37 |
----------------------------------------------------------------------------------

Sachant que la machine de prod est supérieure en performances (x2 CPU et x8 mem), les temps d’exécutions de la requête nous semblent cohérents...

Doit-on rechercher une cause sur la transmission des données entre le serveur et le poste client ?

Merci d'avance pour vos réponses.

Fred.

[Ikebukuro]

Plusieurs remarques :
- la base de test n'est pas identique à celle de prod en terme de volumétrie donc c'est difficile de comparer les temps d'exécution des deux bases sur deux machines différentes :
TEST : 7259 rows récupérées
PROD : 140 000 rows récupérées soit 20 fois plus
- en plus, la machine de prod est plus puissante que la machine de test au niveau hardware donc les plans d'exécutions ne peuvent être comparés

- néanmoins : les temps d'exécution sont grosso modo les mêmes, 1 minute 05 en TEST, 1 minute 37 en PROD --> et tu dis que le problème est en PROD mais pour 20 fois plus de rows récupérées, on a presque le même temps d'exécution; je trouve que la machine de prod est rapide (si je compare à celle de TEST que tu dis fonctionner normalement)

[fredodeveloppeur]

Bonjour & merci pour ces nouveaux éléments.

Volumétrie : ce qui est étrange c'est que le résultat (export des données en fichier plat) donne à 99% le même nombre de lignes. (140k)
Je ne sais pas expliquer pourquoi le plan d'exécution n'en fait pas état sur la machine de test, avez-vous une explication ?

Autre information : les traitements subissant les lenteurs en utilisant la base de données sont des traitements SSIS sur une autre machine en conection ado.net ?

Quelle piste suivre maintenant ? Réseau? tampon de données ? applicatif oracle ?

Merci pour votre aide.
Fred

[mnitu]

Activez une trace SQL étendue lors de l'exécution des traitements qui subissent des lenteurs et analysez le fichier ainsi générée. Vous allez comprendre ce qui est devenu lent.

[Waldar]

Par contre aucun de vos deux plans n'utilise d'index, est-il bien valide et pertinent dans la requête ?

[Ikebukuro]

Volumétrie : ce qui est étrange c'est que le résultat (export des données en fichier plat) donne à 99% le même nombre de lignes. (140k)
Je ne sais pas expliquer pourquoi le plan d'exécution n'en fait pas état sur la machine de test, avez-vous une explication ?

Est-ce que tu as obtenu les plans d'exécution en faisant un explain plan, un set autotrace ou bien l'ordre SQL a vraiment été exécuté puis tu as utilisé DBMS_XPLAN.DISPLAY_CURSOR?
Si c'est un explain plan que tu as fait, l'ordre SQL n'a pas été exécuté mais seulement analysé par l'optimiseur qui te sort un plan d'exécution en se basant sur les stats de la base. Je pense que sur la machine de test les stats ne sont pas à jour : utiliser le package DBMS_STATS pour les régénérer et réexécute l'ordre SQLpour voir si le plan d'exécution a le même nombre de rows que sur la machine de prod.

[mnitu]

Vous avez changé quelque chose à votre base de données et vous constatez que certaines traitement ont ralentis. Quoi faire ?

• Etape 1) Collectez les données opérationnelles nécessaires à l'analyse du traitement. Il y a plusieurs façon de faire mais le plus simple (en réalité le plus simple c'est d'utiliser la méthode que vous maitrisez) est de faire la trace SQL étendue.
• Etape 2) Analysez ces données pour comprendre où le temps passe. Il y a des programmes de type profiler qui peuvent vous aider mais en gros cela reviens à chercher dans le fichier brut de trace les évènements avec un temps "elapsed" important.
• Etape 3) Analysez ces évènements et essayez de comprendre à qui cela correspondent: attentes dues aux disques lors de l'exécution des requêtes , attente dues au réseau, attentes dues aux verrous, etc.
• Etape 4) En fonction des conclusions de l'étape 3 chercher les solutions qui vous permettront d'améliorer le temps d'exécution des traitements.

Comme vous voyez cette démarche est "holistique" elle ne se réduit pas à l'analyse d'une requête qui souvent n'est qu'un détail de la résolution des problèmes de performance.

[fredodeveloppeur]

Bonjour à tous,

Après avoir recalculé les stats sur le TEST, nous avons maintenant un résultat inquiétant puisque le TEST met 1:06 pour retourner les 140 klignes et la PROD 01:37...

TEST

Code :

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

1
2
3
4
5
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   140K|    82M|  5437   (1)| 00:01:06 |
|*  1 |  TABLE ACCESS FULL| XXXXXXXXXXXX |   140K|    82M|  5437   (1)| 00:01:06 |
----------------------------------------------------------------------------------

PROD

Code :

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

1
2
3
4
5
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   140K|    83M|  8067   (1)| 00:01:37 |
|*  1 |  TABLE ACCESS FULL| TTFACR200301 |   140K|    83M|  8067   (1)| 00:01:37 |
----------------------------------------------------------------------------------

Concernant les index cette requête n'utilise aucun de ceux existants.

C'est donc bien qu'il y a un problème de perf sur le prod ?

Comment fait -on le "sql trace etendue" avec l'outils sql developpeur ?

Merci pour vos différentes suggestions.
Fred.

[Ikebukuro]

Une requête SQL n'utilise pas systématiquement un index, même si celui-ci existe : c'est l'optimiseur (le CBO) qui le décide.

Je te conseille de tester la sélectivité de ton WHERE :
par exemple si l'ordre SQL est :

Code :

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

1
2
3
SELECT COL1, COL2
FROM TAB1
WHERE NOM = 'DUBISON';

et que l'index sur NOM n'est pas utilisé, ce peut être normal si dans TAB1 on a 80% des rows pour lesquels NOM = 'DUBISON'.

Donc fait

Code :

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

1
2
3
SELECT COUNT(*)
FROM TAB1
WHERE NOM = 'DUBISON';

et tu auras une idée de la sélectivité (0% à 100%) de ta clause WHERE : si tu as plus de 10%, je pense que le CBO n'utilisera pas l'index.

Au fait, on peut avoir l'ordre SQL?


Autre chose : les stats ont été régénérées mais le COST en PROD est de 8067 au lieu de 5437, soit un écart de 50%, ce qui est énorme mais là je n'ai pas d'idée.

[fredodeveloppeur]

Bonjour,

Je me suis peut-être mal exprimé, en effet il n'y a pas d'index qui correspond au critère de sélection ni en TEST ni en PROD

La requête est très simple :

Code :

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

select * from schema.table where champ = 'valeur'

Sélectivité :
PROD : 140K / 376 K : 37%
TEST : 140K / 247 K : 56%

On ne sais plus ou chercher... ni comment aborder le sujet.

Fred

[Waldar]

Vous ne pourrez pas récupérer la trace directement avec des commandes purement SQL, voyez avec un DBA.
La méthode indiquée par mnitu est la marche à suivre.

Regardez par ici :
http://oracle.developpez.com/guide/tuning/tkprof/

[fredodeveloppeur]

Bonjour,

Nouvelles informations :

TEST

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Statistics
----------------------------------------------------------
         36  recursive calls
          0  db block gets
      28245  consistent gets
      19630  physical reads
          0  redo size
   25329173  bytes sent via SQL*Net to client
     102714  bytes received via SQL*Net from client
       9292  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     139351  rows processed

PROD

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Statistics
----------------------------------------------------------
         36  recursive calls
          0  db block gets
      39151  consistent gets
      30483  physical reads
          0  redo size
   25467279  bytes sent via SQL*Net to client
     103406  bytes received via SQL*Net from client
       9355  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     140296  rows processed

Donc la valeur de physical reads est plus importante en prod ou c'est plus lent ? Cause plausible ?

[13thFloor]

Hello,
50% de physical reads en plus pour lire sensiblement le même nombre de lignes => il y a un souci.
Problème de chainage des lignes de la table ?
Je tenterai bien un move de la table + rebuild de ses index :
alter table schema.table move;
select 'alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='INVALID';
Si index partitionnés globaux :
select 'alter index '||index_owner||'.'||index_name||' rebuild index partition('||partition_name||');' from dba_ind_subpartitions where status='INVALID'

C'est quand même curieux que le simple déplacement de datafile d'index perturbe les full table scan (sous réserve que la table elle-même ne soit pas stockée dans un des datafiles d'index).

[mnitu]

Avant de "tenter le coup" vérifiez qu'il s'agit bien des "chained rows"

[Ikebukuro]

Juste une question : ce que tu appelles TEST et PROD, ce sont bien deux bases de données différentes? Je dis ça car dans une même base de données, on pourrait créer deux schémas différents (PROD et TEST) avec les mêmes objets pour simuler deux bases différentes.
Tu peux afficher le DB_BLOCK_SIZE de chaque base? Si la taille est différente, ça pourrait expliquer cette différence de 50% sur les physical reads.

Autre chose : ATTENTION, c'est DANGEREUX!
Peux-tu faire cette opération avec ta base PROD à une heure de très faible activité et avec ta base de TEST :
- alter database flush buffer cache puis tu régénère la sortie contenant les physicals reads --> si ça se trouve, la différence sur le nombre de physicals reads tient aufait que certaines donnée sont déjà dans le buffer cache de la base TEST.

[fredodeveloppeur]

Bonjour,

Nous avons vérifié :

Mnitu:

Code :

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

SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

Aucun résultat ni en test ni en prod

13thFloor :
Aucun index n'est INVALID ni en test ni en Prod

Ikebukuro :
les deux "DB_BLOCK_SIZE" ont la même valeur 8192

Autres :
- Test et prod sont bien deux serveur différents et sur deux machines différentes
- Les datafile sont toujours dans des tablespaces séparés.


Pour ceux qui est des 'grosses' opérations il faut que l'on trouve un créneau HNO...

Merci.

[13thFloor]

Les index invalides dont je parlais c'est suite à un move.
Juste pour info, quelle taille occupe ce segment de table dans les 2 bases ?

Code :

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

1
2
3
4
5
select segment_name,owner,segment_type,PARTITION_NAME,bytes/1024/1024 "Mo",blocks
from dba_segments
where segment_type like 'TABLE%'
and segment_name='TTFACR200301 '
order by 1,4;

[Waldar]

Ça peut également être une différence de PCTFREE, de COMPRESS :

Code :

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

1
2
select owner, table_name, pct_free, compression, compress_for
  from all_tables;

Ou aussi le nombre de multiblock read :

Code :

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

show parameters db_file_multiblock_read_count;