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.

Tous les Hints disponibles

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

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

Un workaround pour les connexions trop fréquentes

Certaines applis n’utilisent pas de connexion permanente ni de pool de connexion. Les connexions trop fréquentes sont une catastrophe sur le serveur Oracle: création de process, allocation de ressources, etc.

Le symptôme: dans le Time Model c’est mesuré dans ‘connection management call elapsed time’.

Les solutions:

  1. corriger l’application.
  2. utiliser Shared Servers (connu il y a longtemps sous le nom MTS)
    qui est plutôt fait pour de nombreuses connexions qui restent longtemps idle.
  3. depuis la 11g, utiliser le Database Resident Connection Pool
    très simple à configurer avec dbms_connection_pool et qui permet à l’application de ne pas devoir garder la connexion hors des appels à la base

Un test en mesurant le temps de 10000 connexions successives.

Les connexions SHARED et POOLED apportent une amélioration équivalente par rapport aux DEDICATED. Les deux vont diminuer le nombre de process serveurs, mais vont ensuite gérére les ressources de manière très différentes: SHARED les mutualise en SGA, POOLED va simplement limiter le nombre de sessions.

DRCP peut être très simple à configurer:

SQL> exec dbms_connection_pool.start_pool
PL/SQL procedure successfully completed.

Ceci crée un pool de connexion par défaut de 4 connexions, pouvant s’étendre à 40. Attention, avec les valeurs par défaut la session est killée au bout de 5 minutes d’inactivité, voir 2 minutes si elle n’a rien fait depuis la connexion.

Et pour l’utiliser, on rajoute SERVER=POOLED dans la chaîne de connexion:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=service)(SERVER=POOLED))(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port)))

et il y a même une syntaxe EZCONNECT:

//host:port/service:POOLED

La config est visible dans DBA_CPOOL_INFO et peut être modifiée avec DBMS_CONNECTION_POOL

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

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