Shulou(Shulou.com)06/02 Report--

Library cache is a part of Shared pool, which is almost the most complex part of Oracle memory structure.

First, what does Library cache store (the units of information stored are called objects)?

The units of information stored in Library are called objects, which can be divided into two categories:

1. Storage object

two。 Transition objects (cursor Cursor, where cursors refer to generated executable objects, and multiple processes running the same SQL can share cursors generated by this SQL, saving memory.)

a. SQL submitted by the user

B. parsing trees related to SQL statements

c. Carry out the plan

d. PL/SQL blocks submitted by users (including anonymous blocks, procedure,packages,function, etc.)

E. objects such as table,index,view on which the PL/SQL object depends

f. Control structure: lock,pin,dependency table, etc.

Note: objects for LIBRARY CACHE can be found in V$DB_OBJECT_CACHE. This view is based on X$KGLOB.

Second, the parsing and cursor of SQL

SQL mainly completes the following steps during the parsing phase:

1. Save the parent cursor to Library Cache (as described later in the concept reference for the parent cursor, this step does not actually include

During parsing)

First convert SQL to ASCII values, then perform the operation of hash function on these ASCII values to generate hash value (10g and a unique SQL_ID), and then match the hash bucket in library cache (hash bucket simply uses the hash algorithm to represent the SQL in library cache through a similar two-dimensional array, such as t [3] [6]. Each search, the bucket number is calculated by hash algorithm, and the corresponding bucket is found. For example, number 3 in the previous t [3] [6], after each bucket, all the object handle that satisfies the hash algorithm will be mounted, and the object handle will store the SQL name [SQL text for SQL], namespace, etc.), and then match the handle on the hash bucket, that is, the handle. If the match is successful, then go to the sub-cursor (the concept of the sub-cursor refers to the instructions below, find the sub-cursor and execute it directly. If the child cursor is swapped out of the library cache, then reload a child cursor is reconstructed from the parent cursor information. If it is not successful, that is, there is no shared parent cursor, some memory (Chunk) is allocated in the library cache, and the newly generated parent cursor is saved in the library cache to generate a handle (object handle) and mount it to the hash bucket. Next, do the hard parsing.

2. Contains constraints for VPD (virtual private database)

The details of the virtual private database VPD can be found in the notes below. For example, for HR salary query, select salary from emp; if you set VPD, you will implicitly add each user's own account, and you can only view your own, and the sentence will become similar: select salary from emp where name='susan'

3. Check the grammar of the SQL statement and exit the parsing process if there is a grammar error

Verify that the sql statement is written correctly (for example, no from,select misspelling, etc.)

4. Check the existence of the objects and columns involved in SQL to the data dictionary, and exit the parsing process if it does not exist, which loads the Dictionary Cache.

5. Convert the names of objects, such as converting synonym to actual objects, and so on. Exit parsing if the conversion fails.

6. Check whether the user issuing the SQL statement (usually the connected user) has access to the objects referenced in the SQL, and if not, exit parsing.

7. Logic optimization-use certain conversion techniques (Transforming Queries, query converter) to generate new SQL statements that are syntactically and semantically equivalent. The form of the query statement will affect the resulting execution plan, and the function of the query converter is to change the form of the query statement to produce a better execution plan. Four common transformation techniques: view merging (View Merging), predicate advance (Predicate Pushing), non-nested subquery (Subquery Unnesting), and query rewriting of materialized views (Query)

Rewrite with Materialized Views).

For more information, please refer to the following documents and comments below:

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i37745

8. Physical optimization-first, an execution plan related to the sql statement generated by each logical optimization is generated, and then the relevant statistics or dynamically collected statistics are found according to the data dictionary, and the overhead associated with the execution plan is calculated. Finally, select the execution plan with the lowest cost. It involves a lot of mathematical operations, so this step consumes the most CPU resources. In this step, the subcursor is generated, and the execution plan, binding variables, and execution environment are the main contents of the subcursor.

9. Load the child cursor to the library cache-first allocate memory (Chunk), then store the shared child cursor, and finally associate it with the parent cursor. The key content related to the child cursor is the execution plan and execution environment. Once saved to the library cache, the parent and child cursors can be materialized through the view v$sqlarea and v$sql, respectively.

In v$sql, a child cursor is determined by child_number,hash_value,address, while v$sqlarea can determine a parent cursor through address and hash_value; after 10g, a cursor can be determined by sql_id; find out whether there is a shared parent cursor

Hard parsing and hard parsing are two different processes. Whether the parent cursor is shared or not is not directly related to hard parsing. The shared state of child cursors determines soft and hard parsing.

