question sur les hints [Résolu]

[slausseur]

Bonjour,

Je dois étudier une requête et l'optimiser si besoin et pour cela, j'aurais voulu avoir des précisions sur les hints:
Peut-on rajouter par un hint un index qui n'existe pas sur une colonne juste pour observer le plan d'exécution résultant, ou faut-il absolument créer cet index?

Merci d'avance.

[orafrance]

Non, mais avec l'option NOSEGMENT tu peux créer un index "virtuel"

[slausseur]

NOSEGMENT?
Et a quel moment tu l'utilises? Et comment?
En fait, je m'impregne de la doc pdf que j'ai trouvé sur ce site, mais je ne vois pas parler de cette option.

Comme tu l'auras compris, je débute en tant que tunneuse !!!

Désolée si mes questions semblent un peu bêtes.

Merci d'avance.

[orafrance]

c'est un paramètre du CREATE INDEX. Ca crée ton index mais vide, donc évidemment il n'est pas utilisé mais ça permet de voir ce que ça donne dans les plans d'exécution.

pense à calculer les stats dessus aussi

[slausseur]

Merci j'essaye tout de suite.

Si encore des problèmes, je te recontacte. Sinon, je clos.

Merci encore.

[laurentschneider]

Code :

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
 
SQL> CREATE TABLE t AS SELECT * FROM all_objects
TABLE created.
SQL> CREATE INDEX i ON t(object_id) NOSEGMENT
INDEX created.
SQL> ALTER session SET "_use_nosegment_indexes"=true
Session altered.
SQL> SELECT * FROM t WHERE object_id=1
no rows selected.
 
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=5 Card=1 Bytes=128)
   1    0    TABLE ACCESS BY INDEX ROWID AUDBA.T (Cost=5 Card=1 Bytes=128)
   2    1      INDEX RANGE SCAN AUDBA.I (Cost=1 Card=18)
SQL> ALTER session SET "_use_nosegment_indexes"=false
Session altered.
SQL> SELECT * FROM t WHERE object_id=1
no rows selected.
 
Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=17 Card=1 Bytes=128)
   1    0    TABLE ACCESS FULL AUDBA.T (Cost=17 Card=1 Bytes=128)
SQL> DROP TABLE t
TABLE dropped.

l'avantage de l'index NOSEGMENT, c'est qu'il est vite créé et n'occupe pas de place. l'inconvénient c'est qu'il ne sert à rien

[slausseur]

Déjà, s'il me permet de voir sur le plan d'exécution une amélioration au niveau du cout, ce serait pas mal.
J'ai des requêtes qui sont vraiment trop longues, et elles mettent à genou le serveur.
Donc avec la création d'un index "virtuel", je devrais deja voir si ca m'avance un peu.

Merci encore...

[laurentschneider]

bon, disons qu'il faudrait déjà revoir ta requête, puis ensuite ton plan d'execution. Si tu as des full tables scans, tu peux créer un index pour voir si ça améliore quelque chose...

[slausseur]

Mon problème est bien là... J'ai des accès en full scan sur des tables de plusieurs dizaines de millions de lignes...

Autre question un peu bête, sûrement. Un index sur 2 colonnes ne sera jamais utilisé si la requête ne concerne que l'une d'entre elles?

[chrifo]

Bonjour,

Envoyé par slausseur

Autre question un peu bête, sûrement. Un index sur 2 colonnes ne sera jamais utilisé si la requête ne concerne que l'une d'entre elles?

Si, si c'est la première

[laurentschneider]

si l'index est sur 2 colonnes et que tu as une condition sur la première colonne alors il sera utilisé. Sinon, probablement pas, mais peut-être quand même.

Il faudrait que tu nous donnes ta requête et ton plan

[slausseur]

Voici la requete:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT a.code_magasin, a.lib_magasin, a.saison, a.code_groupe, a.lib_groupe, 
       a.code_rayon, a.lib_rayon, a.code_famille, a.lib_famille, a.code_produit, 
       a.code_ug, a.lib_ug, a.statut_marchandise, a.date_fin_de_vie, 
       a.qte_en_stock, a.pa_unitaire, a.stock_a_pa, sum(c.selling_unit_retail)
       pv_repere, decode(sum(a.qte_en_stock), '0', NULL, sum(a.qte_en_stock))
       * decode(sum(c.selling_unit_retail), '0', NULL, sum(
       c.selling_unit_retail)) stock_a_pv, b.taux_ana, b.anciennete_compta, 
       b.taux_compta, to_char(add_months(sysdate, -1), 'YYYYMM')
       periode_extraction
    FROM inter1 a, inter2 b, item_loc c, dual
    WHERE a.code_ug = b.code_ug
      AND a.code_magasin = b.code_magasin
      AND a.saison = b.saison
      AND a.date_fin_de_vie = b.date_fin_de_vie
      AND a.code_ug = c.item
      AND a.code_magasin = c.loc
    GROUP BY a.code_magasin, a.lib_magasin, a.saison, a.code_groupe, 
             a.lib_groupe, a.code_rayon, a.lib_rayon, a.code_famille, 
             a.lib_famille, a.code_produit, a.code_ug, a.lib_ug, 
             a.statut_marchandise, a.date_fin_de_vie, a.qte_en_stock, 
             a.pa_unitaire, a.stock_a_pa, b.taux_ana, b.anciennete_compta, 
             b.taux_compta, to_char(add_months(sysdate, -1), 'YYYYMM');

