Chapter 2 procedural performance tuning of PL/SQL applications

Chapter 2 procedural performance tuning of PL/SQL applications

I. Reasons for PL/SQL Performance problems

When the execution efficiency of PL/SQL-based applications is low, it is usually caused by poor SQL statements and programming methods, poor understanding of PL/SQL basics or misuse of shared memory.

• Bad SQL statements in PL/SQL

PL/SQL programming seems relatively simple, because their complex content is hidden in SQL statements, and SQL statements often share a lot of work. This is why bad SQL statements are executed
The main cause of inefficiency is. If a program contains many bad SQL statements, no matter how good PL/SQL statements are written, it will not help.

If SQL statements reduce the speed of our programs, analyze their execution plans and performance in the following list, and then rewrite the SQL statements. For example, the prompt of the Query Optimizer may eliminate the problem, if there is no necessary full table scan.

1. Explain plan statement
2. Use the SQL Trace function of TKPROF
3. Oracle Trace function

• Bad programming habits

In general, bad programming habits also negatively affect the program. In this case, even the code written by experienced programmers may impede performance.

For a given task, no matter how appropriate the selected programming language is, writing poorly-quality subprograms (for example, a very slow classification or retrieval function) may destroy the overall performance. Assume that one of
Query functions that are frequently called by applications. If this function uses linear search instead of hash or binary, the efficiency is greatly affected. A bad program refers to variables that have never been used.
To pass unnecessary parameters, and put initialization or computing into programs executed in unnecessary loops.

• Repeated built-in functions

PL/SQL provides many highly optimized functions, such as replace, translate, substr, instr, rpad, and ltrim. Do not write our own version manually, because the built-in functions are very efficient. Even if the built-in functions far exceed our needs, do not manually implement the subset of their functions.

• Inefficient process control statements

When calculating the logical expression value, PL/SQL uses the short-circuit calculation method. That is to say, once the result can be determined, PL/SQL will stop the remaining expression calculation. For example
If sal is smaller than 1500, the value on the left of the operator is TRUE. Therefore, PL/SQL does not calculate the value of the expression on the right of the operator:

IF (Sal <1, 1500)OR (CommIS  NULL )THEN ... END  IF ;

Now, consider the AND expression below:

IF Credit_ OK (cust_id)AND (Loan <5000)THEN ... END  IF ;

In the preceding function, the Boolean function credit_ OK is always called. However, if we change the positions of two expressions as follows:

IF (Loan <5000)AND Credit_ OK (cust_id)THEN ... END  IF ;

Then, the function is called only WHEN the value of the expression loan <5000 is TRUE, which is also applicable to the EXIT-WHEN statement.

• Implicit data type conversion

During runtime, PL/SQL can implicitly convert different structured data types. For example, if the PLS_INTEGER variable is assigned to a NUMBER variable, implicit data type conversion is triggered because their internal representations are different.

Avoiding implicit type conversion can improve performance. In the following example, 15 is a signed 4-byte number. Before addition, PL/SQL must convert it to the Oracle numeric type. However, floating point 15.0 uses a 22-byte Oracle number, so there is no need to convert it.

DECLARE NNUMBER ; CCHAR (5 ); BEGIN N: = n + 15;-- Converted N: = n + 15.0;-- Not converted ... END ;

Here is an example:

DECLARE CCHAR (5 ); BEGIN C: = 25;-- Converted C: ='25' ;-- Not converted ... END ;

• Improper numeric type declaration

The data type NUMBER and its sub-types are both 22 bytes in database format. They are easy to transplant and adapt to different lengths and precision. When we need to declare an integer variable
PLS_INTEGER is the most efficient numeric type. This is because PLS_INTEGER requires less memory than INTEGER and NUMBER. Same
Example: PLS_INTEGER is a machine operation, so it is much faster than BINARY_INTEGER, INTEGER, or NUMBER.

In addition, INTEGER, NATURAL, natura1, POSITIVE, POSITIVEN, and SIGNTYPE are all constrained child types. Therefore, their variables need to check the accuracy at runtime, which will affect the efficiency.

