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';

Create user, for example PERFTEST
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 project. Follow these steps to do so:

Create a new Oracle PL/SQL project.
Add the folder containing your source code and the .veplsqlperf file.
In the folder selection screen, select the folder, then choose both file extensions (.plsql and .veplsqlperf), and click [Next].
Enter your project name and details, then complete the wizard by clicking [Next].
On the final screen, select Open Project Setup and click [Finish].
Select Default DB items, enter the Default Schema Name, and click [OK].
When prompted to refresh the code analysis, click [Yes]. Visual Expert will reload and display the Oracle code performance analysis.

==================== 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

More from Visual Expert Code Performance

Visual Expert Featured Resources

Videos

Novalys Websites

Find Us