Espace technique ORACLE

Oracle hint pour transformation de requêtes

Les hints de cette catégorie sont :

•     . USE_CONCAT : Ce hint est utilisé quant la requête contient un WHERE avec l’opérateur OR. La requête est transformé en deux requêtes avec l’opérateur UNION.

•     . NO_EXPAND : Ce hint empêche l’optimiseur de considérer un coût supplémentaire pour l’opérateur OR.

•     . REWRITE : Le hint REWRITE indique à l’optimiseur de réécrire les requêtes en utilisant des vues matérialisées

•     . MERGE : Dans le cas d’utilisation d’une vue, il fusionne le SELECT de la requête dans le SELECT global

•    . STAR_TRANSFORMATION : Permet à l’optimiseur de choisir le meilleur plan d’exécution parmi les plans de la requête, et les plans de la requête transformée.

•

Oracle SQL Hints pour Access Paths

Les hints qui imposent un type d’accès à une table sont :

•     . FULL : Impose un ACCESS FULL à une table

Exemple :

SELECT /*+ FULL(E) */ NOM, PRENOM

FROM EMPLOYE E

WHERE CODE_SERVICE='INFO‘

•

     . ROWID : L’accès aux lignes de la table se fait par ROW_ID

•     . CLUSTER : Utilise un accès par le CLUSTER ( Si la table est dans un cluster)

•     . HASH : Utilise un accès par HASH ( Si la table est dans un cluster)

•     . INDEX : Force l’utilisation d’un index pour accéder à une table.

Exemple :

SELECT /*+ INDEX EMPLOYE IX_SERVICE  */ NOM, PRENOM

FROM EMPLOYE

WHERE CODE_SERVICE='INFO' 

Oracle SQL hint approche optimisation

Les hints de cette catégorie permettent d’orienter l’optimiseur à choisir la méthode à utiliser :

RULE : Si ce hint est utilisé, l’optimiseur se base sur les règles pour générer le plan d’exécution.

ALL_ROWS : Si ce hint est utilisé, l’optimiseur utilise l’approche basée sur le coût, il ramène toutes les lignes avec un coût minimum.

FIRST_ROWS(n) : Dans ce cas, l’optimiseur ramène les premières lignes avec un coût minimum (Approche basée sur le coût).

CHOOSE : Avec ce hint, le dba laisse l’optimiseur choisir entre une approche basée sur le coût, et une approche basée sur les règles.

Exemple :

SELECT /*+ ALL_ROWS */ NOM, PRENOM

FROM EMPLOYE;

Oracle SQL hint

L’optimiseur permet de choisir un plan d’exécution efficace en se basant sur les statistiques ou sur les règles. Le DBA peut imposer des choix à l’optimiseur, dans le cas où il a connaissance de certains paramètres que l’optimiseur n’intègre pas dans ses choix ( Statistiques ne sont pas à jour, besoin de lire juste une partie des données …).

Les hints permettent au DBA de prendre des décisions à la place de l’optimiseur.

Les hints sont ajoutés dans le SELECT des requêtes SQL entre « /*+ » et « */ ».

Exemple: 

Pour imposer l’utilisation d’un index

SELECT /*+ index(E IX_EMP_SAL) */ NUMERO, NOM, PRENOM, SALAIRE

FROM EMPLOYE E

WHERE SALAIRE > 1500;

Remarque : Si les hints ne sont pas indiqués correctement, Oracle les ignore sans retourner une erreur.

Les différentes catégories des hints sont :

• Hints pour approche optimisation
• Hints pour Access Paths
• Hints pour transformation de requête
• Hints for Join Orders
• Hints pour les opérations de jointure
• Hints pour l’exécution parallèle
• Autres Hints
• Hints pour l’approche d’optimisation

Library Cache Misses

Pour calculer le Library Cache Misses, la requête est :

select sum(RELOADS) / sum(PINS) miss_ratio
from  v$librarycache;

MISS_RATIO
----------
,003250392

Ce ratio doit être inférieur à 1%.

hit ratio dictionnaire de données V$ROWCACHE

La requête pour calculer le hit ration du dictionnaire de données :

select
(1 - (sum(GETMISSES) / sum(GETS))) * 100 AS DATA_DIC_RATIO
from  v$rowcache;

Il faut avoir un hit ratio suppérieur à 90%.

Buffer cache hit ratio

Le buffer cache est la partie de la SGA où sont stocker les blocks de données utilisateurs. Le cache hit ratio est un taux qui determine le nombre de fois où oracle cherche les données dans le buffer  et n'accède pas au disque. Pour les performances, c'est mieux d'avoir le cache hit ratio le plus grand possible, car cela minimise les I/O disque.

Pour avoir un buffer cache bien dimentionner, il faut un cache hit ratio suppérieur à 90%, ce qui signifie que dans plus de 9 fois sur 10 Oracle cherche les données directement dans le Buffer cache (Mémoire) au lieu de les chercher sur le disque.
La requête pour calculer le Hit ratio est :

La requête pour calculer le cache hit ratio est :

SELECT (V1.value + V2.value - V3.value) / (V1.value + V2.value) AS "Buffer cache"
FROM   v$sysstat V1, v$sysstat V2, v$sysstat V3
WHERE  V1.name = 'db block gets'
AND    V2.name = 'consistent gets'
AND    V3.name = 'physical reads';