• Unnecessary not null Constraint

In PL/SQL, using the NOT NULL constraint can also lead to performance loss. For example:

PROCEDURE Calc_mIS MNUMBER  NOT  NULL : = 0; ANUMBER ; BNUMBER ; BEGIN ... M: = a + B; ... END ;

Because m is constrained by not null, the value of expression a + B is assigned to the temporary variable, and PL/SQL tests the temporary variable. If the variable is not empty, its value can be assigned to m. Otherwise, an exception occurs. However, if m is not restricted, the result value is directly assigned to m. More efficient writing is as follows:

PROCEDURE Calc_mIS MNUMBER ;-- No constraint ANUMBER ; BNUMBER ; BEGIN ... M: = a + B; IF MIS  NULL  THEN -- Enforce constraint programmatically ... END  IF ; END ;

Note that both natura1and POSTIVEN are not null, so they also affect performance.

• VARCHAR2 Variable Length Declaration

For the VARCHAR2 type, we need to make a balance between memory usage and efficiency. Dynamic PL/SQL allocation for VARCHAR2 (length> = 2000) variables
Memory to store the actual value, but for VARCHAR2 (length <2000) variables, PL/SQL will allocate enough memory in advance. Therefore, if we put the same 500-byte value
In a VARCHAR2 (2000) and a VARCHAR2 (1999) variable, the latter occupies 1499 bytes of memory.

• Misuse of shared memory in PL/SQL programs

When the package subroutine is called for the first time, the entire package is loaded into the shared memory pool. Therefore, when we call related subprograms in the package, we no longer need to read the disk, which will speed up code execution. However, if the package is cleared from the memory, we must reload it When referencing it again.

We can improve performance by setting the size of the shared memory pool correctly. Make sure that the shared memory has enough space to store frequently used packages, but the space should not be too large to avoid wasting memory.

• Pinned Packages)

Another way to improve performance is to keep frequently used packets to the shared memory pool. When a package is maintained, it will not be used by Oracle at least recently (LRU)
Algorithm cleanup. No matter how full the pool is or how frequently we access the package, the package will always be kept in the pool. We can use the system package DBMS_SHARED_POOL to keep the package.

• Continuous reusable packages

To help us manage memory usage, PL/SQL provides the Compilation instruction SERIALLY_REUSABLE, which enables us to mark some packages as reusable continuously
(Serially
Reusable ). If the status of a package is only required during the server call time, we can use this flag for this package (for example, an OCI call to the server or a server call
Server RPC ).

The memory allocated for such a package will be placed in the system global zone (SGA), rather than allocated to the user global zone (UGA) used by independent users ). In this way, the workspace of the package can be used repeatedly. When the server call ends, the memory will be returned to the shared pool. Each time the package is re-used, its public variables are initialized to their default values or null.

The maximum number of users using the package in the workspace required for a package is the number of users currently using the package, which is usually smaller than the number of login users. The size of SGA memory is larger than that of UGA memory. In addition, if Oracle wants to recycle the SGA memory, it will expire the unused workspace.

For packages without a package body, you can use the following syntax in the package description to compile the instructions:

PRAGMA SERIALLY_REUSABLE;

For a package with a package body, you must write the compilation instructions in the description and package body. We cannot write compilation instructions only in the package body. The following example demonstrates how to use a public variable in a continuous reuse package:

CREATE  Package Pkg1Is Pragma SERIALLY_REUSABLE; NumNumber : = 0; Procedure Init_pkg_state (nNumber ); Procedure Print_pkg_state; End Pkg1; / Create  Package  Body Pkg1Is Pragma SERIALLY_REUSABLE; Procedure Init_pkg_state (nNumber )Is Begin Pkg1.num: = n; End ; Procedure Print_pkg_stateIs Begin Dbms_output.put_line ('Num :' | Pkg1.num ); End ; End Pkg1; / Begin /* Initialize package state .*/ Pkg1.init _ pkg_state (4 ); /* On same server call, print package state .*/ Pkg1.print _ pkg_state;-- Prints 4 End ; / -- Subsequent server call Begin -- The package public variable is initialized -- To the default value automatically Pkg1.print _ pkg_state;-- Prints 0 END ;

