Discussion :

[grouhan]

Bonjour,

dans l'AWR un nombre important de requête SELECT c from t AS OF SCN sont remontées.

Avez-vous une idée de l'origine de ces requêtes ?

Savez-vous si elles peuvent être responsabmle des nombres I/O waits remontées par Oracle ?

[Ikebukuro]

Salut,
La copie écran de ton rapport AWR ne nous apprend rien, la requête dont tu parles en est absente. Aurais-tu une autre image, notamment avec le user qui lance la requête?

Les requêtes de type "SELECT...AS OF SCN" sont des requêtes Flashback : cela signifie dire que tu veux voir les données dans le passé.
A priori c'est une requête non interne à Oracle, sinon le FROM serait sur une table du genre obj$, tab$... il faudrait donc identifier qui lance ce SELECT.

[grouhan]

Bonjour,

merci pour ton retour, ci-joint le top 10 (les requêtes select from Table OF SCN sont identifiés par le module JDBC Thin Client)



Ci-joint un exemple de requêtes

Code :

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

SELECT "MOUANAANN", "MOUANANUM", "MOUANAOOP" FROM ZMOUANA0 AS OF SCN 22265335847 WHERE MOUANADOP >= 1220407 AND MOUANAETA = 1

[vanagreg]

Bonjour,

Les requêtes ont l'air de vendre des règles de nommage qui font rêver. Bref, d'après ta copie-écran, ces requêtes ne sont responsables que de 3% du temps de la fenêtre choisie. Soit la fenêtre choisie est trop large, soit elles ne posent pas de problème. D'ailleurs, quel est le problème que tu essaies d'identifier?

On peut voir également d'autres requêtes qui forcent l'utilisation d'un index, ce qui est très rarement nécessaire (et peut d'ailleurs avoir des conséquences inverses que celles escomptées) .

Il nous faudrait aussi la section "Top 10 foreground events by Total Wait Time".

[grouhan]

Bonjour,

les requêtes qui te font rêver, sont générées à partir d'un programme de portage ISERIES -> UNIX pour émuler un existant fonctionnant sur ISeries/DB2 et le porter sur Unix.
Sur Iseries, les requêtes se positionnent sur un enregistrement et effectuent des NEXT sur les enregistrements suivants.
La requête ci-dessous permet de reproduire ce comportement sous ORACLE, en forçant l'utilisation de l'index souhaité pour avoir le tri souhaité. Chaque sous requête retourne les enregsitrements suivants ceux de la sous requêtes précédente suivant les conditions de filtre.
J'avoue , les premières fois (ça pique un peu). Cette émulation nous as posé problème lors de la montée en Oracle 12 avec le paramètre _optimizer_batch_table_access_by_rowid que nous avons été obligé de désactiver pour garantir l'ordre dans les sous requête.

Code :

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

