Blog

le 30/03/2010
Lionel Magallon
Infrastructures

Colonnes virtuelles et tuning SQL

La version 11gR1 de la base de données a introduit le concept de colonnes virtuelles. Celles-ci permettent de créer des colonnes qui contiennent des données calculées « à la volée » lors des requêtes et non-pas stockées en base. Elles permettent ainsi de faciliter le travail des développeurs, mais pas seulement. Les colonnes virtuelles permettent d’accélérer fortement certaines requêtes de la forme suivante :
select * from table1 where col1 > col2
Comment ça ?
OK, créons une table qui va servir pour notre exemple :
create table scott.t1 as select object_id id,created updated from dba_objects ; alter table scott.t1 add ( accessed date ) ; alter table scott.t1 add primary key(id) ;
Créons maintenant quelques indexes qui ne serviront à rien mais qui permettront de comprendre les réflexions de l’optimiseur :
create index scott.idx_t1_updated on scott.t1(updated) ; create index scott.idx_t1_accessed on scott.t1(accessed) ;
Remplissons la colonne accessed ; on peut noter que pour l’instant toutes les valeurs de la colonne updated sont inférieures aux valeurs de la colonne accessed :
update scott.t1 set accessed=SYSDATE ; commit ;
On va maintenant créer la valeur intéressante de notre test (la valeur de la colonne updated est supérieure à accessed uniquement pour l’objet 2403) :
update scott.t1 set updated=(SYSDATE+1) where id= 2403 ; commit ;
Calculons les statistiques de la table :
exec dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T1', estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 8,cascade=>TRUE);
Maintenant, on voudrait déterminer les objets dont la date de mise à jour est supérieure à la date de dernier accès :
select * from scott.t1 where updated > accessed ; ID UPDATED ACCESSED ---------------------- ------------------------- ------------------------- 2403 30-MAR-10 29-MAR-10
Comme vous vous en doutiez, par défaut le plan n’est pas optimal : la base fait un « full scan » pour remonter un seul enregistrement (à noter que comme prévu nos indexes créés au début sur les colonnes updated et accessed ne servent strictement à rien) :
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 111 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 787 | 16527 | 111 (1)| 00:00:03 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("UPDATED">"ACCESSED")
De prime abord, difficile de faire mieux. Heureusement il y les colonnes virtuelles : on peut ainsi en créer une avec un index dessus qui va nous dire si la date de mise à jour est supérieure à la date de dernier accès :
alter table scott.t1 add ( upd_sup_acc as (updated - accessed) ) ;
Créeons un index dessus :
create index scott.idx_t1_usa on scott.t1(upd_sup_acc) ;
Recalculons les statistiques :
exec dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'T1', estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 8,cascade=>TRUE);
Relançons maintenant la requête légèrement modifiée pour déterminer les objets dont la date de mise à jour est supérieure à la date de dernier accès :
select * from scott.t1 where (updated - accessed) > 0; ID UPDATED ACCESSED ---------------------- ------------------------- ------------------------- 2403 30-MAR-10 29-MAR-10
Et voici le plan de la requête : ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 5 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 317 | 13631 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T1_USA | 317 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."UPD_SUP_ACC">0)
On a ainsi pu passer d’un cout de 111 à 5.
Evidemment si la majorité des entrées dans la colonne updated est supérieure à accessed, la colonne virtuelle ne sert pas à grand chose. Comme toujours, il est nécessaire d’écrire ses requêtes en ayant à l’esprit les données stockées dans la base.

Lionel Magallon

Voir tous ses articles

10 réflexions sur “Colonnes virtuelles et tuning SQL”

yooo13
13/04/2010 à 06:53

Bonjour,
juste pour en finir avec la question que vous pourriez poser : pourquoi la base ne calcule pas directement un histogramme de fréquence directement ?
Parce que : http://www.oracle.com/technology/deploy/performance/pdf/PS_S961_273961_106-1_FIN_v2.pdf
En gros, la base traque les différentes requêtes sur les colonnes et lorsqu’on lance un calcul de statistiques automatique, il faut d’abord qu’il y ait eu un minimum de requêtes pour que le job de calcul de stats se dise que ca vaut le coup.
yooo13
12/04/2010 à 12:28

