Générer et Exporter des Données de Performance PL/SQL dans un fichier

Visual Expert peut analyser les performances de votre code PL/SQL et générer des statistiques sur son exécution. Cette fonctionnalité repose sur des données que vous allez générer et inclure dans votre projet Visual Expert.

La manière de générer ces données dépend de votre configuration :

  • Si Visual Expert PEUT se connecter à votre base de données Oracle, vous n'avez pas besoin de suivre les étapes suivantes. Suivez simplement cette procédure à la place.
  • Si Visual Expert NE PEUT PAS se connecter à Oracle (dans le cas d'une base de données de production hébergée dans un environnement séparé par exemple) suivez la procédure ci-dessous pour générer un fichier contenant les données requises, et l'inclure dans votre projet Visual Expert.
  1. Connectez-vous à SYS en tant que SYSDBA
  2. CONNECT / AS SYSDB
  3. Créez un répertoire pour écrire un fichier avec des données de performance
    Exemple : PERFDATADIR pointera vers "C:\ODATA" comme suit :
  4. CREATE OR REPLACE DIRECTORY PERFDATADIR AS 'C:\ODATA';
  5. Autorisez l'accès à l'utilisateur en LECTURE ET EN ÉCRITURE sur le répertoire créé (PERFDATADIR) pour exécuter le package "VEPerfUtils".
    Exemple avec l'utilisateur PERFTEST :
  6. GRANT READ, WRITE ON DIRECTORY PERFDATADIR TO PERFTEST;
  7. Autorisez EXECUTE ON UTL_FILE pour exécuter le paquet "VEPerfUtils".
    Exemple avec l'utilisateur PERFTEST :
  8. GRANT EXECUTE ON UTL_FILE TO PERFTEST;
  9. Autorisez SELECT ON v_$SQL pour lire les données de performance ORACLE
    Exemple avec l'utilisateur PERFTEST :
  10. GRANT SELECT ON v_$sql TO PERFTEST;
  11. Connexion à la base de données
    Exemple : Connexion avec l'utilisateur PERFTEST
     
  12. Créez le package VEPerfUtils en exécutant le script VEPerfUtils.PLSQL dont vous trouverez le code à la fin de cette procédure.
     
  13. Exécutez la procédure VEPERFUTILS.WRITEPERFORMANCEDATATOFILE, pour lire et écrire les données de performance dans un fichier.
  14. DECLARE
    FILENAME NVARCHAR2(200);
    DIRECTORY NVARCHAR2(200);
    RECORDLIMIT NUMBER;
    BEGIN
    FILENAME := 'TestPerformanceData.veplsqlperf'; -- provide a filename. It must have the extension .veplsqlperf
    DIRECTORY := 'PERFDATADIR'; -- provide the name of the Directory object created in step #2
    RECORDLIMIT := NULL; -- provide a number to limit records or NULL to write all records
      		VEPERFUTILS.WRITEPERFORMANCEDATATOFILE(
        FILENAME => FILENAME,
        DIRECTORY => DIRECTORY,
        RECORDLIMIT => RECORDLIMIT
      );
    --rollback; 
    END;
    

Une fois que le fichier de performance est prêt, il peut être consommé dans votre Visual Expert. Pour cela, suivez les étapes suivantes :

  1. Ouvrez votre projet Oracle PL/SQL dans Visual Expert.
     
  2. Dans le menu déroulant, accédez à Paramètres > Gérer votre code source.
    Generate and Export PL/SQL Performance Data in a file
  3. Cliquez sur "Ajouter" pour ajouter votre fichier de données de performance (*.veplsqlperf) en tant que nouvel emplacement de code source.
    Generate and Export PL/SQL Performance Data in a file
  4. Dans l'assistant de projet de Visual Expert, sélectionnez "dossiers et/ou fichiers" > localisez le répertoire "PERFDATADIR" > fichier *.veplsqlperf à ajouter comme nouvel emplacement de code source et cliquez sur "Terminer".
    Generate and Export PL/SQL Performance Data in a file
  5. Une nouvelle entrée est ajoutée dans la liste pour le fichier *.veplsqlperf.
    Generate and Export PL/SQL Performance Data in a file
  6. A la fermeture de cette boîte de dialogue, Visual Expert vous demande de rafraîchir l'analyse du code. Cliquez sur "Oui".
    Generate and Export PL/SQL Performance Data in a file
  7. Une fois le processus d'analyse terminé, une nouvelle analyse est automatiquement chargée.
     
  8. Consultez cet article pour savoir comment utiliser pleinement les fonctionnalités d'analyse des performances :
    AMÉLIORER LES PERFORMANCES DU CODE.

 

==================== Code Source du fichier VEPerfUtils.PLSQL =========================

create or replace PACKAGE BODY VEPerfUtils AS 
PROCEDURE WritePerformanceDataToFile(filename nvarchar2, directory nvarchar2, recordLimit number);
END;

/

create or replace PACKAGE BODY VEPerfUtils AS 

PROCEDURE WritePerformanceDataToFile(filename nvarchar2, directory nvarchar2, recordLimit number)
IS

cursor vsql is
	SELECT v.parsing_schema_name,v.EXECUTIONS, Round(v.elapsed_time / 1000, 3) as elapsed_time, v.sql_fulltext from 
	--PerfData
	v$sql 
	v; 

perfdata vsql%rowtype; 

verecord CLOB;
vetemprecord CLOB;
veRecordCounter number := 0;
outputFileName nvarchar2(2000);
colum_delimiter nvarchar2(10) := N'«'||N'¬'||N'»';
row_delimiter nvarchar2(10) := N'«'||N'®'||N'»';

fileReference    utl_file.file_type := NULL;
dataReadLength     NUMBER := 2000; 
startOffset  NUMBER := 1; 
dataLength  NUMBER ; 
dataBuffer    NVARCHAR2(2000); 
readCharsLen NUMBER := 0;
dataBufferLength NUMBER := 0;

BEGIN
  
    verecord := NULL; 
    outputFileName := filename || '.veplsqlperf';
    dbms_output.put_line('Opening :' || directory || outputFileName);
    
    --open file
    fileReference := utl_file.fopen_NCHAR(directory, outputFileName, 'a', 32760);

    	
    for perfdata in vsql loop 
      
      veRecordCounter := veRecordCounter + 1;

	IF (recordLimit IS NOT NULL AND veRecordCounter > recordLimit) THEN
	SYS.utl_file.fclose(fileReference);
	dbms_output.put_line('total records written :'||veRecordCounter);
	return;
	END IF;


      vetemprecord := nvl(perfdata.parsing_schema_name,'') || colum_delimiter
              	|| nvl(perfdata.EXECUTIONS, 0) || colum_delimiter 
                || nvl(perfdata.elapsed_time, 0) || colum_delimiter 
		|| nvl(perfdata.sql_fulltext, '') || colum_delimiter
		|| veRecordCounter 
		|| chr(10) || row_delimiter || chr(10);

  

      verecord := verecord || vetemprecord;
      
      IF (mod(veRecordCounter , 10) = 0) Then
	
	startOffset  := 1;
	dataLength  := nvl(dbms_lob.getlength(verecord), 0); 
	dataBuffer    := ''; 

	if(dataLength > 0) then
	
		while (startOffset < dataLength) 
		loop
			
			dataBuffer := '';
			if ((dataReadLength + startOffset) > dataLength) then
				readCharsLen := dataLength - startOffset + 1;
	dbms_lob.READ(verecord, readCharsLen, startOffset, dataBuffer);
			else
	dbms_lob.READ(verecord, dataReadLength, startOffset, dataBuffer);
			end if;

			dataBufferLength := length(dataBuffer);
			
			if(dataBufferLength > 0) then
				utl_file.putF_NCHAR(fileReference, dataBuffer);
				utl_file.fflush(fileReference);
			end if;
			
			startOffset := startOffset + dataReadLength;
		END LOOP;

		verecord := NULL;

	end if;

      End IF;
      
    end loop;

    
	startOffset  := 1;
	dataLength  := nvl(dbms_lob.getlength(verecord), 0); 
	dataBuffer    := ''; 

	if(dataLength > 0) then

		while (startOffset < dataLength) 
		loop
			
			dataBuffer := '';
			if ((dataReadLength + startOffset) > dataLength) then
				readCharsLen := dataLength - startOffset + 1;
dbms_lob.READ(verecord, readCharsLen, startOffset, dataBuffer);
			else
dbms_lob.READ(verecord, dataReadLength, startOffset, dataBuffer);
			end if;

			dataBufferLength := length(dataBuffer);
			
			if(dataBufferLength > 0) then
				utl_file.putF_NCHAR(fileReference, dataBuffer);
				utl_file.fflush(fileReference);
			end if;

			startOffset := startOffset + dataReadLength;
		END LOOP;

		verecord := NULL;

	end if;
    
	dbms_output.put_line('Completed writing Total Records to file:' || veRecordCounter);
    
--close file
utl_file.fclose(fileReference);

EXCEPTION
	WHEN OTHERS THEN
		--close file
		utl_file.fclose(fileReference);
		dbms_output.put_line('ERROR_STACK: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
		dbms_output.put_line('ERROR_BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
		DBMS_OUTPUT.put_line ( 'Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm);
		dbms_output.put_line('Error Occurred');
		
		
END WritePerformanceDataToFile;
END VEPerfUtils;