IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Performance d'un select [12c]


Sujet :

SQL Oracle

  1. #1
    Membre du Club
    Profil pro
    Inscrit en
    Janvier 2008
    Messages
    60
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2008
    Messages : 60
    Points : 40
    Points
    40
    Par défaut Performance d'un select
    Bonjour à tous,

    Depuis une migration oracle 11g vers 12c (12.2.0.1.0) nous avons des requêtes dont le temps d’exécution est beaucoup plus long.
    Même sur de simples select * from table.
    Soit par exemple la table adresse, qui contient 910 000 lignes et 14 colonnes.
    En activant les traces (ALTER SESSION SET EVENTS '10046 trace name context forever, level 8' ), nous obtenons les résultats suivants (select * from adresse where rownum <300000 -pour limiter le temps d'attente. Requête exécutée en sqlplus depuis le serveur bdd):

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 20001 1.09 1.11 3151 22909 0 299999
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 20003 1.09 1.12 3151 22909 0 299999

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 9
    Number of plan statistics captured: 1

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ---------- ---------- ---------- ---------------------------------------------------
    299999 299999 299999 COUNT STOPKEY (cr=22909 pr=3151 pw=0 time=1541590 us starts=1)
    299999 299999 299999 TABLE ACCESS FULL ADRESSE (cr=22909 pr=3151 pw=0 time=666064 us starts=1 cost=640 size=24899917 card=299999)


    Elapsed times include waiting on following events:
    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 20001 0.00 0.04
    Disk file operations I/O 1 0.00 0.00
    db file sequential read 1 0.00 0.00
    db file scattered read 25 0.00 0.01
    SQL*Net message from client 20001 16.46 215.94
    ********************************************************************************

    Ce qui interpelle ici c'est "SQL*Net message from client" positionné a 215.94. Ce qui signifierait que la base attend pendant plus de 215 s. Le elapsed time est positionné quant a lui a 1.12s ce qui signifie que la base n'aurait été sollicitée durant tout le temps d’exécution de ce select que pendant 1.12 s. A quoi pourrait etre du ce temps d'attente selon vous, sachant que le requête a été lancée sur le serveur de base de donnée (donc à priori pas de latence réseau)

    Merci par avance.

  2. #2
    Membre expérimenté

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Points : 1 359
    Points
    1 359
    Par défaut
    Bonjour,

    La requête renvoie 299,999 lignes en utilisant 20001 fetches. Ce qui veut dire que vous utilisez la valeur par défaut 15 (299,999/20001) du arraysize.

    Voici un article que j’ai écrit afin de faciliter l’interprétation d’un fichier TKPROF

    https://hourim.wordpress.com/2012/12...ichier-tkprof/

    Ceci étant dit, afin de pouvoir éventuellement vous aider il serait préférable que vous postiez ici une vraie requête représentative avec son plan d’exécution accompagné de sa partie prédicat.

    En effet, je ne pense pas qu’un changement de version d’Oracle puisse changer le nombre de lignes sélectionnées et donc augmenter l’effet du SQL*Net message from client

    Bien Cordialement
    Mohamed Houri
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  3. #3
    Membre du Club
    Profil pro
    Inscrit en
    Janvier 2008
    Messages
    60
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2008
    Messages : 60
    Points : 40
    Points
    40
    Par défaut
    Bonjour,

    Merci beaucoup pour votre réponse très instructive.

    En relançant la requête avec un array size à 1000 voici le résultat

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.01          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch      301      0.66       0.77       3151       3398          0      299999
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      303      0.67       0.78       3151       3398          0      299999
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 9
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
        299999     299999     299999  COUNT STOPKEY (cr=3398 pr=3151 pw=0 time=337027 us starts=1)
        299999     299999     299999   TABLE ACCESS FULL ADRESSE (cr=3398 pr=3151 pw=0 time=213465 us starts=1 cost=998 size=24899917 card=299999)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                     301        0.00          0.00
      Disk file operations I/O                        1        0.00          0.00
      db file sequential read                         1        0.02          0.02
      db file scattered read                         25        0.02          0.07
      SQL*Net message from client                   301      194.72        846.46
      SQL*Net more data to client                  2272        0.00          0.04
    ********************************************************************************
    On voit bien dans les fetchs que le nombre de ligne traites est de 1000. Et on passe de 20001 a 301.
    FETCH #140115326205912:c=1290,e=1678,p=0,cr=11,cu=0,mis=0,r=1000,dep=0,og=1,plh=35431491,tim=6773658885
    Malgré cela, le temps d’exécution augmente et un nouveau message apparait : SQL*Net more data to client.

    Également à titre de comparaison, un create table as select * from adresse ne prend que quelques secondes.

  4. #4
    Membre du Club
    Profil pro
    Inscrit en
    Janvier 2008
    Messages
    60
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2008
    Messages : 60
    Points : 40
    Points
    40
    Par défaut
    Un autre exemple de requête:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Select  *
    FROM            individu i
    INNER JOIN      adresse adr_anu ON (adr_anu.cod_ind_ina = i.cod_ind )
    INNER JOIN      annee 	aua 	ON (aua.cod_anu = adr_anu.cod_anu_ina )
    INNER JOIN      adresse adr_fix ON (adr_fix.cod_ind = i.cod_ind)
    INNER JOIN      pays pay_anu    ON (pay_anu.cod_pay = adr_anu.cod_pay)
    INNER JOIN      pays pay_fix    ON (pay_fix.cod_pay = adr_fix.cod_pay)
    LEFT OUTER JOIN commune com_anu ON (com_anu.cod_com = adr_anu.cod_com)
    LEFT OUTER JOIN commune com_fix ON (com_fix.cod_com = adr_fix.cod_com)
    where rownum < 1000
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.15       0.15          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       68      0.13       1.29        717       5445          0         999
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       70      0.28       1.45        717       5445          0         999
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 9
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
           999        999        999  COUNT STOPKEY (cr=5445 pr=717 pw=0 time=173270 us starts=1)
           999        999        999   HASH JOIN  (cr=5445 pr=717 pw=0 time=172695 us starts=1 cost=2840 size=3525344 card=1312)
           214        214        214    TABLE ACCESS FULL PAYS (cr=3 pr=0 pw=0 time=80 us starts=1 cost=3 size=11342 card=214)
           999        999        999    HASH JOIN  (cr=5442 pr=717 pw=0 time=170905 us starts=1 cost=2837 size=2074923 card=999)
           214        214        214     TABLE ACCESS FULL PAYS (cr=3 pr=0 pw=0 time=88 us starts=1 cost=3 size=11342 card=214)
           999        999        999     HASH JOIN RIGHT OUTER (cr=5439 pr=717 pw=0 time=169070 us starts=1 cost=2834 size=1518480 card=999)
         39136      39136      39136      TABLE ACCESS FULL COMMUNE (cr=184 pr=0 pw=0 time=1411 us starts=1 cost=71 size=978400 card=39136)
           999        999        999      HASH JOIN RIGHT OUTER (cr=5255 pr=717 pw=0 time=155350 us starts=1 cost=2644 size=1016000 card=1000)
         39136      39136      39136       TABLE ACCESS FULL COMMUNE (cr=184 pr=0 pw=0 time=1859 us starts=1 cost=71 size=978400 card=39136)
           999        999        999       NESTED LOOPS  (cr=5071 pr=717 pw=0 time=141236 us starts=1 cost=2478 size=537000 card=1000)
           999        999        999        NESTED LOOPS  (cr=4072 pr=717 pw=0 time=4265316 us starts=1 cost=2478 size=537000 card=1227)
           999        999        999         NESTED LOOPS  (cr=3036 pr=717 pw=0 time=4255188 us starts=1 cost=2469 size=557058 card=1227)
           999        999        999          HASH JOIN  (cr=94 pr=0 pw=0 time=20003 us starts=1 cost=14 size=303316 card=1228)
            32         32         32           TABLE ACCESS FULL ANNEE (cr=6 pr=0 pw=0 time=58 us starts=1 cost=4 size=2592 card=32)
          1006       1006       1006           TABLE ACCESS FULL ADRESSE (cr=88 pr=0 pw=0 time=3767 us starts=1 cost=10 size=36002744 card=433768)
           999        999        999          TABLE ACCESS BY INDEX ROWID INDIVIDU (cr=2942 pr=717 pw=0 time=1230030 us starts=999 cost=2 size=207 card=1)
           999        999        999           INDEX UNIQUE SCAN IND_PK (cr=1943 pr=111 pw=0 time=435964 us starts=999 cost=1 size=0 card=1)(object id 93445)
           999        999        999         INDEX UNIQUE SCAN ADR_UK_01 (cr=1036 pr=0 pw=0 time=5545 us starts=999 cost=0 size=0 card=1)(object id 94449)
           999        999        999        TABLE ACCESS BY INDEX ROWID ADRESSE (cr=999 pr=0 pw=0 time=3125 us starts=999 cost=1 size=83 card=1)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      PGA memory operation                           89        0.00          0.00
      SQL*Net message to client                      68        0.00          0.00
      SQL*Net more data to client                     1        0.00          0.00
      Disk file operations I/O                        1        0.00          0.00
      db file sequential read                       143        0.06          0.65
      db file scattered read                        116        0.08          0.52
      SQL*Net message from client                    68      209.76        358.85
    ********************************************************************************
    Merci pour votre aide.

  5. #5
    Membre expérimenté

    Homme Profil pro
    Inscrit en
    Mars 2010
    Messages
    536
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : Mars 2010
    Messages : 536
    Points : 1 359
    Points
    1 359
    Par défaut
    Bonjour,

    Ici on voit bien que c’est le Fetch des données qui prend le plus de temps. Et c’est normal qu’un create table as select soit instantané dans ce cas car cette opération ne fait pas de Fetch.

    Si vous êtes obligé de renvoyer toutes ces 300K de lignes il va falloir en payer le prix.

    Cependant, je ne vois pas encore qu’est-ce qui peut expliquer la différence de comportement de la même requête entre la 11g et la 12cR2. Récemment chez un autre client nous avons eu ce genre de problème de lenteur dû au SQL*Net message from client et nous avons fini par comprendre que cela venait du traitement ligne par ligne fait par la couche PeopleSoft avec les données renvoyées par la requête (ajout d’une option de trace non présente avant la migration).

    Donc si vos traitements ouvrent un curseur sur une requête de 300K lignes puis font des traitements ligne par ligne cela peut expliquer une différence de temps d’exécution entre la 11g et la 12cR2.

    Il faut nous expliquer exactement le traitement que vous faites.

    Bien Cordialement
    Mohamed
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  6. #6
    Membre du Club
    Profil pro
    Inscrit en
    Janvier 2008
    Messages
    60
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2008
    Messages : 60
    Points : 40
    Points
    40
    Par défaut
    Bonjour,

    Les tables sont lues par talend, via jdbc.
    En augmentant la taille du paramètre defaultRowPrefetch (équivalent du rowsize pour sqlplus) le traitement devient bcp plus rapide.
    Ce qui est étonnant est que ce paramètre n’était pas positionné avant. Mais le driver utilisé avant migration était celui d'oracle10g. Peut être que la valeur par défaut y était différente.

    Dans tous le cas le problème est résolu désormais grâce a vos conseils.

    Merci à vous.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 1
    Dernier message: 24/08/2012, 12h54
  2. Réponses: 11
    Dernier message: 21/06/2012, 18h05
  3. Problème performance SELECT avec jointure
    Par Netgamer dans le forum Requêtes
    Réponses: 7
    Dernier message: 05/08/2005, 11h20
  4. [Performance] RecordCount ou select Count(champ) ?
    Par shwin dans le forum Requêtes et SQL.
    Réponses: 9
    Dernier message: 28/09/2004, 18h37
  5. problemes de performances avec les requetes select
    Par berry dans le forum Requêtes
    Réponses: 3
    Dernier message: 10/07/2003, 14h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo