Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what are the Oracle shared cursors?". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First of all, to clarify a concept, the cursors discussed here are mainly shared cursors (shared cursor), which are not the same concept as the cursors defined in the SQL statement (session cursor).

A shared cursor is an executable object generated in Oracle after a user submits a SQL or PL/SQL block to the library cache's share pool. This object is called a cursor. SQL definition cursors are multi-row result sets produced by SELECT statements, which need to be declared, opened, extracted, and closed.

Definition and classification of cursors

Cursors include shared cursor and session cursor:

Shared cursor is a shared cursor, which is generated by SQL statements in the cursor parsing phase, such as sql located in library cache or anonymous pl/sql, etc. Its metadata is materialized in views V$sqlarea and v$sql. If the parent and child cursors in the library cache can be shared, it is a shared cursor. The parent cursor can be shared as the shared parent cursor, and the child cursor can be shared as the shared child cursor.

Session cursor means that the system allocates a cache area to the user to store the execution results of SQL statements. Through this intermediate buffer, the user can pull out the records in the cursor one by one and process them until all the cursor records are processed one by one. Session cursor refers to a memory area (or memory structure) in the PGA of the server process corresponding to this session (or UGA to be exact), that is, its main characteristic is to locate and process records one by one. The metadata of the session cursor is materialized through the v$open_cursor view, where every open or parsed SQL will be located.

The life cycle of the cursor

Shared cursor lifecycle

1) constraints containing vpd: if the SQL statement uses a table that uses row-level security controls, the constraints generated by the security policy are added to the where clause.

2) check syntax, semantics and access rights: check the correctness of SQL statement writing, the existence of objects, and the access rights of users.

3) parent cursor cache: hash the text of the cursor (SQL statement) to get a hash value and look for the same hash value in library cache. If it does not exist, survive the parent cursor and save it in library cache, and complete the following steps in sequence. If there is a parent cursor at this time, it is further determined whether there is a child cursor. If the same child cursor exists, the execution plan of its child cursor is called directly to execute the SQL statement, otherwise go to the next step for logical optimization.

4) Logic optimization: use different conversion techniques to generate semantically equivalent new SQL statements (rewriting of SQL statements). Once this operation is completed, the number of execution plans and search space will increase accordingly. Its main purpose is to find an implementation plan that cannot be considered without conversion.

5) physical optimization: generate the execution plan for the SQL statement in the logic optimization phase, read the statistics in the data dictionary and dynamic sampling statistics, calculate the overhead, and the execution plan with the lowest cost will be selected.

6) Child cursor cache: allocates memory, generates child cursors (that is, the best execution plan), and associates with the parent cursor. You can get specific cursor information in v$sqlarea and v$sql, and parent-child cursors are associated through sql_id.

SQL statements that only complete steps 1 and 2 are soft parsing, otherwise they are hard parsing. The execution mechanism of SQL statements in Oracle is probably similar to this, which can be seen in the original understanding of the execution process and sequence of Oracle SQL statements.

Shared cursors include parent cursors and child cursors.

The parent cursor is generated during hard parsing, and the parent cursor mainly contains two kinds of information: the SQL text and the optimization objective (optimizer goal). The parent cursor is locked for the first time and is not unlocked until all other session closes the cursor. When the parent cursor is locked, the library cache cannot be replaced by the LRU algorithm, and the library cache can be replaced only after it is unlocked, and all the child cursors corresponding to the parent cursor are also replaced with the library cache. Each line in the v$sqlarea represents a parent cursor,address that represents its memory address.

When hard parsing occurs in a child cursor, following the parent cursor produces a corresponding child cursor while the parent cursor is generated, and the value of V$SQL.CHILD_NUMBER is 0. If there is a parent cursor, new child cursors will also be generated because of different running environments, and the CHILD_NUMBER of the new child cursors accumulates in units of 1 on the basis of the existing child cursors. Subcursors include all relevant information about cursors, such as specific execution plans, binding changes, OBJECT, permissions, optimizer settings, and so on. The child cursor can be replaced by library cache by LRU algorithm at any time. When the child cursor is replaced by library cache, oracle can use the information of the parent cursor to reconstruct a child cursor. This process is called reload. 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.

Identify the three main fields of a cursor: address, hash_value, and child_number. Sql_id can uniquely determine a parent cursor, while sql_id and child_number can uniquely determine a child cursor.

Session cursor Lifecycle:

Session cursor needs to allocate memory from UGA, so it has its life cycle. Its life cycle mainly includes:

Open the cursor (allocate the memory area in the UGA based on the name of the cursor declaration)

Parse cursors (associate SQL statements with cursors and load their execution plan into Library Cache)

Define output variables (only if the cursor returns data)

Bind input variables (if bound variables are used in the SQL statement associated with the cursor)