Note:

Namespace:

When using the hash algorithm to calculate the ASCII corresponding to the SQL statement, the parameters passed in are the name of the SQL statement and namespace (different namespace can be queried through v$librarycache, and the value is "SQL AREA" for SQL).

Details of the VPD virtual private database:

Http://www.oracle.com/technology/global/cn/pub/articles/10gdba/week14_10gdba.html

SQL Parsing Flow Diagram [ID 32895.1]

Https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=32895.1

The query converter for the logic optimization part of the parsing process

Since Oracle 8i, there have been four transformation technologies: view merging (View Merging), predicate advance (Predicate Pushing), non-nested subquery (Subquery Unnesting), and query rewriting of materialized views (Query Rewrite with Materialized Views).

View merging: if the SQL statement contains views, after analysis, the views will be placed in a separate "view query block", and each view will generate a view subplan. When generating an execution plan for the whole statement, the view subplan will be directly used without taking into account the integrity of the statement, which can easily lead to the generation of a bad execution plan. The purpose of view merging is to remove the "view query block" and merge the view into an overall query block, so that no view subplan is generated, and the superiority of the execution plan is improved.

Predicate progression: not all views can be merged. For those views that cannot be merged, Oracle pushes the corresponding predicates into the view query block, which are usually indexable or filtered.

Non-nested subqueries: subqueries are placed in separate query blocks as well as views. the query converter converts most subqueries into joins and merges into the same query block, and a small number of subqueries that cannot be converted into joins. Their subplans are arranged in an efficient way.

Query rewriting of materialized view: when query_rewrite_enabled=true, the query converter looks for the materialized view associated with the query statement and rewrites the query statement with the materialized view.

Third, parent and child cursors

Part of the content reference:

Http://www.oraclefans.cn/forum/showblog.jsp?rootid=5553

Http://www.itpub.net/thread-1362874-1-1.html (problem)

During hard parsing, the process holds the library cache latch until the hard parsing ends. The hard parsing process produces two cursors for the SQL, one is the parent cursor and the other is the child cursor.

Discussion on issues related to parent and child cursors:

Http://www.itpub.net/thread-1362874-1-1.html

Parent cursor (parent cursor)

When user An issues a SQL, the Oracle generates a hash value based on the SQL text content (10g with a unique SQL_ID), comparing the hash value in the library cache so that you can quickly find the same SQL that already exists in the Shared pool. If it cannot be found, Oracle creates a parent cursor and a child cursor for the SQL.

The parent cursor mainly contains two kinds of information: the SQL text and the optimization objective (optimizer goal). All you can see from the v$sqlarea view is information about the parent cursor. Each line in the v$sqlarea represents a parent cursor, such as the SQL text corresponding field SQL_TEXT, and the optimizer goal corresponds to the following RUNTIME_MEM,EXECUTIONS,CPU_TIME, DISK_READS, BUFFER_GETS, and so on.

The parent cursor is locked the first time it is opened and is not unlocked until all other session cursors are closed. When the parent cursor is locked, it cannot be swapped out of the library cache, but only after it is unlocked can it be swapped out of the library cache, then all the child cursors corresponding to the parent cursor are also swapped out the library cache.

The structure of a CURSOR includes PARENT CURSOR and CHILD CURSOR, and each CURSOR contains at least one CHILD CURSOR. This CURSOR is distinguished by HASHVALUE, and each PARENT CURSOR contains at least one HEAP0 that stores information about the environment, state, and bound variables. Each PARENT CURSOR has at least one CHILD CURSOR. The handle is actually the parent cursor that is stored, and the real execution plan is stored on the child cursor, that is, heap6.

PARENT CURSOR consists of a handle and an object, and the handle can be found through the hash value in the library cache hash table, and the object contains a pointer to each of its "child" cursor.

In V$SQLAREA, version_count sees how many child cursors a parent cursor corresponds to. The corresponding relationship is connected by hash_value and adress (the address of SQL text). Different child cursors with the same SQL text in V$SQL, hash_value and adress are the same, but the child address child_address is different. The child address here is actually the handel of the Heap0 corresponding to the child cursor. The hild_number number in V$SQL starts at 0, and different child_number with the same SQL text (shared by the parent cursor) corresponds to different child_address. There are three fields bind_data in V$SQL under Oracle10g version.

Optimizer_env, optimizer_env_hash_value should be the field used to decide which subcursor to take. However, these fields are not available in v$sql in 9i, so there is a reference discussion on how to find subcursors:

Http://www.itpub.net/thread-1362874-1-1.html

Subcursor (Child Cursors)

