Adjustment of Performance for the Application of Pl/sql

The oracle database system is the large relation database management system which is applied most broadly at present. The PL/SQL language is a sort of block structured language, and it is the procedural language that the Oracle extends SQL, and it allows encapsulating operation logics, and its advantage is to use inscribed SQL sentence to define and realize 3GL language structure and describe more complex query, trigger, memory process and other objectives. Therefore, to more reasonably use PL/SQL program can exert important function to implement the Oracle database performances. Through the adjustment of database shared pool and the introduction of different technologies of data access, we discussed some technologies to enhance the implementation performance of PL/SQL and optimize the performance of the database system by means of PL/SQL language. PL/SQL (Procedural Language/SQL) is the extension of SQL, and it absorbs many excellent characters of programming language in recent years, such as data encapsulation, information concealment, reloading, and exception treatment. It allows the data control language and query sentence of SQL contained in the block structure and the code process language, and makes PL/SQL become into a sort of transaction processing language with powerful function. Figure 1 shows the client/server system structure of PL/SQL based on the application of Oracle. In the process of software design and development, the implementation performance of software is an important problem which we should consider. The quick and highly effective implementation performance of software is the important limited factor to decide whether its output, input, throughput and customer satisfaction are eligible. In the application of Oracle database, this sort of limitation is more important. The size of Oracle database and the user amount using this application are important factors to decide whether the application achieve maximum implementation performance through adjustment. Therefore, we need to adjust the application of PL/SQL and make its performance implement by the optimal mode according to a great lot of user's throughput and retractility. Before the application is adjusted, we need to know what reasons induce it operate so slowly, so Oracle offers many database supervision and diagnoses tools to analyze the performance of the application, or we can use the mechanism offered by PL/SQL to get the implementation time of centisecond, and this mechanism can be described as that it uses the function DBMS_UTILITY.GER_TIME, and usually transfer this function twice, and get the difference between two return values, and the …

PL/SQL (Procedural Language/SQL) is the extension of SQL, and it absorbs many excellent characters of programming language in recent years, such as data encapsulation, information concealment, reloading, and exception treatment.It allows the data control language and query sentence of SQL contained in the block structure and the code process language, and makes PL/SQL become into a sort of transaction processing language with powerful function.Figure 1 shows the client/server system structure of PL/SQL based on the application of Oracle.
In the process of software design and development, the implementation performance of software is an important problem which we should consider.The quick and highly effective implementation performance of software is the important limited factor to decide whether its output, input, throughput and customer satisfaction are eligible.In the application of Oracle database, this sort of limitation is more important.The size of Oracle database and the user amount using this application are important factors to decide whether the application achieve maximum implementation performance through adjustment.Therefore, we need to adjust the application of PL/SQL and make its performance implement by the optimal mode according to a great lot of user's throughput and retractility.
Before the application is adjusted, we need to know what reasons induce it operate so slowly, so Oracle offers many database supervision and diagnoses tools to analyze the performance of the application, or we can use the mechanism offered by PL/SQL to get the implementation time of centisecond, and this mechanism can be described as that it uses the function DBMS_UTILITY.GER_TIME, and usually transfer this function twice, and get the difference between two return values, and the difference is the time consumption of program operation, so we can confirm where is the bottleneck of the application.

The adjustment to the SGA shared pool
Both the shared memory buffer and the background course are called as an Oracle example.When one Oracle example is started, many Oracle background courses will be started, and every course takes charge the treatment to database for different aspects, and various courses communicate each other through shared memory, and this memory is the whole system SGA.SGA is divided into different areas and one of them is called as the shared pool which contains the text and PL/SQL block of SQL sentence transmitting to the database, and the presentation forms and implementation project after analysis.
Before one memory module is implemented or one memory package is cited, the codes in the coded package must be loaded into SGA.The default method to maintain the package in SGA is to let RDBMS use its arithmetic management codes which are used least recently.When one package is first cited, the coded codes will be loaded into the shared pool, so any user with right of EXECUTE to the package can use it.It will be in the shared pool until other resources based on memory need memory and that package is not used recently, and so the package is washed out from the shared pool.When this package objective is needed next time, the whole package must be loaded into the memory again.Therefore, the shared pool is larger, the probability that the program stays in the memory next time will be higher.However, too large shared pool will waste memory.So we should supervise the shared buffer to ensure it saves all analyzed SQL cursors and PL/SQL code snippets which usually are cited in the application.If too many exchanges occur, we can adjust the parameter of SHARED_POOL in the document of INIT.ORA to add the size of the shared pool (when the physical memory is allowed).