Ii. Determine PL/SQL Performance problems

When we develop more and more PL/SQL applications, we will inevitably encounter performance problems. Therefore, PL/SQL provides profiler
API to analyze runtime behavior and help us identify performance bottlenecks. PL/SQL also provides a trace
The API is used to track program execution on the server. We can use trace to track the execution of subprograms or exceptions.

1. profiler API: dbms_profiler package

The profiler API is implemented by the PL/SQL package dbms_profiler, which collects and stores runtime statistics. This information is stored in the data table for our query. For example, we can know how long each line of PL/SQL and each subroutine takes to execute.

To use Profiler, first open a performance evaluation session, fully run our application to achieve sufficient code coverage, and then save the collected information to the database, stop the performance evaluation session. The procedure is as follows:

1. Call the start_profiler process in the DBMS_PROFILER package and associate a comment with the performance evaluation session.
2. Run the application to be evaluated.
3. During Repeated calls, flush_data saves the collected data and releases the memory.
4. Call stop_profiler to stop the session.

Profiler can track program execution and calculate the time spent on each line and each subroutine. We can use the collected data to help improve performance. For example, we can focus on subprograms that run slowly.

• Analyze collected performance data

The next step is to determine why it takes a lot of time to execute some code segments or access some data structures. Locate the problem with the queried performance data. Focus on the time-consuming subprograms and packages to optimize SQL statements, loops, and recursive functions as much as possible.

• Use tracking data to improve program performance

Use our analysis results to rewrite the inefficient algorithms. For example, in the rapidly expanding data, we may need to use a binary method to replace linear search.

2. Trace API: Package DBMS_TRACE

In large and complex applications, it is difficult to track the calls of subprograms. If we use the tracking API, we can see the execution sequence of the subroutine. The tracking API is implemented by the package DBMS_TRACE and provides tracking subprograms or exceptions.

To use tracing, you must first start a tracing session, run the program, and then stop the tracing session. When a program is executed, the tracing data is collected and saved to the database. In a session, follow these steps:

1. Optional steps: select a specific subroutine to be tracked.
2. Call set_plsql_trace in the DBMS_TRACE package to enable tracing.
3. Run the application to be tracked.
4. Call clear_plsql_trace to stop the trail.

• Control tracking

Tracking large applications may produce a large amount of data that is difficult to manage. Before enabling tracing, we can choose whether to limit the amount of data to be collected.

You can also select a trail level. For example, if we can trace all subroutines and exceptions or only the selected subroutines and exceptions.

Iii. PL/SQL Performance optimization features

We can use the following PL/SQL features and methods to optimize the application:

1. Optimize PL/SQL with local dynamic SQL
2. Optimizing PL/SQL using batch binding
3. The NOCOPY compiler prompts you to optimize PL/SQL.
4. Optimize PL/SQL using the RETURNING clause
5. Optimize PL/SQL using external programs
6. Optimize PL/SQL using object types and sets

These simple and easy-to-use features can significantly improve the execution speed of applications.

1. Use local dynamic SQL to optimize PL/SQL

Some programs must execute some SQL statements that can only be determined at run time. These statements are called dynamic SQL statements. In the past, to execute dynamic SQL statements, you must use the package DBMS_ SQL. Now, we can directly use the local dynamic SQL interface in PL/SQL to execute various dynamic SQL statements.

Local dynamic SQL is easier to use and the execution speed is faster than that of DBMS_ SQL. In the following example, we declare a cursor variable and associate it with a dynamic SELECT statement that can return the data table's emp record:

DECLARE TYPE EmpcurtypIS  REF  CURSOR ; Emp_cv empcurtyp; My_enameVARCHAR2 (15 ); My_salNUMBER : = 1000; BEGIN OPEN Emp_cvFOR  'Select ename, sal FROM emp WHERE sal>: s' USING my_sal; ... END ;