SELECT /*+ FIRST_ROWS INDEX_ASC( ZECHCPB0 IDX_ZECHCPB0) */ ECHCPBETA, ECHCPBPLA, ECHCPBCOM, ECHCPBCMI, ECHCPBDAE, ECHCPBAGI, ECHCPBSTA, ECHCPBMIN, ECHCPBTYM, ECHCPBMAX, ECHCPBTVA, ECHCPBTM1, ECHCPBTT1, ECHCPBMM1, ECHCPBMT1, ECHCPBMR1, ECHCPBTR1, ECHCPBTP1, ECHCPBTM2, ECHCPBTT2, ECHCPBMM2, ECHCPBMT2, ECHCPBMR2, ECHCPBTR2, ECHCPBTP2, ECHCPBTM3, ECHCPBTT3, ECHCPBMM3, ECHCPBMT3, ECHCPBMR3, ECHCPBTR3, ECHCPBTP3, ECHCPBTM4, ECHCPBTT4, ECHCPBMM4, ECHCPBMT4, ECHCPBMR4, ECHCPBTR4, ECHCPBTP4, ECHCPBTM5, ECHCPBTT5, ECHCPBMM5, ECHCPBMT5, ECHCPBMR5, ECHCPBTR5, ECHCPBTP5, ECHCPBTM6, ECHCPBTT6, ECHCPBMM6, ECHCPBMT6, ECHCPBMR6, ECHCPBTR6, ECHCPBTP6, ECHCPBDEV, ECHCPBCUM, ECHCPBBAS, ECHCPBCOV, ECHCPBCOD, ECHCPBTRA, ECHCPBEXO, ECHCPBDAF, ECHCPBPER, ECHCPBTMI, ECHCPBIMI, ECHCPBMMI, ECHCPBRMI, ECHCPBTMA, ECHCPBIMA, ECHCPBMMA, ECHCPBRMA, ECHCPBCET, ECHCPBICN, ID FROM SABSTD.ZECHCPB0 WHERE SAB_MBR = :1 AND ECHCPBETA = :2 AND ECHCPBPLA = :3 AND ECHCPBCOM = :4 AND ECHCPBCMI = :5 AND ECHCPBDAE >= :6 UNION ALL SELECT /*+ FIRST_ROWS INDEX_ASC( ZECHCPB0 IDX_ZECHCPB0) */ ECHCPBETA, ECHCPBPLA, ECHCPBCOM, ECHCPBCMI, ECHCPBDAE, ECHCPBAGI, ECHCPBSTA, ECHCPBMIN, ECHCPBTYM, ECHCPBMAX, ECHCPBTVA, ECHCPBTM1, ECHCPBTT1, ECHCPBMM1, ECHCPBMT1, ECHCPBMR1, ECHCPBTR1, ECHCPBTP1, ECHCPBTM2, ECHCPBTT2, ECHCPBMM2, ECHCPBMT2, ECHCPBMR2, ECHCPBTR2, ECHCPBTP2, ECHCPBTM3, ECHCPBTT3, ECHCPBMM3, ECHCPBMT3, ECHCPBMR3, ECHCPBTR3, ECHCPBTP3, ECHCPBTM4, ECHCPBTT4, ECHCPBMM4, ECHCPBMT4, ECHCPBMR4, ECHCPBTR4, ECHCPBTP4, ECHCPBTM5, ECHCPBTT5, ECHCPBMM5, ECHCPBMT5, ECHCPBMR5, ECHCPBTR5, ECHCPBTP5, ECHCPBTM6, ECHCPBTT6, ECHCPBMM6, ECHCPBMT6, ECHCPBMR6, ECHCPBTR6, ECHCPBTP6, ECHCPBDEV, ECHCPBCUM, ECHCPB BAS, ECHCPBCOV, ECHCPBCOD, ECHCPBTRA, ECHCPBEXO, ECHCPBDAF, ECHCPBPER, ECHCPBTMI, ECHCPBIMI, ECHCPBMMI, ECHCPBRMI, ECHCPBTMA, ECHCPBIMA, ECHCPBMMA, ECHCPBRMA, ECHCPBCET, ECHCPBICN, ID FROM SABSTD.ZECHCPB0 WHERE SAB_MBR = :1 AND ECHCPBETA = :2 AND ECHCPBPLA = :3 AND ECHCPBCOM = :4 AND ECHCPBCMI > :5 UNION ALL SELECT /*+ FIRST_ROWS INDEX_ASC( ZECHCPB0 IDX_ZECHCPB0) */ ECHCPBETA, ECHCPBPLA, ECHCPBCOM, ECHCPBCMI, ECHCPBDAE, ECHCPBAGI, ECHCPBSTA, ECHCPBMIN, ECHCPBTYM, ECHCPBMAX, ECHCPBTVA, ECHCPBTM1, ECHCPBTT1, ECHCPBMM1, ECHCPBMT1, ECHCPBMR1, ECHCPBTR1, ECHCPBTP1, ECHCPBTM2, ECHCPBTT2, ECHCPBMM2, ECHCPBMT2, ECHCPBMR2, ECHCPBTR2, ECHCPBTP2, ECHCPBTM3, ECHCPBTT3, ECHCPBMM3, ECHCPBMT3, ECHCPBMR3, ECHCPBTR3, ECHCPBTP3, ECHCPBTM4, ECHCPBTT4, ECHCPBMM4, ECHCPBMT4, ECHCPBMR4, ECHCPBTR4, ECHCPBTP4, ECHCPBTM5, ECHCPBTT5, ECHCPBMM5, ECHCPBMT5, ECHCPBMR5, ECHCPBTR5, ECHCPBTP5, ECHCPBTM6, ECHCPBTT6, ECHCPBMM6, ECHCPBMT6, ECHCPBMR6, ECHCPBTR6, ECHCPBTP6, ECHCPBDEV, ECHCPBCUM, ECHCPBBAS, ECHCPBCOV, ECHCPBCOD, ECHCPBTRA, ECHCPBEXO, ECHCPBDAF, ECHCPBPER, ECHCPBTMI, ECHCPBIMI, ECHCPBMMI, ECHCPBRMI, ECHCPBTMA, ECHCPBIMA, ECHCPBMMA, ECHCPBRMA, ECHCPBCET, ECHCPBICN, ID FROM SABSTD.ZECHCPB0 WHERE SAB_MBR = :1 AND ECHCPBETA = :2 AND ECHCPBPLA = :3 AND ECHCPBCOM > :4 UNION ALL SELECT /*+ FIRST_ROWS INDEX_ASC( ZECHCPB0 IDX_ZECHCPB0) */ ECHCPBETA, ECHCPBPLA, ECHCPBCOM, ECHCPBCMI, ECHCPBDAE, ECHCPBAGI, ECHCPBSTA, ECHCPBMIN, ECHCPBTYM, ECHCPBMAX, ECHCPBTVA, ECHCPBTM1, ECHCPBTT1, ECHCPBMM1, ECHCPBMT1, ECHCPBMR1, ECHCPBTR1, ECHCPBTP1, ECHCPBTM2, ECHCPBTT2, ECHCPBMM2, ECHCPBMT2, ECHCPBMR2, ECHCPBTR2, ECHCPBTP2, ECHCPBTM3, ECHCPBTT3, ECHCPBMM3, ECHCPBMT3, ECHCPBMR3, ECHCPBTR3, ECHCPB TP3, ECHCPBTM4, ECHCPBTT4, ECHCPBMM4, ECHCPBMT4, ECHCPBMR4, ECHCPBTR4, ECHCPBTP4, ECHCPBTM5, ECHCPBTT5, ECHCPBMM5, ECHCPBMT5, ECHCPBMR5, ECHCPBTR5, ECHCPBTP5, ECHCPBTM6, ECHCPBTT6, ECHCPBMM6, ECHCPBMT6, ECHCPBMR6, ECHCPBTR6, ECHCPBTP6, ECHCPBDEV, ECHCPBCUM, ECHCPBBAS, ECHCPBCOV, ECHCPBCOD, ECHCPBTRA, ECHCPBEXO, ECHCPBDAF, ECHCPBPER, ECHCPBTMI, ECHCPBIMI, ECHCPBMMI, ECHCPBRMI, ECHCPBTMA, ECHCPBIMA, ECHCPBMMA, ECHCPBRMA, ECHCPBCET, ECHCPBICN, ID FROM SABSTD.ZECHCPB0 WHERE SAB_MBR = :1 AND ECHCPBETA = :2 AND ECHCPBPLA > :3 UNION ALL SELECT /*+ FIRST_ROWS INDEX_ASC( ZECHCPB0 IDX_ZECHCPB0) */ ECHCPBETA, ECHCPBPLA, ECHCPBCOM, ECHCPBCMI, ECHCPBDAE, ECHCPBAGI, ECHCPBSTA, ECHCPBMIN, ECHCPBTYM, ECHCPBMAX, ECHCPBTVA, ECHCPBTM1, ECHCPBTT1, ECHCPBMM1, ECHCPBMT1, ECHCPBMR1, ECHCPBTR1, ECHCPBTP1, ECHCPBTM2, ECHCPBTT2, ECHCPBMM2, ECHCPBM T2, ECHCPBMR2, ECHCPBTR2, ECHCPBTP2, ECHCPBTM3, ECHCPBTT3, ECHCPBMM3, ECHCPBMT3, ECHCPBMR3, ECHCPBTR3, ECHCPBTP3, ECHCPBTM4, ECHCPBTT4, ECHCPBMM4, ECHCPBMT4, ECHCPBMR4, ECHCPBTR4, ECHCPBTP4, ECHCPBTM5, ECHCPBTT5, ECHCPBMM5, ECHCPBMT5, ECHCPBMR5, ECHCPBTR5, ECHCPBTP5, ECHCPBTM6, ECHCPBTT6, ECHCPBMM6, ECHCPBMT6, ECHCPBMR6, ECHCPBTR6, ECHCPBTP6, ECHCPBDEV, ECHCPBCUM, ECHCPBBAS, ECHCPBCOV, ECHCPBCOD, ECHCPBTRA, ECHCPBEXO, ECHCPBDAF, ECHCPBPER, ECHCPBTMI, ECHCPBIMI, ECHCPBMMI, ECHCPBRMI, ECHCPBTMA, ECHCPBIMA, ECHCPBMMA, ECHCPBRMA, ECHCPBCET, ECHCPBICN, ID FROM SABSTD.ZECHCPB0 WHERE SAB_MBR = :1 AND ECHCPBETA > :2