The subcursor includes all the information about the cursor, such as specific execution plans, binding variables, and so on. Child cursors can be swapped out of library cache at any time. When child cursors are swapped out of library cache, oracle can reconstruct a child cursor using the information of the parent cursor. This process is called reload. The specific number of child cursors can be reflected in the version_count field of v$sqlarea. Each specific sub-cursor is reflected in the v$sql. The ratio of reload can be determined in the following ways:

SELECT 100*sum (reloads) / sum (pins) Reload_Ratio FROM v$librarycache

A parent cursor can correspond to multiple child cursors. When the value of a specific binding variable is quite different from that of the last binding variable (for example, the length of the value of the last binding variable is 6 bits, but the length of the value of the binding variable executed this time is 200 bits), or when the SQL statement is exactly the same, but the referenced object belongs to a different schema, or the environment in which the SQL is executed is different (the optimizer mode is different), a new subcursor will be created.

With regard to the difference between new subcursors and reload, if all versions of subcursors cannot be shared, a new subcursor (new create) will be created, which refers to situations caused by different lengths of environment or bind var. Reload means that the parent cursor can be shared, and the same child cursor (environment or bind var, etc.) already exists in library cache, but it was out of aged out for some reason, but now it is needed and needs to be reloaded.

Each child cursor is also made up of a handle and an object. Child object is composed of two heap, heap0 and heap6, in which Heap0 contains all the marking information for each version of the SQL statement (such as Environment, Statistics, Bind Variables, etc.). For example, different lengths of binding variable values may lead to different parsed versions of sql; Child cursors are also called versions. Heap6 contains the execution plan

Child cursor contains the metadata of the SQL, that is, all the relevant information that makes the SQL executable, such as OBJECT and permissions, optimizer settings, execution plan, and so on. Each line in v$sql represents a child cursor, which is associated with parent cursor based on hash value and address. Child cursor has its own address, or V$SQL.CHILD_ADDRESS. If there is more than one child cursor, it means that there are multiple versions of parent cursor, and the version_count field in the v$sqlarea is recorded.

Each type of dml statement requires the following stages:

Create a Cursor creates cursors

Parse the Statement parsing statement

Bind Any Variables binding variable

Run the Statement run statement

Close the Cursor closes the cursor

Hard parsing and soft parsing, "soft soft" parsing, RELOAD

Hard parsing-

First, understand the conditions for parent cursor sharing:

1. Character-level comparison requires that the text is completely consistent.

SQL statements must be exactly the same, select * from emp; and select * from emp; are different. Cannot be shared.

two。 You must use a binding variable with the same name (that is, inconsistent text characters), such as

Select age from pepoo where name=:var_p

Select age from pepoo where name=:var_f

(even if the binding variables with different names are given the same value at run time, it will not lead to the parent cursor)

As can be seen from the SQL parsing process, whether the parent cursor is shared or not occurs before hard parsing, so it does not matter whether the parent cursor can be shared or not, but if the parent cursor cannot be shared, it must be hard parsing. For the whole process of hard parsing, see section 2 above. But the sharing of parent cursors is not necessarily soft parsing. Whether hard parsing can be avoided depends on sub-cursors.

Parent cursor sharing has been discussed, and here we discuss several cases of child cursor sharing (assuming CURSOR_SHARING=EXACT):

The first is that the original SQL statement issued by An is exactly the same as the SQL text issued by other user B, and the parent cursor can be shared, but because the optimizer environment setting is different (OPTIMIZER_MISMATCH). The length of the value of the bound variable changes significantly during the second execution (BIND_MISMATCH), authorization mismatch (AUTH_CHECK_MISMATCH) or underlying object transformation mismatch (TRANSLATION_MISMATCH), and so on, the subcursor cannot be shared, and a new subcursor needs to be generated. This is related to SQL sharing (that is, cursor sharing). The execution plan in this case may be different or the same (as we can see through plan_hash_value)

Reload will not happen here because conditions other than SQL TEXT are not met. The subcursor is new create and load, which should be hard parsing. For specific mismatch, you can query V$SQL_SHARED_CURSOR.

For example:

-- window 1 execution

Sys/SYS > alter session set optimizer_mode=all_rows

Session altered.

Sys/SYS > select * from tt

No rows selected

Sys/SYS > alter session set optimizer_mode=first_rows_10

Session altered.

Sys/SYS > select * from tt

No rows selected

-- window 2 execution

Sys/SYS > select hash_value,sql_text,executions,VERSION_COUNT from

V$sqlarea where sql_text like'% from tt'

