Archives pour la catégorie MicroLearning

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)

Jointures externes (outer join)

Publié le par
Répondre

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 ?

Publié le par
Répondre

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

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.

Il faut toujours déclarer les Foreign Key

Publié le par
1

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 ?

Publié le par
Répondre

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

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.

Tous les Hints disponibles

Publié le par
Répondre

La vue V$SQL_HINT donne tous les hints disponibles avec la version où ils sont apparus et le hint inverse.
Assicié à v$sql_feature_hierarchy et v$sql_feature on a une indication de ce qu’ils font. Attention, beaucoup sont non documentés (et donc peuvent avoir des effets non désirés)

La demo montre cette liste pour la 12c.

le nouveaux hints documentés pour la 12c sont:

  • GATHER_OPTIMIZER_STATISTICS/NO_GATHER_OPTIMIZER_STATISTICS pour la fonctionnalité de calcul de statistiques lors de du chargement
  • PQ_CONCURRENT_UNION/NO_PQ_CONCURRENT_UNION pour la fonctionnalité de parallelisme des UNION.
  • PQ_SKEW/NO_PQ_SKEW pour préciser la distribution des colonnes de jointure en parallel query.
  • PQ_FILTER pour définir la distribution en paralle query pour les sous-requêtes
  • USE_CUBE/NO_USE_CUBE pour le nouveau type de jointure entre cubes.

Et tous les nouveaux hints pour la 12c (attention beaucoup sont non documentés):

Hint Description
WITH_PLSQL Nécessaire lorsque une fonction pl/sql est dans une clause WITH qui n’est pas au début de la requête
(NO_)CLUSTER_BY_ROWID Hint du CBO
BITMAP_AND Bitmap tree access path
(NO_)AUTO_REOPTIMIZE Automatic reoptimization (utilise les statistiques de la première exécution pour reoptimiser le plan)
DISABLE_PARALLEL_DML,ENABLE_PARALLEL_DML Equivallent de ENABLE PARALLEL DML pour une requête
(NO_)USE_CUBE, CUBE_AJ , CUBE_SJ Nouvelle methode de jointure Cube Join
(NO_)PARTIAL_JOIN Partial Join Evaluation (transformation lorsque on n’a pas besoin de toutes les lignes retournées par la jointure car il y a un distinct à appliquer ensuite)
USE_HIDDEN_PARTITIONS Fonctionalité non documentée ‘hidden partition’
(NO_)PARTIAL_ROLLUP_PUSHDOWN Hint Parallel Query
(NO_)PQ_CONCURRENT_UNION Parallel Query Рparall̩lisme pour les UNION
PQ_DISTRIBUTE_WINDOW Hint Parallel Query
PQ_FILTER Hint Parallel Query
(NO_)PQ_SKEW Hint Parallel Query
(NO_)PX_FAULT_TOLERANCE Hint Parallel Query
(NO_)PQ_REPLICATE ‘replicate small tables‘ en Parallel Query
(NO_)GATHER_OPTIMIZER_STATISTICS Collection des statistiques lors d’un chargement direct-path sur une table vide
DATA_SECURITY_REWRITE_LIMIT XS Data Security Rewrite
NO_DATA_SECURITY_REWRITE XS Data Security Rewrite
(NO_)DECORRELATE View Decorrelation (transformation pour les jointures LATERAL ?)
(NO_)ZONEMAP materialized zonemap (Exadata) Рressemble aux Storage Indexes mais stock̩ comme vues mat̩rialis̩es
(NO_)BATCH_TABLE_ACCESS_BY_ROWID Batching de l’accès par index à une table
(NO_)CLUSTERING table clustering clause (Exadata) – contrôle l’ordre physique des lignes.

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