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.

[Oracle] : comment réaliser un explain plan ?

L’explain plan chez oracle c’est une possiblité de découvrir comment le moteur traite vos requêtes. Ainsi, vous voyez le cheminement réalisé par le moteur pour vous apporter le résultat.

Explain plan est donc le prinicipal outil pour optimiser vos requêtes et vos bases. Mais attention, le plus dur n’est pas de réaliser l’explain plan mais sa lecture et son interprétation (et ça il faut des années pour devenir un champion des l’optimisation).

Comment lancer un Explain Plan d’une requête ?

Si nécessaire, mettez en place le module utlxplan.sql

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Ensuite, définissez le nom de votre plan :

RENAME PLAN_TABLE TO monplan;

Ensuite lancez la requête qui va construire votre explain plan.

EXPLAIN PLAN FOR
SELECT champs1, champs2, champs3 FROM matable;

Maintenant, vous pouvez consulter le résultat en utilisant le DBMS_XPLAN.

SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY());

Si vous souhaitez voir quelques exemple sur comment paser un explain, vous pouvez consulter la page du site oracle.

Comment l’analyser ?

Clairement, ce n’est pas dans cet article que vous allez apprendre à optimiser vos requêtes et bases.

Sur le blog de Markus Winand, vous trouverez cette page qui va vous permettre de trouver les principaux termes que vous retrouverez dans les résultats d’un explain plan. En effet, le plan repose principalement sur l’exploitations de jointures et d’index pour vous apporter le résultat le plus vite possible.

Un résultat resemble à ceci :

