Archives pour la catégorie 12c

Mécanismes internes du multitenant: liens objects et métadonnées

Publié le par
Répondre

ceci est la traduction de mon article sur le blog dbi-services

Attention: il ne s’agit ici que de jouer avec les mécanismes internes non documentés. A ne faire que sur une base de test prête à être jetée. Jouer avec ces mécanisme est le meilleur moyen de planter la base avec des ORA-600 et de corrompre le dictionnaire.

Sur une base multitenant (CDB) nous savons que chaque pluggable database est isolée afin d’être vue comme une seule base lorsqu’on est connecté à son service. Mais elles partagent des ressource communes puisque le but est de consolider plusieurs bases en une seule. Pas de problème pour la CPU, la mémoire, les journaux redo et undo: il ne sont gérés qu’au niveau CDB. Le multitenant n’a pas changé grand chose là dessus, à part qu’on stocke l’identificateur de conteneur dans différentes structures, par exemple dans les vecteurs de redo pour identifier les objets (object id peuvent être les mêmes entre plusieurs CDB). Au niveau des fichiers, pas de gros changements par rapport à ce qui avait été introduit avec les tablespaces transportables.

Mais ce qui a été un plus gros challenge pour les développeurs d’Oracle 12c, c’est surtout la manière d’implémenter le dictionnaire. Il a été séparé:

  • Tout ce qui est interne au fonctionnement d’Oracle, les métadonnées du dictionnaire, les packages dbms_…, tout ce qui est stocké pour toute l’instance: tables de références, repository AWR, etc. C’est le but: consolider, ne pas répéter plusieurs fois ce qui est identique du moment qu’on est sur la même version d’Oracle
  • Toutes les métadonnées qui concernent l’application: les tables et packages que vous avez créé. Celles-ci sont propres à chaque pluggable tablespace, et sont transportées, clonées avec les oprérations de plug/unplug

et finalement, comme le but principal d’Oracle étant de pouvoir faire dans une PDB tout ce qu’on peut faire sur base, il faut pouvoir foir au niveau PDB les informations stockées au niveau CDB.

Tout ça semble un peu magique par rapport à ce qu’on connait d’Oracle qui n’a jamais fait cette séparation. Par exemple depuis le début d’Oracle on a dans la table SYS.TAB$ les informations de nos tables, ainsi que les informations des tables du dictionnaire – incluant les métadonnées de la table SYS.TAB$ elle-même. La documentation ne va pas très loin sur la manière dont ça a été implémenté. Heureusement on a quelques scripts dans ?/rdbms/admin qui donnes des idées là dessus. ils utilisent une syntaxe un peu particulière, non documentée, et qui n’est disponible que lorsque les scripts internes mettent le paramètre non documenté « _ORACLE_SCRIPT »=true.

Maintenant, ce sont des trucs de geek qui arrivent. Rien à voir avec ce qu^’on fait en production. Nous allons créer nos propres liens objet et métadonnée. Ce qui est fait dans la suite est fait dans une session avec « _ORACLE_SCRIPT »=true. On va voir des nouveaux éléments de syntaxe: cdb$view(), sharing=metadata, sharing=object, common_data

Container data objects

D’abord nous allons voir comment au niveau CDB la racine CDB$ROOT peut voir les informations des autres conteneurs (PDB)

Je suis au niveau CDB:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

et je créé une table normale:

SQL> create table DEMO_REG_TABLE sharing=none as select 111 dummy from dual;
Table created.

SQL> select * from DEMO_REG_TABLE;
     DUMMY
----------
       111

Puis je fais exactement la même chose dans la PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create table DEMO_REG_TABLE sharing=none as select 999 dummy from dual;
Table created.

SQL> select * from DEMO_REG_TABLE;
     DUMMY
----------
       999

et de retour dans la racine, j’utilse la fonction de table CDB$VIEW pour voir ce que j’ai dans chacune de mes PDB:

SQL> select * from  cdb$view(DEMO_REG_TABLE) where con_id in (1,3);
     DUMMY     CON_ID
---------- ----------
       999          3
       111          1