Note : si vous avez trop valeurs distinctes (>254, taille maximale des histogrammes), vous pouvez créer une colonne virtuelle du type sign(date_maj – date_ins)
yooo13
12/04/2010 à 11:54

Bonjour,
je pense avoir la solution ; le problème est causé par deux éléments :
– le nombre de valeurs distinctes générées par mes données est plus importantes que celles que vous générez ;
– il n’y pas d’histogramme de fréquence calculé lors du calcul automatique de statitistiques ;
=> quand l’optimiseur veut calculer son cout, comme il n’y a pas d’histogramme sur la colonne, il prend une sélectivité égale à la densité et estime la cardinalité à num_rows*densité, ce qui fait à peu près 980 dans mon cas et qui fait que le calcul de cout d’index est supérieur au calcul de cout du full
=> quand lui fait calculer une histogramme « height-balanced », ie la taille est inférieure au nb de valeurs distinctes :
BEGIN
DBMS_STATS.GATHER_table_STATS (estimate_percent => 100, OWNNAME => ‘NEW_SQL’, TABNAME => ‘HISTO_MVT’,
METHOD_OPT => ‘FOR COLUMNS SIZE 10 MAJ_SUPERIEUR_INS’);
END;
/
select histogram from user_tab_col_statistics where table_name =’HISTO_MVT’
and column_name = ‘MAJ_SUPERIEUR_INS’ ;
HISTOGRAM
—————
HEIGHT BALANCED
il reprend la densité standard car le > 0 fait que les valeurs sont suceptibles d’être sur le même « bucket » (j’arrive pas à traduire, google donne « seau » ou »godet » ). En plus la distribution des buckets fait que la sélectivité n’apparait pas améliorée.
select endpoint_number,endpoint_value from user_tab_histograms where table_name = ‘HISTO_MVT’
and column_name = ‘MAJ_SUPERIEUR_INS’ ;
ENDPOINT_NUMBER ENDPOINT_VALUE
———————- ———————-
0 -101.508206018519
1 -92.5082060185185
2 -81.5082060185185
3 -71.5082060185185
4 -61.5082060185185
5 -51.5082060185185
6 -41.5082060185185
7 -30.5082060185185
8 -20.5082060185185
9 -10.5082060185185
10 1.00048611111111
=> si je lui fait calculer un histogramme « frequency », l’optimiseur prend bien l’index car il a pu déterminer que la sélectivité est faible et qu’il est plus intéressant de passer par un index :
BEGIN
DBMS_STATS.GATHER_table_STATS (estimate_percent => 100, OWNNAME => ‘NEW_SQL’, TABNAME => ‘HISTO_MVT’,
METHOD_OPT => ‘FOR COLUMNS SIZE 110 MAJ_SUPERIEUR_INS’);
END;
/
select histogram from user_tab_col_statistics where table_name =’HISTO_MVT’
and column_name = ‘MAJ_SUPERIEUR_INS’ ;
HISTOGRAM
—————
FREQUENCY
ENDPOINT_NUMBER ENDPOINT_VALUE
———————- ———————-
2026 -100.508206018519
3053 -99.5082060185185
4059 -98.5082060185185
5034 -97.5082060185185
… plein de lignes …
99011 -2.50820601851852
99999 -1.50820601851852
100000 1.00048611111111
JM Souchard
02/04/2010 à 07:15

Merci de prendre un peu de temps pour répondre 😉
Réponse aux deux questions : Oui j’avais commencé par faire l’Explain plan sur date_maj – date_ins : cela donnait exactement le même coût (105) !
Et oui je suis sûr de l’unicité de mon id (et notamment du 50000 puisque ma fonction generate_series génère un nombre de ligne déterminé par l’intervalle avec comme variable generate_series qui fait plus 1 à chaque fois. De plus la PRIMARY KEY assure cette unicité) : j’ai exécuté le SELECT COUNT qui ramène bien 1.
J’ai réexécuté votre exemple, cela fonctionne… (donc pas de problème de variable d’environnement ou autre…)
J’ai ensuite remplacé dans la requête et l’explain plan « (updated – accessed) » par « upd_sup_acc » (autant utiliser la colonne virtuelle créée) cela donne le même bon plan d’exécution avec un cost de 6 (toujours sur votre exemple) !
Mais sur le mien, je reste toujours dans les deux cas avec un coût de 105 supérieur au 104 …
1. yooo13
  02/04/2010 à 09:10
  
  J’ai rejoué vos scripts et ca fait du full aussi chez moi. C’est fun. Je vais regarder et essayer de comprendre pourquoi. Je vous tiendrai au courrant dès que j’ai plus d’infos.
