"Excellent example": Oracle Code Procedure

Using arrays in PL/SQL is a good practice (for example, using bulk collect and forall ). Batch Processing can greatly reduce the number of environment Switches of the PL/SQL statement execution engine, thus improving its performance.

Another good example is to put all the code in the stored procedure into the locked software package, which can generate module units. Put the stored procedure into a software package to group related programs and functions. When
When a single package is used, the entire package will be loaded into the memory (the memory will start the entire package ?), Minimize the disk access time. In this way, we can also load the entire application package into the memory to prevent
Stop re-loading and code parsing, thus reducing code recursion that seriously affects performance.

Another good example of PL/SQL (and SQL) is to use the appropriate variable type (do not use varchar2 when you need number, or vice versa ). Improper variables (compared with character and number) may cause useless indexes. One way to ensure that the variable type is correct is to use % Type and % rowtype.

In addition, you can always use dbms_profiler or the quest code tester tool like quest software to verify the cyclic logic. Dbms_profiler is a software package provided by Oracle. It enables your code to track the execution time and time required for each line. You can verify that the number of loop executions is the minimum.

You should also verify the appropriate if-then-else structure. I mean you should put the most common options in front (such as exit test ). This method also uses the case structure.

In PL/SQL (and Java, C, C ++, and other Oracle-related 3gls), another more important example is to optimize SQL first. Even if the world's most sophisticated programs contain poor SQL, the performance will be poor. The SQL optimizer, Performance Analyzer, toad, and SQL navigator of Quest can all optimize SQL.

When testing PL/SQL-SQL code, anonymous PL/SQL blocks should be used to ensure the similarity of the processing environment. If you use text instead of binding variables in a standard SQL environment for testing, the execution plan you get is different from the execution plan you get when you use anonymous PL/SQL blocks and bind variables, so your adjustment may not get good results. Using quest code tester ensures that you can obtain the desired results.

Another good programming practice is to make good use of temporary tables and PL/SQL index tables. Improper use of the "normal" table for temporary storage can reduce performance, so use the temporary table. When the memory space is sufficient to run PL/SQL tables, using a temporary table will also greatly reduce the performance. You can also use a view to create a cache table with intermediate results.

A simple and easy-to-use example is to use nocopy on the in out and out variables. In a PL/SQL program that uses in out or out, if the nocopy keyword is not included in the variable declaration of its header, all variables are passed through the copyout Program (passing values instead of passing references ). Using noncopy, all values are passed through reference, which will greatly improve the running performance.

Finally, all SQL developers are required to generate an explain execution plan, which is then reviewed by their peers. In a large development plan, it is impossible for the database administrator to check all the code to see if the best performance is achieved. Therefore, it is necessary for developers to first optimize the SQL code performance. As long as the developer optimizes the SQL index function and adjusts it to the correct logic and technology, the database administrator can perform more advanced Optimization on difficult-to-handle problem code.