Archives de catégorie : Oracle

[Oracle] : comment lister les tables lockées et les dévérouiller ?

Un article oracle, cela faisait longtemps il me semble. Lorsqu’un moteur de base de donnée travaille, il est parfois nécessaire pour lui de vérouiller (locker) certains éléments notamment des tables.

Vous pouvez retrouver d’autres articles de ce type sur la page databases.

L’inconvénient c’est que suivant les cas vous ne pouvez plus y accéder. Attention, généralement un lock a toujours une bonne raison d’être posé (sauvegarde en cours, modifications, plantage, processus en attente…). Bien que chaque moteur est conçu pour limiter au maximum ces verrous cela arrive encore (les verrous se trouvent maintenant plus au niveau des lignes que sur des tables entières et le mvcc intervient également). Mais des moteurs comme mysql myisam ne savent pas fonctionner autrement (au profit d’autres intérêts : performance notamment).

Bref, pour oracle, et pour d’autres moteurs avant d’intervenir vous devez identifier pourquoi les tables sont lockées (parfois cela vient de connexions qui bouclent, de sauvegardes en cours qui buggent…). Bref comment lister les tables en question ?

A l’aide d’une requête sur la vue oracle v$locked :

> sqlplus '/ as sysdba'
> select
obj.owner, obj.object_name, obj.object_type,
sess.sid, sess.serial#, sess.status, sess.osuser, sess.machine
from
v$locked_object lock, v$session sess, dba_objects obj
where
sess.sid = lock.session_id and lock.object_id = obj.object_id;

Vous voyez aussi l’intérêt d’appeler aussi la table dba_objects et la vues v$session car vous pouvez plus facilement identifier la session et le user à l’origine du lock. Remarquez aussi que des locks peuvent être posés sur d’autres objets que des tables.

Mais que faire ensuite ? déverrouiller pardi lol !

Et là c’est simple.

> sqlplus '/ as sysdba'> alter table nom_de_table disable table lock;

Voilà je pense que nous avez fait le tour sur cette petite manip que l’on peut demander à un dba oracle.

[Oracle] : top 10 des requêtes consommatrice de temps présentes en cache

La gestion du cache au niveau d’une base de données est primordial. Une bonne gestion permet de mettre en cache les requêtes les plus courantes de manière à servir le plus rapidement possible l’utilisateur (ou l’application).

Il est donc important de temps en temps de jeter un oeil pour savoir quelles requêtes sont présentes dans ce cache et surtout celles qui y consomment du temps. Après avoir listé ces requêtes, il peut être intéressant de faire un explain plan et de les analyser pour voir comment elles pourraient être optimisées (par exemple par des index ou en segmentant une table…).

Voici donc une requête sql qui va vous permettre de lister ces top requêtes à retravailler ou à analyser :

SELECT sql_id,child_number,sql_text, elapsed_time 
  FROM (SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time,
               disk_reads,
               RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
          FROM v$sql)
 WHERE elapsed_rank <= 10;

Une vue dynamique oracle y est intérogée :

  • v$sql : qui contient les requêteset les informations relatives à celles-ci.

V$SQL contient énormément de champs, je vous invite à vous rendre sur la page d’oracle prévue à cet effet.

[SQL] : créez des constantes et des noms de résultats

DBA et développeur, cet article est pour vous… y’en faut pour tous les goûts comme on dit. Voici deux outils peu utilisés dans les bases de données que j’ai pu voir récemment. Et pourtant, ils permettent de simplifier votre script sql et de le rendre plus lisible et plus structuré (génial lol).

Les constantes en SQL

Ce n’est pas révolutionnaire mais si vous ne le savez pa déjà, vous pouvez définir un groupe de constante en sql avec la clause « WITH … AS ». Cela vous permettra d’utiliser ces données fixes comme des tables pratiquement.

Par exemple si on définit les constantes de temps :

WITH timeconstants AS (
SELECT 60 as seconds,
       60 as minutes,
       24 as hours,
       365 as days,
       7 as week,
       ['Lundi','Mardi','Mercredi','Jeudi','Vendredi','Samedi','Dimanche'] as daysweek,
),

C’est pas mal mais comment utiliser ces constantes ? comme une requête.

SELECT seconds, minutes, hours, days FROM timeconstants;

Enfantin comme dirait l’autre.

Les noms de résultats

Je parlerai plutôt d’alias mais souvent en sql on parle d’alias pour les alias de table par exemple et on l’utilise plutôt dans la clause FROM. En totu cas le principe est presque el même sauf qu’il s’agit à la manière d’un vue d’attribuer un alias à une requête entière.

On le fait ainsi :

myextract AS (
   SELECT
   IF(colonne1 < 10, "little", "big") AS qualified_sale,
   colonne1,   nb_hours,   colonne2 / 10  FROM mytable)

Et si on combine les deux on retrouve des trucs du genre :

SELECT nb_hours * minutes * secondes AS timer
FROM mytable, timeconstants;

Alors je fais un peu n’importe quoi mais c’est un exemple.Ici on utilise les constantes minutes et secondes pour calculer les heures passées en secondes.

L’intérêt vous le voyez c’est que désormais tout possède un nom et que pour appeler les éléments ils doivent être définis à travers le « FROM ». Derrière il est beaucoup plus simple de faire de la modélisation et surtout si vos constantes évoluent vous n’avez pas besoin de revoir toutes vos requêtes.

 

Pour ces éléments SQL que nous venons de voir, on parle de CTE dans le jargon DBA. CTE pour Common Table Expression. Ces petites clauses permettent de jouer sur les « alias » pour créer des pseudos tables qui ne sont pas non plus des vues et qui visent à simplifier le travail de maintenance.

[Oracle] : comment connaître la taille d’un index avant de le créer ? explain plan est ton ami

Lorsque l’on réalise un explain plan, on peut en tirer pour conclusion qu’il est nécessaire de créer un index. Attention, je ne dis pas qu’il faut abuser de ces créations d’index car trop d’index ou des index mal placés ou mal constitués peuvent causer au contraire de al consommation de ressources pour rien.

Ce qui peut être intéressant avant de créer cette index c’est d’évaluer sa taille pour voir le volume qu’il prendra au regarde de votre base. Mais comment faire ?

La encore explain plan d’Oracle est notre ami( c’est pas si souvent que Oracle est ton ami c’est pas faux). Et en plus il est gratuit… youpi !

Pour cela nous allons pratiquer un explain plan sur le create index… eh oui c’est possible.

Ainsi :

#création de l'explain
explain plan for create index monindex on toto_table ( champ_1 );

#consultation du résultat
select * from table(dbms_xplan.display);

Et voilà le tour est joué.

Pas très compliqué donc. Pour en savoir un peu plus sur les explains planset surtout comment les interpréter, je ne peux que vous recommander de consulter cette vidéo de Maria Colgan, expert de chez Oracle.