C’est comme ça que les ‘container objects’ sont définis: ils utilisent la fonction CDB$VIEW pour exécuter une requête sur chaque PDB, consolider le résultat et rajouter l’identifiant de PDB: CON_ID pour montrer d’où viennent les lignes.

Vous voulez savoir comment c’est implémenté? Il semble que ce soit le mécanisme de parallel query qui soit utilisé. Voici la preuve: 

SQL> select * from  cdb$view(DEMO_REG_TABLE);
select * from  cdb$view(DEMO_REG_TABLE)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-00942: table or view does not exist

Lorsque la table n’est pas trouvée (j’ai volontairement créé une table sur quelques conteneurs seulement) c’est un process parallel query qui renvoit l’erreur.

Metadata links

Maintenant, je vais créer une fonction PL/SQ dans la racine et dans une PDB, mais je n’ai pas envie de dupliquer le code dans les deux tablespaces SYSTEM. Je vais utiliser SHARING=METADATA pour créer un metadata link.

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> create function DEMO_MDL_FUNCTION sharing=metadata
  2  return varchar2 as dummy varchar2(100); begin select max(dummy) into dummy from DEMO_REG_TABLE; return dummy; end;
  3  /
Function created.

SQL> select DEMO_MDL_FUNCTION from dual;
DEMO_MDL_FUNCTION
------------------------------
111

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create function DEMO_MDL_FUNCTION sharing=metadata
  2  return varchar2 as dummy varchar2(100); begin select max(dummy) into dummy from DEMO_REG_TABLE; return dummy; end;
  3  /
Function created.

SQL> select DEMO_MDL_FUNCTION from dual;
DEMO_MDL_FUNCTION
------------------------------
999

Je peux donc voir le code dans SYS.SOURCE$ de la racine:

SQL> alter session set container=cdb$root;
Session altered.

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO%');

      OBJ#       LINE SOURCE
---------- ---------- ------------------------------
     95789          1 function DEMO_MDL_FUNCTION

Mais rien dans ma PDB:

 
SQL> alter session set container=pdb1;
Session altered.

SQL> select * from source$ where obj# in (select obj# from obj$ where name like 'DEMO%');

no rows selected

Object links

Ceci était pour ne stocker les métadonnées qu’à un seul endroit, dans la racine, en ayant seulement un lien dessus dans les PDB. On peut aussi faire la même chose pour des données qu’on ne veut stocker que dans la racine, en utilisant SHARING=METADATA pour créer la table et SHARING=OBJECT pour créer une vue dessus

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

SQL> create table DEMO_MDL_TABLE sharing=metadata as select 111 dummy from dual;
Table created.

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id

CON_ID
------------------------------
3

SQL> create table DEMO_MDL_TABLE sharing=metadata as select 999 dummy  from dual;
Table created.

 
SQL> alter session set container=cdb$root;
Session altered.

SQL> select * from  cdb$view(DEMO_MDL_TABLE) where con_id in (1,3);

     DUMMY     CON_ID
---------- ----------
       999          3
       111          1

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

SQL> create view DEMO_OBL_VIEW sharing=object as select * from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_OBL_VIEW;
     DUMMY
----------
       111

 
SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create view DEMO_OBL_VIEW sharing=object as select * from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_OBL_VIEW;
     DUMMY
----------
       111

La vue montre les données stockées dans CDB$ROOT en suivant le ‘object link’ au lieu de lire la table du container courant.

On ne peut pas insérer directement dans un ‘object link':

SQL> insert into DEMO_OBL_VIEW select 9999 dummy from dual;
insert into DEMO_OBL_VIEW select 9999 dummy from dual
            *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

et on a une idée de l’implémentation en regardant le plan d’exécution:

---------------------------------------------
| Id  | Operation        | Name             |
---------------------------------------------
|   0 | SELECT STATEMENT |                  |
|   1 |  FIXED TABLE FULL| X$OBLNK$aed0818c |
---------------------------------------------

C’est une table X$ qui va faire le lien

Common data views

