Cas de recovery en 12c

Savez-vous immédiatement quoi faire en cas de perte d’un fichier de la base de donnée (datafile, controlfile, redolog,…) ? Et éviter d’en restaurer plus que nécessaire ?

La 11g a introduit le Recovery Advisor qui peut:

  1. list failure: montrer les problèmes
  2. advise failure: donner les options de réparation, manuelles et/ou automatiques
  3. repair failure: exécuter les réparations automatiques

Dans quelques blogs suivants, je vais montrer des exemples sur des pannes classiques:
perte d’un datafile, user ou système, perte d’un ou de tous les controlfiles ou redo logs

Et montrer la réparation la plus rapide avec Recovery Advisor

Bien sûr c’et une bonne idée de s’entraîner. Et c’est facile sur une machine de test (qui peut être une machine virtuelle). Voici comment j’ai créé une base de test pour les exemples suivants:

Afin d’isoler mon test, j’utilise un ORACLE_BASE spécifique (/u/DEMO) et je met tout dessous.
Voici mon environnement:

ORACLE_BASE=/u/DEMO
TNS_ADMIN=/u/DEMO/network
ORACLE_SID=DEMO100

Création de la base avec DBCA

mkdir -p /u/DEMO/DATA/DEMO100 /u/DEMO/FRA /du/DEMO/backup
$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName DEMO100 -sid DEMO100 -sysPassword oracle -systemPassword oracle -datafileDestination /u/DEMO/DATA -recoveryAreaDestination /u/DEMO/FRA -storageType FS -characterSet AL32UTF8 -listeners LISTENER -sampleSchema  true -totalMemory 400 -databaseType MULTIPURPOSE -silent

Mise en archivelog, mirroring des redo logs,…

startup mount force;
alter database archivelog;
alter database add supplemental log data;
alter database add logfile member '/u/DEMO/DATA/DEMO100/redo01bis.log' to group 1;
alter database add logfile member '/u/DEMO/DATA/DEMO100/redo02bis.log' to group 2;
alter database add logfile member '/u/DEMO/DATA/DEMO100/redo03bis.log' to group 3;
alter database open;

Configuration RMAN (je met les backups dans un autre r̩pertoire que la FRA Рsauf le controlfile autobackup qui est dans la FRA) et premier backup:

configure controlfile autobackup on;
configure channel device type disk format '/u/DEMO/backup/%U';
configure backup optimization on;
configure compression algorithm 'high';
backup database plus archivelog;

Pour commencer, des cas simples.
Dans les posts suivants nous verrons:

  1. perte d’un datafile de USERS
  2. perte d’un datafile SYSAUX
  3. perte d’un datafile SYSTEM
  4. perte d’un redo log member
  5. perte de tous les redo log member
  6. perte d’un controlfile
  7. perte de tous les controlfiles
  8. perte du spfile

Toutes questions et remarques (comportements différents sur d’autres version par exemple) sont les bienvenues dans les commentaires.

12c: les niveaux de compression changent de nom

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)

Jointures externes (outer join)

Le type de jointure le plus utilisé, Inner Join, renvoie les lignes qui ont des correspondances dans les deux tables, et les renvoie autant de fois qu’il y a de correspondances. Par exemple, une jointure entre DEPT et EMP ne renverra pas les informations des départements qui n’ont pas d’employés, et renverra plusieurs fois les informations des départements qui ont plusieurs employés.

Il y a des variantes: on peut choisir de ne renvoyer qu’une seule fois les infos d’une table même s’il y a plusieurs correspondances dans l’autre: c’est un Semi Join (clause EXISTS ou IN) et on peut renvoyer au contraire celles pour lesquelles il n’y a pas de correspondance (NOT EXISTS ou NOT IN).

Et on peut combiner cela: un Inner Join qui renvoie en plus les infos d’une table même si il n’y a pas de correspondance. Il s’agit d’un Outer Join qui va rajouter (union all) à la jointure Inner Join les lignes venant d’un Anti-Join.

DEPT Left Outer Join EMP renvoie toutes les lignes de DEPT. Une seule fois (et avec des null pour les colonnes de EMP) lorsqu’il n’y a pas d’employés dans le département. Et plusieurs fois lorsqu’il y a plusieurs employés dans le département. On dit que la table DEPT est ici ‘preserved': on n’élimine aucune de ses infos.
Inversement, DEPT Right Outer Join EMP renvoit tous les employés même si on n’a pas d’info dans DEPT. Bien sûr, ce n’est pas le cas lorsqu’on a déclaré la Foreign Key.
Et DEPT full outer join EMP va combiner les deux

