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

Un workaround pour les connexions trop fréquentes

Publié le par
Répondre

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)

Publié le par
Répondre

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

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

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

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

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

Tout ça est testé dans la demo

Comment vérifier et activer Parallel DML (nouveauté 11.2.0.4 et 12c)

Publié le par
Répondre

Vous utilisez Parallel Query pour manipuler des gros volumes de données, lors d’un ETL par exemple. Mais êtes vous sûrs que les inserts /*+ append */ aussi se font en parallèle ?

Voici un plan d’exécution où la partie select est en parallel mais un seul process fait l’insert:

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |     1 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  LOAD AS SELECT                    | TEST     |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                   |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)             | :TQ10000 |     1 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | TEST     |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Le problème ici est que le process coordinateur est le seul à faire l’insert. Il devient donc bottleneck lorsqu’on augmente le DOP.
Ce n’est pas facile à voir. On voit ça dans le plan d’exécution parce que
LOAD AS SELECT est au dessus du PX COORDINATOR, donc exécuté par le seul process coordinateur.

Ce n’est pas parce qu’une table est en parallèle que l’insert va se faire en parallèle. Il faut explicitement activer Parallel DML dans la session.

Voici le plan de la même requête après avoir fait
ALTER SESSION ENABLE PARALLEL DML;

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |     1 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     1 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT                  | TEST     |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | TEST     |     1 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Ce sont les process parallèles TQ10000 qui font l’insert (le LOAD AS SELECT) ici et le coordinateur n’est plus bottleneck. On devient scalable et on peut augmenter le degré de parallélisme tant qu’on a des ressources CPU et I/O.

Pourquoi doit-on explicitement activer Parallel DML alors que c’est automatique pour Parallel Query ?
C’est parce en dehors de la différence de performance, le comportement en concurrence d’accès est très différent: verrouillage exclusif de la table.
Oracle ne va pas faire ça sans demande explicite. On ne peut le faire que lorsqu’on sait qu’il n’y aura pas de DML concurrent sur la table.
Sur un CREATE TABLE AS SELECT, pas besoin puisque de toute façon personne d’autre n’utilise la table.

On ne peut pas activer Parallel DML au niveau de l’instance. C’est au niveau de la session qu’on le fait. Et si on veut que ce soit automatique sans changer le code, une solution est de mettre cet alter session dans un logon trigger pour le user en question.

Et nouveauté en 11.2.0.4 et en 12c – mais non documentée – il est possible d’activer ou désactiver Parallel DML au niveau d’une requête avec les hints ENABLE_PARALLEL_DML et DISABLE_PARALLEL_DML

Des explain plan avec les différentes combinaisons dans la demo.

Désactiver tous les DBLINKS

Publié le par
Répondre

Une question sur le forum: On clone la prod vers un environement de test avec RMAN DUPLICATE. On drop ensuite tous les dblinks pour être sûr que notre environnement de test n’impacte aucune prod. Mais comme RMAN DUPLICATE ouvre la base, comment être sûr que rien (connexions, jobs, etc) n’utilise les dblinks jusqu’à ce qu’on les drop ?
En 12c, il suffit de rajouter NOOPEN au DUPLICATE pour laisser la base en mount. On peut alors l’ouvrir en restricted mode pour dropper les dblinks.
Sinon, il faut supprimer les entrées du tnsnames.ora si celui-ci est utilisé. Mais ce n’est pas toujours le cas.

Une autre solution: mettre le nombre maximum de dblink ouvert par session à zéro:

open_links=0

et chaque appel à un dblink renverra: ORA-02020: too many database links in use

Un exemple dans la demo

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

Publié le par
Répondre

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

Jusqu’en 11g, les solutions sont:

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

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

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

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

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

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

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

Fixer les jointures avec les hints USE_HASH, USE_NL, USE_MERGE

Publié le par
Répondre

Il n’est pas conseillé d’utiliser des hints pour forcer l’optimiseur. Il est préférable de laisser le CBO trouver le bon plan d’exécution en lui donnant de bonnes statistiques.
Mais quelquefois, cette tâche est longue et complexe alors qu’on connaît d’emblée le plan d’exécution optimal.
L’autre cas où j’utilise souvent des hints pour forcer un plan, c’est pour vérifier un plan que je pense être meilleur: en le forçant, je vois d’abord s’il est possible, et ensuite s’il est optimal. Il ne reste plus alors qu’à voir comment arriver plus proprement à ce plan…

Si l’on doit forcer la méthode de jointure, il faut par contre faire attention à définir complètement le plan, et cela nécessite plusieurs hints. Sinon, on risque de provoquer un plan ni voulu ni optimal.

La première chose, c’est de définir l’ordre dans lequel vont se faire les jointures, avec LEADING. Il y aura toutes les tables dans LEADING.
Puis, pour chacune de ces tables, sauf la première il y aura l’un des hints suivants:

  • USE_NL(alias) pour faire un Nested Loop join: pour chaque ligne du résultat précédent, on va aller voir la table précisée dans le hint (alias)
    l’accés à alias se fera autant de fois qu’il y a de ligne venant de la table précédente.
  • USE_HASH(alias) pour faire un Hash Join. Il faut alors rajouter un autre hint pour préciser quelle est la source qui va être hachée:
    • USE_HASH(alias) NO_SWAP_JOIN_INPUTS(alias)

      • Le résultat précédent va être haché, puis on va lire alias et pour chaque ligne aller voir la table de hachage

    • USE_HASH(alias) SWAP_JOIN_INPUTS(alias)

      • C’est la nouvelle table qu’on va voir (alias) qui va être hachée, et interrogée pour chaque ligne du résultat précédent

  • USE_MERGE(alias) pour faire un Sort Merge join: le résultat précédent est trié (si ce n’est pas déjà dans le bon ordre), alias est lu et trié (dans tous les cas) puis la jointure est faite en parcourant les deux. Le résultat suit la même tri.
  • USE_MERGE_CARTESIAN(alias)

    • pour faire une jointure où la plupart des lignes de alias vont satisfaire la jointure pour la plupart des lignes du résultat précédent.
    Dans ce cas, pas de tri, pas de hachage, mais simplement un buffer (qui peut aller en tempfile) pour ne pas avoir à accéder plusieurs fois à alias (comme le ferait un Nested Loop par exemple)

Lorsque je parle du résultat précédent, c’est celui qui vient des jointures des tables de gauche dans le hint LEADING.

Dans la demo, en faisant varier les cardinalités de 3 tables, on voit les risque d’avoir d’autres plans si on ne précise pas tous les alias nécessaires.

Pourquoi SWAP_JOIN_INPUTS ? pour faire passer la table hachée au dessus du plan d’exécution, car c’est la première opération fille d’un HASH JOIN dans le plan d’exécution (parce qu’il faut avoir lu cette table avant de parcourir l’autre). Avec NESTED LOOPS, c’est l’inverse: la table qui est parcourue se trouve en premier. Avec LEADING on liste l’ordre des tables du plan d’exécution. Avec SWAP_JOIN_INPUTS pour Hash Join seulement, on change l’ordre.

12c (et 11.2.0.4) : Data Redaction

Publié le par
2

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

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

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

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

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

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

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

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

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

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

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

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

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

GRANT EXEMPT REDACTION POLICY    TO exp_full_database;

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

12c: colonnes auto-incrémentées

Publié le par
Répondre

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

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

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

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

id number generated by default as identity

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

id number generated by default on null as identity

Les exemples dans la demo

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