How to optimize Oracle PLSQL Procedure performance

In the performance optimization analysis of Java, JProfiler can be used to analyze information such as CPU consumption, Memory usage, and Thread usage during JVM running. The stored procedures and functions of Oracle called in Java code can also output the call time, number of calls, and other information. However, JProfiler won't be able to cope with the internal performance of the process and function.

In an optimization practice, I encountered the above situation. The storage process is a performance problem and needs to be optimized. In this case, we need to use the DBMS_PROFILER package provided by Oracle.

 

1. What is DBMS_PROFILER? What can we do?

It is a toolkit provided by Oracle for performance analysis. The definition from the Oracle document website is roughly as follows:

This package can collect performance data during PLSQL execution, which can be used by developers for program analysis to speed up execution. Developers can open this tool in a Session, which records the performance analysis data of all "database objects (PLSQL Code such as function and procedure, "data" includes the code running times, running time, average running time, maximum running time, and minimum running time.

 

2. Use DBMS_PROFILER to analyze PLSQL Performance

The performance analysis process with it is like this: install DBMS_PROFILER package -- initialize the record table -- open the analysis switch -- run your PLSQL -- go to the analysis table and locate the bottleneck -- to solve it.

The following details:

2.1 enable DBMS_PROFILER

First check whether the installation is complete:

SQL> desc dbms_profiler

If not, run

SQL> @ C: \ oracle \ product \ 10.2.0 \ client_1 \ RDBMS \ ADMIN \ profload. SQL

Reset the record table and execute

SQL> @ C: \ oracle \ product \ 10.2.0 \ client_1 \ RDBMS \ ADMIN \ proftab. SQL

2.2 execute your PLSQL

Begin
DBMS_PROFILER.START_PROFILER ('My test ');
-- Put PLSQL here
DBMS_PROFILER.STOP_PROFILER;
End;

In this program, the profiler settings are enabled, the code to be tested is executed, and the profiler is disabled. This is a typical application. DBMS_PROFILER also supports "Suspend" and "Restore" operations:

DBMS_PROFILER.PAUSE_PROFILER, DBMS_PROFILER.RESUME_PROFILER

Then run the code, and then you can view the analysis data.

2.3 View analysis data

Performance data is recorded in the following three tables:

Select * from PLSQL_PROFILER_RUNS;
Select * from PLSQL_PROFILER_UNITS;
Select * from PLSQL_PROFILER_DATA;

After Oracle 10g is installed, restart the system. If PLSQL is used for connection, no listening is reported.

ORA-03114 PLSQL process compilation disconnection Error

Simple configuration of PLSQL connection to Oracle

Detailed description on Performance Improvement of PLSQL batch Forall operations

Use Oracle SQLDeveloper to connect to the database and create a user

Oracle PL/SQL Developer import and export data

Install the Oracle 11g and Oracle SQL Developer clients in 64-bit Windows 7