Overblog
Suivre ce blog
Administration Créer mon blog
4 décembre 2013 3 04 /12 /décembre /2013 15:02

L'un des moyens d'étudier l'exécution d'une requête SQL, c'est l'autotrace.

L'autotrace s'appuie sur les statistiques mesurées sur la session, pour en sortir un différentiel des grandeurs en question avant / après exécution de la requête.

 

set autot traceonly statistics

SELECT count(*)               

FROM all_objects              

 

Statistics                                                

----------------------------------------------------------

          8  recursive calls                              

          0  db block gets                                

     102739  consistent gets                              

        140  physical reads                               

          0  redo size                                    

        337  bytes sent via SQL*Net to client             

        350  bytes received via SQL*Net from client       

          2  SQL*Net roundtrips to/from client            

        842  sorts (memory)                               

          0  sorts (disk)                                 

          1  rows processed                               

 

Mais ça ne marche pas sur l'exécution d'un bloc ou procédure PL/SQL : 

 

DECLARE            

  i number;        

BEGIN              

  SELECT count(*)  

  INTO i           

  FROM all_objects;

END;               

/                  

 

PL/SQL procedure successfully completed.

 

Il faut donc faire le boulot soit-même !

 

1) Le modèle de données : 

 

A. Le vue v$statname donne la liste des grandeurs mesurées : 

 

STATISTIC# : identifiant de la grandeur

NAME : nom de la statistique

CLASS : identifiant de classe

STAT_ID : identifiant numérique

 

Parmi toutes les grandeurs mesurées, on retrouve celles de l'autotrace (à l'exception du 'rows processed') : 

 

SELECT *                                                  

FROM v$statname                                           

WHERE name IN ( 'recursive calls',                        

                'db block gets',                          

                'consistent gets',                        

                'physical reads',                         

                'redo size',                              

                'bytes sent via SQL*Net to client',       

                'bytes received via SQL*Net from client', 

                'SQL*Net roundtrips to/from client',      

                'sorts (memory)',                         

                'sorts (disk)',                           

                'rows processed');                        

 

STATISTIC# NAME                                    CLASS    STAT_ID

---------- --------------------------------------------- ----------

         7 recursive calls                             1 2656001462

        47 db block gets                               8 1480709069

        50 consistent gets                             8 4162191256

        54 physical reads                              8 2263124246

       134 redo size                                   2 1236385760

       343 bytes sent via SQL*Net to client            1 2967415760

       344 bytes received via SQL*Net from client      1  161936656

       345 SQL*Net roundtrips to/from client           1  159846526

       349 sorts (memory)                             64 2091983730

       350 sorts (disk)                               64 2533123502

 

B. La colonne CLASS quant à elle corresond à une liste des classes suivantes : 

 

1 - User

2 - Redo

4 - Enqueue

8 - Cache

16 - OS

32 - Real Application Clusters

64 - SQL

128 - Debug

 

Comment ça une liste dans une seule colonne, le modèle ne serait-il pas normalisé ??

 

WITH classname AS (                                       

SELECT 1 class, 'User' class_name FROM DUAL UNION ALL     

SELECT 2, 'Redo' FROM DUAL UNION ALL                      

SELECT 4, 'Enqueue' FROM DUAL UNION ALL                   

SELECT 8, 'Cache' FROM DUAL UNION ALL                     

SELECT 16, 'OS' FROM DUAL UNION ALL                       

SELECT 32, 'Real Application Clusters' FROM DUAL UNION ALL

SELECT 64, 'SQL' FROM DUAL UNION ALL                      

SELECT 128, 'Debug' FROM DUAL                             

)                                                         

SELECT *                                                  

FROM v$statname                                           

WHERE class not in (SELECT class                          

                    FROM classname)                       

 

STATISTIC# NAME                                 CLASS    STAT_ID

---------- ------------------------------------------- ---------

        80 DBWR fusion writes                   40 2313150541   

       146 gc cr blocks served                  40 1075941831   

...                                                             

       328 buffer is pinned count               72 1300470380   

       329 buffer is not pinned count           72 2670431739   

       330 no buffer to keep pinned count       72 3528454686   

 

Les classes 40 et 72 représentes respectivement 32 + 8 et 64 + 8... c'est de l'information aggrégée codée en binaire !

Pour la jointure avec les noms de classe, il suffit donc d'utiliser BITAND :

 

WITH classname AS (                                         

SELECT 1 class, 'User' class_name FROM DUAL UNION ALL       

SELECT 2, 'Redo' FROM DUAL UNION ALL                        

SELECT 4, 'Enqueue' FROM DUAL UNION ALL                     

SELECT 8, 'Cache' FROM DUAL UNION ALL                       

SELECT 16, 'OS' FROM DUAL UNION ALL                         

SELECT 32, 'Real Application Clusters' FROM DUAL UNION ALL  

SELECT 64, 'SQL' FROM DUAL UNION ALL                        

SELECT 128, 'Debug' FROM DUAL                               

)                                                           

SELECT name, (SELECT wm_concat(class_name)                  

             FROM classname b                               

             WHERE bitand(a.class, b.class) > 0) class_names

FROM v$statname a                                           

WHERE class IN (40,72)                                      

 

NAME                                  CLASS_NAMES                    

------------------------------------- -------------------------------

DBWR fusion writes                    Cache,Real Application Clusters

gc cr blocks served                   Cache,Real Application Clusters

...                                                                  

buffer is pinned count                Cache,SQL                      

...                                                                  

 

Au passage, la représentation binaire + BITAND est un moyen rigolo, illisible et sous performant de faire des opérations ensemblistes, jusqu'à la division relationnelle... mais passons !

 

C. Les valeurs effectivement mesurée sont disponibles via la vue v$mystat (v$sesstat représentant l'ensemble des mesures pour toutes les sessions)


SID : le SID de la session concernée (toujours le même dans v$mystat)

STATISTIC# : le numéro de statistique, pointant sur la colonne homonyme de la table v$statname

VALUE : la valeur mesurée

 

2) Consolider des snapshots : runstats_pkg

 

L'idée est maintenant de prendre une photo de la valeur des statistiques avant exécution, d'en reprendre une autre après, et d'afficher la différence.

Pour cela, reprenons le travail de l'oncle Tom et son package runstats_pkg : 

 

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

 

.. qui s'appuie sur une vue qui lie les vues v$statname et v$mystat, et d'un GTT qui permet de stocker les photos de statistiques.

 

Ce package par contre, est construit pour comparer deux exécutions différentes : 