finalement, comment une PDB peut voir les données de la racine? Nous avons vu au dessus que la fonction que j’ai créé ci-dessus est stockée dans le SYS.SOURCE$ de la racine seulement. Par contre on peut la voir à partir du DBA_SOURCE de la PDB

SQL> alter session set container=cdb$root;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id
CON_ID
------------------------------
1

SQL> create or replace view DEMO_INT_VIEW common_data (dummy,sharing) as select dummy,case when dummy='222' then 0 else 1 end from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_INT_VIEW;

     DUMMY    SHARING
---------- ----------
       111          1
       222          0

J’ai créé une ‘common data view’ en rajoutant une colonne ‘sharing’ pour montrer d’où viennent les lignes.

SQL> alter session set container=pdb1;
Session altered.

SQL> show con_name
CON_NAME
------------------------------
PDB1

SQL> show con_id
CON_ID
------------------------------
3

SQL> create or replace view DEMO_INT_VIEW common_data (dummy,sharing) as select dummy,case when dummy='222' then 0 else 1 end from DEMO_MDL_TABLE;
View created.

SQL> select * from DEMO_INT_VIEW;
     DUMMY    SHARING ORIGIN_CON_ID
---------- ---------- -------------
       999          1             3
       111          1             1

SQL> set autotrace on
SQL> select * from DEMO_INT_VIEW;

     DUMMY    SHARING ORIGIN_CON_ID
---------- ---------- -------------
       111          1             1
       999          1             3

Execution Plan
----------------------------------------------------------
Plan hash value: 3158883863

--------------------------------------------------------------------------------------------
|Id  | Operation               | Name            |Pstart|Pstop |   TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |                 |      |      |       |      |            |
|  1 |  PX COORDINATOR         |                 |      |      |       |      |            |
|  2 |   PX SEND QC (RANDOM)   | :TQ10000        |      |      | Q1,00 | P->S | QC (RAND)  |
|  3 |    PX PARTITION LIST ALL|                 |    1 |    2 | Q1,00 | PCWC |            |
|  4 |     FIXED TABLE FULL    | X$COMVW$e40eb386|      |      | Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------

On voit que les données de chaque conteneur est retourné comme une partition.

12c: les niveaux de compression changent de nom

Publié le par
Répondre

En 12c les niveaux de compression changent de noms, en présisant le stockage en ligne ou colonne

  • en ligne (ROW STORE): les colonnes d’une même ligne sont ensembles, les lignes sont stockées les unes à la suite des autres dans des blocs
  • ou en colonne (COLUMN STORE): les valeurs des colonnes de plusieurs lignes sont regroupées en vecteurs, Les vecteurs sont stockés dans le Compression Unit
    Si vous avez déjà utilisé des Host Array pour insérer en bulk, c’est un peu la même idée. On a une meilleur compression lorsqu’on retrouve les mêmes valeurs d’une ligne à l’autre.

Sans option Advanced compression:

NOCOMPRESS

Pas de compression

ROW STORE COMPRESS BASIC

Basic Table Compression (aka ‘COMPRESS’ ou ‘ROW STORE COMPRESS’)
Lors des insert en direct-path, au niveau de chaque bloc, les valeurs dupliquées ne sont stockées qu’une fois (en utilisant des pointeurs – utile seulement pour des tailles supérieures à quelques octets)
Lors de DML conventionnel, les lignes sont décompressées, donc utile en chargement de masse seulement.

Avec option Advanced Compression

ROW STORE COMPRESS ADVANCED

Advanced Table Compression (aka ‘COMPRESS FOR OLTP’)
Même chose que BASIC, mais le DML conventionnel permet de compresser aussi.

sous Exadata et ZFS, avec stockage colonne

COLUMN STORE COMPRESS FOR QUERY LOW/HIGH

Warehouse compression (aka Hybrid Columnar Compression QUERY LOW/HIGH)
Stockage en colonne dans des Compression Unit, utilise plus de CPU (surtout en HIGH)
Lors des inserts en direct-path seulement, le DML conventionnel remet les lignes en stockage ligne, donc avec moins bon taux de compression et chained rows.
QUERY: Valable pour des données qui ne sont pas modifiées, mais la lecture est rapide (l’économie d’I/O compense le surplus de CPU)

