A good memory is better than a rotten pen. Oracle SQL Optimization (2)

*sql optimization based on ORACLE11GR2 reading notes * Third, cursor in Oracle

The cursor in Oracle is a vector of SQL parsing and execution in an Oracle database and is a data structure for C (Oracle is written in C).

There are two types of cursor in an Oracle database: One is a shared cursor and the other is a session cursor.

1. Shared Cursor

Let's take a look at what is a library cache, a library cache object.

We know that Oracle has a global memory area SGA, and the SGA can be divided into Java pools, large pools, shared pools, empty pools, and so on. The library cache is a chunk of memory in a shared pool, and its primary role is to cache the SQL statements and PL/s statements that have just been executed, such as the execution plans, parse trees, pcode, mcode, etc., for stored procedures, functions, packages, triggers, and so on, when the same SQL statements and pl/ Once the SQL statement is executed again, it is possible to take advantage of those related objects that have been cached in the library cache without having to start the parsing again, which increases the efficiency of these SQL statements and the PL/SQL statements when they are executed repeatedly.

The object that is cached in the library cache is the library cache object. All library cache objects are stored in a library cache with a C structure called the library cache object handle, and Oracle accesses the corresponding library cache object by accessing the associated library cache object handle. The library cache object handle is stored in the library cache as a hash table. A library cache object handle with the same hash value is stored in the same hash slot as a chained structure, forming a chain of handles.

Anyway

A shared cursor in an Oracle database is a library cache object that is cached in the library cache, and plainly refers to a library cache object that caches SQL statements in the library cache and anonymous PL/SQL statements. The Shared cursor stores the SQL text of the target SQL, the parse tree, the object definition involved in the SQL, the type and length of the binding variable used by the SQL, and the execution plan of the SQL.

The Shared Cursor is subdivided into the parent cursor and child cursor, and can be viewed by querying views V$sqlarea and V$sql, respectively, to view the parent cursor and child cursor information currently cached in the library cache.

The structure of the parent cursor and the child cursor is the same as the library cache object handle, the difference being that the parent cursor stores the SQL text of the target SQL in the Name property, and the child cursor's Name property value is empty. The parse tree and execution plan for the target SQL is stored in its child cursor, which corresponds to a child cursor in the execution plan. The parent cursor stores the handle address of all the child cursor of the parent cursor, which means that all of its child cursor can be found through the parent cursor. This structure determines that any target SQL in the Oracle database must correspond to two shared Cursor, a Parent cursor and a child cursor,parent cursor that stores the text of the target SQL. The parse tree and execution plan that the target SQL can really be reused is stored in the child cursor.

The above describes the library cache object handle is stored in a hash map of the structure, then the library cache object handle is what to generate a hash value? The library cache object handle generates a hash value with its Name property and the Namespace property, and for the parent cursor is the SQL text and namespace property of the target SQL (value CRSR).

Knowing the information stored in the shared cursor and how it is stored in the library cache, the following describes how Oracle performs the reuse of the plan.

When Oracle resolves the target SQL, it first looks for matching library cache objects in the library cache, with the following lookup order:

1) hash The SQL text of the target SQL (the Name property of the library cache object handle) and use the resulting hash to find the corresponding library cache object in the library cache. Because the Namespace property value of its cached object handle is fixed CRSR for shared cursor, it is ignored here.

2) then find the matching parent Cursor in the list of library cache objects in the hash slot based on the hash value found. The SQL text is compared to the target SQL during the lookup of a matching parent cursor, because different SQL literals may have the same hash value computed.

3) Find success in step 2, then traverse the parent cursor to find a matching child cursor.

4) The lookup failure in step 2 means that there is no parse tree and execution plan that can be shared at this time, and Oracle will parse from the beginning, generating the parent cursor and child cursor and placing it in the corresponding hash slot.

5) Step 3 if the corresponding child cursor is found, Oracle will reuse the parse tree and execution plan stored in the child cursor instead of parsing from the beginning.