rs_start : prise de photo initiale

rs_middle : prise de photo après la première exécution

rs_stop : prise de photo après la deuxième exécution et comparaison des deux photos (ou plutôt des différences entre photo 2 et 1, et photo 3 et 2)

 

Pour mesurer les stats sur une seule exécution, on va donc juste ajouter une procédure de fin supplémentaire rs_stop_single : 

 

create or replace package runstats_pkg                              

as                                                                  

    procedure rs_start;                                             

    procedure rs_middle;                                            

    procedure rs_stop( p_difference_threshold in number default 0 );

    procedure rs_stop_single;                                       

end;                                                                

/                                                                   

 

create or replace package body runstats_pkg                         

as                                                                  

[...]                                                               

 

procedure rs_stop_single is                                         

begin                                                               

    rs_middle;                                                      

    dbms_output.put_line                                            

    ( 'Bloc ran in ' || g_run1 || ' cpu hsecs' );                   

 

    dbms_output.put_line( chr(9) );                                 

 

    dbms_output.put_line                                            

    ( rpad( 'Name', 30 ) || lpad( 'Value', 12 ));                   

 

    for x in                                                        

    ( select rpad( replace(a.name,'STAT...'), 30 ) ||               

             to_char( b.value-a.value, '999,999,999' ) data         

        from run_stats a                                            

          join run_stats b on a.name = b.name                       

       where a.runid = 'before'                                     

         and b.runid = 'after 1'                                    

         and replace(a.name, 'STAT...') in ( 'recursive calls',     

                'db block gets',                                    

                'consistent gets',                                  

                'physical reads',                                   

                'redo size',                                        

                'bytes sent via SQL*Net to client',                 

                'bytes received via SQL*Net from client',           

                'SQL*Net roundtrips to/from client',                

                'sorts (memory)',                                   

                'sorts (disk)',                                     

                'rows processed')                                   

    ) loop                                                          

        dbms_output.put_line( x.data );                             

    end loop;                                                       

end;                                                                

[...]                                                               

 

 