COLUMN STORE COMPRESS FOR ARCHIVE LOW/HIGH

Archive compression (aka Hybrid Columnar Compression ARCHIVE LOW/HIGH)
Stockage en colonne dans des Compression Unit, utilise plus de CPU (surtout en HIGH)
Lors des inserts en direct-path seulement, le DML conventionnel remet les lignes en stockage ligne, donc avec moins bon taux de compression et chained rows.
ARCHIVE: Valable pour des données qui ne sont pas modifiées et peu lues (car forte utilisation CPU pour décompresser aussi)

12c: des noms de Wait Events plus parlants

Publié le par
Répondre

Certains noms de wait events sont trompeurs. C’est le cas par exemple de ‘db file sequential read’ qui n’est pas ce qu’on entend par lecture disque séquentielle, mais plutôt une lecture monobloc (donc plutôt des i/o randoms).
Alors que ‘log file sequential read’ est bien une lecture de plusieurs blocs contigus.

La doc précise leur définition, mais en 12c on a aussi un ‘Display Name’ qui permet à Oracle de montrer un nom un peu plus parlant.

Voici ceux dont le ‘display name’ est différent du nom de l’event:

select wait_class,name, display_name from v$event_name where display_name != name order by 1,2;
WAIT_CLASS NAME DISPLAY_NAME
Administrative concurrent I/O completion online move datafile IO completion
Administrative datafile copy range completion online move datafile copy range completion
Administrative wait for possible quiesce finish quiesce database completion
Commit log file sync commit: log file sync
Configuration log buffer space log buffer full – LGWR bottleneck
Idle LGWR real time apply sync standby apply advance notification
Other DFS db file lock quiesce for datafile offline
Other Image redo gen delay redo resource management
Other datafile move cleanup during resize online move datafile resize cleanup
System I/O control file sequential read control file read
System I/O control file single write control file write
System I/O db file parallel write db list of blocks write
System I/O log file parallel write log file redo write
System I/O log file sequential read log file multiblock read
System I/O log file single write log file header write
User I/O db file parallel read db list of blocks read
User I/O db file scattered read db multiblock read
User I/O db file sequential read db single block read
User I/O db file single write db single block write

La différentiation des i/o single block et multiblock est particulièrement intéressante.

12c: Parallel Query ‘replicate’ au lieu de ‘broadcast’ pour les petites tables

Publié le par
1

Sur un Hash Join en parallel query, lorsque la table hachée est petite, il est préférable parfois de l’envoyer entière à chacun des process parallèle qui effectuent la jointure.
En 12c il y a le Hybrid Hash qui va décider du broadcast au moment de l’exécution si la table petite.
Mais si le plan force un broadcast, il y a une nouvelle distribution possible en 12c: PQ_REPLICATE

Voici un plan d’exécution en 11g avec un DOP 8 avec les outlines suivants:

 parallel(dept) parallel(emp) leading(dept emp) use_hash(emp) no_swap_join_inputs(emp) pq_distribute(emp broadcast none)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |     4 (100)|        |      |            |     14 |00:00:00.13 |       8 |       |       |          |
|   1 |  PX COORDINATOR         |          |      1 |        |            |        |      |            |     14 |00:00:00.13 |       8 |       |       |          |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |      0 |     14 |     4   (0)|  Q1,01 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN            |          |      8 |     14 |     4   (0)|  Q1,01 | PCWP |            |     14 |00:00:00.08 |      56 |   684K|   684K|     8/0/0|
|   4 |     PX RECEIVE          |          |      8 |      4 |     2   (0)|  Q1,01 | PCWP |            |     32 |00:00:00.06 |       0 |       |       |          |
|   5 |      PX SEND BROADCAST  | :TQ10000 |      0 |      4 |     2   (0)|  Q1,00 | P->P | BROADCAST  |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       PX BLOCK ITERATOR |          |      8 |      4 |     2   (0)|  Q1,00 | PCWC |            |      4 |00:00:00.01 |      12 |       |       |          |
|*  7 |        TABLE ACCESS FULL| DEPT     |      4 |      4 |     2   (0)|  Q1,00 | PCWP |            |      4 |00:00:00.01 |      12 |       |       |          |
|   8 |     PX BLOCK ITERATOR   |          |      8 |     14 |     2   (0)|  Q1,01 | PCWC |            |     14 |00:00:00.02 |      56 |       |       |          |
|*  9 |      TABLE ACCESS FULL  | EMP      |     14 |     14 |     2   (0)|  Q1,01 | PCWP |            |     14 |00:00:00.01 |      56 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Ici DEPT (4 lignes) est lue en parallèle par les 8 process parallèles du set 1 (Q1,00) et toutes les lignes sont envoyées au set 2 (Q1,01) pour la jointure. On le voit aux 4×8=32 lignes reçues dans A-Rows.