2. Optimizing PL/SQL using batch binding

When SQL is executed in a collection loop, frequent switching between PL/SQL and SQL engines will affect the execution speed. FOR example, the following UPDATE statement is continuously sent to the SQL engine in the FOR statement:

DECLARE TYPE NumlistIS VARRAY (20)OF  NUMBER ; Depts numlist: = numlist (10, 30, 70 ,...);-- Department numbers BEGIN ... FOR IIN Depts. First... Depts. LastLOOP ... UPDATE EMPSET Sal = Sal * 1.10WHERE Deptno = depts (I ); END  LOOP ; END ;

In this case, if the SQL statement affects four or more rows of data, the use of batch binding significantly improves the performance. For example, the following UPDATE statement can send the data of the entire nested table to the SQL engine at once:

FORALL IIN Depts. First... Depts. Last UPDATE EMPSET Sal = Sal * 1.10WHERE Deptno = depts (I );

To improve performance as much as possible, we need to write a program as follows:

1. If an INSERT, UPDATE, or DELETE statement is executed in a loop and referenced to elements in the set, it is placed in the FORALL statement.
2. If the select into, fetche into, or returning into clause references a set, use it with the bulk collect clause.
3. If possible, use the primary array to pass the set between the application and the server.
4. If the DML operation fails and is not a major problem, you can use save exceptions in the FORALL statement and report or clear errors using the % BULK_EXCEPTIONS attribute in future cycles.

Do not ignore these small issues because they can help us analyze process control and program dependencies.

3. The NOCOPY compiler prompts you to optimize PL/SQL.

By default, parameters in out and in out modes are passed by value. That is to say, an in out real parameter will copy its copy to the corresponding form parameter. Then, if the program runs correctly, this value will be re-assigned to the real parameters of OUT and in out.

However, when real parameters are big data structures such as collections, records, and object instances, generating a copy will greatly reduce execution efficiency and consume a large amount of memory. To solve this problem, we can use the compiler
NOCOPY, which allows the compiler to pass OUT and in out parameters by reference. IN the following example, the compiler can pass the in out parameter my_unit by reference:

DECLARE TYPE PlatoonIS Varray (200)OF Soldier; PROCEDURE Reorganize (my_unitIN  OUT  NOCOPY Platoon)IS ... BEGIN ... END ; END ;

4. Use the RETURNING clause to optimize PL/SQL

Generally, the application needs to obtain the row information affected by the SQL operation. The INSERT, UPDATE, and DELETE statements can both contain a RETURNING clause.
Can return the processed field information. You do not need to use SELECT to query the affected data after INSERT, UPDATE, or DELETE. This can also reduce network traffic and
Short CPU time, requires a small amount of cursor and server memory requirements.

In the following example, we will assign the name and new salary of the current employee to PL/SQL variables while updating the employee's salary:

PROCEDURE Update_salary (emp_idNUMBER )IS "Name"VARCHAR2 (15 ); New_salNUMBER ; BEGIN UPDATE EMP SET Sal = Sal * 1.1 WHERE Empno = emp_id returning ename, SalINTO "Name", new_sal; -- Now do computations involving name and new_sal END ;

5. Optimize PL/SQL using external programs

PL/SQL provides interfaces for calling programs written in other languages. PL/SQL can call standard libraries written in other languages from the program. This improves reusability, efficiency, and program modularization.

PL/SQL is specially used for SQL transaction processing. Some tasks are more effective in low-level languages such as C.

To improve the execution speed, we can re-compile the program subject to the computing limit in C language. In addition, we can also port such a program from the client to the server, which can reduce network traffic and make more effective use of resources.

For example, we use the C language to write a method that uses the graphical object type, encapsulate it into a dynamic link library (DLL), and register it in PL/SQL, then we can call it from the application. During runtime, the database is dynamically loaded. For security reasons, it runs in a separate address space.

6. Optimize PL/SQL using object types and sets

The collection type and object type can help us improve our efficiency when modeling real-world entities. Complex entities and relationships are directly mapped to object types. In addition, a well-built object model can eliminate multi-Table connections and reduce round-trip and so on, thus improving application performance.