2. yooo13
  02/04/2010 à 10:58
  
  J’ai l’impression que c’est un bug de l’optimiseur : j’ai rejouer votre procédure avec 1 million de lignes
  dans la table histo_mvt :
  – pour la requête select * from histo_mvt where date_maj – date_ins > 0, l’optimiseur passe en full avec un cout de 803 et la requête s’exécute en 0,752 s ;
  – pour la requête select /*+ INDEX(histo_mvt IDX_HISTO_MVT_SUPERIEUR) */ * from histo_mvt where date_maj – date_ins > 0, l’optimiseur utilise l’index avec un cout de 3449 et la requête s’exécute en 0,001 s !!
  La question est : pourquoi calcule-t-il un cout si élevé avec l’index ? hypothèse : ca vient du clustering_factor de l’index qui vaut 312267 pour 1000000 de lignes
  => j’ai créé un autre index IDX_HISTO_MSUPI sur sign(date_maj – date_ins) ; on obtient un clustering_factor de 3597, ce qui est franchement mieux :
  – pour la requête select * from histo_mvt where sign(date_maj – date_ins) = 1, l’optimiseur passe quand même en full avec un cout de 804 et la requête s’exécute en 0,828 s ;
  – pour la requête select /*+ INDEX(histo_mvt IDX_HISTO_MSUPI) */ * from histo_mvt where sign(date_maj – date_ins) = 1, l’optimiseur utilise l’index avec un cout de 2853 et la requête s’exécute en 0,003 s !!
  à suivre …
  1. JM Souchard
    08/04/2010 à 06:56
    
    Merci pour ces explications.
    J’ai aussi réalisé des essais complémentaires qui me laissent à nouveau perplexe. Mon script devient ceci :
    DROP TYPE new_sql.table_nombre;
    CREATE OR REPLACE TYPE new_sql.ligne_gen AS OBJECT
    — Donnée utilisée pour le générateur de lignes
    (generate_series NUMBER(10));
    CREATE OR REPLACE TYPE new_sql.table_nombre AS TABLE OF new_sql.ligne_gen;
    — Table de génération
    CREATE OR REPLACE FUNCTION generate_series(borne_inf NUMBER,borne_sup NUMBER) RETURN table_nombre PIPELINED IS
    — Fonction qui génère un nombre de lignes compris entre borne_inf et borne_sup
    ligne_generee ligne_gen:=ligne_gen(NULL);
    BEGIN
    FOR i IN borne_inf..borne_sup LOOP
    ligne_generee.generate_series:=i;
    PIPE ROW(ligne_generee);
    END LOOP;
    RETURN;
    END generate_series;
    /
    DROP TABLE new_sql.histo_mvt;
    CREATE TABLE new_sql.histo_mvt AS
    SELECT generate_series AS id,
    SYSDATE AS date_ins,
    TO_DATE(’01/01/2009′,’DD/MM/YYYY’)+
    TRUNC(DBMS_RANDOM.VALUE(0,TRUNC(SYSDATE-TO_DATE(’01/01/2010′,’DD/MM/YYYY’)))) AS date_maj
    — Génération d’une date aléatoire comprise entre 01/01/2010 et la date du jour
    FROM TABLE(new_sql.generate_series(1,100000));
    ALTER TABLE new_sql.histo_mvt ADD CONSTRAINT pk_histo_mvt PRIMARY KEY(id);
    CREATE INDEX new_sql.idx_histo_mvt_date_maj ON new_sql.histo_mvt(date_maj);
    CREATE INDEX new_sql.idx_histo_mvt_date_ins ON new_sql.histo_mvt(date_ins);
    UPDATE new_sql.histo_mvt SET date_maj=SYSDATE+1 WHERE id=4200;
    COMMIT;
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ‘NEW_SQL’, TABNAME => ‘HISTO_MVT’,ESTIMATE_PERCENT => 100, –
    METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,CASCADE=>TRUE);
    SELECT * FROM new_sql.histo_mvt WHERE date_maj > date_ins;
    EXPLAIN PLAN FOR SELECT * FROM new_sql.histo_mvt WHERE date_maj > date_ins;
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,NULL,’SERIAL’));
    /*
    ——————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————-
    | 0 | SELECT STATEMENT | | 2697 | 56637 | 104 (1)| 00:00:02 |
    |* 1 | TABLE ACCESS FULL| HISTO_MVT | 2697 | 56637 | 104 (1)| 00:00:02 |
    ——————————————————————————-
    Predicate Information (identified by operation id):
    —————————————————
    1 – filter(« DATE_MAJ »> »DATE_INS »)
    */
    ALTER TABLE new_sql.histo_mvt ADD (maj_superieur_ins AS (date_maj – date_ins)) ;
    CREATE INDEX new_sql.idx_histo_mvt_superieur ON new_sql.histo_mvt(maj_superieur_ins) ;
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ‘NEW_SQL’, TABNAME => ‘HISTO_MVT’,ESTIMATE_PERCENT => 100, –
    METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,CASCADE=>TRUE);
    SELECT * FROM new_sql.histo_mvt WHERE maj_superieur_ins > 0;
    EXPLAIN PLAN FOR SELECT * FROM new_sql.histo_mvt WHERE maj_superieur_ins > 0;
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,NULL,’SERIAL’));
    /*
    ——————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————-
    | 0 | SELECT STATEMENT | | 1099 | 47257 | 105 (2)| 00:00:02 |
    |* 1 | TABLE ACCESS FULL| HISTO_MVT | 1099 | 47257 | 105 (2)| 00:00:02 |
    ——————————————————————————-
    Predicate Information (identified by operation id):
    —————————————————
    1 – filter(« MAJ_SUPERIEUR_INS »>0)
    */
    –Essai de création d’index bitmap sur fonction de colonne virtuelle :
    –CREATE BITMAP INDEX new_sql.idx_bmp_histo_mvt_superieur ON new_sql.histo_mvt(SIGN(maj_superieur_ins));
    –Ne fonctionne pas : ORA-54034: les colonnes virtuelles ne sont pas autorisées dans les expressions d’index fonctionnel
    CREATE BITMAP INDEX new_sql.idx_bmp_histo_mvt_superieur ON new_sql.histo_mvt(SIGN(date_maj – date_ins));
    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ‘NEW_SQL’, TABNAME => ‘HISTO_MVT’,ESTIMATE_PERCENT => 100, –
    METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’,CASCADE=>TRUE);
    SELECT * FROM new_sql.histo_mvt WHERE maj_superieur_ins > 0;
    EXPLAIN PLAN FOR SELECT * FROM new_sql.histo_mvt WHERE maj_superieur_ins > 0;
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘PLAN_TABLE’,NULL,’SERIAL’));
    /*
    ——————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————————-
    | 0 | SELECT STATEMENT | | 1 | 43 | 4 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| HISTO_MVT | 1 | 43 | 4 (0)| 00:00:01 |
    |* 2 | INDEX RANGE SCAN | IDX_HISTO_MVT_SUPERIEUR | 1 | | 3 (0)| 00:00:01 |
    ——————————————————————————–
    PLAN_TABLE_OUTPUT
    ——————————————————————————————————-
    Predicate Information (identified by operation id):
    —————————————————
    2 – access(« MAJ_SUPERIEUR_INS »>0)
    */
    Résumé :
    Premier essai avec index classique : fonctionnement normal, il n’utilise pas l’index et fait un FULL TABLE SCAN.
    Deuxième essai avec index sur colonne virtuelle : fonctionnement inexpliqué, il n’utilise pas l’index sur colonne virtuelle et continue à faire un FULL TABLE SCAN.
    Troisième essai avec index bitmap sur signe de la différence des deux colonnes (index sur fonction de colonne virtuelle interdit cf. message d’erreur dans script) : il utilise l’index créé lors de l’essai 2 🙁
