Materialized view, query rewrite

Version imprimable

28/01/2009, 18h52
pacmann

Materialized view, query rewrite

Salut !

J'essaie de faire marcher le query rewrite sur materialized view.
En fait, il s'agit d'un subterfuge pour gruger un progiciel (dont je ne peux modifier les requêtes, donc) qui calcule à chaque étape d'un certain traitement un gros agrégat.
Au cours de l'exécution, le résultat de la VM est altéré, mais tout le monde s'en fout parce que ce calcul correspond à une option du progiciel que nous n'utilisons pas.
Donc j'ai mis le système en QUERY_REWRITE_INTEGRITY = STALE_TOLERATED.

- Lorsque j'exécute ma requête qui doit être réécrite par TOAD ou sqlplus en interactif, ça passe : le plan d'exécution donné par EXPLAIN PLAN confirme l'utilisation de la VM. Je modifie la table, la VM passe en stale, je réessaie : c'est toujours bon.

- Pour le traitement en "batch", lorsque j'utilise "Kill/Trace Session" de Toad, le plan d'exécution affiché est également celui avec la VM.

- Par contre, toujours pour le traitement "batch", comme le temps d'exécution en découlant ne correspondait pas à une économie de gros agrégat, j'ai tracé la session : et là, le plan d'exécution affiché indique que la VM n'est pas prise !

Bon, j'ai essayé de me documenter un peu sur le sujet, et voici quelques indications supplémentaires :
- La VM est créée avec l'option ENABLE QUERY REWRITE, sans indication de rafraîchissement
- QUERY_REWRITE_ENABLE = TRUE
- Optimizer mode : chose
- l'unique table utilisée dans la vue est analysée
- les conteneurs des VM ont été analysées
- le traitement batch s'exécute avec le même USER que les tests en interactif : c'est l'OWNER
- dans le doute, mon DBA a passé : optimizer_secure_view_merging = FALSE

Ah, oui : Oracle 10.2.0.3

Ma question (on l'aura devinée) : Pourquoi est-ce que ma vue matérialisée n'est pas utilisée ??
29/01/2009, 15h03
pacmann

Ouais, sous le poids du désespoir, j'ai également tenté :

- QUERY_REWRITE_ENABLE = FORCE

... Sans résultat.
Personne n'a d'idée ?

[EDIT]
Allez, comme je continue à chercher, j'ai lu que selon les cas, l'utilisation de bind variables peut empêcher le rewrite.
Donc précision : il n'y a pas de bind variables dans le traitement batch.
29/01/2009, 20h53
mnitu
Je pense que tu doit fournir un peu plus d'informations:
- le select de création de la mv
- la requête exécuté par le batch
Ce n'est jamais Toad ou sqlplus qui re écrit la requête, c'est toujours l'optimiseur d'Oracle. Si pour la session 1 cella se passe bien et pour la session 2 il y a un souci il faut examiner les différences entre la session 1 et la session 2 et non pas jouer avec les paramètres de l'optimiseur. Peu importe que la session 1 est crée via Toad et la 2 via sqlplus, java ou .net tant que les paramètres de la sessions (alter session set ...) sont les mêmes.

Merci Mnitu !

1) C'est un peu ridicule, mais je change les noms pour garder l'anonymat :)
VM :
Code:

1 2 3 4 5 6 7 8 create materialized view ma_vm enable query rewrite as SELECT t23.flag, t23.flag2, SUM (t23.amount), COUNT (t23.record_id) FROM matable t23 WHERE t23.tableid = 77 AND (t23.state = 3 OR t23.state = 7) GROUP BY t23.flag, t23.flag2
La requête exécutée par le progiciel en batch, tirée du tkprof :
Code:

1 2 3 4 5 SELECT t23.FLAG, t23.FLAG2, SUM(t23.AMOUNT), COUNT(t23.RECORD_ID) FROM matable t23 WHERE t23.ACCT_ID = 77 AND (t23.STATE = 3 OR t23.STATE = 7) GROUP BY t23.FLAG, t23.FLAG2
=> A vrai dire, je suis parti de la trace connaître la source de nos problèmes de perf... et donc créer la VM