Cette procédure est copiée - collée - légèrement modifiée de la procédure rs_stop (au lieu de faire la différence sur 2 runs, simplement afficher les résultas de l'unique run et nettoyer ce qui ne sert à rien).

Allez, on teste : 

 

 

exec runstats_pkg.rs_start;

 

DECLARE                    

  i number;                

BEGIN                      

  SELECT count(*)          

  INTO i                   

  FROM all_objects;        

END;                       

/                          

 

PL/SQL procedure successfully completed.

 

exec runstats_pkg.rs_stop_single;

 

Bloc ran in 46 cpu hsecs

 

Name                          Value

bytes sent via SQL*Net          592

bytes received via SQL*       1,005

SQL*Net roundtrips to/f           4

sorts (memory)                1,344

sorts (disk)                      0

recursive calls                   4

db block gets                    20

consistent gets             112,153

physical reads                    0

redo size                     3,036

 

PL/SQL procedure successfully completed.

 

Voilà !

Bien entendu, une fois que ces résultats sont consolidés dans la GTT, on peut chercher n'importe quelle valeur parmi la tripotée de statistiques mesurées...

 

Et de manière plus générale, le package peut être retouché à souhait. 

Pour ma part, je n'aime pas trop le rs_stop qui affiche les différences selon un seuil exprimé en valeurs absolues... (sachant qu'on mesure des statistiques qui n'ont pas même unité)

Published by Pacman - dans SGBD
commenter cet article
18 novembre 2013 1 18 /11 /novembre /2013 13:08

 

Quand on veut savoir pourquoi une procédure rame, on peut par exemple la tracer.

"alter session set events 10046 trace name context forever, level 8" est en ce sens un outil efficace, car il permet de déterminer ce qui a fait attendre l'exécution.

 

C'est donc ce que j'ai fait l'autre jour, et voilà le résultat : 

 

INSERT INTO MA_TABLE

VALUES (...)

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  47914     60.45     573.76      93619      11588    1221462       47914

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    47915     60.45     573.76      93619      11588    1221462       47914

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 189     (recursive depth: 1)

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                     93619        0.83        517.81

  latch free                                      1        0.00          0.00

  latch: shared pool                              1        0.00          0.00

  buffer busy waits                               1        0.00          0.00

  undo segment extension                          1        0.00          0.00

  latch: object queue header operation            2        0.00          0.00

********************************************************************************

 

On a donc attendu environ 9 minutes en lecture d'index pour insérer 47K lignes.

 

C'est bien beau tout ça, mais quel(s) index ? La répartition du temps d'attente est homogène ?

 

Quand on regarde dans le fichier trace brut, on voit plein de lignes de ce genre : 


WAIT #78: nam='db file sequential read' ela= 8907 file#=26 block#=1865617 blocks=1 obj#=1717754 tim=1352309684636336

 

Et l'obj#, quand on vérifie dans dba_objects, c'est MA_TABLE_FK_ART.

 

On va donc travailler sur cette lueur d'espoir.

 

A. Tout d'abord, extraire les waits : 

 

grep '^WAIT' mon_fichier_trace.trc > trc_dir/my_trc_file.trc

 

B. Ensuite, créer une table externe pour lire ce fichier en SQL (des compétences correctes en shell Unix m'auraient permis de parser tout ça en live)

 

CREATE DIRECTORY trc_dir AS '/home/oracle/trc_dir';

 

CREATE TABLE trc_load  

      (msg varchar2(255))

    ORGANIZATION EXTERNAL

      (TYPE ORACLE_LOADER

      DEFAULT DIRECTORY trc_dir

      ACCESS PARAMETERS

        (RECORDS DELIMITED BY NEWLINE

         FIELDS (msg char(255))

        )

      LOCATION ('my_trc_file.trc')

      );  

 

C. L'analyse de la trace

 

select object_name

  , regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*') evt

  , sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 totelaela 

  , count(*) cnt

from trc_load a

  join dba_objects b on regexp_substr(regexp_substr(msg, 'obj#=([[:alnum:]]+) tim'), '[[:digit:]]+') = b.object_id

group by regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*'), object_name  

order by sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 desc     

 

OBJECT_NAME  EVT  TOTELAELA  CNT
MA_TABLE_PK  'db file sequential read' 261.571248 45877
MA_TABLE_ART_FK  'db file sequential read' 254.222979 47453
... 'db file sequential read'    
MA_TABLE_DEV_FK  'db file sequential read' 1.097991 174
... 'db file sequential read'    
MA_TABLE_PHO_FK  'db file sequential read' 0.951472 123
MA_TABLE_DEP_FK  'db file sequential read' 0.820889 4555

 

A présent, on peut enfin comprendre et envisager des solutions : 

- Cette table consolide pour une occurence donnée, la photo du stock d'un magasin. 

L'index PK est ordonné ainsi (article, courleur, taille, photo, magasin). Ce qui veut donc dire que le clustering factor est particulièrement mauvais, puisque les insertions se font par le module qui rame, par photo sur un magasin.

=> En réordonnançant la PK, (photo, magasin, article, courleur, taille), il n'y a plus aucune attente pour cet index

- L'index FK article est extrêmement redondante, puisque le nombre de références distinctes est finalement relavitement restreinte, mais dupliquées pour chaque photo de chaque magasin.

=> La compression de l'index divise sa taille part 3, réduisant également le nombre de blocs lus et mis à jour.

Published by Pacman - dans SGBD
commenter cet article
22 octobre 2013 2 22 /10 /octobre /2013 15:02

J'avais lu, il y a très longtemps de cela, une astuce de l'oncle Tom pour indexer uniquement quelques valeurs très sélectives d'une colonne.

Typiquement un flag ou statut de traitement.

 

1) FBI is watching you

La vieille méthode avant 11G : 

 

CREATE TABLE test_ind AS

SELECT level n, CASE WHEN level <= 100 then 1 ELSE 0 END flag

FROM DUAL

CONNECT BY LEVEL <= 1000000;

 

CREATE INDEX test_ind_ix ON test_ind(flag);

CREATE INDEX test_ind_ix2 ON test_ind(nullif(flag, 0));

 

exec dbms_stats.gather_table_stats(null, 'TEST_IND', cascade => true)

 

La mécanique se base sur le fait que lorsque toutes les valeurs de la clef à indexer sont nulles, l'entrée n'est pas stockée dans l'index.

Créer un Function Based Index avec NULLIF permet donc d'exclure les valeurs non désirables de l'index.

 

Regardons maintenant quelques différences entre l'index "normal" et l'index custom : 

 

SELECT index_name, blevel, leaf_blocks, index_type

FROM dba_indexes

WHERE table_name = 'TEST_IND';

 

INDEX_NAME                         BLEVEL LEAF_BLOCKS INDEX_TYPE

------------------------------ ---------- ----------- ---------------------------

TEST_IND_IX                             2        1812 NORMAL

TEST_IND_IX2                            0           1 FUNCTION-BASED NORMAL

 

- Très peu de blocs pour l'index custom, donc très rapide à scanner et optimal en consommation de ressources

- B-tree level à 0 pour l'index custom, donc deux lectures d'économisées à chaque accès

- Index_type = FUNCTION-BASED NORMAL pour l'index custom

 

Pour utiliser l'index, il faut par contre requêter la colonne après application de la fonction qui définit l'index : 

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE nullif(flag, 0) = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

---------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |   100 |   800 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IND     |   100 |   800 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | TEST_IND_IX2 |   100 |       |     1   (0)|

---------------------------------------------------------------------------------

 

Côté plan d'exécution, on constate que ça torche sévère, et qu'en plus la cardinalité est estimée correctement même sans histogramme !

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE flag = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |   500K|  3906K|   472   (3)| 00:00:06 |

|*  1 |  TABLE ACCESS FULL| TEST_IND |   500K|  3906K|   472   (3)| 00:00:06 |

------------------------------------------------------------------------------

 

... ce qui n'est pas le cas de l'index classique. Il faudrait calculer un histogramme, mais même dans ce cas, il peut y avoir des suprises. (Je montrerais un de ces jours un exemple de prod que j'ai recontré il y a peu)

 

Côté dictionnaire, on constate qu'Oracle a créé une colonne virtuelle cachée, pour stocker la valeur de la fonction, et l'indexer : 

 

SELECT column_name, hidden_column, virtual_column, data_default

FROM dba_tab_cols

WHERE table_name = 'TEST_IND';

 

COLUMN_NAME               HID VIR DATA_DEFAULT

------------------------- --- --- --------------------------------------------

SYS_NC00003$              YES YES CASE "FLAG" WHEN 0 THEN NULL ELSE "FLAG" END

FLAG                      NO  NO

N                         NO  NO

 

Au passage, c'est la majeure différence entre DBA_TAB_COLUMNS et DBA_TAB_COLS : 

l'affichage des colonnes cachées. 

(Pour ma part, je requête évidemment toujours dba_tab_cols parce que c'est plus court à taper...)


2) 11g et virtual column

 

L'autre jour, l'ami Waldar faisait remarquer que c'est une pratique obscure, et qu'on pouvait, à partir de 11g (si je ne me trompe pas), créer une colonne virtuelle et l'indexer.

"Big up" à lui, comme diraient les jeunes aujourd'hui : 

 

ALTER TABLE test_ind ADD ghost_flag NUMBER GENERATED ALWAYS AS (nullif(flag, 0));

 

ALTER TABLE test_ind ADD ghost_flag NUMBER GENERATED ALWAYS AS (nullif(flag, 0))

                         *

ERROR at line 1:

ORA-54015: Duplicate column expression was specified

 

Paf. Oui, au passage, on ne peut créer deux colonnes virtuelles de même définition.

 

DROP INDEX test_ind_ix2;

ALTER TABLE test_ind ADD ghost_flag NUMBER AS (nullif(flag, 0));

CREATE INDEX test_ind_ix3 ON test_ind(ghost_flag);

exec dbms_stats.gather_table_stats(null, 'TEST_IND', cascade => true)

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE ghost_flag = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

---------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |   100 |   800 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IND     |   100 |   800 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | TEST_IND_IX3 |   100 |       |     1   (0)|

---------------------------------------------------------------------------------

 

Effectivement, cela donne le même résultat.

Si ce n'est que la colonne n'est plus cachée bien que virtuelle : 

 

SELECT column_name, hidden_column, virtual_column, data_default

FROM dba_tab_cols 

 WHERE table_name = 'TEST_IND';

 

 

COLUMN_NAME                    HID VIR DATA_DEFAULT

------------------------------ --- --- -------------------------

GHOST_FLAG                     NO  YES CASE "FLAG" WHEN 0 THEN NULL ELSE "FLAG" END

FLAG                           NO  NO

N                              NO  NO

Published by Pacman - dans SGBD
commenter cet article
15 août 2013 4 15 /08 /août /2013 14:21

- Moi : "Salut, ça te dérange si je crée des JOB sur la base de test ? Ca n'ira jamais en prod"

 

- Mon DBA : "Bah pas de problème, je t'ai attribué le rôle DBA, fais toi plais'. Cela dit, si c'est pour des rapports XXXX, ils sont déjà déposés en automatique [...je vous passe les détails]"

 

- Moi : "Non, non, c'est pour Rémy, qui m'a demandé de lui rappeler tous les jours qu'il doit commander le repas"

 

- Mon DBA : "Euh... ouais. Dans ce cas, j'ai une 11GR2 de test pour préparer la migration, je te crée un compte dessus"

 

Bref, je me retrouve avec une 11gR2 et je vais faire joujou avec dbms_schudeler et utl_mail.

 

1) La structure

 

Bon, histoire de faire un peu de paramétrage sans faire d'usine à gaz non plus, on se limite : 

 

Aux données personnes : 

 

PACMANN>CREATE TABLE personne (

  2  id_personne NUMBER NOT NULL PRIMARY KEY,

  3  nom VARCHAR2(50) NOT NULL,

  4  prenom VARCHAR2(50) NOT NULL,

  5  email VARCHAR2(50));

 

Table created.

 

Aux notifications, identifiées fonctionnellement par un code

 

PACMANN>CREATE TABLE message (

  2  id_message NUMBER NOT NULL PRIMARY KEY,

  3  objet VARCHAR2(100) NOT NULL,

  4  corps VARCHAR2(4000),

  5  code_message VARCHAR2(50) NOT NULL UNIQUE);

 

Table created.

 

Une table de lien pour abonner les gens aux notifications : 

 

PACMANN>CREATE TABLE message_destinataire (

  2  id_message NUMBER NOT NULL,

  3  id_personne NUMBER NOT NULL,

  4  CONSTRAINT pk_mess_dest PRIMARY KEY (id_message, id_personne),

  5  CONSTRAINT fk_md_message FOREIGN KEY (id_message) REFERENCES message(id_message),

  6  CONSTRAINT fk_md_personne FOREIGN KEY (id_personne) REFERENCES personne(id_personne));

 

Table created.

 

2) Les données

 