Utilizing package data to implement minimized SQL access
When announcing one variable in a package or package explanation, its function area is not limited in any special process or function.So the function area of package data is the whole Oracle dialog, and the value of that data will be continually effectively in the whole term of dialog, and we can utilize this fact to minimize the times of access from the SQL layer.The structured implementation query aiming at PGA is much quickly than the query to SGA from A to Izzard, so when the application needs to implement the query that the term of multiple re-dialogs is not changed, the above method is the most convenient one.For example, suppose that one program needs to get one exclusive dialog id to avoid folding with other dialogs, one method to realize this aim is to transfer DBMS_LOCK.ALLOCATE_UNIQUE and pick up the exclusive latch appellation.

Citing the transfer parameter
The citation of transfer parameter is to use the transfer of a finger pointing to the argument to replace the parameter transfer mechanism which transfers the copy of the argument.
The call-back of subprogram parameter is decided by the parameter type and parameter mode.The argument and the parameter in the parameter type are corresponding.For parameter, the parameter modes are respectively IN, OUT and INOUT.Two transfer modes include value transfer and address transfer when the argument is transferred.PL/SQL adopts the mode of address transfer for the parameter of IN mode, and adopts the mode of value transfer for the parameter of OUT and INOUT mode to transfer them.Because the copy of value must be made, so when large-sized data result uses OUT or INOUT parameters, the transfer of value will occupy more implementation time and consume more memory space, especially transferring the result set, the recorded index-by tables or objectives.In order to reduce this sort of consumption, we should adopt the coder clew of NOCOPY.Table 1 describes the behaviors of appointed IN, OUT, INOUT and NOCOPY.
Because of avoiding copying the argument to the parameter, the mode of cited transfer makes the program implement quicker, and when large-sized data is transferred, its meaning is more obvious.

Using local dynamical SQL and batch binding
The local dynamical SQL means the announced sentence when operating and the SQL sentence implementing dynamic confirmation, which is the interior support of dynamic SQL in PL/SQL explainer, and is implemented through the mode of IMMEDIATE and OPEN FOR without relative API.The implementation performance of local dynamic SQL is relative to the using times of dialog of local dynamic SQL, and if the dynamic SQL sentence is implemented many times in one dialog, so the local dynamic SQL is implemented quickly, because the local dynamic SQL sentence will analyze the SQL sentence when it is implemented every time.Otherwise, because the local dynamic SQL announced in the expression doesn't contain API, so it can minimize the system spending of process transfer, on the other hand, because same SQL sentences are implemented by the binding variables with different values, so one cursor can be shared by many SQL sentences, thus the binding variables in the local dynamical SQL will be implemented quicker.
The batch binding means to use set input or take many rows of records from the table to one set, then only use one order to insert, renovate and delete many rows of records in the table.This process needs few switches of context between PL/SQL and SQL, and this advantage is combined with the quickness of implementation of local dynamical SQL, which can fully enhance the implementation performance of program.

Using objective type and objective set
The objective type can simulate the entity in the real world, and it and its methods operating on these types integrate data and methods into one structure, which reduces the demands to multiple tables.The introductions of objective REFS and REF line in the database are more convenient than the use of exterior keys, because it avoids complex links.Through reducing the system spending of link treatment, we can save and operate the objectives as one whole to enhance the implementation performance of program.

Using pipelined table functions (from Oracle9i)
Pipelined table function means the function that uses PIPELINED sentence in the definition of function uses PIPEROW sentence in the interior function to return the set by the pipelined way.
The steps to establish the pipelined table functions include following aspects.

Figure 1 .
Figure 1.PL/SQL in Oracle Client/Server System Structure