To explain the life cycle of Oracle Middle-bid

In the Web or in books, it is easy to understand that the life cycle of the Oracle midstream includes the following sections:

1, open the cursor--open cursor, this step applies a piece of memory to the cursor in the UGA, this time the cursor has not been associated with the SQL statement.
2, parse the cursor--SQL is associated with the cursor, parsing the contents of the SQL (including the execution plan), and the parsed content is loaded into the shared pool (share pool--library cache). The memory requested in the UGA is used to save points to the location of the shared cursor (share cursor) in the library cache.
3, define the output variable--if the SQL statement returns data, you must first define the variables that receive the data. This is important not only for query statements, but also for delete, insert, and UPDATE statements that use returning.
4, bind the input variable--if the SQL statement uses the binding variable, you must supply their value. The process of binding is not doing anything check. If invalid data is specified, a run-time error is burst during execution.
5, execute cursor--executes the SQL associated with the cursor. Note that the database does not always do important things in this step. In fact, for many types of query statements, the real process is usually deferred to the fetch data phase.
6, get the cursor--if the SQL statement returns data, this step accepts the data. Especially in query statements, most of the processing work is done in this step. In a query statement, you may only read part of the record, in other words, the cursor may be closed before fetching all the records.
7, close the cursor-frees the resources associated with this cursor in the UGA so that these resources are available for use by other cursors. The share cursor in the library cache will not be purged, and it continues to remain in the library cache, waiting to be reused (soft parse reuse).

Repeat the content, we do not introduce too much, today we look at the role of each part of the downstream standard lifecycle and how to use them to optimize our programs.

Using the code snippet from the Oracle Performance Diagnostics art, let's look at the lifecycle of the cursor;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 The

The execution efficiency of tracking lines of code through Plsql Profiler is as follows:

As you can see, the various parts of the cursor lifecycle occupy the execution time, so if you can eliminate some of the execution steps, you obviously improve performance. Those operations can eliminate that? participate in the following figure

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26-27--28 29---30 31--32 33 34 35 36 37 38-39 40 41 42 45 46 47 48 49 50 51 52 53 54 55

--                      -----------  --                     | Open_cursor |   --                      -----------  --                            |   --                            |   --                            v   --    &NBsp;                    -----  --         ------------>| Parse |   --         |               -----  --         |                  |   --         |                  |---------  --          |                  v         |   --         |  &Nbsp;       --------------  |   --         |-------->| bind_variable | |   --         |     ^     -------------   |   --         |     |            |         |   --         |     -----------|          |   --         |                  |<--------  --          |                &nbsp V   --         |                Query?----------Yes---------  --          |                  |                            |   --         |                 no                            |   --         |                  |                            |   --         |                  v                            v    --         |              -------                  -------------  --         |---- ------->| Execute |           ->| Define_column |   --         |             -------              |   -------------  --          |                  |------------    |          |   --         |                  |             |   ----------|   --         |                  v             |              v   --         |           --------------     |           -------  --         |       ->| Variable_value |   | ------>| Execute |   --         |      |    --------------     | |        -------   --         |      |           |            | |             |   --         |       ----------|            | |             |   --         |                  |             | |            v   --          |                  |            | |        ----------  --         |                  |<-----------   |----->| Fetch_rows |   --         |                 |               |        ----------  --         |                  |               |            |   --         |                  |               |            v   --          |                  |              |    --------------------  --         |                  |               |  | column_value         |   --         |                  |               |  | variable_value       |   --         |                  |             &nbsp. |   ---------------------  --         |                  |               |             |   --         |                  |<--------------------------  --          |                  |   --         -----------------|   --                            |   --                            v   --                      ------------  --                     | Close_cursor |   --                      ------------  --

First, you modify the cursor's opening and closing to execute only once, and the effect is as follows

To eliminate the resolution, first empty the shared pool to see if the cursor resolution consumes a large amount of resources

It can be seen that the analysis or consumption of resources, especially hard parsing, but also can see the open_cursor consumption of time, so less than the last resort, do not empty the shared pool. If you want to eliminate parsing, each executed statement must be consistent and can be implemented by means of a bandage variable. Modify the program code as follows

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 The

Implementation efficiency is as follows:

Here we will optimize the most time-consuming part by increasing the value of CNT (similar to the set arraysize in Sqlplus)

Efficiency has improved a lot.

Summary: Understanding the execution process of cursors, as much as possible to repeat the various sub links can effectively improve efficiency.