6) Step 3 if the corresponding child cursor is not found, it means that there is no parse tree and execution plan that can be shared, and Oracle will parse from the beginning, reborn into a child cursor, and hang the child cursor under the corresponding parent cursor. Now that you can find the parent cursor stating that the SQL execution plan has been cached, how can there be no child cursor found? First, the shared cursor object is a globally shared object, followed by more than one table space in Oracle, and the table under different table spaces can have the same name. If two users do the same thing for a table of the same name in a different tablespace, then the same parent Cursor is obtained based on the SQL hash value and the SQL text comparison, obviously the execution plan must not be the same at this time (perhaps the definition of the table is not the same), so there will be no child found The cursor situation.

Hard Parse

Hard parsing means that when you execute a target SQL, you cannot find a parse tree and execution plan in the library cache that can be reused, and you must parse the target SQL from scratch and generate the corresponding parent cursor and child cursor.

In the previous introduction to the order of the lookup library cache objects, it is easy to get two scenarios where hard parsing occurs: one is that the parent cursor cannot be found and must be hard-parsed, and the other is that the parent cursor is found but cannot find the child cursor.

Hard-to-parse hazards:

1) Hard parsing may result in contention for the shared Pool latch. When a hard parse occurs, at least one child Cursor is generated, that is, a chunk of memory must be allocated in the library cache, and the library cache is a chunk of memory in the shared pool, so you actually need to allocate a piece of memory in the shared pool One of the functions of latch is to protect the allocation of shared pool memory when concurrency occurs, and to hold a shared pool latch to allocate pooled memory.

2) Hard parsing may result in contention for library cache-related latch and mutexes. When looking for a library cache object handle (or finding the parent Cursor), you need to scan the library cache object handle chain, which needs to hold library cache Latch (11GR1 version and later with mutex).

Soft parsing

Soft parsing refers to the process of finding a matching parent cursor and child cursor in the library cache while executing the target SQL, and reusing the parse tree and execution plan without parsing from the beginning.

First soft parsing does not exist for shared Pool latch contention, because memory is not required. Soft parsing also requires scanning the library to cache object handle chains to find reusable parse trees and execution plans, so there is inevitably contention for library cache latch. But compared to hard parsing, the first, soft-analytic library cache latch contention is much less, the library cache latch holding time will be relatively short.

Summarize

If soft parsing is widely used in OLTP-type systems when executing target SQL, the performance and scalability of the system will be significantly higher than the full use of hard parsing, and the system resources consumed by the execution of the target SQL (mainly on the CPU) will also be significantly reduced.

Session Cursor

The session cursor is the vector of the current session parsing and executing SQL. Like the shared cursor, the Session cursor is a complex structure of the C language, which is cached in a hash table, except in the case of the PGA, rather than caching it in the library cache of the shared pool.

A few points about the cursor in session:

1) The session cursor and session are one by one corresponding, the session cursor of different session cannot be shared.

2) The cursor of the Session has a life cycle. Each session cursor will experience at least one or more phases in Open,parse,bind,execute,fetch and close during use, and the used session cache may not be cached in the PGA corresponding to the session. This depends on whether the value of Session_cached_cursors is greater than 0.

3) When Oracle parses and executes the target SQL, it will go to the PGA in the current session to find out if there is a matching session Cursor. When Oracle parses and executes the target SQL for the first time, it will be reborn as a session Cursor and a pair of shared cursor (parent and child), and the shared cursor stores the Reused content (such as parse trees and execution plans), and the session cursor undergoes one or more stages in open, Parse, Bind, Execute, Fetch, and close. Before 11g, the parent cursor's library cache object handle address is saved in the session cursor so that the session cursor can be used to navigate directly to the parent cursor.

When the above target SQL is parsed and executed in a hard-resolved manner, the shared cursor of the target SQL lock is cached in the library cache, and the corresponding session cursor is already used, and there are two scenarios:

First: If the value of the parameter session_cached_cursors is 0, then the session cursor will perform the close operation normally, so that when the above SQL is executed again, the corresponding session cursor is not found in the current session. However, the parent cursor and child cursor can be found in the library cache, at which point Oracle must also be reborn as a session cursor for the SQL, and the session cursor will undergo another open,parse,bind, One or more of the Execute,fetch and close, which is soft parsing.

