Oracle cursor and bound variable

Oracle executes SQL statements to open, parse, execute, and close a cursor. After learning about the several phases of the cursor, We have figured out the SQL Execution process. This is the first content to be introduced in this article. In java programming, we usually say that SQL statements should be written in the form of preprocessing (for example, select * from table where A = ?), That is, the form of variable binding. Because, this is highly efficient. So why is it more efficient to bind A variable than to not bind A variable (for example, select * from table where A = 123? This is the second content to be introduced in this article. Www.2cto.com 1. The execution of an SQL statement in the lifecycle of a cursor is the lifecycle of a cursor. As shown in:
1. Open cursor: The system allocates a memory structure for the cursor. 2. Resolve the cursor: Associate an SQL statement with this cursor. Parse this SQL statement and load the parsed result to the shared pool. 3. Define output variables: If this SQL statement returns data, first define the variables for receiving data. 4. Define input variables: If the SQL statement uses bind variables, provide their values. 5. Execute the cursor: Execute the SQL statement. 6. Obtain the cursor: If the SQL statement returns data, it receives the returned data. 7. Close the cursor: Release the memory allocated in the first step for other cursors. However, the SQL results (that is, shared cursors) parsed in the second step will not be released to be reused. We can use a PL/SQL code to view the cursor steps: DECLAREl_enameemp.ename % TYPE: = SCOTT; Role % TYPE; l_cursorINTEGER; l_retvalINTEGER; BEGINl_cursor: = dbms_ SQL .open_cursor; /* Open the cursor */dbms_ SQL .parse (l_cursor, SELECTempnoFROMempWHEREename =: ename, 1);/* resolve the cursor */dbms_ SQL .define_column (l_cursor, 1, l_empno ); /* define the output variable */dbms_ SQL .bind_variable (l_cursor,: ename, l_ename);/* define the input variable */l_retval: =dbms_ SQL .exe cute (l_cursor);/* execute the cursor */IFdbms_sq L. fetch_rows (l_cursor)> 0/* Get cursor */THENdbms_ SQL .column_value (l_cursor, 1, l_empno); dbms_output.put_line (l_empno); ENDIF; then (l_cursor ); /* close the cursor */END; 2. in the process of cursor parsing, the only thing we can affect is the parsing process. The fast and slow parsing process is directly related to the SQL statements we write. So what is the cursor parsing process (that is, the SQL parsing process? See:
1. constraints check that includes VPD: If a virtual private database is used in the system and a table referenced in the parsed SQL statement activates it, the constraint conditions generated by the security policy will be added to the where condition (to be honest, I don't understand it either, no matter what it is) 2. syntax, semantics, and access permission check: Check whether the SQL statements we write are correct, and whether the referenced table exists. 3. Save the parent cursor to the database cache: if the shared parent cursor is not found, the parent cursor will be cached in the database cache. The parent cursor stores the text information of this SQL statement. If you re-execute this SQL statement in the future, this parent cursor can be reused. 4. Logical optimization and physical optimization: generate all possible execution plans for this SQL statement, and select an execution plan with the minimum overhead Based on the overhead of the execution plan. 5. Save the sub-cursor to the database cache: The best execution plan information selected in the previous step and the current execution environment are saved to the database cache as the sub-game subject and associated with the parent cursor. In short, the parent cursor stores SQL text information and can be reused in the future. The sub-cursor stores the optimal execution plan of the SQL statement selected in the current execution environment. If the parent cursor is reused and the execution environment remains unchanged, the sub-cursor is also reused. When both the parent cursor and child cursor can be reused, you only need to perform the first two steps. The corresponding Parsing is called soft parsing. If neither the parent cursor nor the child game tag can be reused, when all steps are executed, it is what we call hard parsing. Because logical optimization and physical Optimization in hard parsing are very dependent on cpu operations, hard Parsing is relatively time-consuming. That is why we should avoid hard parsing as much as possible. III. the advantage of variable binding can effectively eliminate hard parsing. EXECUTE the following SQL text: DROPTABLEt; CREATETABLEt (nNUMBER, vVARCHAR2 (4000); ALTERSYSTEMFLUSHSHARED_POOL; VARIABLEnNUMBERVARIABLEvVARCHAR2 (32) EXECUTE: n: = 1;: v: = Helicon; INSERTINTOt (n, v) VALUES (: n,: v); EXECUTE: n: = 2;: v: = Trantor; INSERTINTOt (n, v) VALUES (: n,: v); EXECUTE: n: = 3;: v: = Kalgan; INSERTINTOt (n, v) VALUES (: n ,: v); SELECTsql_id, child_number, executionsFROMv $ sqlWHEREsql_text = INSERTINTOt (n, v) VALUES (: n,: v); droptablet; the execution result of the last select statement is shown in:
This statement generates only one parent cursor, but the parent cursor is executed three times. That is, when we split the First insert, we performed a hard parsing. For the next two times, we will perform soft parsing. Www.2cto.com 4. Execution Plan with variable binding disadvantages that affect the low efficiency of oracle selection. Because the bound variables are used, both the parent cursor and the Child cursor can be shared and reused (except for the first hard parsing, each other is soft parsing ). The sub-cursor is reused every time (except for the first time), so the execution plan is the same every time. If the execution plan in the sub-cursor confirms that the full table scan is performed, because the first time you want to query the vast majority of data in the table, oracle considers that the full table scan is fast. If you only need to scan a small part of the data for the second time, the index scan is faster. Because the sub-cursor is reused, a full table scan is also executed. Let's take an example: execute the following SQL text: Explain; DROPTABLEt; CREATETABLEtASSELECTrownumASid, rpad (*, 100, *) ASpadFROMdualCONNECTBYlevel <= 1000; ALTERTABLEtADDCONSTRAINTt_pkPRIMARYKEY (id ); revoke (ownname => user, tabname => t, estimate_percent => 100, method_opt => forallcolumnssize1); END;/EXECUTE: id: = 990; SELECTcount (pad) FROMtWHEREid <: id; SELECT * FROMtable (dbms_xplan.display_cursor (NULL, NULL, basic); EXECUTE: id: = 10; SELECTcount (pad) FROMtWHEREid <: id; SELECT * FROMtable (dbms_xplan.display_cursor (NULL, NULL, basic); we found that when id = 10, that is, when querying a very small part of data, we still execute a full table scan, such:
5. When should I bind a variable and avoid it? We can see that the Bind Variable is mainly used to avoid hard parsing, that is, to speed up the SQL parsing time, but may lead to an execution plan with low Oracle reuse efficiency. That is, to extend the SQL Execution time. In this case, we should weigh whether the SQL statement has a long parsing time or execution time. Www.2cto. com1. if only a small part of data is processed at a time, the parsing time is equal to or later than the execution time, it is recommended to bind the variable. 2. If the execution time for processing a large volume of data is several orders of magnitude higher than the resolution time, there is no need to bind the variable to accelerate the parsing time. In addition, the execution plan that may cause low oracle reuse efficiency may greatly affect the SQL Execution speed.