La demo montre ces jointures externes (left, right et full) en for̤ant avec des hints toutes les m̩thodes de jointures possible Рet dans chaque ordre possible Рafin de d̩terminer quelles sont les m̩thodes compatibles avec Outer Join.

Il n’y a que Hash Join qui a la possibilité de faire des Hash Join dans les deux sens: quelle que soit la ‘preserved’ table, il est toujours possible de choisir l’une ou l’autre pour construire la table de hachage. Les autres méthodes n’acceptent pas la ‘preserved table’ en second.

Ce qui veut dire que le ‘Full Outer join’ n’est possible directement (sans union all) que par Hash Join

Avec EMP comme table de hachage:

select /*+ leading(dept emp) use_hash(emp) swap_join_inputs(emp) */ * from DEPT full outer join EMP using(deptno)

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     10 |00:00:00.01 |      15 |       |       |          |
|   1 |  VIEW                 | VW_FOJ_0 |      1 |      9 |     10 |00:00:00.01 |      15 |       |       |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |      9 |     10 |00:00:00.01 |      15 |  1055K|  1055K|  517K (0)|
|   3 |    TABLE ACCESS FULL  | EMP      |      1 |      8 |      8 |00:00:00.01 |       7 |       |       |          |
|   4 |    TABLE ACCESS FULL  | DEPT     |      1 |      3 |      3 |00:00:00.01 |       8 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Avec DEPT comme table de hachage:

select /*+ leading(dept emp) use_hash(emp) no_swap_join_inputs(emp) */ * from DEPT full outer join EMP using(deptno)

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |     10 |00:00:00.01 |      15 |       |       |          |
|   1 |  VIEW                 | VW_FOJ_0 |      1 |      9 |     10 |00:00:00.01 |      15 |       |       |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |      9 |     10 |00:00:00.01 |      15 |  1321K|  1321K|  636K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT     |      1 |      3 |      3 |00:00:00.01 |       7 |       |       |          |
|   4 |    TABLE ACCESS FULL  | EMP      |      1 |      8 |      8 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------------

Que fait ‘startup’ sur une standby ?

En 11g, si on fait STARTUP ou ALTER DATABASE OPEN sur une physical standby, elle s’ouvre en read-only:

SQL> select open_mode from v$database;

OPEN_MODE            
-------------------- 
MOUNTED              

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE            
-------------------- 
READ ONLY            

Si on gère la standby manuellement, parce qu’on est en Standard Edition, sans DataGuard, alors l’apply va repasser la base en MOUNTED dès qu’on va passer en mode recovery:

SQL> alter database recover standby database until cancel;
alter database recover standby database until cancel
*
ERROR at line 1:
ORA-00279: change 996735 generated at 10/30/2013 12:41:21 needed for thread 1
ORA-00289: suggestion : /app/oracle/product/11.2.0.4/dbs/arch1_48_830170163.dbf
ORA-00280: change 996735 for thread 1 is in sequence #48


SQL> select open_mode from v$database;

OPEN_MODE            
-------------------- 
MOUNTED              

Si par contre on est en managed standby (DataGuard), lorsque l’apply va se faire – et ça peut arriver automatiquent si le broker est en apply-on:

SQL> select open_mode from v$database;

OPEN_MODE            
-------------------- 
MOUNTED              

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE            
-------------------- 
READ ONLY            

SQL> alter database recover managed standby database disconnect;

SQL> select open_mode from v$database;

OPEN_MODE            
-------------------- 
READ ONLY WITH APPLY              

On passe automatiquement en ‘realtime apply’.
Le problème: ceci active la licence Active Data Guard :(

Donc, si même si on n’a pas choisi cette option payante, il suffit de faire par erreur un ‘startup’ au lieu du’un ‘startup mount’ pour se retrouver à devoir payer cette option !

Les solutions:

1. Penser (=documenter) à désactiver l’apply si on ouvre la base. Ne pas faire d’autostart, ou alors utiliser Oracle Restart et préciser MOUNT comme option de startup.

2. Le paramètre non documenté _query_on_physical qui désactive l’option en empêchant d’ouvrir une base en cours de recovery.
Inconvénient:

SQL> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krdsmr_rsb], [1], [1], [0], [0],
[], [], [], [], [], [], []