En 12c, ‘PQ replicate small tables’ va économiser un set de serveurs parallèles en faisant lire l’ensemble de la table DEPT par chacun des process qui fait la jointure:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     4 (100)|        |      |            |     14 |00:00:00.11 |       5 |       |       |          |
|   1 |  PX COORDINATOR       |          |      1 |        |            |        |      |            |     14 |00:00:00.11 |       5 |       |       |          |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |      0 |     14 |     4   (0)|  Q1,00 | P->S | QC (RAND)  |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN          |          |      8 |     14 |     4   (0)|  Q1,00 | PCWP |            |     14 |00:00:00.02 |     104 |   684K|   684K|     8/0/0|
|   4 |     TABLE ACCESS FULL | DEPT     |      8 |      4 |     2   (0)|  Q1,00 | PCWP |            |     32 |00:00:00.01 |      48 |       |       |          |
|   5 |     PX BLOCK ITERATOR |          |      8 |     14 |     2   (0)|  Q1,00 | PCWC |            |     14 |00:00:00.01 |      56 |       |       |          |
|*  6 |      TABLE ACCESS FULL| EMP      |     14 |     14 |     2   (0)|  Q1,00 | PCWP |            |     14 |00:00:00.01 |      56 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Maintenant, DEPT est lue en entier (pas de block iterator) par chacun des process. On a un seul set de process parallèle (Q1,00) qui fait tout.

Ca fait un peu plus de blocs à lire, mais une économie de process et de messages parallel query. Efficace lorsque on sait que la table est petite. Et c’est en principe le cas si on choisit une distribution broadcast.

La demo montre aussi un plan possible en 11g où DEPT n’est pas lue par les process parallèle, mais est broadcastée quand même.

Le hint pour contrôler cette fonctionnalité: PQ_REPLICATE. A noter que l’on n’a plus de Adaptive Parallel Query Distribution (HYBRID HASH) dans ce cas.

12c: la transformation Partial Join Evaluation

Publié le par
Répondre

Faut-il remplacer les jointures par des EXISTS lorsque c’est possible ?
Une sous-requête EXISTS n’a pas besoin de ramener toutes les lignes d’une jointure: dès qu’il y a une correspondance pour une valeur de la jointure, on peut passer à la suivante. C’est un Semi Hash Join, qui est plus rapide qu’un Hash Join. Très souvent le CBO va faire cette réécriture lors des transformations de la phase d’optimisation.
En en 12c on a une transformation automatique de plus lorsque les lignes de la jointure seront au final dédoublonnées avec un DISTINCT.

Par exemple, la requête suivante:

select distinct status_label from TEST_STATUS 
 join TEST using(status_id) where flag='Y';

sera transformée en:

select status_label from TEST_STATUS 
where exists(
 select null from TEST 
 where flag='Y' and TEST.status_id=TEST_STATUS.status_id
);

vu qu’on ne renvoit que les colonnes de TEST_STATUS, un EXISTS (ou IN) est suffisant. Et on obtient le plan d’exécution suivant:

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |     10 |00:00:00.01 |       7 |       |       |          |
|   1 |  HASH UNIQUE        |             |      1 |     10 |     10 |00:00:00.01 |       7 |  1214K|  1214K| 1105K (0)|
|*  2 |   HASH JOIN SEMI    |             |      1 |     10 |     10 |00:00:00.01 |       7 |  1185K|  1185K| 1142K (0)|
|   3 |    TABLE ACCESS FULL| TEST_STATUS |      1 |     10 |     10 |00:00:00.01 |       3 |       |       |          |
|*  4 |    TABLE ACCESS FULL| TEST        |      1 |  50000 |     10 |00:00:00.01 |       4 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