yooo13
01/04/2010 à 17:26

Bonjour,
vous pouvez faire un explain plan de SELECT * FROM new_sql.histo_mvt WHERE date_maj – date_ins > 0
?
1. yooo13
  01/04/2010 à 17:40
  
  Bon j’ai fait le test, l’optimiseur prend bien l’index aussi. vous êtes sûr qu’il n’y a qu’une seule ligne avec
  date_maj > date_ins ?
  c’est quoi le résultat de SELECT count(*) FROM new_sql.histo_mvt WHERE date_maj – date_ins > 0
JM Souchard
01/04/2010 à 15:10

Bonjour,
Intéressant article : j’ai voulu le rejouer en l’adaptant (notamment, j’implémente une fonction de génération d’un ensemble de valeurs et je génère une date aléatoire, en plus de le franciser), le problème c’est que cela ne marche pas… C’est même plus couteux !
Voici le code de l’essai :
DROP TYPE new_sql.table_nombre;
CREATE OR REPLACE TYPE new_sql.ligne_gen AS OBJECT
— Donnée utilisée pour le générateur de lignes
(generate_series NUMBER(10));
CREATE OR REPLACE TYPE new_sql.table_nombre AS TABLE OF new_sql.ligne_gen;
— Table de génération
CREATE OR REPLACE FUNCTION generate_series(borne_inf NUMBER,borne_sup NUMBER) RETURN table_nombre PIPELINED IS
— Fonction qui génère un nombre de lignes compris entre borne_inf et borne_sup
ligne_generee ligne_gen:=ligne_gen(NULL);
BEGIN
FOR i IN borne_inf..borne_sup LOOP
ligne_generee.generate_series:=i;
PIPE ROW(ligne_generee);
END LOOP;
RETURN;
END generate_series;
/
DROP TABLE new_sql.histo_mvt;
CREATE TABLE new_sql.histo_mvt AS
SELECT generate_series AS id,
SYSDATE AS date_ins,
TO_DATE(’01/01/2010′,’DD/MM/YYYY’)+
TRUNC(DBMS_RANDOM.VALUE(0,TRUNC(SYSDATE-TO_DATE(’01/01/2010′,’DD/MM/YYYY’)))) AS date_maj
— Génération d’une date aléatoire comprise entre 01/01/2010 et la date du jour
FROM TABLE(new_sql.generate_series(1,100000));
ALTER TABLE new_sql.histo_mvt ADD PRIMARY KEY(id);
CREATE INDEX new_sql.idx_histo_mvt_date_maj ON new_sql.histo_mvt(date_maj);
CREATE INDEX new_sql.idx_histo_mvt_date_ins ON new_sql.histo_mvt(date_ins);
UPDATE new_sql.histo_mvt SET date_maj=SYSDATE+1 WHERE id=50000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘NEW_SQL’, tabname => ‘HISTO_MVT’,estimate_percent => 100, –
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,cascade=>TRUE);
SELECT * FROM new_sql.histo_mvt WHERE date_maj > date_ins;
EXPLAIN PLAN FOR SELECT * FROM new_sql.histo_mvt WHERE date_maj > date_ins;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘plan_table’,null,’serial’));
/*
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 2697 | 56637 | 104 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| HISTO_MVT | 2697 | 56637 | 104 (1)| 00:00:02 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(« DATE_MAJ »> »DATE_INS »)
*/
ALTER TABLE new_sql.histo_mvt ADD (maj_superieur_ins AS (date_maj – date_ins)) ;
CREATE INDEX new_sql.idx_histo_mvt_superieur ON new_sql.histo_mvt(maj_superieur_ins) ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘NEW_SQL’, tabname => ‘HISTO_MVT’,estimate_percent => 100, –
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,cascade=>TRUE);
SELECT * FROM new_sql.histo_mvt WHERE maj_superieur_ins > 0;
EXPLAIN PLAN FOR SELECT * FROM new_sql.histo_mvt WHERE maj_superieur_ins > 0;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(‘plan_table’,null,’serial’));
/*
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1099 | 47257 | 105 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| HISTO_MVT | 1099 | 47257 | 105 (2)| 00:00:02 |
——————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(« MAJ_SUPERIEUR_INS »>0)
*/
Et cela me laisse perplexe… Je ne vois pas où est l’erreur !

Les commentaires sont fermés.

Blog

Colonnes virtuelles et tuning SQL

10 réflexions sur “Colonnes virtuelles et tuning SQL”

Articles récents