2) Je suis conscient que c'est l'optimiseur qui réécrit. Je sais également que ça dépend des paramètres sur la session ; je vais refaire des tests avec mon DBA cet aprèm, notamment pour comparer l'ensemble des paramètres entre les deux contexts d'exécution : merci d'avoir mis le doigt dessus (en fait, on avait comparé seulement quelques paramètres en direct, tel que query_rewrite_enabled, query_rewrite_integrity, ...)

Par contre, ce qui m'a traumatisé, c'est l'option Kill/Trace session de TOAD : je pensais que la vision des sessions que ça donne était exacte, notament au niveau des exec plans. Ce n'est pas le cas, puisque la trace montre un autre plan.

A partir de ton exemple j'ai fabriqué un petit jeux d'essaie que j'ai testé sur une base Oracle 9

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
drop materialized view ma_vm;
/
drop table matable
/
create table matable (
   tableid      number,
   record_id    number,
   state        number(1),
   flag         varchar2(1), 
   flag2        varchar2(1), 
   amount       number
)
/
alter table matable
add constraint pk_matable primary key(tableid, record_id)
/
insert into matable 
  Select 77 table_id, level record_id, 3 state, 'Y', 'O', trunc(dbms_random.value * 100) amount
    from dual
    connect by level <= 100   
/  
CREATE materialized VIEW ma_vm
build immediate
enable query rewrite AS
SELECT   t23.flag, t23.flag2, SUM (t23.amount), COUNT (t23.record_id) 
FROM matable t23 
WHERE t23.tableid = 77 
AND (t23.state = 3 OR t23.state = 7)
GROUP BY t23.flag, t23.flag2
/
set autotrace traceonly explain
SELECT t23.FLAG, t23.FLAG2, SUM(t23.AMOUNT), COUNT(t23.RECORD_ID)
FROM
 matable t23 WHERE t23.tableid = 77 AND (t23.STATE = 3 OR t23.STATE = 7)
  GROUP BY t23.FLAG, t23.FLAG2
/
 
...
SQL> SQL>   2    3    4    5
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=327 Bytes
          =9810)
 
   1    0   TABLE ACCESS (FULL) OF 'MA_VM' (Cost=2 Card=327 Bytes=9810
          )

On voit bien l'utilisation de la vue. Essaie quelque chose de similaire dans une sesion sqlplus et ensuite dans ton batch.

PS. Par contre j'ai changé dans ta requête acct_id par tableid !

C'est vraiment nécessaire que je recrée, sachant que j'ai la même définition au "build immediate" près ?

Sinon, j'ai relancé l'autotrace : toujours le même résultat en SQL*Plus, la VM est bien choisie...

Code:

1
2
3
4
5
6
7
 
SQL> show parameter query
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      FORCE
query_rewrite_integrity              string      STALE_TOLERATED

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SQL> set autotrace traceonly explain
 
SQL> SELECT t23.flag, t23.flag2, sum(t23.amount), count(t23.record_id)
  2  from matable t23
  3  where acct_id = 77
  4  and (t23.state = 3 or t23.state = 7)
  5  group by t23.flag, t23.flag2
  6  /
Elapsed: 00:00:00.00
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3602202693
 
----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     2 |    22 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| MA_VM |     2 |    22 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Bon à part ça, on a tracé les sessions SQL*Plus et Batch pour comparer au moins les contextes d'exécution... (flush shared pool + trace 10053)

Et les paramètres de l'optimiseur sont les mêmes. De plus, dans le cas du batch, il ne considère même pas l'accès à la VM.

(Par contre, la trace, c'est vraiment chiant à lire...)

03/02/2009, 11h49
mnitu

Le batch et sqlplus c’est le même utilisateur de connexion ? La trace SQL (alter session set sql_trace=true ou similaire) via exécution sqlplus utilise la VM ?

Ouais, c'est le même user.
La trace, c'est event 10053. Pour la session SQL*Plus, ça donne :

Code:

1
2
3
4
5
6
7
8
9
10
 
============
Plan Table
============
-----------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name         | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |              |       |       |     2 |           |
| 1   |  MAT_VIEW REWRITE ACCESS FULL | ma_vm|     2 |    22 |     2 |  00:00:01 |
-----------------------------------------------------+-----------------------------------+