Generate and Export PL/SQL Performance Data in a file

Visual Expert can analyze the performance of your PL/SQL code and generate some statistics about its execution. This feature relies on data that you will generate and include in your Visual Expert project.

The way to generate this data depends on your configuration:

If Visual Expert can connect to your Oracle DB, you DON’T NEED to follow this procedure. Just follow this article instead.
If Visual Expert CANNOT connect to Oracle - for instance with a production database hosted in a separate environment - follow the procedure below to generate a file containing the required data, and include it in your Visual Expert project.

Connect to SYS as SYSDBA

CONNECT / AS SYSDB

Create a directory to write a file with performance data
Example: PERFDATADIR will point to 'C:\ODATA' as follows:

CREATE OR REPLACE DIRECTORY PERFDATADIR AS 'C:\ODATA';

Grant READ AND WRITE access on the above created directory (PERFDATADIR) to user to execute package "VEPerfUtils".
Example with the user PERFTEST:

GRANT READ, WRITE ON DIRECTORY PERFDATADIR TO PERFTEST;

Grant EXECUTE ON UTL_FILE to execute the package "VEPerfUtils".
Example with the user PERFTEST:

GRANT EXECUTE ON UTL_FILE TO PERFTEST;

Grant Select ON v_$SQL to read performance data from ORACLE
Example with the user PERFTEST:

GRANT SELECT ON v_$sql TO PERFTEST;

Connect to the database
Example: Connect with the user PERFTEST
Create package VEPerfUtils by executing the script VEPerfUtils.PLSQL listed at the end of this procedure.
Execute the procedure VEPERFUTILS.WRITEPERFORMANCEDATATOFILE, to read and write performance data in a file

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;

Once the performance file is ready after following the above mentioned process, it can be consumed inside your Visual Expert. Follow these steps to do so:

Open your Oracle PL/SQL project in Visual Expert.
From the ribbon menu, navigate to Settings > Manage your source code.
Click on “Add” to add your performance data file (*.veplsqlperf) as a new source code location.
In the Visual Expert Project Wizard, select “folders and/or files” > locate the directory 'PERFDATADIR' > *.veplsqlperf file to add as a new source code location. Click on “Finish”.
A new entry is added in the list for the *.veplsqlperf file.
Upon closing this dialog, Visual Expert notifies to refresh the code analysis. Click on “Yes”.
After the completion of the Analysis Process, a new analysis is loaded automatically.
Visit this article to leverage the latest features available for analyzing performances:
IMPROVE CODE PERFORMANCE.

==================== Source of the file 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;

Generate and Export PL/SQL Performance Data in a file

Visual Expert Featured Resources

Videos

Novalys Websites

Find Us