J'insère Rémy et moi. Anonymisés bien sûr dans le post de blog.

On note aussi que la syntaxe INSERT ALL est tout à fait superflue ici, l'esprit étant d'insérer dans plusieurs fois (potentiellement dans des tables différentes) à partir d'une même source.

(Ici dual, puisque comme dit, il s'agit juste d'épater les newbs)

 

PACMANN>INSERT ALL

  2  INTO personne VALUES(1, 'PACMANN', 'Pacmann', 'yyyy@yyyy.com')

  3  INTO personne VALUES(2, 'ANONYMOUS', 'Rémy', 'xxxx@xxxx.com')

  4  SELECT * FROM dual;

 

2 rows created.

 

PACMANN>commit;

 

Commit complete.

 

Dans le message, on voit apparaître une variable $1, qui permettra ensuite de personnaliser les messages à l'exécution.

 

PACMANN>INSERT INTO message

  2  VALUES (1, 'Notification repas',

  3  'N''oublies pas de commander ton repas de midi, $1 !',

  4  'NOTIF_REPAS_MIDI');

 

1 row created.

 

PACMANN>commit;

 

Commit complete.

 

PACMANN>INSERT INTO message_destinataire

  2  SELECT id_message, id_personne

  3  FROM message

  4    CROSS JOIN personne;

 

2 rows created.

 

PACMANN>commit;

 

Commit complete.

 

3) Le package d'envoi des mails : 

 

Dans le package de notification, la procédure send_notif_perso envoie une notification à tous les abonnés.

Comme dit, il suffit d'inclure des $1 et $2 pour personnaliser les messages avec les noms et prénoms des abonnés.

 

J'avais hésité à faire un envoi global à tous les abonnés, ce qui m'aurait permis, pour la première fois de ma vie, d'utiliser listagg pour constituer la chaîne aggrégée des destinataires... tant pis

(C'est pour cela que la procédure s'appelle send_notif_perso, ça appelle un send_notif_global)

 

PACMANN>CREATE OR REPLACE PACKAGE pkg_notif AS

  2  PROCEDURE send_notif_perso(p_code_message IN VARCHAR2);

  3  END pkg_notif;

  4  /

 

Package created.

 

PACMANN>CREATE OR REPLACE PACKAGE BODY pkg_notif

  2  AS

  3

  4    PROCEDURE send_notif_perso(p_code_message IN VARCHAR2) IS

  5

  6    BEGIN

  7        FOR crow IN (

  8            SELECT c.nom, c.prenom, c.email, a.objet, a.corps

  9            FROM message a

 10              INNER JOIN message_destinataire b on a.id_message = b.id_message

 11              INNER JOIN personne c on b.id_personne = c.id_personne

 12            WHERE a.code_message = p_code_message)

 13        LOOP

 14

 15          UTL_MAIL.SEND (

 16              sender => 'Mr_Notif',

 17              recipients => crow.email,

 18              subject => crow.objet,

 19              message => replace(replace(crow.corps, '$1', crow.prenom), '$2', crow.nom));

 20

 21        END LOOP;

 22    END send_notif_perso;

 23

 24  END pkg_notif;

 25  /

 

Package body created.

 

4) La planification

 

Puis on enpaquette tout ça dans un job, qu'on programme du lundi au vendredi à 10h30, sachant que les commandes se ferment à 11h.

 

PACMANN>BEGIN

  2  DBMS_SCHEDULER.create_job (

  3    job_name        => 'notification_repas_midi',

  4    job_type        => 'PLSQL_BLOCK',

  5    job_action      => 'BEGIN pkg_notif.send_notif_perso(''NOTIF_REPAS_MIDI''); END;',

  6    start_date      => sysdate,

  7    repeat_interval => 'freq=weekly;byday=mon,tue,wed,thu,fri;byhour=10;byminute=30',

  8    end_date        => NULL,

  9    enabled         => TRUE,

 10    comments        => 'La notif quotidienne pour ne pas oublier de commander le repas de midi !');

 11  END;

 12  /

 

PL/SQL procedure successfully completed.

 

Un petit lancement manuel du job pour vérifier que ça marche :

 

PACMANN>exec DBMS_SCHEDULER.RUN_JOB('notification_repas_midi');

 

PL/SQL procedure successfully completed.

 

Il n'aura plus d'excuses pour mourir de faim, Rémy. Enfin si, si le job est shooté, si la base joujou est dézinguée, ... bref, si ça arrive, cela sera clairement son destin.

 

