Discussion :

[jacquesh]

bonjour,

voila plus jour que je galère sur l'impossibilité de me débarrasserd'un "TABLE ACCESS FULL" !!

J'ai une table comme suit les documents XML font ~30ko.

Code :

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

1
2
3
4
5
CREATE TABLE XYZREGISTRE of XMLType
    XMLTYPE store AS OBJECT RELATIONAL
    XMLSCHEMA "http://X.Y.Z/projet/ABC/schema/xyzmessage.xsd"
	ELEMENT "xyzmessage"
/

Plein d'index de ce genre.

Code :

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

1
2
CREATE INDEX idx_xyzreg_1 ON XYZREGISTRE
	(extractvalue(object_value,'/xyzmessage/META_DATA/md_hopcreator')) TABLESPACE INDX;

si je fait un requete que ne recupère que 1 champs (indexé) par de pb.

par contre comment peut on optimiser cette requete :

Code :

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

SELECT extract(object_value,'/xyzmessage').getClobVal() FROM XYZREGISTRE WHERE existsNode(object_value,'/xyzmessage[NOID="x007tzg4263') = 1;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |  2548 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |             |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000    |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |             |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| XYZREGISTRE |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
 
 4 - filter("XYZREGISTRE"."SYS_NC00017$"='x007tzg4263')

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Statistiques
----------------------------------------------------------
         37  recursive calls
          3  db block gets
       5206  consistent gets
          0  physical reads
        628  redo size
       1065  bytes sent via SQL*Net to client
        674  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

pour eviter un access full.
note :: le .getClobVal() est OBLIGATOIRE.

merci de votre aide !!

[mnitu]

Et pour quoi veut tu te débarrasser du FULL ? Les résultats n’ont pas l’air d’indiquer un souci de performance ?

[orafrance]

j'y connais pas grand chose (en fait rien ) mais d'après l'exemple de la doc j'ai pas l'impression que l'index porte sur la même chose que la requête.

Je me serais attendu à un WHERE du genre :

Code :

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

object_value,'/xyzmessage[META_DATA="x007tzg4263'

ou trouver NOID dans l'index

Sinon, sache qu'il n'est pas forcément intéressant de passer par un index, si Oracle ramène beaucoup de ligne (par rapport au nombre de référence dans l'index) un FTS est plus intéressant

[orafrance]

cet exemple à l'air intéressant aussi : http://download.oracle.com/docs/cd/B...tm#sthref10107

tu peux essayer un index comme ça ?

Code :

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

CREATE INDEX idx_xyzreg_1 ON XYZREGISTRE (XMLDATA."NOID")

[jacquesh]

en fait j'ai tourné le problème un peu dans tous les sens.

en effet il n'y a pas problème de perf - pour le moment - c'est une base de dev.
J'ai fait des test d'accès à 100, 1000, 10000 lignes ça fonctionne avec des temps d'accès <3sec... mais pour plus tard...

Les temps données par explain plan sont un peu faussé lorsque la requête a déjà été exécuté...

en ce qui concerne le fait d'utiliser "WHERE extractValu"e au lieu de "/xpath[param=value] "; j'ai remarqué que c'était moins couteux en E/S pour oracle - Maintenant savoir pourquoi ?-

la ou cela me pose problème. c'est que Oracle n'utilise pas les index que j'ai mis en place.

Autre exemple :

quand je fait un

Code :

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

1
2
SELECT * FROM TOTO WHERE ROWID=(
SELECT ROWID FROM TOTO WHERE A=1) ;

exemple avec XMLTYPE

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
 
select extract(object_value,'/abcmessage').getClobVal() from XYZREGISTRE where rowid=(
SELECT rowid FROM XYZREGISTRE WHERE existsNode(object_value,'/abcmessage[NODC="fj65743Hde"]') = 1);
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |  2548 |     3   (0)| 00:00:01 |        |      |            |
|*  1 |  FILTER                    |             |       |       |            |          |        |      |            |
|   2 |   FAST DUAL                |             |     1 |       |     2   (0)| 00:00:01 |        |      |            |
|   3 |  TABLE ACCESS BY USER ROWID| XYZREGISTRE |     1 |  2548 |     1   (0)| 00:00:01 |        |      |            |
|   4 |   PX COORDINATOR           |             |       |       |            |          |        |      |            |
|   5 |    PX SEND QC (RANDOM)     | :TQ10000    |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   6 |     PX BLOCK ITERATOR      |             |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  7 |      TABLE ACCESS FULL     | XYZREGISTRE |     1 |  2548 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------

L'index est bien utilisé pour le rowID, par contre le fait de mettre * ou une liste de champs que je souhaite récupérer l'explain fini par un ACCESS FULL !!

De plus je n'arrive pas a forcer l'usage des index même avec /*index()*/ dans la requete !!

une idée...

[mnitu]

Si t’a pas un problème de perf ne t’invente pas un problème d’optimisation !
Si tu penses que les gros volumes vont poser des problèmes fais un benchmark et vérifiez.
Si tu veut obtenir les informations concernant les choix de l’optimiseur fait une trace de l’événement 10053, mais comprendre et interpréter les résultats n’est pas simple. Il y a « N » raisons pour expliquer pourquoi Oracle n’utilise un index.

Les temps données par explain plan sont un peu faussé lorsque la requête a déjà été exécuté...

C'est pas vrai. Mais tu peut faire un

Code :

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

1
2
 
alter system flush shared_pool

si tu pense que ça aide.

[orafrance]

si je vois bien tu utilises le parallélisme ce qui peut expliquer l'usage des FTS

[Michel SALAIS]

SELECT * FROM TOTO WHERE ROWID=(
SELECT ROWID FROM TOTO WHERE A=1) ;

C'est très curieux comme approche!
Pourquoi ne pas faire

Code :

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

SELECT * FROM TOTO WHERE A=1

[jacquesh]

C'est très curieux comme approche!
Pourquoi ne pas faire

Code :

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

SELECT * FROM TOTO WHERE A=1

parce que suivant la configuration de l'XML TABLE même avec un index j'ai

Code :

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

1
2
 
select F1 from toto where A=1

la tout va bien

par contre

Code :

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

select * from  toto where A=1

TABLE ACCESS FULL !!

je me suis dit que si je trouve rapidement le rowid, ramener toutes les informations de ce rowid serai une facon d'aider oracle à trouver l'information sans tout scanner....
mais malheureusement TABLE ACCESS FULL !! too.

bizard... vous avez dis bizard

pour répondre à "orafrance" : j'ignorai que le parallélisme imposait le FTS... je vais fouiller dans cette direction.

merci pour l'info

[jacquesh]

Bingo !!

c'était bien le parallélisme qui provoquai le non-usage des index !!
si quelqu'un a de ma littérature sur le sujet;je suis preneur.

Merci encore.

SOLVED

[orafrance]

qui dit parallélisme dit FTS moins couteux pour l'optimiseur donc le CBO rechigne moins à y recourir

c'est pas automatique mais ça risque d'arriver plus souvent... encore une démonstration que le parallélisme est loin d'être toujours miraculeux