ce n’est pas toujours très propre…

3. Dans un ‘after startup’ trigger (on l’utilise souvent pour préciser un service read-only en fonction du rôle) empêcher un OPEN (par un alter database close) lorsque le rôle est STANDBY. exemple ici.

12c: des noms de Wait Events plus parlants

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.

Il faut toujours déclarer les Foreign Key

Les contraintes d’intégrité ne servent pas seulement à vérifier l’intégrité.
Même si vous êtes sûrs de l’intégrité des données (parce que le chargement ETL le garantit par exemple), il faut déclarer les Foreign Key. C’est une information que l’on donne à l’Optimiseur sur l’état de nos données, et qui lui permettra de choisir un meilleur plan d’exécution.

La performance des chargement n’est pas une raison valable pour ne pas déclarer les foreign Key:

  • On peut choisir de ne pas valider les données existantes (NOVALIDATE) et la création de la clé étrangère sera instantanée
  • On peut choisir de ne pas vérifier les données futures (DISABLE) et les DML futurs ne seront pas pénalisés.

Mais il faut alors garantir l’intégrité des données à Oracle en mettant la contrainte en RELY et mettant le Query Rewrite à TRUSTED

La demo présente deux cas.
Un premier cas de Query Rewrite où l’utilisation de la vue matérialisée est rendue possible par la Foreign Key (soit en RELY NOVALIDATE avec query_rewrite_integrity=TRUSTED, soit en VALIDATE avec query_rewrite_integrity=enforced)
Un deuxième cas où l’optimiseur évite de faire une jointure lorsque la Foreign Key est en RELY NOVALIDATE.
Et si ce n’est pas suffisant pour justifier la création de Foreign Key en datawarehouse (où la validation est inutile vu qu’on a probablement passé des jobs de Data Quality) il faut savoir que l’optimisation STAR TRANSFORMATION sur un modèle dimensionnel ne peut se faire que si les Foreign Key vers les dimensions sont déclarées.

Bien sûr, il ne faut pas mentir à l’optimiseur, et être certain de l’intégrité de nos données. Sinon on aura un résultat faux.

Pourquoi cette demo en 11.2.0.3 ?
Je n’ai pas réussi à faire fonctionner la demo en 11.2.0.4 ni en 12.1.0.1 car le query rewrite ne se fait pas: QSM-01219: no suitable materialized view found to rewrite this query
Un SR est ouvert…

Exadata STORAGE FULL FIRST ROWS – bug ?

Une question sur le forum montre une mauvaise performance de la requête suivante:

SELECT * FROM (
   SELECT ... FROM ...
   WHERE condition_booleene = 'N' -- prédicat très selectif
   ORDER BY ...
) WHERE rownum < :variable

Exadata choisit un Smart Scan mais c’est long. Un index range scan est plus rapide même si l’index ne correspond pas à l’ordre de l’ORDER BY.
C’est bien sûr étonnant car cest le cas idéal du SmartScan: besoin de lire toute la table mais prédicat très selectif pour predicate offloading.

Lorsqu’on utilise rownum < :variable l’optimiseur passe en mode FIRST_ROWS, ce qui est logique puisqu’on ne va chercher que les premières lignes.
Mais Exadata fait une optimisation lorsqu’il sait qu’il va chercher peu de lignes: il ne démarre pas tout de suite en SmartScan car l’établissement d’une session SmartScan a un certain overhead et n’est pas justifié pour lire quelques lignes seulement. Il ne passe en SmartScan que s’il n’a pas trouvé rapidement ce qu’il cherchait.

Mais dans le cas présenté ici, avec l’ORDER BY, ça n’a pas de sens. Comme on fait un FULL SCAN, qui ne renvoit les lignes sans aucun ordre, il faudra lire toutes les lignes et les trier avant de pouvoir sortir les quelques premières. Et le tri ne se fait pas sur le Storage Cell. tout doit être renvoyé à la base. En bref, FIRST ROWS est un effet de bord du rownum, mais il n’est pas bon ici puisque il empêche le SmartScan de démarrer tout de suite. C’est probablement pourquoi l’accès par index est plus rapide. Si on ne fait pas de SmartScan, alors autant bénéficier d’un index, du buffer cache, et de flash cache…

Pour répondre à cette question du forum, j’ai fait un test sur une table de 32MB (lorsqu’elle est plus grosse, l’effet se voit moins) en faisant un:

select /*+ gather_plan_statistics */ * from (
  select * from TEST order by n 
) where rownum<=10

en désactivant cette fonctionnalité ‘FIRST ROWS’ avec le paramètre _kcfis_fast_response_enabled dont la description est ‘Enable smart scan optimization for fast response (first rows)’, puis avec l’environnement par défaut.

avec _kcfis_fast_response_enabled=false, les 4000 blocs de ma table sont lus:

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |      1 |        |     10 |00:00:00.20 |    4019 |   4017 |
|*  1 |  COUNT STOPKEY                          |      |      1 |        |     10 |00:00:00.20 |    4019 |   4017 |
|   2 |   VIEW                                  |      |      1 |   1000K|     10 |00:00:00.20 |    4019 |   4017 |
|*  3 |    SORT ORDER BY STOPKEY                |      |      1 |   1000K|     10 |00:00:00.20 |    4019 |   4017 |
|*  4 |     TABLE ACCESS STORAGE FULL FIRST ROWS| TEST |      1 |   1000K|   1000K|00:00:00.13 |    4019 |   4017 |
-------------------------------------------------------------------------------------------------------------------

avec _kcfis_fast_response_enabled=true (défaut) seulement la moitié:

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |      1 |        |     10 |00:00:00.21 |    2137 |   2135 |
|*  1 |  COUNT STOPKEY                          |      |      1 |        |     10 |00:00:00.21 |    2137 |   2135 |
|   2 |   VIEW                                  |      |      1 |   1000K|     10 |00:00:00.21 |    2137 |   2135 |
|*  3 |    SORT ORDER BY STOPKEY                |      |      1 |   1000K|     10 |00:00:00.21 |    2137 |   2135 |
|*  4 |     TABLE ACCESS STORAGE FULL FIRST ROWS| TEST |      1 |   1000K|   1000K|00:00:00.12 |    2137 |   2135 |
-------------------------------------------------------------------------------------------------------------------

Ça semble formidable … mais c’est impossible: on sait bien qu’il faut tout lire avant de pouvoir donner une réponse correcte.
En fait, c’est juste que Oracle a oublié de compter les blocs qu’il a lu avant de faire le SmartScan… ceux qui ont été lus de manière beaucoup moins efficace…

On comprend mieux sur les stats suivantes.

avec _kcfis_fast_response_enabled=false:

NAME                                                                                VALUE
---------------------------------------------------------------- ------------------------
cell IO uncompressed bytes                                                     33,120,256
cell blocks processed by cache layer                                                5,913
cell blocks processed by data layer                                                 4,043
cell blocks processed by txn layer                                                  5,913
cell physical IO bytes eligible for predicate offload                          32,915,456
cell physical IO interconnect bytes                                            29,833,624
cell physical IO interconnect bytes returned by smart scan                     29,825,432
cell scans                                                                              2
physical read bytes                                                            32,923,648
physical read total bytes                                                      32,923,648

On a 32MB à lire, et c’est ce qu’on fait. Dans mon cas, pas de prédicat ni projection, donc presque tout est renvoyé par SmartScan.

avec _kcfis_fast_response_enabled=true (défaut):

NAME                                                                                VALUE
---------------------------------------------------------------- ------------------------
cell IO uncompressed bytes                                                     17,596,416
cell blocks processed by cache layer                                                3,329
cell blocks processed by data layer                                                 2,148
cell blocks processed by txn layer                                                  3,329
cell num fast response sessions                                                         1
cell num fast response sessions continuing to smart scan                                1
cell physical IO bytes eligible for predicate offload                          17,498,112
cell physical IO interconnect bytes                                            31,273,680
cell physical IO interconnect bytes returned by smart scan                     15,848,144
cell scans                                                                              2
physical read bytes                                                            17,506,304
physical read total bytes                                                      32,923,648

On a seulement 17MB éligibles au SmartScan. Le reste a été lu sans SmartScan, au début de l’exécution, à cause du mode FIRST_ROWS (cell num fast response sessions), avant de passer en SmartScan (cell num fast response sessions continuing to smart scan).
Et malheureusement, les premières lectures ne sont pas comptées là où il faut: elles devraient être inclues dans ‘physical read bytes‘ et bien sûr dans les stats du plan d’exécution.

D’après les wait events, ces premières lectures étaient mono-bloc: ‘cell single block physical read’. C’est la raison pour laquelle elles sont plus lentes (c’est l’equivalent Exadata de db file sequential read).

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

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

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.