Le plan d'exécution est le suivant:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 Exec
  Ord EXPLAIN Plan
----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   10   SELECT STATEMENT OPT_MODE:CHOOSE(COST=57064,CARD=283778,BYTES=51080040)
    9  . SORT (GROUP BY) (COST=57064,CARD=283778,BYTES=51080040)
    8  .. HASH JOIN (COST=53246,CARD=283778,BYTES=51080040)
    1  ... TABLE ACCESS (FULL) OF 'RMS11.INTER2' (COST=76,CARD=284514,BYTES=8819934)
    7  ... HASH JOIN (COST=52459,CARD=284069,BYTES=42326281)
    5  .... MERGE JOIN (CARTESIAN) (COST=281,CARD=284805,BYTES=37309455)
    2  ....| TABLE ACCESS (FULL) OF 'SYS.DUAL' (COST=2,CARD=1,BYTES=)
    4  ....| BUFFER (SORT) (COST=279,CARD=284805,BYTES=37309455)
    3  ....|. TABLE ACCESS (FULL) OF 'RMS11.INTER1' (COST=279,CARD=284805,BYTES=37309455)
    6  .... TABLE ACCESS (FULL) OF 'RMS11.ITEM_LOC' (COST=38946,CARD=34647635,BYTES=623657430)

La table la plus problématique à mon gout est item_loc, qui a 6 index:

INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
ITEM_LOC_I2 PRIMARY_SUPP 2
ITEM_LOC_I2 ITEM 1
ITEM_LOC_I3 ITEM_PARENT 1
ITEM_LOC_I4 ITEM_GRANDPARENT 1
ITEM_LOC_I5 PRIMARY_VARIANT 1
ITEM_LOC_I6 SELLING_UOM 1
PK_ITEM_LOC ITEM 1
PK_ITEM_LOC LOC 2


Merci d'avance

[laurentschneider]

1) FROM inter1 a, inter2 b, item_loc c, dual
enlève-moi vite ce dual de là !!!

2) La table la plus problématique à mon gout est item_loc, qui a 6 index
Non, tu sélectionnes toutes les lignes, donc un index ne va pas te servir à grand chose, un full table scan est dans ton cas tout à fait efficace.

Si tu as plusieurs millions de lignes, il faut plutôt se pencher vers le partitioning et le parallélisme

[laurentschneider]

enlève aussi le
to_char(add_months(sysdate, -1), 'YYYYMM')
de la clause group by

[slausseur]

La requete n'est pas de moi, et je ne sais pas pour quelle raison ils avaient besoin de dual.

Je vais me renseigner à la rigueur.
Mais c'est le dual qui fait que le plan d'exécution est si mauvais?

Quand tu parles de partitionning, c'est sur cette fameuse table de 34 millions de lignes? Malheureusement, cette table étant déjà en prod, je ne sais pas si c'est réalisable.

[laurentschneider]

1) le plan n'est pas du tout mauvais
2) pour dual, c'est parcequ'il y a des ânes qui croient que SYSDATE est une colonne de dual
3) le partitionement d'une table existante est une tâche de maintenance comme une autre. Attention, l'option Partitioning n'est pas gratuite et nécessite Enterprise Edition + Partitioning

[slausseur]

Ok pour le dual, j'aurais dû/pu y penser toute seule.....

merci

Pour le reste, je vais voir si je peux chronométrer ma requête pour voir si elle est acceptable... Savoir s'ils veulent bien que je mette a genou leur serveur....

Pour info, la table fait 6,25Go pour 34 million de lignes, c'est correct.... Je ne sais pas si ca sert à quelque chose de partitionner la table; je vais leur proposer quand-même.

[laurentschneider]

c'est sûr que ça sert, surtout si le serveur est multiprocesseur.

Il faut être sûr aussi d'avoir une valeur de PGA_AGGREGATE_TARGET suffisante.

En gros, si tu as des ressources (mémoire+processeurs), tu peux terriblement réduire le temps d'exécutation, style avec une dizaine de processeurs et une dizaine de gigas de mémoire, tu pourras passer de plusieurs heures à quelques minutes.

bien sûr si tu as une machine monoprocesseur avec Windows, tu risque d'être déçue...

A+
Laurent

[orafrance]

encore faut-il que le parallélisme soit paramétré ou au moins l'async IO sinon, ça va pas donner grand chose

[slausseur]

Bon, plus de peur que de mal, la requête met 6 min à s'exécuter. C'est donc pas dramatique, sachant que c'est un traitement mensuel nocturne.

Je vais voir avec le client ce qu'il en pense.

Merci encore