Oracle PL/SQL Combat Learning-Avoid misuse

These days to see the Oracle PL/SQL Real-combat this book, out of the study of the performance of the statement, the downstream standard on the impact of the code.

1. Progressive processing

The program declares a cursor c1, and then implicitly opens the cursor with a cursor for loop, the program queries the Customers table for each row fetched from the cursor C1, populates the values of first_name and last_name into variables, and then inserts a row of data into the Top_sales_ Customers table.

Question 1:

The code listing 1-1 has a very problematic programming approach. Even if the SQL statement that is called in the loop is highly optimized, the execution of the program consumes a lot of time. Assuming that the SQL statement that queries the Customers table consumes 0.1 seconds and the INSERT statement consumes 0.1 seconds, it takes 0.2 seconds for each execution in the loop. If the cursor c1 out 100 000 rows, then the total time is 100 000 times 0.2 seconds, or 20 000 seconds, or about 5.5 hours. It is difficult to optimize the structure of this program. For obvious reasons, Tomkyte defines this approach as slow and slow processing (slow-by-slow processing).

Question 2:

Code listing 1-1 's code also has an inherent problem. The SQL statements that are invoked from the PL/SQL loop are repeatedly executed between the PL/SQL engine and the SQL engine, and the switchover between the two environments is called a context switch. Context switching increases the time that the program runs and increases the unnecessary CPU overhead. You should reduce the number of context switches by eliminating or reducing the switchover between these two environments. You should generally prohibit line-by-row processing, and a better programming practice is to convert code listing 1-1 into an SQL statement. Code listing 1-2 rewrites the code, completely avoiding PL/SQL

The code listing 1-2 has more advantages in addition to addressing the pitfalls of progressive processing. The rewritten SQL statements can be tuned using parallel execution, which can significantly reduce execution time by using multiple parallel execution processes. Also, the code becomes concise and readable.

2. Nested line-by-row processing

In the code listing 1-3 , C1, C2, and C3 are nested cursors. Cursor C1 is a top-level cursor that obtains data from table T1, C2 is an open cursor, passes the value obtained from the cursor C1, C3 is also an open cursor, passing the value obtained by the cursor C2. An UPDATE statement is executed once for each row returned by the cursor C3. Although the UPDATE statement has been optimized to run for 0.01 seconds at a time, the performance of the program is intolerable due to deep nesting of cursors. Assuming that cursors C1, C2, and C3 return rows of 20, 50, and 100 respectively, the code above needs to loop 100 000 rows, and the total execution time of the program exceeds 1000 seconds. Tuning this type of program usually requires a complete rewrite of it.

Another problem with code in code listing 1-3 is that an UPDATE statement is executed first. If the UPDATE statement produces a
No_data_found exception ④, then execute an INSERT statement. This type of problem can take advantage of the merge statement from PL/SQL
Go to SQL engine processing.
Conceptually, the triple loop in Code listing 1-3 represents the equivalent connection between tables T1, T2, and T3. Code Listing 1-4 Shows
An SQL statement that uses the table alias T as overridden by the above logic. The combination of the update and insert logic is replaced with the merge statement,
The merge syntax provides the ability to update existing rows and insert rows that do not exist.

Do not write code for deep nested cursors in the PL/SQL language. Review the logic of this type of code to see if it can be replaced with SQL statements.

3. Find-in Query

Lookup queries (lookup query) are generally used to populate certain variables or perform legitimate validation of data. However, performing a lookup query in a loop can cause performance problems. In code listing 1-5, the highlighted section is to use a lookup query to get the Country_name value. The program executes a query on each row in the cursor c1 to get the value of country_name. When the number of rows taken from the cursor C1 increases, the number of lookup queries is increased, which results in inefficient code.

Code listing 1-5 's code is simplistic, and a lookup query to Country_name can actually override a connection in the primary cursor C1 itself. The first step is to modify the lookup query to a connection, but in the actual application it is not necessarily possible to implement such a rewrite. If you cannot use the rewrite code to reduce the number of executions of a lookup query, there is another option. You can define an associative array to cache the results of a lookup query in order to reuse the array in subsequent executions, which can also effectively reduce the execution of lookup queries. Listing 1-6 demonstrates the array caching technique. Instead of executing a query in each row returned by the cursor C1 to get country_name, a associative array named L_country_names is used to store the country_id and country_name key-value pairs in this example. An associative array is similar to an index, and any given value can be accessed through a key value. Before performing a lookup query, a exists operation is performed to validate the existence of an element in an array that matches the COUNTRY_ID key value, and if such an element exists in the array, the country_name is fetched from the array without
To perform a lookup query. If there is no such element, then a lookup query is performed and the results are stored as a new element in the array. You also need to understand that this technique is well suited for different statements with very few key values, in this case, the less the number of unique values in the country_id column, the fewer executions the lookup query can perform. If you use the sample pattern, the number of lookup queries performed is up to 23, because the country_id column has only 23 different values.

Note that the memory required for the associative array is allocated in the PGA (Program Global area, the programs globals) of the dedicated server process in the database server, and if thousands of connections are to cache the intermediate results of the program into an array, the memory footprint will increase significantly. You should master the increase in memory usage for each process and design the database server to accommodate memory growth.

Oracle PL/SQL Combat Learning-Avoid misuse