Client programs, including PL/SQL programs, can declare objects and sets, pass them as parameters, store them in the database, and retrieve them. Likewise, object types can encapsulate data operations, remove data maintenance code from SQL scripts, and put PL/SQL blocks into methods.

Objects and collections are more efficient in storage and retrieval because they are operated as a whole. Likewise, object types can be integrated with databases to take advantage of the scalability and Performance Improvement provided by Oracle itself.

7. Compile the locally executed PL/SQL code

We can put the PL/SQL process compilation cost code into the shared library, which can improve its execution speed. The process can also be converted to C code, compiled using a common C compiler, and connected
Oracle process. We can use this technology in the packages provided by Oracle and in our own compiling process. In this way, the compilation process can work in various server environments. Because of this technology
It is not obvious to improve the efficiency of SQL statements called from PL/SQL. Therefore, it is usually applied to PL/SQL processes with high computing and few SQL Execution time.

To speed up the execution of one or more processes, we can use this technology as follows:

1. Update makefile and enter the appropriate path and other values for our system. The makefile path is $ ORACLE_HOME/plsql/spnc_makefile.mk.
2. Use the alter system or alter session command, or update the initialization file to set the PLSQL_COMPILER_FLAGS parameter to include the value of NATIVE. The default value is INTERPRETED, which must be deleted from the parameter value.
3. Use the following methods to compile one or more processes:
   1. Use the alter procedure or alter package command to recompile the process or the entire PACKAGE.
   2. Delete the process and recreate it.
   3. Use create or replace to recompile the process.
   4. Run the SQL * Plus script to create an Oracle system package.
   5. Create a database with an initialization file containing PLSQL_COMPILER_FLAGS = NATIVE. When creating a database, run and compile the Oracle system package using the UTLIRP script.
4. Yes
   Check whether the steps are valid. You can query the data dictionary to check whether the process is compiled locally. The query view is USER_STORED_SETTINGS,
   DBA_STORED_SETTINGS and ALL_STORED_SETTINGS. For example, to view the status of MY_PROC, We can enter:

   SELECT Param_value FROM User_stored_settings WHERE Param_name ='Plsql _ COMPILER_FLAGS' AND Object_name ='My _ proc' ;

   When the PARAM_VALUE field value is NATIVE, it indicates that the process is locally compiled and executed; otherwise, it is INTERPRETED.

After the process is compiled, it will be transferred to the shared library, and they will be automatically connected to the Oracle process. We do not need to restart the database or store the shared database in another place. We can
They are repeatedly called between processes, regardless of whether they are compiled in the default method (interpreted), local execution or two mixed compilation methods.

Because PLSQL_COMPILER_FLAGS is saved in the Library Unit of each process, the original compilation method will be used during re-Compilation When the process executed by compilation cost fails.

We can use the alter system or alter session command, or set parameters in the initialization file to control the local compilation of PL/SQL:

1. PLSQL_COMPILER_FLAGS
2. PLSQL_NATIVE_LIBRARY_DIR (for security reasons, you cannot use alter session to set)
3. PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
4. PLSQL_NATIVE_MAKE_UTILITY
5. PLSQL_NATIVE_MAKE_FILE_NAME

An example of compiling a local PL/SQL statement:

CONNECT Scott/tiger; SET ServeroutputON ; ALTER SESSIONSET Plsql_native_library_dir ='/Home/orauser/lib' ; ALTER SESSIONSET Plsql_native_make_utility ='Gmake' ; ALTER SESSIONSet Plsql_native_make_file_name ='/Home/orauser/spnc_makefile.mk' ; Alter SESSIONSet Plsql_compiler_flags ='Native' ; Create  Or ReplaceProcedure Hello_native_compilationAs Begin Dbms_output.put_line ('Hello world' ); Select  Sysdate  From Dual; End ;

During compilation, we can see various compiled and connected commands executed. Then the process can be called immediately and run directly as a shared database in the Oracle process.