Archives pour la catégorie MicroLearning

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.

12c: faibles améliorations sur les verrous en intégrité référentielle

Publié le par
Répondre

En 11g les verrous Row-S posés sur la table opposée lors de DML sur une table ayant un intégrité référentielle sont devenus des Row-X
Et cela a amené pas mal de régression: lorsqu’il y a des tables verrouillées en Share (à cause de foreign key non indexées) l’impact est devenu beaucoup plus important. Cette régression a été introduite pour corriger un bug: Bug 5909305 – Change to DML (TM) lock modes for foreign key constraints (Doc ID 5909305.8)

Il semble qu’en 12c ce soit un peu mieux: Certains Row-X redeviennent des Row-S, verrous légers qui ne bloquent aucun DML.

  • Sur insert dans table parente. C’est maintenant un Rows-S qui est posé sur les tables filles, comme avant la 11g
  • Lorsqu’on fait un delete sur une table fille on retrouve un Row-S sur la table parent comme avant la 11g

Par contre, on a toujours un Row-X sur la table parent lorsqu’on insert dans la table fille ou qu’on update la foreign key.

La liste complète des verrous posés par différentes opérations dans la demo.

Les explications de ces verrous dans la préz (11g): http://prezi.com/uzdd5ttg4cu0/indexing-foreign-keys-in-oracle/

Et une explication sur les modes de verrous: http://www.soug.ch/fileadmin/user_upload/Newsletter/NL_public/NL_2013_1_Award_Article.pdf et http://prezi.com/cdckwsgqxeyi/oracle-table-lock-modes/

12c: ‘update global indexes’ enfin très rapide sur drop et truncate

Publié le par
Répondre

On partitionne les tables pour 2 raisons:

  • Performance: aller lire un plus petit segment en full scan, ou un plus petit index local grâce au partition pruning
  • Maintenabilité: faire des opérations de purge, archivage, reorganisation, voir chargement (avec exchange partition) de manière très facile

Mais toutes les requêtes ne font pas de partition pruning, et on est amené créer des index globaux pour des raisons de performances. Mais alors on perd de la maintenabilité: les opérations sur les partitions vont:

  • soit rendre l’index unusable, qu’il faudra rebuilder complètement. Ce n’est pas acceptable lorsque notre maintenance ne touchait qu’une partition parmi beaucoup d’autres
  • soit utiliser ‘update global indexes’ pour maintenir l’index au fur et à mesure. Mais l’opération devient alors très longue.

Et pourtant, s’il s’agit d’un DROP PARTITION ou TRUNCATE PARTITION, cette maintenance d’index consiste seulement à nettoyer les entrées d’index qui deviennent orphelines (i.e qui adressent un rowid qui n’existe plus).

Alors la 12c a optimisé cela en faisant l’opération en 2 temps, utilisant la fonctionnalité ‘Asynchronous Global Index Maintenance’ ou ‘Fast Index maintenance':

  1. un ALTER TABLE … DROP/TRUNCATE PARTITION UPDATE GLOBAL INDEXES

    2. ne va pas toucher aux index globaux. Il va seulement enregistrer le data_object_id du segment qu’on vient de supprimer (un truncate ne supprime pas vraiement le segment, mais en change son data_object_id ce qui revient au même logiquement). C’est enregistré dans la table du dictionnaire SYS.INDEX_ORPHANED_ENTRY$.

  2. les requêtes qui lisent l’index chargent ces infos pour ignorer tous les rowid qui référencent ce data_object_id
  3. un job automatique (un de plus) va faire le nettoyage de manière asynchrone

On peut faire ce nettoyage nous même avec

ALTER INDEX ... COALESCE CLEANUP;

mais aussi appeler

exec dbms_part.cleanup_gidx;

qui va faire ce coalesce sur tous les index référencés par

SYS.INDEX_ORPHANED_ENTRY$

Différentes variations dans la demo.
Le cleanup peut charger la machine, c’est peut-être mieux de l’anticiper à un moment de faible charge plutôt que de le laisser tourner à 2 heures du matin comme c’est planifié par défaut.

C’est une amélioration importante: en Entreprise Edition, il n’y a plus à hésiter pour partitionner les tables qui grossissent afin que l’augmentation du volume de l’historique ne pénalise pas les performances. Sans avoir peur de définir trop d’index global. Une fois partitionné, ce sera facile de maintenir les plus anciennes partitions: purger, archiver, déplacer sur des tablespaces (move online en 12c) qui sont sur des disques moins chers. Et plus on laisse les tables grossir, plus l’opération de partitionnement sera compliquée !