Archives pour la catégorie 12c

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: 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.

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).

12c: Indexer les mêmes colonnes avec différents types d’index

Publié le par
Répondre

La 11g avait apporté les index invisibles: on peut créer des index qui sont maintenus, mais ne sont pas utilisés par les requêtes. Pour pouvoir les tester par exemple.
Mais il était toujours impossible de tester un différent type d’index sur des colonnes déjà indexées (pour le passer en bitmap, en reverse, le compresser,…).
L’erreur était:

ORA-01408:  such column list already indexed
Cause:  A CREATE INDEX statement specified a column that is already indexed. A single column may be indexed only once. Additional indexes may be created on the column if it is used as a portion of a concatenated index, that is, if the index consists of multiple columns.
Action: Do not attempt to re-index the column, as it is unnecessary. To create a concatenated key, specify one or more additional columns in the CREATE INDEX statement.

En 12c, cette restriction est levée dans le cas où l’index que l’on crée est invisible.
Par exemple, je n’ai pas d’erreur en exécutant ceci:

create unique index TEST1 on TEST(num) ;
create unique index TEST2 on TEST(num) reverse invisible;

On a donc 2 index sur les mêmes colonnes. Ils sont maintenus (donc utilisables) mais un seul n’est utilisé: celui qui est visible.
Il est facile de passer de l’un à l’autre en changeant leur visibilité: c’est une opération online (pas de verrou).

Deux gros avantage:

  • créer un index peut être long. On peut le faire online, mais le fait de devoir supprimer l’ancien avant nous laissait un certain temps dans index.
  • si on ne le crée pas online, la création du nouvel index peut être très rapide car elle utilise l’ancien.

On a donc le choix: maximiser la disponibilité de l’application en le créant online, ou minimiser le temps de création de l’index.

Si l’index supporte une contrainte d’intégrité, c’est un peu plus complexe.
Le détail dans la demo avec une introduction sur de nouvelles opérations online apportées par la 12c (drop index et drop constraint).

12c: Voir le texte SQL après remplacement de toutes les vues

Publié le par
Répondre

On a parfois des requêtes qui paraissent simples, mais lisent des vues sur des vues, sur des vues,…
Ou des requêtes sur lesquelles Virtual Private Database ajoute des prédicats.

En 12c, on a un moyen simple de voir la requête finale après tous les remplacement.
C’est peut-être utile pour montrer au développeur qu’une requête qui paraît simple ne l’est pas tant que ça.

Exemple sous sqlplus:

set autoprint on long 10000 longc 10000
variable c clob
exec dbms_utility.expand_sql_text(input_sql_text=>'select * from dba_objects',output_sql_text=>:c)

Un exemple sur la demo

12c: Statistiques privées pour les tables temporaires (GTT)

Publié le par
Répondre

On travaille souvent avec des volumes différents sur les Global Temporary Tables. Assez souvent, le même code est appelé en online pour quelques enregistrements ou en batch pour des millions.
Alors quelles statistiques mettre pour les Global Temporary Table concernées ?

  • Pas de statistiques. les requêtes feront du dynamic sampling.
    Oui mais c’est seulement la première qui va optimiser le plan d’exécution qui va faire ce dynamic sampling. Les suivantes réutiliseront le même plan. Et un jour on va se retrouver avec un batch qui va travailler sur des millions de lignes en faisant un NESTED LOOP qui était prévu pour quelques lignes.
  • L’autre solution jusqu’en 11g est de fixer des stats qui amènent à choisir un plan d’exécution correct pour les 2 cas.
    Très souvent, le plan prévu pour un grand nombre de lignes est valable aussi sur un faible volume. Il vaut faire un HASH JOIN même pour quelques lignes, plutôt que de risquer de faire un NESTED LOOP à partir d’une grosse table.

En 12c, une autre solution: les statistiques privées, propres à la session. Chaque session calcule les stats sur la table temporaire chargée (et en 12c c’est automatique pendant le chargement – cf. post précédent). Et si les stats sont différentes, le curseur ne sera pas partagé. On aura donc toujours un plan d’exécution optimisé pour les données de la session.

Le choix de la visibilité des stats se fait avec dbms_stats.set_table_prefs: GLOBAL_TEMP_TABLE_STATS peut être ‘SHARED’ (comme c’était avant en 11g) ou ‘SESSION’.
La réutilisation d’un curseur dépendra aussi de ces stats.

