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 !

12c: inclure une fonction PL/SQL dans la requête SQL

Publié le par
1

En 12c, on peut mettre une fonction PL/SQL dans la clause WITH d’une requête SQL.
Par exemple:

with
     function to_number_or_null(n varchar2)
     return number deterministic
     is
      begin
       return to_number(n);
      exception
       when others then return null;
      end;
select * from v$parameter where name='db_block_size' and to_number_or_null(value)=8192;
/

Ici j’ai déclaré une fonction vite fait qui m’évite de récupérer un ‘ORA-01722: invalid number’ lorsque la valeur n’est pas un nombre.

On s’aperçoit que l’exécution est beaucoup plus rapide lorsqu’il y a beaucoup d’appels. (cf. demo). C’est plus rapide que d’appeler une fonction stockée – sauf pour une fonction deterministic qui bénéficie du cache.

A noter: le temps d’exécution n’est pas comptabilisé dans ‘PL/SQL execution elapsed time’ du Time Model

Attention: certains clients (comme sqlplus 11g) posent des problèmes à cause des points-virgules dans la requête.

Différence entre SHRINK SPACE et SHRINK SPACE COMPACT

Publié le par
Répondre

ALTER TABLE … SHRINK SPACE permet de:

  1. déplacer les lignes vers le début de la table lorsqu’il y a de l’espace libre dans les blocs
  2. abaisser le High Water Mark pour ne plus aller lire les blocks vidés (lors d’un full table scan) et pour libérer les extents qui sont au dessus.

La première opération doit déplacer les lignes, donc elle nécessite ‘enable row movement’. Elle peut être longue (les lignes sont déplacées une par une, avec maintenance des index, comme un delete+insert) mais elle est totalement online (comme un delete+insert).

La deuxième peut poser un problème lorsqu’il y a des sessions concurrentes.
D’une part parce qu’un verrou exclusif est posé, d’autre part avec le risque que les requêtes en cours sur la table se plantent avec un ‘ORA-10632: Invalid rowid’.

Pour diminuer la plage de maintenance, il est possible de faire cela en 2 temps:

ALTER TABLE ... SHRINK SPACE COMPACT

pour faire tout le travail long de l’étape 1 – totalement online

Puis, lorsqu’il n’y a plus d’activité concurrente:

ALTER TABLE ... SHRINK SPACE

qui sera plus rapide puisque le déplacement des lignes a déjà été fait.

La demo montre les erreurs possibles et les verrous posés.

Il ne faut pas oublier non plus que dès que les stats seront calculées, des plans d’exécution pourront changer. Le nombre de BLOCKS de la table sera plus faible (favorise les FULL TABLE SCAN) et les CLUSTERING_FACTOR des index peuvent être moins bons (défavorise les accès par index).

Passer WORKAREA_SIZE_POLICY en manuel pour allouer plus de mémoire aux tris

Publié le par
Répondre

En PGA automatique (pga_aggregate_target) il y a de nombreux paramètres qui limitent la PGA allouée par un process. La plus forte limite est _pga_max_size qui fait que par défaut un process n’allouera pas plus de 200M au total, et pas plus de 100M pour une opération de tri.
C’est très bien en fonctionnement normal, le but étant d’éviter de se retrouver avec de nombreux process allouant trop de mémoire et dépassant la mémoire physique. Un serveur Oracle ne doit pas swapper vu que justement la majorité de la mémoire utilisée est allouée pour éviter des accès disque.

Mais lorsqu’on maîtrise la charge globale du système (opération de maintenance offline, batch offline, voire même OLTP avec un pool de connexion limité), on peut se permettre d’avoir des process utilisant quelques centaines de Mo. Il peut être tentant de jouer sur ces paramètres cachés mais il y a tellement de calculs par défaut qui changent de version en version qu’on risque d’amener beaucoup de problèmes.

La bonne solution, c’est de passer en gestion manuelle et de définir les sort_area_size et hash_area_size souhaités.Par exemple, pour allouer 800M aux tris:

alter session set workarea_size_policy=manual sort_area_size=838860800;

En parallel query, c’est chaque parallel process qui va allouer la taille spécifiée, d’où l’importance de maîtriser aussi le degré de parallélisme.
Un exemple dans la demo

Lire la suite →

Paramètres documentés et cachés

Publié le par
Répondre

La vue V$PARAMETER montre seulement les paramètres documentés (ceux qui ne commencent pas par un underscore).

Pour voir aussi les paramètres cachés, il faut aller voir les tables fixes:

  • X$KSPPI montre tous les paramètres et X$KSPPSV montre leurs valeurs (aussi visible par V$PARAMETER)
  • X$KSPVLD_VALUES montre les valeurs possibles pour les paramètres qui ont un choix fixe de valeurs (aussi visible par V$PARAMETER_VALID_VALUES)
  • X$KSPPO montre les paramètres qui ont été dépréciés (supprimés) ou rendus cachés

Pour voir la valeur d’un paramètre:

select ksppinm,ksppstdvl from sys.X$KSPPI join sys.X$KSPPSV using(indx)
where ksppinm like '%db_name%'