| Id| Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
| 0 | SELECT STATEMENT            |               |     4 | 16208 |     3   (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| CONSULTANT    |     4 | 16208 |     3   (0)| 00:00:01 |
|*2 |   INDEX RANGE SCAN          | CONSULTANT_PK |     4 |       |     2   (0)| 00:00:01 |

Source : SOAT

Dedans on peuty découvrir :

  • le type d’opération
  • le nombre de lignes parcourues
  • le volume utilisé
  • le coût
  • le temps

Mais le principal c’est l’ordre. Un plan se lit de bas en haut. Le moteur commence par les actions du bas et remonte progressivement. Donc dans l’exemple, il accède à un index CONSULTANT_PK puis à la table CONSULTANT.

En apprendre plus par les experts oracle

Pour cela il existe une très bonne vidéo d’une interventionde Maria Colgan.

Et si vous voulez en voir une un peu plus courte mais déjà bien complète, commencez par celle de Jeff Smith.

Je vous invite à lire le document : xplan_webinar.pdf

[Oracle] : récupérer les ddl ou le sql des index d’un schéma

Oracle est tellement performant mais pourtant pas si simple à administrer. En tout cas, ce moteur génère du boulot pour les DBA lol.

Comment récupérer les ddl des index d’un schéma ? ou plutôt en français comment récupérer les sql pour voir comment sont créés les index ?

sqlplus '/a sysdba'

SQL > select dbms_metadata.get_ddl('INDEX', index_name, owner)
from all_indexes
where owner in ('NOM_SCHEMA'); 

Et voilà c’est pas plus compliqué que cela avec le dbms qui va bien Oracle vous sort le sql sans problème. Avec cela vous allez pouvoir revoir, recréer vos index sans difficultés.

[Oracle] : lister les synonymes et connaître le OWNER et la source

Oracle n’est pas toujours simple à comprendre et aujourd’hui faisons un petit focus sur les synonymes. En gros un synonyme c’est une sorte d’alias qui permet de se référer à un autre objet de l’instance. Il permet notamment de gérer les droits et les accès à certaines données.

Un synonyme peut être public dans ce cas il est ouvert à tout le monde. A l’opposé il peut être privé et dans ce cas seul le schéma (utilisateur) qui l’a créé pourra y accéder.

Attention tout le monde ne peut pas créer des synonyme, l’utilisateur doit disposer du GRANT CREATE SYNONYM pour pouvoir le faire.

Alors vous avez une base, comment lister les synonymes et y voir plus clair ?

A l’aide de la table all_synonyms :

SQL> desc all_synonyms
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER						    VARCHAR2(30)
 SYNONYM_NAME					    VARCHAR2(30)
 TABLE_OWNER					    VARCHAR2(30)
 TABLE_NAME					    VARCHAR2(30)
 DB_LINK					    VARCHAR2(128)


Vous pouvez lancer :

SQL> SET LINESIZE 1000
SQL> select SYNONYM_NAME, TABLE_NAME, TABLE_OWNER, OWNER from all_synonyms;


Pour créer un synonyme :

CREATE SYNONYME schema.nom_synonyme for schema.nom_objet 


[Oracle] : killer les sessions d’un user (déconnecter)

Un petit tips Oracle pour déconnecter un user/schéma oracle. Combien de fois on tente de passer un script ou de vider un schéma avec des connexions sui trainent encore sur l’instance.

Voici donc le sql qui génère le sql à lancer pour killer les sessions :

select 'alter system kill session ' || sid || ',' || serial# || ';'
 from v$session where username = '';
black and white hands mask bussinesman

Photo by Vijay Putra on Pexels.com

 

[Oracle] : supprimer user/schema et tablespace

Comment supprimer un schéma oracle ?

SQL> drop user TOTO cascade;

Et pour réaliser la suppression d’une liste de schémas (users), il suffit de faire une belle boucle for sur le fichier liste.schema contenant la liste suivi d’un echo puis d’un pipe avec sqlplus et le compte sysdba :

for i in $(cat liste.schema );do echo "drop user ${i} cascade;" | sqlplus '/ as sysdba'; done

Pour supprimer des tablespaces et leurs datafiles :

drop tablespace TABLESPACE_NOM including contents and datafiles ;

[Oracle] : Reconstruire les index d’un user/schéma

Cela pourrait être classé dans la rubrique tips tellement c’est simple et court. Je vous propose de réaliser une reconstruction des index d’un schéma d’une instance oracle. Rien de bien compliqué mais plutôt que de rechercher toujours comment faire, voici la soluce une fois de plus présente sur le web lol.

Dans un premier temps, comment lister les index d’un schéma ?

SELECT index_name FROM all_indexes WHERE OWNER='TOTO_OWNER';

Le mieux est bien sûr de créer un script sql qui listera tous les index et rédigera le SQL en même temps. Donc le voici :

spool rebuild_all.sql;
SELECT 'ALTER INDEX ' || index_name || ' REBUILD;' FROM all_indexes WHERE OWNER='TOTO_OWNER';
spool off;
@rebuild_all.sql

Et voilà donc, on vient de générer un ficher rebuild_all.sql dans lequel on créera une ligne par index et on ordonne un rebuild au moteur. Et on peut lancer tout cela avec un petit sqlplus ‘/as sysdba’.

black and red laptop computer beside white puma curve brim cap

Photo by Danny Meneses on Pexels.com

Promis la prochaine fois on va taper plus haut. Je pense que dans un prochain article nous verrons comment suivre le switch des redo log. Notamment pour analyser combien de switch sont réalisées chaque heure de la journée. Un bon moyen de suivre les périodes où les écritures sont les plus importantes sur votre instance oracle.

Tiens et au passage faut que je pense à vous parler de comment intégrer ce genre de script sql dans un outil comme rundeck (en terme de rédaction… échappement de quote…).

[Oracle] : suivre l’utilisation du tablepace temporaire

Hé ! un petit coup de DBA pour ce nouvel article, penchons nous sur oracle. Si vous voulez découvrir mes autres articles oracle, rendez-vous sur cette page. Le tablespace temporaire permet de faire des opération volumineuse principalement les tris. Le problème c’est de dimensionner ce TEMP de manière suffisament importante sans trop que celui-ci ne prenne de place. Si celui-ci est trop important cela peut aussi signifier une mauvaise utilisation du moteur (mauvais modèle de base de données par exemple ou mauvaises requêtes).

Un élément important pour savoir si celui-ci est assez grand c’est de connaître son utilisation. Le problème c’est que si on réalise une qequête standard pour suivre les tablespaces on tombe sur le HWM « High Water Mark ». Et comme souvent son utilisation a déjà atteint 100%, on ne sait pas son utilisation réelle.

Donc voici la requête qui vous permet de connaître son niveau à un instant T :

echo '
set lines 120;
set pages 999;
set FEEDBACK OFF;
set HEADING OFF;
SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
'|sqlplus -S '/ as sysdba'

Vous pouvez ensuite placer ce code et le logger dans un cron ou encore mettre un watch et manger du pop-corn lol !

Pour être plus complet sur le suivi de cette thématique, voici ci-dessous un ensemble de requêtes très utiles :

  • utilisation du tablespace TEMP,
  • users et requête dans le TEMP,
  • requête en cours sur le moteur
col sid_serial format a10
col username format a17
col osuser format a15
col spid format 99999
col module format a15
col program format a30
col mb_used format 999999.999
col mb_total format 999999.999
col tablespace format a15
col statements format 999
col hash_value format 99999999999
col sql_text format a50
col service_name format a15

prompt 
prompt #####################################################################
prompt #######################LOCAL TEMP USAGE#############################
prompt #####################################################################
prompt 

 SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

prompt 
prompt #####################################################################
prompt #######################LOCAL TEMP USERS#############################
prompt #####################################################################
prompt 

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, 
--S.module,
--P.program,
s.service_name,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$tempseg_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, 
S.module,
P.program,
s.service_name,TBS.block_size, T.tablespace
ORDER BY mb_used;

--prompt 
--prompt #####################################################################
--prompt #######################LOCAL ACTIVE SQLS ############################
--prompt #####################################################################
--prompt 
--
SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text,vsu.segtype, vsu.tablespace,vs.service_name,
        sum_blocks*dt.block_size/1024/1024 usage_mb
    FROM
    (
            SELECT username, sqladdr, sqlhash, sql_id, tablespace, segtype,session_addr,
                 sum(blocks) sum_blocks
            FROM v$tempseg_usage
        group by  username, sqladdr, sqlhash, sql_id, tablespace, segtype,session_addr
    ) "VSU",
    v$sqltext vst,
    v$session vs,
    v$process vp,
    dba_tablespaces dt
 WHERE vs.sql_id = vst.sql_id
    AND vsu.session_addr = vs.saddr
    AND vs.paddr = vp.addr
    AND vst.piece = 0
    AND vs.status='ACTIVE'
    AND dt.tablespace_name = vsu.tablespace
 order by usage_mb;
--
--prompt 
--prompt #####################################################################
--prompt #######################LOCAL TEMP SQLS##############################
--prompt #####################################################################
--prompt 
SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.sql_id, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$tempseg_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
--
--