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.

[JeitEmgie]

C'est typiquement le genre de travail à faire avec une ou plusieurs MATERIALIZED VIEW,
au moins une en FAST REFRESH avec tous les concaténations et sans les LISTAGG (çà ne marchera probablement pas)
et une TABLE alimentée par un trigger qui contiendra les LIST_AGG par ID_RCI,
ce qui peut se faire simplement :

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
25
26
 
SELECT 
	id_rci,
	listagg(exp_id,',') within group(order by exp_id) as exp_ids, 
	listagg(form_id,',') within group(order by form_id) as form_ids, 
	listagg(carte_id,',') within group(order by carte_id) as carte_ids
FROM dd01405_competence,
json_table( json_liaison,
	'$'
	columns (
		nested path '$.experiences[*]'
		columns (
			exp_id path '$.id'
		),
		nested path '$.formations[*]'
		columns (
			form_id path '$.id'
		),
		nested path '$.cartesvisite[*]'
		columns (
			carte_id path '$.id'
		)
	)
)
GROUP BY id_rci
;

En JOINant la MV et cette table, le résultat devrait être instantané, avec INDEXes sur le ID_RCI dans les 2.

[pierho34]

C'est typiquement le genre de travail à faire avec une ou plusieurs MATERIALIZED VIEW,
au moins une en FAST REFRESH avec tous les concaténations et sans les LISTAGG (çà ne marchera probablement pas)
et une TABLE alimentée par un trigger qui contiendra les LIST_AGG par ID_RCI,
ce qui peut se faire simplement :

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
25
26
 
SELECT 
	id_rci,
	listagg(exp_id,',') within group(order by exp_id) as exp_ids, 
	listagg(form_id,',') within group(order by form_id) as form_ids, 
	listagg(carte_id,',') within group(order by carte_id) as carte_ids
FROM dd01405_competence,
json_table( json_liaison,
	'$'
	columns (
		nested path '$.experiences[*]'
		columns (
			exp_id path '$.id'
		),
		nested path '$.formations[*]'
		columns (
			form_id path '$.id'
		),
		nested path '$.cartesvisite[*]'
		columns (
			carte_id path '$.id'
		)
	)
)
GROUP BY id_rci
;

En JOINant la MV et cette table, le résultat devrait être instantané, avec INDEXes sur le ID_RCI dans les 2.

Merci je vais tester cela lundi matin et je fournirais également les plans d'exécutions.

[pierho34]

Je met le plan d'execution de la requete :

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000K| 3843M| 32G (1)|349:22:38 |
| 1 | SORT GROUP BY | | 1 | 2 | | |
| 2 | JSONTABLE EVALUATION | | | | | |
| 3 | SORT GROUP BY | | 1 | 2 | | |
| 4 | JSONTABLE EVALUATION | | | | | |
| 5 | SORT GROUP BY | | 1 | 2 | | |
| 6 | JSONTABLE EVALUATION | | | | | |
|* 7 | VIEW | | 2000K| 3843M| 32G (1)|349:22:38 |
|* 8 | WINDOW NOSORT STOPKEY | | 535M| 35G| 32G (1)|349:22:38 |
| 9 | TABLE ACCESS FULL | DD01405_COMPETENCE | 535M| 35G| 797K (1)| 00:00:32 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("from$_subquery$_005"."rowlimit_$$_rownumber"<=2000000)
8 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=2000000)
La vue matérialisé je la base que quel critère ?

La (ou les vue) matérialisé je la base que quels critères ?
Comment je fabrique la table contenant les listaggs ? Comme ci-desous ?

create table t1 as (select ID_RCI as ID,(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)
as ligne_competence FROM DD01405_COMPETENCE);

Merci