La liste de tous les paramètres sur une instance 12c dans la demo.
(600 paramètres de plus qu’en 11gR2…)

12c: PL/SQL: retour implicite d’un refcursor au client

Publié le par
Répondre

En 11g pour afficher le résultat d’un curseur le pl/sql devait renvoyer le curseur dans une variable:

variable r refcursor
exec open :r for select * from dba_users where user_id <10;
print r

En 12c avec dbms_sql.return_result() on peut le faire afficher à sqlplus un peu à la manière de dbms_output

declare
  c sys_refcursor;
 begin
  open c for select * from dba_users where user_id <10;
  dbms_sql.return_result(c);
 end;
 /

La demo est plus parlante.

Cette fonctionnalité a été introduite pour pouvoir retourner un ResultSet avec Statement.getResultSet() par exemple en Java, même pour un statement qui serait un appel à une procédure stockée. Afin le code client soit compatible avec le fonctionnement d’autres SGBD, et donc possibilité de changer de SGBD en ne modifiant que le code des procédures stockées (et ni leur signature, ni le code client).

12c EM express

Publié le par
Répondre

En 12 dbconsole est remplacée par EM express. Il est configuré automatiquement avec DBCA.

Pour configurer/changer le port:

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

PL/SQL procedure successfully completed.

Pour voir sur quel port il est configuré:

SQL> select dbms_xdb_config.gethttpsport() from dual;

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                          5500

Pour vérifier que le dispatcher est bien configuré pour XDB:

SQL> show parameter dispatcher

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP) (SERVICE=O12CDBXDB)
$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 26-JUN-2013 20:53:14

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel64)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                26-JUN-2013 20:44:40
Uptime                    0 days 0 hr. 8 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/12c/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/oel64/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel64)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oel64)(PORT=5500))(Security=(my_wallet_directory=/app/oracle/admin/O12CDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "O12CDB" has 1 instance(s).
  Instance "O12CDB", status READY, has 1 handler(s) for this service...
Service "O12CDBXDB" has 1 instance(s).
  Instance "O12CDB", status READY, has 1 handler(s) for this service...
Service "o12pdb01" has 1 instance(s).
  Instance "O12CDB", status READY, has 1 handler(s) for this service...
The command completed successfully

Je peux donc accéder à EM express sur: https://oel64:5500/em

12c Nouvelles opérations online

Publié le par
Répondre

On a vu une opération online sur les fichiers: 12c: déplacer un datafile online et la 12c amène plusieurs nouvelles opérations sur les tables qui peuvent être faites online.

Commençons par celles qui ne nécessitent pas le mot clé ‘ONLINE’ et qui sont donc possible en Standard Edition: on peut rendre un index visible/invisible sans poser de verrou DML.

Et les opérations qui peuvent se faire avec ‘ONLINE’ en Enterprise Edition:

ALTER INDEX ... UNUSABLE ONLINE;
DROP INDEX ... ONLINE;
ALTER TABLE ... DROP CONSTRAINT ... ONLINE;
ALTER TABLE ... SET UNUSED(...) ONLINE;
ALTER TABLE ... MOVE PARTITION ... ONLINE;

Le MOVE d’une table non partitionnée ne prends pas le ‘ONLINE’, mais on pouvait déjà le faire avec dbms_redefinition.

Les exemples avec les modes de verrous exacts posés par ces opérations dans la demo.

La plupart de ces opérations posent seulement un Row-S et c’est vraiment online puisque seulement les DDL exclusifs sont bloqués/bloquants.
Mais certaines posent un Row-X qui peuvent être bloqués par des opérations DML (cas de la foreign key non indexée).

Contention sur Result Cache en accès concurrent

Publié le par
Répondre

Le Result Cache peut amener facilement un gain de performance énorme s’il est bien utilisé: si l’appli fait souvent la même requête avec les mêmes paramètres, il peut suffire de mettre RESULT_CACHE dans la requête ou la fonction pour optimiser cela. C’est beaucoup plus rapide à mettre en oeuvre que de créer un cache au niveau de l’appli, gérer son invalidation, etc.

Mais ce cache n’est pas aussi optimisé que le Buffer Cache, et on peut amener une autre contention lorsque beaucoup de sessions concurrents y accèdent.
Et la raison, c’est qu’il n’y a qu’un seul Latch pour gérer le result cache de l’instance. Le symptôme, c’est une attente sur ‘enq: RC – Result Cache: Contention’

L’analyse complète de cette contention dans la demo.

Le cas le pire: utiliser result_cache avec beaucoup de valeurs différentes, ou lorsque le cache est invalidé souvent. A chaque fois qu’il y a un ‘cache miss’ c’est plusieurs latch exclusifs qui doivent être acquis.

Sur des données statiques, c’est moins grave car le latch n’est pas exclusif sur un ‘cache hit’. Attention: seulement depuis 11gR2. En 11gR1, c’était toujours exclusif.

Donc le Use Case idéal pour Result Cache, c’est plutôt des données statiques (référentiel) accédées souvent – mais pas accédées tout le temps par toutes les sessions !