Second: If the value of the parameter session_cached_cursors is greater than 0, then when additional conditions are met (as mentioned later in this condition), Oracle does not perform a close operation on the session cursor, but instead marks it as soft Closed. When SQL is repeatedly executed, it can be directly located to the parent cursor through the session cursor, the parse tree, execution plan and so on reuse, this is called soft and soft parsing. Compared to soft parsing, soft and soft parsing eliminates the overhead of creating a new session cursor, and of course the rest of the open,parse,bind,execute,fetch still needs to be done.

Correlation parameter resolution of cursor in Session

Open_cursors

Used to set the total number of session cursor in a single session that can coexist in open state. View V$open_cursor can be used to query the database for the number and specific information (such as sqlid and SQL text) of a session cursor that has a status of open or has been cached in the PGA.

Session_cached_cursors

Used to set the total number of session cursor that can coexist in the soft closed state in a single session, that is, the total number of session cursor that a single session can cache in the PGA. Oracle uses the LRU algorithm to manage these session Cursor. The session cursor is cached in the PGA with additional conditions, and in 11GR2, a session cursor can be cached in the PGA if the SQL is parsed and executed more than 3 times.

The type and usage of the cursor in Session

The session cursor in Oracle is subdivided into three different types: implicit, explicit, and reference (ref CURSOR). These three types are often used in Sql,pl/sql code.

An implicit cursor

The most common session Cursor, it's everywhere. When executing SQL, Oracle automatically helped us create an implicit cursor to act as a carrier for that SQL, whose life cycle was entirely the responsibility of the SQL engine or the PL/SQL engine. Even so, we can detect some information about an implicit cursor by the following four properties (the most recent SQL execution related information):

Sql%found

Sql%notfound

Sql%isopen This property is always false for an implicit cursor

Sql%rowcount

Display Cursors

Typically used in PL/SQL code, its lifecycle is explicitly controlled by us in PL/SQL code. It also has four properties:

Cursorname%found

Cursorname%notfound

Cursorname%isopen

Cursorname%rowcount

Reference cursors

Also known as dynamic cursors.

Binding variables

Using bound variables can effectively reduce the hard-resolved number of OLTP type application systems. Because the same type of SQL that is identical to the input values in the SQL text is the same as the other parts, if the binding variable is used, the SQL text becomes exactly the same, and the hash value is exactly the same, which means that the underlying conditions for reusing the parse tree and execution plan are available.

The use syntax for a bound variable is ": Variable_name", which is to replace the specific input value in the target SQL text with a combination of a colon and a custom variable name. The variable name here can be a combination of letters, numbers, or letters and numbers.

Typical usage of bound variables

Using binding variables in SQL statements

var x number;//First define variables

EXEC:X: = 1000;//assigns a value to a variable

--Using bound variables in SQL

Selecxfrom emp WHERE empno =: x;

Binding variable Usage for SELECT statements in PL/SQL

The execute immediate [target SQL statement with bound variable] using [corresponds to the specific input value of the binding variable], for example:

DECLARE    v_name VARCHAR2 (n); Begin   execute immediate ' select name from person WHERE name =: 1 ' into v_name using ' s Ara ';  Dbms_output.put_line (v_name); end;

Binding variable usage of DML statements in PL/SQL

DECLARE    V_SQL1 VARCHAR2 (4000);   V_SQL2 varchar2 (4000); begin   V_SQL1: = ' INSERT into person (NAME,DOB,HOME_ADDR,WORK_ADDR) VALUES (: 1,:2,:3,:4) ';   Execute immediate V_SQL1 using ' link ', to_date (' 1987-01-28 ', ' yyyy-mm-dd '), ' SZ ', ' HN ';   Dbms_output.put_line (sql%rowcount);   V_SQL2: = ' INSERT into person (NAME,DOB,HOME_ADDR,WORK_ADDR) VALUES (: 1,:1,:1,:1) ';   Execute immediate V_SQL1 using ' DD ', to_date (' 1987-01-28 ', ' yyyy-mm-dd '), ' SZ ', ' HN ';   Dbms_output.put_line (sql%rowcount); end;