Comme d’habitude, la demo

12c: Calcul automatique des statistiques au chargement de la table

Publié le par
Répondre

En 11g après avoir chargé une table il fallait lancer dbms_stats.gather_table_stats pour avoir les statistiques correspondant aux données chargées, ce qui se rajoute au temps de chargement.
Pour les index, les stats sont calculées lors de la création de l’index.

En 12g Oracle calcule les stats pendant qu’il charge, sur un CREATE TABLE AS SELECT, ou un INSERT /*+ APPEND */ (à condition que la table soit vide – suite à un create ou un truncate) – ce qui évite d’aller relire la table ensuite pour un calcul de stats.

Attention, si on veut des histogrammes, ou calculer les stats sur les index existants, ou avoir des stats pour chaque partition, il faudra quand même lancer dbms_stats.gather_table_stats.
Mais avec l’option ‘GATHER AUTO’ dbms_stats ne recalculera que ce qui est nécessaire. Donc on se retrouve quand même avec un gain de temps pour un process ETL par exemple.

Comme d’habitude, la demo complète.

Si l’on veut ne pas calculer les stats – pour garder les anciennes ou pour garder la table sans stats, on peut utiliser le hint NO_GATHER_OPTIMIZER_STATISTICS.
Mais de toute façon, si on veut empêcher un calcul de stats il est mieux d’utiliser dbms_stats.lock_table_stats

Cela s’applique aussi sur les Global Temporary Table, pour les statistiques privées ou partagées.

12c: déplacer un datafile online

Publié le par
Répondre

Jusqu’en 11g pour déplacer un datafile (pour le mettre sur un autre filesystem) il fallait:

  • mettre le tablespace offline
  • copier le(s) fichier(s) à partir de l’OS
  • faire ALTER DATABASE RENAME FILE … TO …

Pour diminuer le downtime, il est aussi possible de faire la copie sans mettre le tablespace offline, seulement en BEGIN BACKUP. Puis il n’y aura qu’à le mettre offline que pendant le recovery.

En 12c, plus de problème. Il suffit de faire : ALTER DATABASE MOVE DATAFILE … TO …;

C’est une opération totalement online, sans aucun verrou, et très peu d’overhead: simplement pendant que Oracle fait la copie de fichier, les process qui veulent y accéder en écriture écriront à la fois dans l’ancien fichier et le nouveau.
Et ils liront uniquement l’ancien jusqu’à ce que la copie soit terminée.
Une fois que la copie est terminée, ils liront uniquement le nouveau fichier. Et l’ancien fichier sera supprimé sauf si on a précisé KEEP.

La demo montre les statistiques de session lors d’une activité sur un tablespace en cours de move.

On voit cette double écriture dans ‘physical write total bytes’. Par contre, on ne voit jamais les fichiers en double (et ils ont le même file_id), sauf si in regarde au niveau de l’OS.
L’opération ne modifie que le controlfile, c’est donc indépendant sur des bases en DataGuard, et ce n’est pas concerné par flashback database.

Dans l’alert.log on voit au début de l’opération:

Moving datafile /tmp/TESTMOVE1.dbf (2) to /tmp/TESTMOVE2.dbf

A partir de ce moment la session crée le fichier secondaire et signale aux autres session qu’elles doivent écrire en double vers ce fichier aussi.
Puis la copie commence (‘db file sequential read’ et ‘db file single write’ par i/o de 1MB)

puis lorsque tous les blocs sont copiés, on voit:

Move operation committed for file /tmp/TESTMOVE2.dbf

les session peuvent alors ne travailler que sur le deuxième fichier. Puis l’ancien fichier est supprimé.

Completed: alter database move datafile '/tmp/TESTMOVE1.dbf' to '/tmp/TESTMOVE2.dbf'

Oracle a introduit ça pour pouvoir déplacer des partitions vers un storage moins cher au lieu de purger/archiver.
Mais c’est aussi une solution pour passer en ASM sans arrêt de la base, ou pour n’importe quel changement de filesystem.
En ASM, pas besoin de cela: on a l’équivalent en rajoutant le nouveau disque et en supprimant l’ancien.