5) Quelques compléments

 

J'ai un peu lutté sur quelques points : 

- UTL_MAIL non installé sur la base. En sys : 

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb

alter system set smtp_out_server = ton_serveur scope=both

 

- A partir de 11g, les utilitaires du type UTL_MAIL sont restreint par des ACL (access control list), j'ai bidouillé par l'intermédiaire de ce site : 

http://www.oracleflash.com/36/Oracle-11g-Access-Control-List-for-External-Network-Services.html

(A noter que la première fois, j'ai connement exécuté les exemples sans remplacer par le nom de mon smtp... erghl)

 

- Comme je manquais de patience avec la doc oracle, je suis allé chercher les exemples de définitions de planifications chez Donald (ne le dites pas trop fort, ma fille va vouloir y aller aussi)

http://www.dba-oracle.com/t_dbms_scheduler_examples.htm

 

- Comme j'ai des goûts de chiotte, je change régulièrement de mise en forme pour les requêtes que je poste ici. Vous pouvez laisser un message pour me dire que j'ai vraiment des goûts de chiotte.

Published by Pacman - dans SGBD
commenter cet article
8 août 2013 4 08 /08 /août /2013 09:43

Let's have a look at Laurent Schneider's funny query (that he had already posted on several blogs :))


select from dual where lnnvl(1=0) and lnnvl(1=0) is null;

 

D

-

X

 

Does it mean that lnnvl(1=0) is both true and unknown ?

 

Of course not.

Actually, it is the same issue as the REGEXP_LIKE "function" i played with a few days ago : 

http://pacmann.over-blog.com/article-why-regexp_like-is-more-than-a-function-119416964.html

 

lnnvl Should be used as a condition... and when doing so, it does behave as expected : 

 

SELECT CASE WHEN lnnvl(1=0) THEN 'True' WHEN NOT lnnvl(1=0) THEN 'False' ELSE 'Null' END "1=0"

, CASE WHEN lnnvl(1=1) THEN 'True' WHEN NOT lnnvl(1=1) THEN 'False' ELSE 'Null' END "1=1"

, CASE WHEN lnnvl(1=null) THEN 'True' WHEN NOT lnnvl(1=null) THEN 'False' ELSE 'Null' END "1=null"

, CASE WHEN lnnvl(null=null) THEN 'True' WHEN NOT lnnvl(null=null) THEN 'False' ELSE 'Null' END "null=null"

FROM dual;

 

1=0   1=1   1=nul null=

----- ----- ----- -----

True  False True  True

 

 

 

Remember, lnnvl(cond) is evaluated to true whenever cond is false or null and evaluated to false otherwise, making a simpler syntax for handling null cases in predicates.

 

But now, try this : 

 

SELECT lnnvl(1=0) "1=0"

, lnnvl(1=1) "1=1"

, lnnvl(1=null) "1=null"

, lnnvl(null=null) "null=null"

FROM dual;

 

       1=0        1=1     1=null  null=null

---------- ---------- ---------- ----------


 

lnnvl always returns NULL !

 

But i would not expect lnnvl to return something else... but rather raise an exception, since it cannot return a boolean value within a SQL statement !

 

Also in my opinion, Oracle documentation is wrong when stating : 

 

The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE.

 

It just behaves, and should be used, the same way as REGEXP_LIKE, LIKE, (1 = 0) does : these are logical conditions being evaluated, not functions that return any value.

 

SELECT regexp_like('a', 'a')

FROM dual;

 

SELECT regexp_like('a', 'a')

       *

ERROR at line 1:

ORA-00904: "REGEXP_LIKE": invalid identifier


 

 

Anyway, I wish Oracle SQL logical algebra would just work as any logical algebra does, have REGEXP_LIKE and LNNVL really return a boolean, and having both statements being equivalent : LNNVL(cond) or LNNVL(cond) = true...

 

Lnnvl has always been kind of myth in Oracle, until it became documented in 10gR1 release... but buggy !

Funnily enough, even Tom Kyte was not aware of lnnvl beeing documented (read the comments) : 

http://awads.net/wp/2006/10/11/previously-undocumented-lnnvl-sql-function-buggy/

Published by Pacman - dans SGBD
commenter cet article
6 août 2013 2 06 /08 /août /2013 11:18

Last week, i was quite confused when i read on a forum that Oracle SQL does not support boolean type. Posted question looked like this : 

 

CREATE OR REPLACE FUNCTION test_bool(i number) RETURN boolean AS

BEGIN

return true;

END;

 

Function created.

 

SELECT *

FROM dual

WHERE test_bool(1) = true

 

ERROR at line 3:

ORA-00904: "TRUE": invalid identifier

 

I was extremely confident that it could be working this way : 


SELECT *

FROM dual

WHERE test_bool(1);         

 

... or also like this :  

 

 

SELECT CASE WHEN test_bool(1) THEN 'hello world' END

FROM dual;

 

... but it ends up with the same error message : 

 

ORA-00920: invalid relational operator

 

Why would i expect such a syntax to work ?


SELECT *

FROM dual

WHERE regexp_like('a', 'a');

 

D

-

X

 

SELECT CASE WHEN regexp_like('a', 'a') THEN 'hello world' END

FROM dual;

 

CASEWHENREG

-----------

hello world

 

I often read that regexp_like returns a boolean. While it clearly returns a boolean in PL/SQL, it does not look like it does in SQL...

 

DECLARE

b boolean;

BEGIN

b:=regexp_like('a', 'a');

if b then

dbms_output.put_line('I love UMP !');

end if;

END;

 

I love UMP !

 

By the way, just have a look into the standard package : 

 

SELECT *

FROM dba_source

WHERE line BETWEEN 2312 AND 2318

  AND owner = 'SYS'

  AND name = 'STANDARD'

  AND type = 'PACKAGE'

ORDER BY line

 

TEXT

-----------------------------------------------------------------------

  -- REGEXP_LIKE --

  function REGEXP_LIKE (srcstr   VARCHAR2 CHARACTER SET ANY_CS,

                        pattern  VARCHAR2 CHARACTER SET srcstr%CHARSET,

                        modifier VARCHAR2 DEFAULT NULL)

    return BOOLEAN;

    pragma FIPSFLAG('REGEXP_LIKE', 1452);


 

I presume there is a specific implementation of these functions for SQL use, probably through another object wrapping the basic regexp_like function, while the standard function only works in PL/SQL : 


DECLARE

b boolean;

BEGIN

b:=sys.standard.regexp_like('a', 'a');