The input value of the binding variable passed in after the keyword using is only related to the position of the corresponding bound variable in the SQL statement, regardless of the name. From the above example, you can see that the name of the binding variable in the same SQL statement can be the same.

DECLARE    V_SQL1 VARCHAR2 (4000);   V_name varchar2; begin   V_SQL1: = ' Delete from the person where name =: 1 returning name Into:2 ';   Execute immediate V_SQL1 using ' DD ' returning into V_name;   Dbms_output.put_line (v_name); end;

The keyword returning can be used with SQL with a bound variable to take the value of the corresponding column of the target row affected by the SQL.

Typical usage of bulk binding in PL/SQL

Bulk binding is an optimized way to use bound variables, the core of which is the following two points:

1, bulk binding or will use the binding variable in the way described previously

2, the advantage of bulk binding is that it is a one-time processing of a batch of data, rather than just a single piece of data in the usual way, so it can effectively reduce the number of PL/SQL engine context switching, and thus improve execution efficiency. In theoretical PL/SQL code, the interaction between the PL/SQL engine and the SQL engine can occur as long as the execution of the statement is executed, but the interactions that affect the performance of PL-SQL code occur in the following two in the PL code.

A) the display cursor or the reference cursor needs to loop through the fetch operation. The loop operation here requires a PL/SQL engine to handle, and a fetch record corresponding to the one that is executed requires the SQL engine to process it, so if you do not optimize it, the engine needs to switch once for each fetch.

b) The internal loop needs to perform SQL operations, as in the first case, if you do not optimize the cycle of a record, the engine will switch once.

The syntax for bulk fetch corresponds to the following:

FETCH cursor_name Bulk collect into [custom array] <limit batch_size>

The syntax for executing a batch of SQL statements at a time is as follows:

ForAll i in 1.. [Array length]

Execute immediate [target SQL with bound variable] using [specific input value of the corresponding binding variable]

The keyword ForAll represents a batch of SQL statements that can be used in conjunction with INSERT, UPDATE, DELETE statements.

Binding variable snooping

We know that with the specific input values, the selectable rate of the where condition of the target SQL and the number of rows of the result set may change, while the selectable rate and the number of rows in the result set directly affect the CBO's estimate of the cost value of the relevant execution steps, which in turn affects the CBO's choice of the target SQL execution plan. This means that the execution plan for the target SQL may change as the specific input values are different. For target SQL that uses bound variables, the situation is completely different, because now regardless of the specific input value of the corresponding binding variable, the SQL text of the target SQL is exactly the same, so how should oracle determine the execution plan of the target SQL?

For target SQL that uses bound variables, Oracle can choose from two ways to determine its execution plan:

1. Using bound variables to spy

2. If you do not use a binding variable for snooping, the default selection rate may be used for predicate conditions where the selectable rate might vary depending on the specific input value.

Binding variable snooping is introduced in 9i, and whether the bind variable is enabled is controlled by the implied parameter _optim_peek_user_binds, which defaults to true. Enable binding variable when the Oracle parses the target SQL using the bound variable in a hard-parse manner, Oracle actually takes a look at the specific input values of the corresponding bound variables and uses those specific input values as the standard. To determine the number of selectable and result set rows for the where condition of the target SQL that used the bound variable, and select the execution plan for that SQL accordingly. However, this "snooping" action is only performed when hard parsing, when the target SQL using the binding variable is executed again (soft parsing or soft and soft parsing), even if the specific input value of the corresponding binding variable is different from the value of the previous hard parse, Oracle also inherits the parse tree and execution plan generated by the previous hard parse, without repeating the snooping action described above.

The advantage of a binding variable snooping is that it avoids using the default selectable rate, and there is greater likelihood of getting an accurate execution plan; the downside of the same bound variable snooping is that for those SQL that executes a plan that differs from the input value, once enabled, the execution plan is fixed. As to what the next execution plan is, it relies entirely on the exact value of the corresponding binding variable passed in by the SQL at hard parse time.