Ici on voit le gros avantage dans la colonne A-Rows: le Full Table Scan de la table TEST qui fait 100000 lignes, s’est arrêté dès qu’on a trouvé les 10 correspondances avec la table TEST_STATUS. Pas besoin de continuer: le prédicat EXISTS est vérifié.
C’est le HASH JOIN SEMI qui fait ça alors qu’un HASH JOIN aurait lu 100000 lignes et renvoyé 50000 lignes jusqu’au DISTINCT.

Voici le plan en désactivant les transformations:

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |     10 |00:00:00.09 |     214 |       |       |          |
|   1 |  HASH UNIQUE        |             |      1 |     10 |     10 |00:00:00.09 |     214 |  1214K|  1214K| 1099K (0)|
|*  2 |   HASH JOIN         |             |      1 |  50000 |    100K|00:00:00.07 |     214 |  1185K|  1185K| 1129K (0)|
|   3 |    TABLE ACCESS FULL| TEST_STATUS |      1 |     10 |     10 |00:00:00.01 |       3 |       |       |          |
|*  4 |    TABLE ACCESS FULL| TEST        |      1 |  50000 |    100K|00:00:00.01 |     211 |       |       |          |
------------------------------------------------------------------------------------------------------------------------

A noter que en 11.2 il y avait déjà une transformation utile dans ce cas, Distinct Placement qui fait descendre le DISTINCT sous la jointure, mais qui nécessite quand même de lire toute la table.

Demo, hints, plans d’exécution et traces 10053 complétes dans la demo

Alors pourquoi faire ça plutôt que de réécrire la requête ? Le gros intérêt de ces transformations, c’est lorsqu’on on fait une requête sur une vue dans laquelle il y a la jointure. La vue va souvent chercher des colonnes dont on a pas besoin. Les transformations permettent alors d’éliminer les opérations inutiles.

12c: Index partiel sur tables partitionnées

Publié le par
Répondre

On n’a pas toujours besoin d’indexer toutes les partitions. Dans une table qui garde tout l’historique, on peut avoir le besoin de n’indexer que la partition courante, accédées en transactionnel. Les anciennes partitions ont toujours les données, pour des besoins de reporting, mais sans devoir stocker tous les index utiles à la partition courante.
Avant la 12c, on peut faire ̤a avec des index locaux en les rendant unusable (et skip_unusable_indexes=true). Mais pas de solution pour les index globaux Рsinon archiver les anciennes lignes dans une autre table et faire une vue UNION ALL.

En 12c, on peut peut avoir des index partiels:

CREATE INDEX ... GLOBAL INDEXING PARTIAL
CREATE INDEX ... LOCAL INDEXING PARTIAL

Et on précise au niveau de la tables les partitions qui ne seront pas indexées par les Partial Index:

ALTER TABLE ... MODIFY PARTITION ... INDEXING OFF

Le résultat:

  • Les Partial Local Index auront leur partitions en ‘indexing off’ au status UNUSABLE (donc garder la valeur par défaut skip_unusable_indexes=true)
  • Les Partial Global Index n’auront pas d’entrée pour les lignes des partitions en ‘indexing off’. S’il y en avait auparavant, elles deviendront orphelines

L’opération inverse elle devra reconstruire les partitions locales, et réindexer les index globaux pour ces partitions.