Mais je m'égare. Mon pb initial était de comprendre l'origine des requête SELECT x from T AS SCN et de comprendre leur impact sur les événements d'attente remontés dans l'AWR et notamment les 73% DBTIme.

Je ne comprend pas très bien non plus les attentes db file sequentiel read et le read by other session lors de la requête d'INSERT.

Ci-dessous les différentes captures demandées

[Ikebukuro]

Comme je disais, la requête suivante permet d'interroger ta table ZMOUANA0 dans le passé, lorsque le SCN valait 22265335847.

Code :

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

SELECT "MOUANAANN", "MOUANANUM", "MOUANAOOP" FROM ZMOUANA0 AS OF SCN 22265335847 WHERE MOUANADOP >= 1220407 AND MOUANAETA = 1;

Autre remarque, c'est normal d'avoir autant de hints dans tes requêtes, notamment le FIRST_ROWS?

[grouhan]

la présence des hint FIRST_ROWS permet de favoriser l'envoi des premiers résultats de la requête, le traitement ne devant potentiellement ne traiter qu'une partie de l'ensemble des enregistrements de la requête. Les premiers enregistrements à traiter et attendus étant ceux de la première sous requête dans l'ordre garantie par le INDEX_ASC(IDX).
Comme expliqué précédemment , ces requêtes sont "imposées" par un mécanisme de portage permettant de minimiser les coûts de récriture et pour reproduire un fonctionnement existant pour une autre système de d'exploitation et de base de données. Je te rejoins où cela n'est probablement pas optimal mais je dois pour l'instant composer avec.


