A partir de la version 11g d'Oracle, il est possible d'avoir un cache de fonction PL/SQL inter-session. Ce cache s'appuie sur la fonctionnalité de result_cache décrite dans un autre article. La SGA est utilisée pour stocker le résultat de la fonction PL/SQL.
la clause RESULT_CACHE dans la déclaration de la fonction indique à Oracle que la fonction peut utiliser le cache de résultat. La clause optionnelle RELIES_ON permet de spécifier quelle(s) table(s) influence le cache de résultat de la fonction. Si une ou plus des tables en "relies_on" est modifiée (DML/DDL), le cache est invalidé.
Si le système à besoin de mémoire il peut invalidé les caches les plus anciens.
PL/SQL Cache de Fonction | |
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE FUNCTION GetChangeRate(i_SrcCurrId, i_TgtCurrId, i_RefDate) RETURN NUMBER RESULT_CACHE RELIES_ON (TransCurrenciesDate) IS result NUMBER BEGIN SELECT Change_Rate INTO result FROM TransCurrenciesDate WHERE SrcCurrId = i_SrcCurrId AND TgtCurrId=i_TgtCurrId AND RefDate = i_RefDate; RETURN RESULT; END; |
Le cache de résultat pour les fonctions PL/SQL ne peut être utilisé dans les cas suivants:
Une nouvelle fonctionnalité de la version 11g d'Oracle est l'introduction du cache de résultat ou "Result Cache". Ce cache est différent du cache de block déja présent ou db_cache/SGA. Il s'agit d'un cache en base de données coté base de données pour les jeux de données renvoyés par les requêtes ou des sous-requêtes.
Le cache de résultat est intégré dans le pool partagé (shared pool), il est utilisé par toutes les sessions sauf s'il est considéré comme invalide. L'invalidité d'un cache de résultat est déclaré lorsqu'un des objets utilisés par la requête est modifié.
Les requêtes qui balayent un grande quantité de blocks et retourne une faible quantité de données sont des bonnes candidates pour le result cache. Les requêtes de type décisionelle correspondent donc bien à cette définition.
Un paramètre d'initialisation : RESULT_CACHE_MODE permet de déterminer le comportement de la base :
Des paramètres d'initialisation permettent de limiter la consommation mémoire :
Lire la suite : Oracle 11g : Result Cache ou le cache de resultat
Une nouveauté sur la 11g est l'apparition de l'ACS pour Adaptive Cursor Sharing. Cette nouvelle fonctionnalité permet de pallier aux problèmes de bind rencontrer sur les requêtes SQL utilisant des paramètres.
Rappel : Il est possible d'utiliser des variables ou des litéraux dans les requêtes. Les litéraux sont des valeurs en dur dans la requête (type='manager' par exemple). Chaque requête génère alors un plan potentiellement différent pour chaque valeur du litéral. Plus le nombre requête avec des valeurs différentes est important plus la consommation mémoire dans le sharepool est importante.
De plus le calcul d'un nouveau plan (hard parse) est très couteux en CPU. Pour pallier cette problématique, l'utilisation des variables dans les requêtes permet de limiter ce problème car un seul plan est déterminé par l'optimiseur et les valeurs. Les requêtes suivantes vont reprendre le plan calculé (soft parse). Le problème c'est que le premier plan calculer n'est pas forcement le plus optimal suivant les valeurs utilisées pour chaque variable.
Un paramètre d'initialisation : CURSOR_SHARING pouvait prendre plusieurs valeurs et le comportement de l'optimiseur est différent suivant qu'il y ai ou non des histogrammes sur les colonnes :
Prenons un exemple de requête :
SELECT * FROM emp WHERE TYPE='Manager'
CURSOR_SHARING | Consommation Mémoire | Performance de la requête | Comportement de l'optimiseur |
EXACT | La plus forte (chaque requête a son propre curseur) | la meilleure (chaque requête a son propre plan, optimal pour les litéraux utilisés) |
l'optimiseur vous la requête tel quelle (qu'il y ai ou non des histogrammes) |
FORCE | La meilleure (la plus réduite possible) | potentiellement le pire, l'optimiseur forcant l'utilisation de variable et ne calculant alors qu'un seul plan. | l'optimiseur force le remplacement du ou des litéraux par des variables (qu'il y ai ou non des histogrammes) --> where type=:a |
SIMILAR sans Histo | La meilleure (la plus réduite possible) | potentiellement le pire, l'optimiseur forcant l'utilisation de variable et ne calculant alors qu'un seul plan. | Le fait qu'il n'y ai pas d'histogramme indique pour l'optimiseur que la colonne est répartie de manière équitable (not skew = pas de biais), il va donc utiliser le remplacement de la ou les variables --> where type=:a |
SIMILAR avec Histo | Pas autant que le mode EXACT mais assez proche tout de même | la meilleure (chaque requête a son propre plan, optimal pour les litéraux utilisés) | Pour l'optimiseur le fait qu'il y ai un histogramme lui indique que la colonne contient des données non uniformement distribuées (les valeurs peuvent influencer le plan). Dans ce cas, il n'opére pas de modification de la requête. --> where type='Manager' |
Avec l'ACS, le comportement de l'optimiseur est un peut plus complexe :
Lire la suite : Oracle 11g : Adaptive Cursor Sharing
Avec la version 11g, il est possible de définir la caractéristique INVISIBLE pour un index. Cela signifie que l'index est physiquement présent, qu'il est tenu à jour si des opérations DML ont lieu sur la table mais que l'optimiseur ne prend pas en compte cet index du moment ou le paramètre OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE (valeur par defaut).
Il est possible de créer un index invisible :
CREATE INDEX IX1 ON TABLE T1(cola,colb) INVISIBLE;
Il est également possible de rendre un index invisible par alter:
ALTER INDEX IX1 VISIBLE; ALTER INDEX IX1 INVISIBLE;
Afin de tester la pertinance d'un index, il suffit pour le DBA de modifier le paramètre de session OPTIMIZER_USE_INVISIBLE_INDEXES à TRUE et de vérifier si le plan d'éxécution utilise bien l'index invisible. Si le plan est satisfaisant et que l'index est bien utilisé, le DBA peut alors rendre l'index VISIBLE.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;