C’est transparent: les SELECT qui utilisent l’index feront un UNION ALL pour aller chercher les données indexées via index, et les autres via partition full scan.
Par exemple, lorsque la partition 1 est en ‘indexing off’, la partition 2 en ‘indexing on’, et l’index global en ‘indexing partial':

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |     1 |     8 |    32   (0)| 00:00:01 |       |       |
|   1 |  VIEW                                        | VW_TE_2 |     2 |    52 |    32   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |         |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TEST    |     1 |     8 |     2   (0)| 00:00:01 |     2 |     2 |
|*  4 |     INDEX RANGE SCAN                         | TEST_N  |     1 |       |     1   (0)| 00:00:01 |       |       |
|   5 |    PARTITION LIST SINGLE                     |         |     1 |     8 |    30   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL                        | TEST    |     1 |     8 |    30   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------

L’exemple complet dans la demo

12c: chaines de caractères >4000 (jusqu’à 32k)

Publié le par
Répondre

En 12c on peut stocker des chaînes de caractères jusqu’à 32ko (au lieu de 4000 pour les VARCHAR2 et 2000 pour les CHAR). RAW peut aussi aller jusqu’à 32ko.
Ce n’est pas activé par défaut. Il faut passer max_string_size=EXTENDED(default STANDARD).
Et ce n’est pas tout. S’il y avait des vues (ou colonnes virtuelles) crées avec des expression qui dépassaient les 4000 il faut les recompiler avec un script fourni (utl32k.sql). Pour cette raison la modif se fait en ‘OPEN MIGRATE’. Donc la procédure est la suivante:

SHUTDOWN IMMEDIATE;
startup upgrade;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k.sql
shutdown immediate;
startup;

Si on crée une table avec des extended datatypes, ils seront stockés comme des LOB. Il n’y a par contre pas l’inconvénient des LOB qui sont sont récupérés un à un (LOBREAD). Ici les 32k peuvent être renvoyés avec la ligne.

Sur une table existante, lorsqu’on augmente la taille d’une colonne de telle sorte qu’elle devienne un extended datatype, les lignes utiliseront le row chaining pour stocker les valeurs plus larges. Il faut donc recréer la table (ce qui peut être fait avec dbms_redefinition) pour avoir un stockage optimal.

On ne peut pas indexer des valeurs plus larges qu’un bloc (et même un peu moins – il faut que tout loge dans une branche d’index). Mais la 12c amène une autre amélioration: un index sur SUBSTR(…,1,…) peut être utilisé de manière transparente (l’optimiseur rajoute un prédicat avec la fonction en question afin de pouvoir utiliser un INDEX RANGE SCAN). On peut aussi utiliser STANDARD_HASH si on interroge uniquement avec une égalité.

Tout ça est testé dans la demo

12c: optimisation des appels de fonctions PL/SQL à partir de SQL

Publié le par
Répondre

Dans les requêtes SQL, on peut utiliser des fonctions standard (substr, to_date,… ) et lorsqu’on a un besoin un peu plus spécifique, on peut créer des des UDF (User Defined Function).
Avec Oracle, les fonctions standard sont codées en C, directement exécutable lors de l’exécution SQL. Mais les UDF sont simplement des appels à des fonctions PL/SQL stockées. Le problème lorsqu’on passe d’un language (SQL) à un autre (PL/SQL) en cours d’exécution: il y a un switch de contexte à chaque fois. C’est très consommateur de CPU. Surtout lorsqu’on appelle la fonction pour chaque ligne.

Jusqu’en 11g, les solutions sont:

  • Utiliser seulement des fonctions standard
  • Eviter l’appel ligne à ligne avec les pipelined functions
  • Lorsque le résultat de la fonction est déterministe et qu’il est applelé plusieurs fois pour les même valeurs, déclarer la fonction en deterministic

En 12c on a des vrais UDF optimisées pour les appels à partir de SQL.

On peut utiliser le pragma UDF pour une fonction stockée, par exemple:

create or replace function TEST_SPELL(n number) return varchar2 as pragma UDF; begin return to_char( date'-4712-01-01'+n-1,'Jsp'); end;
/
select max(spelled) from ( select TEST_SPELL(num) spelled from TEST);

Et on peut aussi déclarer la fonction comme un bloc PL/SQL anonyme dans la requête SQL:

with function TEST_SPELL(n number) return varchar2 as begin return to_char( date'-4712-01-01'+n-1,'Jsp'); end;
select /*+ TEST1 */ max(spelled) from ( select TEST_SPELL(num) spelled from TEST)
 /

