Archives de catégorie : 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