Discussion :

[pierho34]

Bonjour,

La table DD01405_COMPETENCE fait plus de 500 millions de lignes.

Cette requête prend 3 heures, savez-vous comment je peux l'optimiser ?

Code :

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

1
2
3
4
5
6
7
8
9
SELECT ID_RCI ||'¥' ||ID_COMPETENCE||'¥' ||REPLACE(REPLACE(LIBELLE_COMPETENCE,CHR(10),' '),CHR(13),' ')||'¥' ||TYPE||'¥' ||CODE||'¥' ||
TO_CHAR(DATE_AJOUT,'DD/MM/YYYY HH24:MI:SS')||'¥' ||CODE_STATUT||'¥' ||CODE_NIVEAU||'¥' ||ORIGINE_DONNEE||'¥' ||VISIBILITE_DONNEE||'¥' ||
CODE_ACTEUR||'¥' ||CODE_ORIGINE||'¥' ||TO_CHAR(DATE_MODIFICATION,'DD/MM/YYYY HH24:MI:SS')||'¥' ||TO_CHAR(DATE_VALIDITE,'DD/MM/YYYY HH24:MI:SS')||
'¥' ||(SELECT LISTAGG(LST.ID_EL,',') WITHIN GROUP(ORDER BY LST.ID_EL) CSV 
FROM JSON_TABLE(NVL(JSON_LIAISON,'{}'), '$.experiences[*]' COLUMNS ( "ID_EL" PATH '$.id')) LST) ||'¥' ||(SELECT LISTAGG(LST.ID_EL,',') 
WITHIN GROUP(ORDER BY LST.ID_EL) CSV FROM JSON_TABLE(NVL(JSON_LIAISON,'{}'), '$.formations[*]' COLUMNS ( "ID_EL" PATH '$.id')) LST) ||'¥' ||(SELECT LISTAGG(LST.ID_EL,',') 
WITHIN GROUP(ORDER BY LST.ID_EL) CSV FROM JSON_TABLE(NVL(JSON_LIAISON,'{}'), '$.cartesvisite[*]' COLUMNS ( "ID_EL" PATH '$.id')) LST) ||'¥' 
AS LIGNE_COMPETENCE 
FROM DD01405_COMPETENCE

Merci !

[binarygirl]

Il faudrait poster la structure des tables impliquées, y compris index etc pour avoir un début de réponse et des suggestions.
Juste par curiosité, combien de lignes retourne cette requête, sur les 500 millions que contient la table DD01405_COMPETENCE ?

[JeitEmgie]

Il faudrait poster la structure des tables impliquées, y compris index etc pour avoir un début de réponse et des suggestions.
Juste par curiosité, combien de lignes retourne cette requête, sur les 500 millions que contient la table DD01405_COMPETENCE ?

Il n'y a pas de WHERE clause… çà devrait répondre à la question.
Une optimisation serait d'avoir un seul JSON_TABLE dans une clause WITH au lieu de 3 subselect.

[binarygirl]

J'ai quand même envie de savoir à quoi ressemblerait le résultat final et quel est le but de la manoeuvre. Si c'est pour tirer la totalité de la table, alors il ne faut pas s'étonner que ça prenne du temps Le volume de données peut être conséquent et prendre du temps à être transféré via le réseau, quand bien même la requête en elle-même serait rapide. Mais alors il faut se demander pourquoi on tirer la totalité, plutôt qu'un échantillon filtré.

Pour trouver les goulets d'étranglement, il faut faire tourner un plan d'exécution, à défaut un moyen encore plus rudimentaire est de virer tout ce qui est subselect, jointures, fonctions d'aggrégation et voir combien de temps prend la requête, puis rajouter morceau par morceau, retester et voir là où les temps de réponse deviennent inacceptables.

[pierho34]

Le résultat final de la manœuvre est l'extraction de données dans un fichier pour être travaillé via d'autre batch. Ce petit bout de requête est l'extraction d'un script de batch que j'ai extrait suite à des rapports AWR et ADDM et qui fait partie du "TOP SQL statements".
J'ai essayé d'améliorer les temps de perf avec du tuning Oracle (SGA, cache) mais je suis au max. Donc je demande des conseils si possible pour améliorer cette requête. Mais je ne suis pas developpeur SQL (enfin très peu).

[vanagreg]

Bonjour,

Pas de clause WHERE, et des requêtes scalaires qui font du JSON sur chacune des 500 millions de ligne, je doute qu'on puisse faire grand chose. Peut-être faire la requête avec un hint PARALLEL, et encore je ne suis pas sûr que ça fonctionne étant donné le JSON dans la SELECT list.
Il faudrait que tu voies si ces données sont filtrées plus tard sur certains critères, auquel cas tu pourrais les appliquer déjà dans la requête.