if b then

dbms_output.put_line('I love JF Copé !');

end if;

END;

 

I love JF Copé !

 

... but it does not in SQL !


SELECT *

FROM dual

WHERE sys.standard.regexp_like('a', 'a');

 

ERROR at line 3:

ORA-00920: invalid relational operator

 

Unlike for REGEXP_SUBSTR, Oracle documentation actually does not describe REGEXP_LIKE as a function that returns a boolean, but rather as a condition that evaluates to true or false.

 

REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE. This condition evaluates strings using characters as defined by the input character set.

 

http://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions001.htm

A condition could be said to be of a logical datatype, although Oracle Database does not formally support such a datatype.

 

Oracle SQL implements boolean algebra without booleans (by replacing them with condition evaluations !)... kind of creepy :)

Published by Pacman - dans SGBD
commenter cet article
30 juillet 2013 2 30 /07 /juillet /2013 15:44

L'autre jour, allez savoir pourquoi, je jouais avec des Index Organized Tables (IOT).

Ces créatures ont cela de spécial qu'il n'y a pas de table à proprement parler, ou plutôt que l'index clef primaire contient toutes les données.

 
Cette structure est particulièrement sympa quand la table n'est pas très large (c'est à dire des lignes pas très grosses), et que l'accès par la clef primaire est très courant. 
En effet, le clustering factor de la PK est par construction parfait pour une IOT.

 
 
Par contre, on a des petits effets rigolos quant on parle d'adressage des lignes : Quand vous insérez une ligne dans une table, on s'en tape un peu de l'endroit physique : soit tout à la fin (aux environs du high water mark), soit quelque part où il y a de la place à réutiliser.

 
 
Dans un index en revanche, les entrées sont ordonnées selon la clef de l'index, puisque l'objectif est quand même à la base d'accélérer la recherche d'une (plage de) valeur donnée.... la suite tout au long de la démo.


1) Le logical rowid
 
Allez, une petite IOT et son index secondaire. 

J'ajoute une colonne m pour pouvoir l'indexer, et une une autre m pour avoir une colonne totalement non indexée.

 

CREATE TABLE testiot (l primary key, m, n) 
 ORGANIZATION INDEX AS
 SELECT 1, 1, 1 
 FROM dual 
 UNION ALL 
 SELECT 10000, 10000, 10000
 FROM dual;

Table created.

CREATE INDEX testiotii ON TESTIOT(m);
Index created.

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

 PL/SQL procedure successfully completed.

Pour une IOT, Oracle maintient un logical rowid. Ce logical rowid est stocké dans les indexes secondaires, et permet de faire un accès à l'IOT, comme dans un ACCESS BY INDEX ROWID classique.

Vous remarquerez au passage qu'il est bien plus laid de frangliser avec "logical rowid" que de traduire rowid logique. C'ezt mon choix.
SELECT l, m, n, rowid 
FROM testiot;

         L          M          N ROWID
---------- ---------- ---------- -----------------------------------------
         1          1          1 *BAEACwQCwQL+
     10000      10000      10000 *BAEACwQCwwL+


2) Pas fiable à requêter !

Mais si on ajoute une foultitude de clefs entre les deux premières valeurs, que va-t-il arriver à ce rowid ?  

INSERT INTO testiot
SELECT level + 1, level + 1, level + 1
FROM dual
CONNECT BY level <= 9900;

commit;

Commit complete.

set autot on explain

SELECT l, m, n, rowid
FROM testiot
WHERE m in (1, 10000);

        L          M          N ROWID
---------- ---------- ---------- -----------------------------------------
         1          1          1 *BAEACwgCwQL+
     10000      10000      10000 *BAEAWT8CwwL+

-----------------------------------------------------------------------------
| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                     |     2 |    18 |     1   (0)|
|*  1 |  INDEX FULL SCAN | SYS_IOT_TOP_1443683 |     2 |    18 |     1   (0)|
-----------------------------------------------------------------------------

La deuxième ligne a été déplacée, comme attendu.

Maintenant, premier fun fact : 

SELECT
/*+index(testiot testiotii)*/ l, m, rowid
FROM testiot
WHERE m in (1, 10000);

         L          M ROWID
---------- ---------- -----------------------------------------
         1          1 *BAEACwQCwQL+
     10000      10000 *BAEACwQCwwL+

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    18 |     1   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR  |           |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| TESTIOTII |     2 |    18 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

La pseudo colonne rowid ne renvoit pas la même chose en fonction du chemin d'accès !
En fait, comme le logical rowid est susceptible de varier souvent, Oracle ne remet pas à jour tous les indexes à chaque changement d'adressage dans l'IOT.

On se retrouve donc avec un rowid pas frais stocké avec la clef de l'index secondaire TESTIOTII.
Et c'est pour cela qu'en plus du logical rowid, la clef primaire de l'IOT est également stockée dans l'index secondaire !

On le voit d'ailleurs très bien dans le plan de cette dernière requête : on SELECT la colonne l, qui n'est pas dans l'index TESTIOTII, mais pourtant il n'y a pas besoin d'accès à l'IOT pour la récupérer.

3) Et un de plus.

Comme je n'a plus de colonne à indexer, je fais un FBI bidon, puis rajoute quelques lignes : 

CREATE INDEX
testiotii2 ON TESTIOT(m * 1);

Index created.

INSERT INTO
testiot
SELECT level + 1.5, level + 1.5, level + 1.5
FROM dual
CONNECT BY level <= 9900;

9900 rows created.

commit;

Commit complete.

Et là, ça devient n'importe quoi, on a 3 trois rowid différents pour la même ligne : 

SELECT
/*+index(testiot testiotii)*/ 'ii' accesstype, rowid, 10000
FROM testiot
WHERE m = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'ii2', rowid, 10000
FROM testiot
WHERE m * 1 = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'tab', rowid, n
FROM testiot
WHERE m = 10000; 

ACC ROWID                                          10000
--- ----------------------------------------- ----------
ii  *BAEACwQCwwL+                                  10000
ii2 *BAEAWT8CwwL+                                  10000
tab *BAEAGj0CwwL+                                  10000

-------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     3 |    27 |    31  (97)|
|   1 |  UNION-ALL         |                     |       |       |            |
|*  2 |   INDEX RANGE SCAN | TESTIOTII           |     1 |     9 |     1   (0)|
|*  3 |   INDEX RANGE SCAN | TESTIOTII2          |     1 |     9 |     1   (0)|
|*  4 |   INDEX UNIQUE SCAN| SYS_IOT_TOP_1443683 |     1 |     9 |    29   (0)|
|   5 |    INDEX FULL SCAN | TESTIOTII2          |  9902 |       |    29   (0)|
-------------------------------------------------------------------------------