With the histogram statistics appearing in Oracle 10g, the drawbacks of the bound variable snooping are magnified further, and in order to overcome the drawbacks of the binding variable snooping, Oracle 11g introduces adaptive cursor sharing. Adaptive cursor sharing allows target SQL that uses a bound variable to use the binding variable to spy on the precondition, no longer only to follow the previous hard parsing generated by the parse tree and execution plan, that is, adaptive cursor sharing can be enabled in the binding variable snooping under the premise of the condition, Having the target SQL "adaptively" Choose between its possible multiple execution plans does not have to rigidly follow the parse tree and execution plan generated by the SQL hard parse as it did before.

How do I manually invalidate a previously hard-parsed execution plan? DDL operations can be performed on the tables involved in the target SQL, and all shared cursor in the library cache that contains the table in the SQL text will be marked as invalidated. Another way is to use Dbms_shared_pool. PURGE. It is a way to start referencing from Oracle 10.2.0.4, which can be used to delete a shared Cursor that is cached in the library cache.

Binding variable Rating

The binding variable rating means that Oracle divides the binding variables of these text types into four levels in PL/SQL code based on the length of the defined text-bound variable (for the non-applicability of the numeric type).

A text-type binding variable that is defined within 32 bytes is divided into the first rank, the second level between 33~128 bytes, the third level between 129~2000 bytes, and the fourth rank for more than 2000 bytes. So why grade it? Because when you execute the target SQL. Each bound variable in the target SQL is replaced with the actual value, so the session cursor must reserve a certain length of memory for the bound variable in the PGA. Ideally, the actual length is the number of allocations, but this is cumbersome to deal with, in order to simplify, Oracle divided the above four levels. A fixed allocation of 32 bytes of memory for a bound variable of length within the first level; a binding variable of length in the second level allocates 128 bytes of memory; A binding variable of length in the third level allocates 2000 bytes of memory, and a bound variable of length in the fourth level, If the actual length of the variable is less than or equal to 2000, allocate 2000, otherwise allocate 4000 bytes of memory. For example:

----Defining variables

V Varchar2 (28);

V_sql VARCHAR2 (2000);

----Assignment

v:= ' ABCD ';

----because the definition of V is a maximum of 28 and less than 32, it allocates 32 bytes of memory

V_sql: = ' SELECT * FROM table_name WHERE col1 =: 1 ';

Execute immediately v_sql using V;

The purpose of the binding variable level here is to introduce a knowledge point:

For target SQL that uses text-bound variables in PL/SQL code, the size of the memory space that Oracle allocates for these bound variables changes as long as the definition length of the text-bound variable in the literal is changed, and is cached in the child The parse tree and execution plan in the cursor cannot be reused. Because in addition to storing the parse tree and execution plan of the target SQL in the child cursor, the type and length of the binding variable used by the SQL is stored, even if the text of the SQL does not change, as long as the definition length of the text variable in its SQL text changes. Then the SQL will have to be hard-parsed when it executes again.

---Clean up the shared pool alter system flush Shared_pool;declare   v_name_1 varchar2 (+);  V_sql varchar2 (+);  V_name_2 varchar2 (133); begin   V_name_1: = ' link ';  v_name_2: = ' link ';  V_sql: = ' select * from the person where name =: 1 ';  Execute immediate v_sql using V_name_1;  

To view the value of a bound variable in an executed target SQL

Query view V$sql_bind_capture If the v$sql_bind_capture is not found, then it is possible that the shared cursor has been out of the library cache by age, and can attempt to go to awr Repository is found in the Related Data dictionary table dba_hist_sqlstat or Dba_hist_sqlbind.

For parsing and executing SQL statements that contain bound variables, if one of the following two conditions is met, the specific input values of the bound variables in that SQL are captured and can be queried through the view v$sql_bind_capture:

1. When SQL with bound variables is executed in a hard parse manner

2. When SQL with bound variables is executed with soft parsing or soft-soft parsing, the specific input value of the bound variable in the SQL may also be captured, except that the capture action occurs every 15 minutes.

Oracle captures only those specific input values for the binding variables that are located in the where statement.

A good memory is better than a rotten pen. Oracle SQL Optimization (2)