Ces requêtes exécutées par le mécanisme de flashback, contribuent-elles fortement à l' événement d'attente db file sequential read ?

[Mohamed.Houri]

Hello,

Avant d'analyser les requêtes, vous devez analyser pourquoi vos lectures (db file sequential read, read by other session) et écritures (log file sync) depuis et vers le disque sont extrêmement lents. Avec ces temps moyens affichés dans votre rapport AWR, vous ne pouvez pas avoir une application fonctionnant normalement. Impossible.

Deuxièmement, le mode FIRST_ROWS contient du code empirique qui dit : s'il existe un index épousant la clause ORDER BY de la requête alors il faut utiliser cet index en le traversant, dans l'ordre, du premier au dernier leaf block (INDEX FULL SCAN). Et ce, quelque soit le cout de l’opération INDEX FULL SCAN. Vous trouverez, dans l’article suivant, une explication:

https://hourim.wordpress.com/2012/03...nd-first_rows/

Par contre, nonobstant la présence et l’utilisation d’un index afin de lire les données dans l’ordre des colonnes de l’index, votre requête doit absolument contenir la clause ORDER BY (ce qui n’est pas le cas ici).

Vous avez eu des soucis lors de votre passage en 12cR1, parce qu'Oracle ne peut pas éviter l’opération ORDER BY, même en présence de l’index adéquat, lorsque la table est visitée via TABLE ACCESS BY INDEX ROWID BATCHED