Le problème, quand on a des estimations fausses du rowid, c'est qu'on commence par lire un bloc pour rien, puis on déplie l'index PK pour récupérer la ligne finale... plein de boulot dans le vent, en somme.
Les accès directs réussis par rowid sont d'ailleurs recencés dans les stats de l'index : 

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

PL/SQL procedure successfully completed.

set autot off

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234                50
TESTIOTII2                              1      19802         110                53

4) Re-link-er correctement

Du coup, quand ça devient trop le foutoir, il faut y remettre de l'ordre : 

ALTER INDEX
testiotii UPDATE BLOCK REFERENCES;

Index altered.

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

PL/SQL procedure successfully completed.

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234               100
TESTIOTII2                              1      19802         110                50

5) Ou rebuild !

Un dernier petit test, où on va claquer la moitié de l'IOT : 

DELETE FROM
testiot
WHERE l <= 5000;

9998 rows deleted.

commit
;

Commit complete.

On réorg l'IOT pour ramener tout le monde vers le début : 

ALTER TABLE
testiot MOVE;

Table altered.

... et on rebuild l'index : 

ALTER INDEX
testiotii2 REBUILD ONLINE;

Index altered.

SELECT
/*+index(testiot testiotii)*/ 'ii' accesstype, rowid, 10000
FROM testiot
WHERE m = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'ii2', rowid, 10000
FROM testiot
WHERE m * 1 = 10000
UNION ALL
SELECT /*+index(testiot testiotii2)*/ 'tab', rowid, n
FROM testiot
WHERE m = 10000; 

ACC ROWID                                          10000
--- ----------------------------------------- ----------
ii  *BAEAGj0CwwL+                                  10000
ii2 *BAEAGqsCwwL+                                  10000
tab *BAEAGqsCwwL+                                  10000

Voilà, le rebuild a réaligné son estimation de rowid de l'index sur le rowid logique de l'IOT !

A noter que les stats de l'index sont recalculés au rebuild (c'est vrai que tant qu'à ce repalucher toutes les données, pourquoi ne pas en profiter...)

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1      19802         209                 0
TESTIOTII                               1      19802         234                 0
TESTIOTII2                              1       9804          29               100

COMPUTE STATISTICS Clause

This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. 
This clause is supported for backward compatibility and will not cause errors.

Bref, notons surtout que COALESCE, qui permet également de recompacter l'index, ne remet pas à jour les rowid (normal, on re-façonne juste l'arbre)

ALTER INDEX
TESTIOTII COALESCE;

exec dbms_stats.gather_table_stats('PACMAN', 'TESTIOT', cascade => true)

SELECT
index_name, blevel, num_rows, leaf_blocks, pct_direct_access
FROM dba_indexes
WHERE table_name = 'TESTIOT';

INDEX_NAME                         BLEVEL   NUM_ROWS LEAF_BLOCKS PCT_DIRECT_ACCESS
------------------------------ ---------- ---------- ----------- -----------------
SYS_IOT_TOP_1443683                     1       9804          29                 0
TESTIOTII                               1       9804         116                 0
TESTIOTII2                              1       9804          29               100
Published by Pacman - dans SGBD
commenter cet article
24 janvier 2012 2 24 /01 /janvier /2012 08:44

Hier c'étaient des replays de starcraft 2, une autre fois c'était Jack Bauer, ... Aujourd'hui au réveil, une soudaine envie de jouer avec des index.

Un index, c'est une structure arborescente qui, à une clef, potentiellement composite, associe une adresse (rowid) dans une table.


Le nombre d'étages de cet arbre, ou niveau de l'arbre, ou encore "b-tree level", dépend du nombre de blocs feuilles à référencer (le bloc de départ peut contenir n références vers des blocs au niveau inférieur, qui eux même peuvent contenir m références, ...), mais pas seulement !


 

tmp.jpg


1) Un petit exemple rigolo :

On va illustrer avec une table à deux colonnes : l'une constante, l'autre identifiant unique.  

CREATE TABLE t AS SELECT lpad('x', 255, 'X') c, level l FROM DUAL CONNECT BY level <= 1000;

On construit deux index composites sur ces deux colonnes en intervertissant l'ordre

CREATE INDEX idxt1 ON t(c, l) COMPUTE STATISTICS;

CREATE INDEX idxt2 ON t(l, c) COMPUTE STATISTICS;

Et là, on vérifie le niveau du b-tree index :

SELECT index_name, blevel
FROM dba_indexes
WHERE table_name = 'T';

INDEX_NAME                         BLEVEL
------------------------------ ----------
IDXT1                                   2
IDXT2                                   1

Quand on met la colonne la plus sélective en premier, ça baisse le niveau de l'index !

2) Ca marche aussi pour les index concaténés au lieu de composites

CREATE INDEX IDXT3 ON t(c||l) COMPUTE STATISTICS;

CREATE INDEX IDXT4 ON t(l||c) COMPUTE STATISTICS;

SELECT index_name, blevel
FROM dba_indexes
WHERE table_name = 'T';

INDEX_NAME                         BLEVEL
------------------------------ ----------
IDXT1                                   2
IDXT2                                   1
IDXT3                                   2
IDXT4                                   1

Même sentence.

L'idée en fait, c'est que pour référencer un niveau inférieur de l'arbre, vous n'avez pas besoin de toute la clef.


Supposez que dans le premier niveau d'indirection, vous référenciez un bloc feuille de la manière suivante :
Pour les valeurs entre '1;xxx...x' à '500;xxx...x', rendez-vous bloc 4233 !
... se reformule aisément en :
Pour les valeurs entre '1*' à '500*', rendez-vous bloc 4233 !
=> Les références sont beaucoup plus courtes, on peut en coincer beaucoup plus dans un bloc, et on a donc besoin de moins de niveaux d'indirection !

3) La même bien sûr sans concaténation de l'index

Cette fois, le pattern est présent naturellement dans la colonne.

CREATE TABLE u AS SELECT level || lpad('x', 255, 'X') c FROM DUAL CONNECT BY level <= 1000;

CREATE INDEX idxu1 ON u(c) COMPUTE STATISTICS;

CREATE TABLE W AS SELECT lpad('x', 255, 'X') ||level c FROM DUAL CONNECT BY level <= 1000;

CREATE INDEX idxw1 ON w(c) COMPUTE STATISTICS;