Dans la demo j’appelle une fonction simple pour 1 million de lignes
Dans la version 11g, les temps ‘sql execute elapsed time’ et ‘PL/SQL execution elapsed time’ sont considérables, alors que ni la requête ni la fonction ne sont très complexes.
C’est parce chacun inclut le switch de contexte vers l’autre language.
En utilisant pragma udf ou le with plsql (déjà exposé ici) c’est une centaine de secondes que l’on gagne pour les 1 million d’appels.

12c (et 11.2.0.4) : Data Redaction

Publié le par
2

Que faire lorsqu’on a des données sensitives (numéros de carte de crédit par exemple) qu’on souhaite masquer sans perturber le fonctionnement de l’application ?

Lorsqu’on veut changer les données stockées, on peut faire du ‘Data Masking': définitivement modifier ces données sensibles. Par exemple sur une base de test issue de la prod.

Sur une base de prod, on peut utiliser Virtual Private Database pour ne pas afficher certaines colonnes.

Mais la 12c apporte une nouvelle fonctionnalité: Data Redaction

Rien n’est modifié dans la base de donnée, mais les valeurs seront masquées lorsqu’elles seront envoyées au client. Il y a plusieurs types de masquages différents:

FULL – le plus simple
Les nombres sont transformés en 0, les dates en 1er janvier de l’an 1, les chaines de caractère en un espace. L’avantage: on ne change pas le type de données pour que ce soit le plus transparent pour l’application.

PARTIAL – pour personnaliser un peu le masque sur un format spécifique, à la manière des ticket de Carte Bleue (quelques chiffres transformés en ‘*’)

REGEXP Рune expression r̩guli̬re de transformation
On peut par exemple masquer une adresse e-mail tout en gardant un format compatible avec une adresse e-mail. Toujours pour que l’application fonctionne lorsqu’elle vérifie le format.

RANDOM – généré par dbms_random pour le type de données. Par exemple pour anonymiser des noms de personnes. Les caractères aléatoires ont l’avantage de monter visiblement que ce ne sont pas des vrais noms. Pour des nombres ou des dates, au contraire, c’est un moyen de faire passer les valeurs pour des vraies.

NONE – pour afficher les vraies valeurs par exemple pour une vue sur une table qui a aurait une policy.

Et le Data Redaction peut dépendre d’un contexte, par exemple d’un role.

Par contre si l’application permet de faire des recherches sur la colonne, il sera possible de deviner les valeurs même si elles ne sont pas affichés. Il s’agit seulement de modifier les valeurs avant de les retourner dans un résultat. Il faut le coupler avec d’autres fonctionnalité pour assurer une véritable sécurité des données.

Tout cela se controle par dbms_redact.
Les différentes syntaxes et exemples dans la demo
Dans la demo, je montre que le role DBA n’applique la le Data Redaction. Pour avoir le même comportement en 11.2.0.4 qu’en 12c, j’ai dû:

GRANT EXEMPT REDACTION POLICY    TO exp_full_database;

car ce n’est pas fait par l’upgrade 11.2.0.4 (bug probablement puisque contraire à la doc et au fonctionnement de la 12c)

12c: colonnes auto-incrémentées

Publié le par
Répondre

En 12c on peut déclarer des colonnes auto-incrémentées en suivant la syntaxe SQL standard:

create table TEST ( id number generated always as identity , n number );

Plus besoin de déclarer la sequence, c’est implicite. Et cette syntaxe empêche de mettre une autre valeur que celle qui sera générée.

En 12c on peut aussi utiliser une sequence comme valeur par défaut (générée seulement lorsqu’on ne spécifie pas la valeur). et la syntaxe pour les colonnes quto-incrémentées est la suivante:

id number generated by default as identity

Enfin, nouvelle feature en 12c, on peut préciser si la valeur par défaut remplace un null:

id number generated by default on null as identity

Les exemples dans la demo

Attention, la colonne est NOT NULL, mais ne déclare pas de primary key (ni d’unicité) implicitement.