https://hourim.wordpress.com/2014/09...rowid-batched/

Bien Cordialement
Mohamed

[grouhan]

Hello,

je confirme que les temps de réponse ne sont pas bon mais ma première piste était de me dire que cela était du aux requêtes de flashback.

Maintenant quelles sont vos préconisations pour identifier pourquoi les lectures et écritures sont si lentes ? Si l'origine n'est pas liée à l'activité sur la base de données il faut se tourner vers un pb matériel et ou logiciel (style antivirus) ?

Sous linux, quelles seraient les outils commandes permettant d'identifier clairement le pb ?

[Mohamed.Houri]

Hello,
Sous linux, quelles seraient les outils commandes permettant d'identifier clairement le pb ?

Généralement un ralentissement, surtout du log file sync, peut-être attribué à un manque de CPU (manque qui peut lui même être lié à un swap de mémoire surtout si vous n'utlisez pas les Larges pages)

Sous linux vous pouvez vérifier le manque de CPU en temps réel (i.e. le ralentissement est en cours) avec les deux commandes suivantes

Code :

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

1
2
3
4
[oracle@localhost ~]$ w
 12:48:03 up  1:19,  1 user,  load average: 0.16, 0.10, 0.11
USER     TTY      FROM             LOGIN@   IDLE   JCPU   PCPU WHAT
vagrant  pts/0    10.0.2.2         11:29    2.00s  0.08s  0.04s sshd: vagrant [priv]

Code :

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

1
2
3
4
5
6
[oracle@localhost ~]$ vmstat 5 3
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0   5888 204644   4168 3038600    0    1   207    88  523  829  4  4 91  0  0
 0  0   5888 204636   4168 3038640    0    0     0     8  887 1538  1  3 96  0  0
 0  0   5888 204636   4168 3038640    0    0     0    13 1122 1602  0  3 97  0  0

Il faut lire attentivement les valeurs de r (demande de CPU) et b (demande I/O process Linux dans le statut D : Uninteruptible)

r- cpu = le nombre de process en attente dans la CPU runqueue

Les valeurs si et so indiquent s'il y a du swapping ou pas

Par contre, si le ralentissement a lieu il y a deux jours(par exemple) alors il faut passer par la commande sar à savoir

Pour la cpu

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[oracle@localhost ~]$ sar -p -2
Linux 5.4.17-2036.102.0.2.el8uek.x86_64 (localhost.localdomain)         02/20/2022      _x86_64_        (2 CPU)

11:00:36     LINUX RESTART      (2 CPU)

11:10:13 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
11:20:13 AM     all      0.03      9.36      0.82      0.30      0.00     89.50
11:30:13 AM     all      0.02      0.00      0.18      0.00      0.00     99.80
11:40:13 AM     all      0.02      0.00      0.19      0.00      0.00     99.79
11:50:13 AM     all      0.02      0.00      0.18      0.00      0.00     99.81
12:00:13 PM     all      0.02      0.00      0.17      0.00      0.00     99.81
12:10:13 PM     all      0.02      0.00      0.19      0.00      0.00     99.79
12:20:13 PM     all      0.02      0.05      0.19      0.00      0.00     99.74
12:30:13 PM     all      0.02      0.00      0.16      0.00      0.00     99.82

Et pour la mémoire

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[oracle@localhost ~]$ sar -B -2
Linux 5.4.17-2036.102.0.2.el8uek.x86_64 (localhost.localdomain)         02/20/2022      _x86_64_        (2 CPU)

11:00:36     LINUX RESTART      (2 CPU)

11:10:13 AM  pgpgin/s pgpgout/s   fault/s  majflt/s  pgfree/s pgscank/s pgscand/s pgsteal/s    %vmeff
11:20:13 AM    138.58    362.47    305.27      0.10    382.43      0.00      0.00      0.00      0.00
11:30:13 AM      0.00      0.52      1.38      0.00      1.18      0.00      0.00      0.00      0.00
11:40:13 AM      0.00      0.58      2.21      0.00      1.91      0.00      0.00      0.00      0.00
11:50:13 AM      0.00      0.54      1.36      0.00      1.19      0.00      0.00      0.00      0.00
12:00:13 PM      0.00      0.40      1.35      0.00      1.19      0.00      0.00      0.00      0.00
12:10:13 PM      0.00      0.71      4.84      0.00      3.56      0.00      0.00      0.00      0.00
12:20:13 PM      0.00      1.02     23.97      0.00     15.16      0.00      0.00      0.00      0.00
12:30:13 PM      0.00      0.52      1.35      0.00      1.19      0.00      0.00      0.00      0.00
12:40:13 PM      0.00      0.37      1.37      0.00      1.20      0.00      0.00      0.00      0.00
12:50:13 PM      0.00      0.37      1.36      0.00      1.20      0.00      0.00      0.00      0.00
01:00:13 PM      0.00      0.56      1.36      0.00      1.18      0.00      0.00      0.00      0.00
01:10:13 PM      0.00      0.51      4.87      0.00      3.63      0.00      0.00      0.00      0.00
01:20:13 PM      0.00      1.10     23.96      0.00     15.08      0.00      0.00      0.00      0.00
01:30:13 PM      0.00      0.40      1.36      0.00      1.22      0.00      0.00      0.00      0.00
01:40:13 PM      0.00      0.54      1.36      0.00      1.20      0.00      0.00      0.00      0.00
01:50:13 PM      0.00      0.38      1.36      0.00      1.19      0.00      0.00      0.00      0.00
02:00:13 PM      0.00      0.40      1.36      0.00      1.10      0.00      0.00      0.00      0.00
02:10:13 PM   2057.60    115.10   1194.59      0.34    313.10      0.00      0.00      0.00      0.00

Enfin vous avez la commande iostats pour vérifier la réponse du disque

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[oracle@localhost ~]$ iostat 5 3
Linux 5.4.17-2036.102.0.2.el8uek.x86_64 (localhost.localdomain)         04/22/2022      _x86_64_        (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.69    1.09    3.73    0.27    0.00   92.22

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda              11.01       327.60       144.74    1916142     846599

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.52    0.00    2.37    0.00    0.00   97.11

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               1.80         1.60        12.80          8         64

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.41    0.00    2.77    0.10    0.00   96.71

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               1.20         0.00        10.00          0         50

Bien Cordialement
Mohamed Houri

[grouhan]

Merci pour ton retour, cela veux dire que dans l'AWR les informations remontées dans les rubriques HOST CPU, Instances CPU, Operating system Statictics, IOSTATS , MEMORY STAT par exemple ne sont pas exploitables pour identifier un manque de CPU, pb disque et manque mémoire ?

[Mohamed.Houri]

Bonjour,

Il y a certaines informations dans le rapport AWR qui sont intéressantes dans ce contexte. Par exemple, VM_INBYTES et VM_OUTBYTES indiquent le swapping de mémoire. le Load (end and begin) représente l'équivalent de la commande linux w lancée au début et à la fin du snapshot respectivement. Mais dans ce cas il ne faut pas considerer l'information Load fournie par le rapport AWR comme indiquant exclusivement de la demande CPU. Ca inclus aussi la demande en I/O comme expliqué dans mon message précédent. Il y a d'autres astuces de lecture que je peux tirer du rapport AWR pour savoir s'il y a vraiement un manque de CPU ou pas mais que je ne peux toutes les lister ici.

Bien Cordialement
Mohamed Houri

[grouhan]

Merci pour l'ensemble de vos retours