Execute cursors (that is, execute SQL statements)

Get the cursor (that is, get the record result of the SQL statement, and act on the record as needed. The cursor will fetch the query records one by one until all the records have been fetched)

Close the cursor (release the relevant resources occupied by the cursor in the UGA, but the execution plan of the cursor in Library Cache is cleared according to the LRU principle, providing the possibility for its cursor sharing)

For session cursor, cursors can be understood as arbitrary DML,DQL statements (personal understanding, to be verified). That is, a SQL statement is actually a cursor, but session cursor is divided into display cursors and implicit cursors, as well as cursor pointers. From the life cycle of the cursor above, any cursor (SQL statement) must go through the process of memory allocation, parsing, execution, and closing. Therefore, for implicit cursors, all the processes of the life cycle are completed by the system automatically. For all DML and single-line queries (select. Into.) As far as is concerned, the system automatically uses implicit cursors. DQL for multi-row result sets usually uses display cursors.

A session cursor can only correspond to one shared cursor, while a shared cursor may correspond to multiple session cursor at the same time.

Examples of shared cursors

Suppose you have users SCOTT and KING, both of which have table EMP. First, take SCOTT as an example, execute the following statement:

Select * from emp where empno = 7788

SELECT * from emp where empno = 7788

SELECT * FROM emp WHERE empno = 7788

Select * from emp where empno = 7788

For the above four sentences, articles 1 and 4 are exactly the same, while articles 1, 2 and 3 are different in case. Query v$sqlarea:

Select sql_id, sql_text, executions

From v$sqlarea

Where sql_text like'% empno = 7788%'

And sql_text not like'% from vangusqlarea%'

The execution result is shown in the following figure, with three records, indicating that three parent cursors were generated, one of which was executed twice. This means that SQL statements must be identical (case, carriage return, and so on) before they can be shared, thus avoiding hard parsing.

The child cursors corresponding to these three parent cursors can be obtained in v$sql:

Select sql_id

Hash_value

Child_number

Plan_hash_value

Sql_text

Executions

From v$sql

Where sql_text like'% empno = 7788%'

And sql_text not like'% from vSecretsql%'

The execution result is shown in the following figure, which shows that when the parent cursor is generated, a child cursor with 0 as child_number is also generated, and its sql_id and hash_value are the same as the parent cursor.

Then use the KING user to execute the same statement. Then query the v$sqlarea parent cursor, and the result is still 3 records, but the number of execution has changed:

If you query the subcursors again, the result is 6 records, indicating that different subcursors are generated due to the different environment in which the statement is executed:

There are many reasons for generating subcursors, such as the above example of user (SCHEMA) change, of course, there are many other reasons that can also lead to the generation of subcursors, such as the change of optimizer mode, or peeping of bound variables, etc. If you want to determine which reason is caused, you need to check v$sql_shared_cursor.

Share other knowledge points of cursors

You can view statement sharing through two data dictionaries: V$SQLAREA and V$SQL. V$SQLAREA retains the parent cursor information of the SQL statement, which can be identified by SQL_ID, where the VERSION_ count column represents the number of child cursors. V$SQL retains the child cursor information of the SQL statement, which can be identified by SQL_ID and CHILD_NUMBER. V$SQL_SHARED_CURSOR can see why the statement generates subcursors.

For more information about the v$sql and v$sqlarea view fields and their detailed explanation, see "Oracle High Resource consumption SQL statement location".

The key information of the parent cursor is the sql text, and the key information of the child cursor is the execution plan and execution environment.

Hard parsing is usually caused by unshared parent cursors, such as frequently changing SQL statements, or dynamic SQL or not using bound variables.

The solution to hard parsing is usually to use binding variables.

When the SQL text of the parent cursor is exactly the same, multiple identical SQL statements can share a parent cursor.

If the SQL text and execution environment are completely consistent, the subcursors can be shared, otherwise, if the execution environment is inconsistent, new subcursors will be generated.

Cursors can be shared by all processes, that is, if all 100 processes execute the same SQL statement, all of them can use the cursors generated by the SQL statement at the same time, saving memory. Each cursor is represented by two or more objects in the library cache, at least two objects: an object called the parent cursor, which contains the name of the cursor and other information independent of the submitting user. What you can see from the v$sqlarea view is information about the parent cursor. One or more objects are called child cursors. If the SQL text is the same, but the user who submitted the SQL statement may be different, or the user submits the SQL statement involving the same noun, it is possible to generate different subcursors. Because the text of these SQL statements is exactly the same, but the context is different, such SQL statements are not an executable object and must be refined into multiple subcursors before they can be executed. Subcursors contain blocks of program code that execute plans or PL/SQL objects, and so on.

This is the end of the content of "what are the Oracle shared cursors"? thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.