SELECT index_name, blevel
FROM dba_indexes
WHERE table_name IN ('U', 'W');

INDEX_NAME                         BLEVEL
------------------------------ ----------
IDXU1                                   1
IDXW1                                   2

Exactement la même... comme on pouvait s'y attendre.
C'est dans le mécanisme même de construction de l'index.

4) Juste pour déconner

Sur un index à forte redondance sur les premières positions de la clef composite, on peut (depuis 9i si je ne me trompe pas), compresser :

ALTER INDEX idxt1 REBUILD COMPRESS 1 COMPUTE STATISTICS;


SELECT index_name, blevel
FROM dba_indexes
WHERE table_name = 'T';

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
IDXT1                                   1           3
IDXT2                                   1          39
IDXT3                                   2          39
IDXT4                                   1          39

IDXT1 est devenu tout petit non seulement quant à son niveau, mais aussi pour les blocs feuilles qui sont elles aussi compressés.... c'est Imbattable :)

Published by Pacman - dans SGBD
commenter cet article
23 décembre 2011 5 23 /12 /décembre /2011 11:48

A mes débuts dans Oracle, je lisais plein de conneries sur l'optimisation.
Parmi les gros mythes, il y avait : "rebuild un index améliore le clustering factor".

C'est bien entendu absurde :
- Le clustering factor se calcule comme le nombre de changement de bloc sur les lignes de la table que tu effectues en parcourant séquentiellement les feuilles de l'index.
- Les feuilles sont triées par valeur de la clef, donc le fait de le recontruire ne change pas l'ordre des feuilles

Puis je me disais : peut-être que pour les valeurs de clef ex-aequo l'ordre d'insertion pourrait augmenter artificiellement le nombre de changement de blocs, permettant ainsi au rebuild de faire décroître le clustering factor.

Essayons !

CREATE TABLE t (i NUMBER, j NUMBER);

CREATE INDEX idxt ON t(i);

INSERT INTO t
SELECT level, level
FROM DUAL
CONNECT BY level <= 1000;

commit;

SELECT dbms_rowid.rowid_block_number(rowid) blk, count(*) cnt, min(i) mini, max(i) maxi
FROM t
GROUP BY dbms_rowid.rowid_block_number(rowid);

       BLK        CNT       MINI       MAXI
---------- ---------- ---------- ----------
   1452237        574          1        574
   1452238        426        575       1000

De i = 1 à 574, on est dans le premier bloc. Les autres sont dans le deuxième bloc.

Un petit coup de stats...

exec dbms_stats.gather_table_stats(null, 'T', cascade => true)

SELECT clustering_factor
FROM dba_indexes
WHERE index_name = 'IDXT';

CLUSTERING_FACTOR
-----------------
                2

... et ça fait bien 2, qui correspond au passage de i=574 à 575 où on passe au deuxième bloc.

Maintenant, on va séquentiellement updater i = 1 dans un bloc, puis dans l'autre.

UPDATE t
SET i = 1
WHERE i = 575;

commit;

exec dbms_stats.gather_table_stats(null, 'T', cascade => true)

SELECT clustering_factor
FROM dba_indexes
WHERE index_name = 'IDXT';

CLUSTERING_FACTOR
-----------------
                4
 

 

=> Lors du parcours de l'index, pour i = 1, on switche sur le deuxième bloc, puis on revient au premier bloc pour i = 2 : ça augmente donc le clustering factor de 2
Jusque là, pas de surprise :)

UPDATE t
SET i = 1
WHERE i = 2;

commit;

exec dbms_stats.gather_table_stats(null, 'T', cascade => true)

SELECT clustering_factor
FROM dba_indexes
WHERE index_name = 'IDXT';

CLUSTERING_FACTOR
-----------------
                4

Cette fois, je me serais dit que j'insère une feuille i = 1 après celles qui existent, et donc je devrais ajouter 2 au clustering factor... mais non !

SELECT /*+INDEX(t idxt)*/ i, dbms_rowid.rowid_block_number(rowid)
FROM t
WHERE i = 1;

        I DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
--------- ------------------------------------
        1                              1452237
        1                              1452237
        1                              1452238
        1                              1452238

En parcourant l'index, on voit (même si cette requête n'est pas vraiment une preuve fiable) que les blocs sont bien lus "ensemble", et non pèle-mêle !

Ah ben ouais, les feuilles sont triées sur le "couple" (clefs, rowid) !

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i5671

Leaf Blocks
All leaf blocks are at the same depth from the root branch block. Leaf blocks store the following:
    *      The complete key value for every row
    *      ROWIDs of the table rows
All key and ROWID pairs are linked to their left and right siblings. They are sorted by (key, ROWID).

La vie est belle... Bonne dinde à tous !

Published by Pacman - dans SGBD
commenter cet article
7 juillet 2011 4 07 /07 /juillet /2011 13:49

Tiens aujourd'hui, mon jeune Padawan me demandait :
"Maître, quand on fait un UNION ALL, est-ce que cela garantit que le resultset comportera en premier la première requête ?"

 

On dit toujours a fort juste titre que si on veut qu'un résultat soit trié, il faut un ORDER BY.
Par exemple :
- Le GROUP BY (ne serait-ce que parce que le HASH GROUP BY est venu remplacer le SORT GROUP BY)
- Le DISTINCT (ne serait-ce que parce que le HASH UNIQUE est venu remplacer le SORT UNIQUE)
... et :
- Le UNION ALL (ne serait-ce qu'à cause du parallèle)

 

Allez, juste un petit test :

 

Dans une première table, que des "1" :

 

SQL> CREATE TABLE paral1 PARALLEL 4 AS 
 SELECT 1 n 
 FROM DUAL 
 CONNECT BY LEVEL <= 100;

 

Table crÚÚe.

 

Dans deuxième table, que des "2" :

 

SQL> CREATE TABLE paral2 PARALLEL 4 AS 
 SELECT 2 n 
 FROM DUAL 
 CONNECT BY LEVEL <= 100;

 

Table crÚÚe.

 

Et là :

SQL> SELECT
 FROM paral1 
 UNION ALL 
 SELECT
 FROM paral2;

 

... droumdroumdroumdroumdroum ... (si vous avez une meilleure version phonétique de l'onomatopée du tambour, n'hésitez pas) :

 

Tadaaam ! (c'est mélangé, hoho)

 

         N
----------
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         1
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2
         2

200 ligne(s) sÚlectionnÚe(s).

 

Published by Pacman - dans SGBD
commenter cet article