HASH_VALUE SQL_TEXT EXECUTIONS VERSION_COUNT

-

3762890390 select * from tt 2 2

Sys/SYS > select HASH_VALUE,child_number,sql_text from v$sql where sql_text like'% from tt'

HASH_VALUE CHILD_NUMBER SQL_TEXT

3762890390 0 select * from tt

3762890390 1 select * from tt

As you can see, the SQL text is identical, so the two child cursors share a parent cursor. However, due to the difference in optimizer_mode, two child cursors are generated. If a child cursor is generated, it means that some kind of mismatch must have been generated, and how to see what caused the mismatch, through v$sql_shared_cursor.

Sys/SYS > select kglhdpar, address,auth_check_mismatch, translation_mismatch,OPTIMIZER_MISMATCH

2 from v$sql_shared_cursor

3 where kglhdpar in

4 (select address

5 from v$sql

6 where sql_text like'% from tt')

KGLHDPAR ADDRESS A T O

--

89BB8948 89BB83CC N N N

89BB8948 89BB5E78 N N Y

You can see that the value of the second row of the OPTIMIZER_MISMATCH column is Y, which indicates that the subcursors are generated because of the difference in optimizer_mode. Finally, what is the meaning of parent and child cursors? In fact, it's all about sharing. In order to reduce the waste of resources for re-analysis.

The second is that the original SQL statement issued by An is exactly the same as the SQL text in shared pool, the parent cursor can be shared, the child cursor does not have the so-called mismatch, and currently exists in the library cache, and the child cursor can be shared, so it should be soft parsing.

Third, the parent cursor can be shared, the difference is that the child cursor can be shared, but is currently swapped out (aged out) library cache, then reload the child cursor, that is, using the information of the parent cursor to reconstruct a child cursor, Oracle already knows which child cursor should be shared, but it is temporarily swapped out of the library cache, reload should not belong to hard parsing, does it belong to soft parsing? Although it is cached out of the library by aged out, some information about this subcursor may be recorded somewhere, without the need to regenerate the information about the subcursor (such as execution plan, etc.), but only reload (what the specific process of reload needs to be studied).

Finding whether there is a shared parent cursor and hard parsing are two different processes. Whether the parent cursor is shared or not is not directly related to hard parsing. The shared state of child cursors determines soft and hard parsing.

From a performance point of view, try to avoid hard parsing. Why?

First: because both logical optimization (Transforming Queries) and physical optimization (choosing the optimal execution plan) rely heavily on the operation of CPU.

Second: memory needs to be allocated to save the parent and child cursors to the library cache. Because the library cache is shared among all sessions

The memory allocation in the library cache must be executed serially.

Soft analysis, "soft soft" analysis-

Soft parsing is relative to hard parsing. In fact, as long as the corresponding SQL text can be matched in hash bucket (count as a get), it is soft parsing, indicating that the sql has been run before. in fact, only one or more steps can be skipped during the execution of sql, then we can be defined as soft parsing. If the SQ statement is not found, hard parsing is performed. There are three types of soft parsing:

a. The first is that the SQL statement issued by a session is consistent with the SQL issued by other session in the library cache, parent and child cursors can be shared, logical optimization (Transforming Queries), and physical optimization (selecting the best execution plan) and loading this information into the heap of the library cache can be omitted, table name, column name, name conversion and permission check are still required.

b. The second is that the SQL issued by a session is a previously executed SQL issued by that session. At this point, the parsing process only needs grammar checking and permission checking.

c. The third is that when session_cached_cursors is set, when a session executes the same SQL for the third time, the cursor information of the SQL will be transferred to the PGA of the session. In this way, when the session executes the same SQL statement later, it directly takes the execution plan out of the PGA and skips all the steps of hard parsing, which is the most efficient parsing method, but consumes a lot of memory. It is commonly known as "soft" analysis.

Reload

With regard to the difference between new sub-cursors and reload, if none of the versions of sub-cursors can be shared, a new sub-cursor (new create) will be created, which refers to the situation caused by different lengths of environment or bound variables. Reload means that the parent cursor can be shared, and the same child cursor (execution plan, environment, bind var, etc.) already exists in library cache, but it is out of aged out for some reason, but now it needs to be reloaded (the Oracle database may have saved the same child cursor information somewhere).

Look for SQL in Hash bucket, if any, count it as a get, and find the execution plan of the SQL statement. If the execution plan no longer exists (age out) or exists but is not available (Invalidation), then you must reload this sql statement, which is called reload. If the execution plan exists and is available, oracle executes this sentence, which is called execution.

Fifth, bind variables (Bind Variables)

