Archives pour la catégorie MicroLearning

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 →

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 !

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