Explain PLSQL optimisation

[nathieb]

Bonjour,

J'ai besoin de vos services

Je suis en train de développer un module PLSQL pour optimiser
une application. connaissez vous un moyen d'obtenir une trace
de l'exécution de ma procédure PLSQL . Sachant que je suis développeur
et que je dois demander des autorisations à un DBA system si je dois
faire des appels systèmes.
EXPLAIN peut être utilisé ?

Merci
olivier

Eclairez moi, le soleil vous le rendra

[ggounouman]

Envoyé par nathieb

Bonjour,

J'ai besoin de vos services

Je suis en train de développer un module PLSQL pour optimiser
une application. connaissez vous un moyen d'obtenir une trace
de l'exécution de ma procédure PLSQL . Sachant que je suis développeur
et que je dois demander des autorisations à un DBA system si je dois
faire des appels systèmes.
EXPLAIN peut être utilisé ?

Merci
olivier

Eclairez moi, le soleil vous le rendra

Tu peux faire comme ca :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
CREATE OR REPLACE PROCEDURE P_Trace (
   vmachine   IN      VARCHAR2,
   vsource	  IN	  VARCHAR2, 
   vversion   IN      VARCHAR2,
   vmessage   IN      VARCHAR2
)
AS
   file_handle UTL_FILE.FILE_TYPE;
BEGIN
   --Ouverture du fichier de trace en mode 'Ajout'
   DBMS_OUTPUT.put_line ('début');
 
   file_handle := UTL_FILE.FOPEN('/home/oracle/admin/My_BDD/utl','trace.log','a');
   DBMS_OUTPUT.put_line ('/home/oracle/admin/My_BDD/utl');
 
   -- Ecriture de la ligne de trace
   UTL_FILE.put_line(file_handle, TO_CHAR(SYSDATE,'YYYYMMDD-HH24:Mi:ss')||' 0  '||LPAD(vmachine,8,' ')||' '|| LPAD(vsource,32,' ')||' '||LPAD(vversion,8,' ')||' '||vmessage);   
   DBMS_OUTPUT.put_line ('UTL_FILE.put_line');
 
   -- On vide le buffer dans le fichier de trace
   UTL_FILE.FFLUSH(file_handle);   
   DBMS_OUTPUT.put_line ('UTL_FILE.FFLUSH');
 
   -- Fermeture du fichier
   UTL_FILE.FCLOSE(file_handle);  
  DBMS_OUTPUT.put_line ('UTL_FILE.FCLOSE');
 
EXCEPTION  WHEN utl_file.invalid_mode THEN
    RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
  WHEN utl_file.invalid_path THEN
    RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
  WHEN utl_file.invalid_filehandle THEN
    RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
  WHEN utl_file.invalid_operation THEN
    RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
  WHEN utl_file.read_error THEN
    RAISE_APPLICATION_ERROR (-20055, 'Read Error');
  WHEN utl_file.internal_error THEN
    RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
  WHEN utl_file.charsetmismatch THEN
    RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR But Later I/O Inconsistent');
 WHEN utl_file.file_open THEN
    RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
 WHEN utl_file.invalid_maxlinesize THEN
    RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
 WHEN utl_file.invalid_filename THEN
    RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
 WHEN utl_file.access_denied THEN
    RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
 WHEN utl_file.invalid_offset THEN
    RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
 WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
	  UTL_FILE.FCLOSE(file_handle);
END P_Trace;
/

[nathieb]

Bonjour,

Et comment je fais pour l'intégrer je l'appelle de l'éxtérieur ou dans mon package

désolé pour mon ignorance

olivier

[ggounouman]

Envoyé par nathieb

Bonjour,

Et comment je fais pour l'intégrer je l'appelle de l'éxtérieur ou dans mon package

désolé pour mon ignorance

olivier

J'utilise la procédure que je vous ai fourni dans l'application, car j'y passe en paramètre le nom de la machine, le nom de l'application, la version et le message.
Vous pouvez l'insérer dans votre application, ou l'executer dans une autre procédure stockée présente que vous utiliser.
A vous de l'adapter selon vos besoins.

[Pozzo]

Bonjour,

De ce que je comprends la routine envoyée par ggounouman va à chaque fois qu'elle sera invoquée ajouter une ligne datée dans le fichier trace qu'elle manipule. Ceci permet de d'horodater le passage dans telle ou telle partie de la procédure à optimiser dans laquelle on parsème des appels à cette routine.

L'allusion à EXPLAIN de nathieb me fait penser à un package mis à disposition par Oracle : le DBMS_PROFILER. Ce package doit être installé par le dba en lançant connecté SYS le fichier profload.sql situé dans rdbms\admin.
Il est un peu ardu à exploiter "brut" mais il permet de déterminer à quel endroit la procédure à optimiser passe du temps. Carrément puissant (j'ai testé sous Oracle 9 et 10).

Ensuite une fois qu'on a repéré la requête qui est la plus consommatrice en temps on peut en effet utiliser la commande EXPLAIN PLAN pour l'optimiser.

PS : Si vous disposez d'un environnement windows de développement (du type PlSqldeveloper gratuit 30 jours chez allroundautomations) son exploitation devient plus facile.

Pozzo

[LBO72]

Pourquoi ne veuxtu pas utiliser le EXPLAIN PLAN qui te donne le chemin d'accés de ta requête et en plus une estimation du temps de réponse de ta requête.

LBO72.

[lallio]

Je ne suis pas sur de comprendre.
Tu veux écrire un module pour optimiser une appli ?
De quels ordres sont tes pb ?

Si l'unique objet de ce module est est de trouver d'ou viennent les pb de lenteurs, autant utulise tout l'armada fournit par Oracle.

En premier lieu demande à ton dba d'installer statspack (package fournit par oracle) et demande lui de générer des snap shot toutes les 15 '

Ensuite il faut qu'il te donne un compte pour pouvoir générer des rapports entre deux snap. A partir de la tu pourras identifier les différents goulets d'étranglement (requete / Accès disques / mémoire,...)

Pour info 80% des pbs de temps de réponses sont en général liés à une appli mal concu ou mal écrite(sql mauvais, pas d'index) et rarement à des pbs purement oracle (pas de stats, répartition disques,...)

[nathieb]

Pozzo a répondu partiellement à ma question.
J'ai crée un package avec plusieurs fonctions.

Ils accéèdent à une table de 67000 lignes.
Pour chaque, lignes , j'ai environ vingt champs.
Dont une dizaine doivent être controlé non null.
après ce premier controle, je fais des triples contrôles
qui s'appuie sur des tables externes.

Le Pb c'est que c'est un script PHP qui va appeler la procédure stockée.

Je cherche donc le moyen d'optimiser et de mieux comprendre ou
pourrait être mes erreurs ( requêtes, algo , ...)

olivier

[lallio]

L'un n'empeche pas l'autre. Je te garantis que Statspack est capable de sortir les requetes consommatrice, il y a également des conseils en terme de mémoire....
Par ailleurs, si dans tes requetes tu as beaucoup de
... where is null (ou is not null), je te conseille 'utiliser des indexe de types "user fonction" en utilisant la fonction nvl.
Ca permet de contourner le fait que les valeurs non nulles ne sont pas indexées.
Laurent