Advantages: sharing cursors, reducing hard parsing

Bind variable rating--

As mentioned earlier, changes in the execution environment, such as different type sizes defined by binding variables, will lead to the generation of different cursors, which is created in order to reduce the number of cursors. This function divides the length of variables into four levels, 0-32 bytes, 33-128bytes, 129128bytes, > 2000 bytes. Needless to say, a change in the same binding variable (length) can generate up to four cursors.

Cons: binding variables also has drawbacks. The disadvantage is that it weakens the functionality of the query optimizer relative to the literal amount.

For example:

Select count (*) from t where id > 10

Select count (*) from t where id > 99999

Based on the id value 1099999 and the table statistics, it is reasonable that the query optimizer may choose a full table scan or an index scan.

When binding variables are used, the optimizer ignores their specific values, resulting in the same execution plan. In order to solve this problem

Oracle9i introduces binding variable snooping (bind variable peeking).

The advantage of binding variable snooping is to peek into the values of bound variables and use them as literals. The advantage is that you can get the optimal query path, such as whether to choose a full table scan or an index scan.

Binding variable snooping also has the disadvantage that the generated execution plan depends on the value provided when the execution plan is generated for the first time. For example, if the first time is a full scan, it will always be a full table scan. The disadvantage of this approach for non-OLTP systems is obvious, because one set of bound variables may return a result set containing only a few hundred rows of data, while another set of bound variables may return millions of rows of data, so Oracle recommends leaving CURSOR_SHARING as the default value for this initialization parameter to force a new and more efficient execution plan (see below for a detailed explanation of cursor_sharing).

So how to avoid this shortcoming? Only upgrade to oracle11g.

Oracle11g refers to a new feature, Adaptive cursor sharing (ACS). This function is to generate different child cursors and different execution plans for the same sql statement according to the value of the binding variable. ACS uses two new measurement mechanisms: binding sensitivity and binding awareness. You can refer to the Oracle11g documentation for details.

When not to use bound variables?

Batch task processing, report generation, the use of OLAP data warehouse, because this kind of large query time is longer, a new hard parsing relative to this query time is nothing, so there is no impact without binding variables. If you use a binding variable, 10g or earlier, once the value provided by the binding variable for the first time is small, it may be an index scan, but the second time may be a typical large-time query in a data warehouse, requiring a full table scan, but still using the previous index scan, which leads to performance degradation. Most of the OLTP types are small and intensive operations, so the relatively optimal execution plan when using bound variables is stable.

When we do not use conditional judgment such as where, we should try our best to use bound variables (such as ordinary insert operations), and there is no reason not to use bound variables; when it comes to cardinality selective judgment, we should try to avoid using bound variables, because there is a greater negative risk in the physical optimization phase of binding variable snooping.

You can also consult the following two suggestions:

If sql processes less data and the parsing time is obviously much longer than the execution time, then we should try our best to use binding variables, which are suitable for OLTP (online transaction processing system).

If it is a database of data warehouse type, we should be cautious in using binding variables, because the execution time may be much longer than the parsing time, and the parsing time is almost negligible relative to the execution time, so we should try not to use binding variables at this time.

Parameter CURSOR_SHARING

Oracle is designed to satisfy some previously developed programs, which have a large number of similar statement, do not make good use of binding variables, but rewrite a parameter that is used in unrealistic cases. And oracle does not recommend modifying this parameter. Just keep it by default.

Syntax CURSOR_SHARING = {SIMILAR | EXACT | FORCE}, default is EXACT

EXACT--

Only absolutely the same SQL statements are allowed to share the same cursor. When a SQL statement is parsed, first go to the shared pool area to see if the exact same statement exists, and if it does not exist (in fact, the shared parent cursor cannot be found at this time), execute hard parse.

SIMILAR--

If the exact same statement cannot be found in shared pool, a new lookup will be made in shared pool, that is, a SQL statement that is similar to the statement currently being parsed. Similar statements are statements that are the same everywhere except value of some literal. Such as the following:

Select * from a where age=2

Select * from a where age=5

If such a statement is found in shared pool, the next step will be checked to see if the execution plan of the statement cached in shared pool is suitable for the currently parsed statement, and if so, the statement of shared pool will be used instead of hard parse.

FORCE--

Forces SQL statements that have different literals but are otherwise the same to share cursors. If cursor_sharing is set to force, when similar statement is found in shared pool, the execution plan will not be checked and this statement will be used directly in shared pool.

Setting cursor_sharing to force is actually dangerous. This may form an execution plan for suboptimal. For example, for a scope search statement, such as select * from a where a > 10 and a

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.